【MySQL】二、MySQL存储引擎介绍

时间：2024-11-05 19:06:34

一、SQL语句执行过程

【MySQL】二、MySQL存储引擎介绍_存储引擎

说明：

  • ① SQL通过连接器提交到查询器

查询器会首先会根据查询的SQL作为key去查询缓存，如果缓存中存在对应的SQL对应的数据缓存，则直接将数据返回，否则进行第2步

  • ② 查询器将SQL提交到解析器

1、词法分析：根据MySQL的关键字、标识符、运算符等将对应的SQL打散成一个个的token，并进行分类

2、语法分析：会对词法分析得到的数据结果根据SQL的语法规则进行校验，并最终组成一棵语法解析树

  • ③ 解析器将处理后得到的解析树提交到预处理器

预处理器负责对解析树进行语义分析，并校验SQL语句中的表名、列名以及数据类型是否存在或正确

  • ④ 通过预处理器校验的SQL提交到SQL优化器

生成执行计划，选择合适的索引、连接方式、排序方式等

可以通过explain 命令查看SQL的执行计划：

【MySQL】二、MySQL存储引擎介绍_表空间_02

对应官网MySQL架构说明：

【MySQL】二、MySQL存储引擎介绍_数据_03


二、存储引擎类型

查看数据库存储引擎类型：

SHOW ENGINES;

【MySQL】二、MySQL存储引擎介绍_存储引擎_04

可以看到在MySQL8.0版本下支持这些存储引擎

2.1、InnoDB

官网链接

在MySQL8.0之后，InnoDB是MySQL的默认存储引擎。如果我们在创建表的时候不显示地指定ENGINE=xxx，则表示使用InnoDB存储引擎

2.1.1、InnoDB的架构模型

【MySQL】二、MySQL存储引擎介绍_数据_05

InnoDB的内存模型：

  • Buffer Pool

参考链接

buffer pool是表数据和索引缓存的主要内存区域，也是操作最频繁的区域，基本上绝大部分的操作都会先操作这块内存区然后再去同步磁盘做持久化。所以buffer pool主要是为了提升读写性能而设计的。它采用的是LRU算法+独特的链表结构：

【MySQL】二、MySQL存储引擎介绍_存储引擎_06

新插入的数据并不是直接放在队列的头或尾，而是放到5/8的中间节点上，规避了冷 数据放在队列头浪费空间和热 数据放在队尾容易直接被丢弃的弊端

  • Change Buffer

参考链接

因为二级索引的插入和修改一般都是无序的，所以change buffer在增、删、该的时候将那些在change buffer中不存在的二级索引页有变更的数据缓存缓存起来，并稍后在其他的读操作将page页加载到buffer pool时进行合并，一定程度上减少了I/O提升了性能。但这也意味着在读多写少的场景下并不是特别适合

【MySQL】二、MySQL存储引擎介绍_表空间_07

change buffer空间大小在5.6之后的版本默认是内存空间的25%，可修改

  • Adaptive Hash Index

参考链接

InnoDB有一个监控索引搜索的机制，如果发现查询可以从构建一个哈希索引收益，那么就会自动构建一个哈希索引。它使用索引键的前缀来构建

  • Log Buffer

参考链接

log buffer是对要写入磁盘数据的缓存，比如有大数据量的数据要写入磁盘，InnoDB可以先开辟出一块内存空间将这部分数据缓存起来，在后续异步写入磁盘，这样就提升了吞吐性能。所以适用场景就是就是有大批量数据写入或更新的场景，适当调大这个内存区域的大小可以提升I/O性能


2.1.2、InnoDB的优势

  • 支持ACID事务模型
  • 支持行级锁，支持多用户并发读的数据一致性
  • 主键ID有序，尽可能减少了主键检索时的io次数
  • 支持外键，在数据添加、修改、删除的时候可以进行数据完整性校验


2.1.3、InnoDB最佳实践

  • 每个表用最常用到查询的字段作为主键，或者用自增ID作为主键
  • 保证外键走索引，并且对应的字段在各表中的数据类型是一样的
  • 尽可能关闭自动提交，可以将一组操作用start transaction/commit来对事务操作分组
  • 不要锁表，InnoDB支持行级锁：select ... for update
  • 不要把所有数据都怼到系统表空间，可以开启innodb_file_per_table 来将数据放到独立表空间，或者放到通用表空间
  • 如果有需要的话InnoDB还支持数据压缩


2.1.4、查看InnoDB的状态：

SHOW ENGINE INNODB STATUS;

默认状态：


=====================================
2024-11-05 09:37:09 135228326627072 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 31 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 11 srv_active, 0 srv_shutdown, 321455 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 31
OS WAIT ARRAY INFO: signal count 32
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1884
Purge done for trx's n:o < 1884 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 416703475921704, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 416703475920896, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 416703475920088, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 416703475919280, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 416703475918472, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests ((null))
I/O thread 1 state: waiting for completed aio requests (insert buffer thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
1120 OS file reads, 783 OS file writes, 458 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number          31893696
Log buffer assigned up to    31893696
Log buffer completed up to   31893696
Log written up to            31893696
Log flushed up to            31893696
Added dirty pages up to      31893696
Pages flushed up to          31893696
Last checkpoint at           31893696
Log minimum file id is       9
Log maximum file id is       9
197 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 632878
Buffer pool size   8192
Free buffers       6913
Database pages     1267
Old database pages 447
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 184, not young 33
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1096, created 173, written 445
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1267, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=135227998545664 , state=sleeping
Number of rows inserted 48, updated 0, deleted 0, read 48
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 96, updated 371, deleted 9, read 25671
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

2.1.5、InnoDB的磁盘结构

2.1.5.1、表
  • 行格式

REDUNDANT, COMPACT, DYNAMIC, and COMPRESSED

其中，DYNAMIC 是默认值，参考链接

【MySQL】二、MySQL存储引擎介绍_存储引擎_08

  • 主键

强烈建议每个表都设置一个主键，如果不显示设置的话InnoDB会将隐藏字段rowid作为隐藏主键

建议用自增ID作为主键

  • 创建表（指定数据目录）
CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/external/directory';

通过上面语句，会将数据文件保存在对应的目录


2.1.5.2、索引
  • 索引类型

InnoDB索引分为clustered索引和secondary索引，或者叫聚簇索引和非聚簇索引，通常情况下或者叫主键索引和二级索引。

在InnoDB中，主键索引一般就是作为clustered索引，如果没有定义主键索引，那么InnoDB会将第一个的非空的唯一索引作为clustered索引，如果连一个非空的唯一索引都没有的话，系统会自动在一个由rowid合成的列上建立一个名为GEN_CLUST_INDEX 的索引作为clustered索引。rowid是6个字节长度的随着数据插入自增的并且物理上有序的列。


  • 索引结构

InnoDB的主键索引采用B+树的索引结构，它是一棵多路查找树，这种多路 的方式方式和普通二叉树相比可以有效减少层级，从而提升I/O查找效率，InnoDB的B+树结构如下图所示

【MySQL】二、MySQL存储引擎介绍_存储引擎_09

① 所有的数据都在叶子节点上

② 叶子节点是根据id有序的排列的链表

③ 树的层级深度由数据量、page页的大小以及key的长度综合决定，总的来说key的长度越小，每个page页能放的数就越多（换句话说就是所谓的多路的“路”就越多），层级就越有可能少，I/O的次数就有可能越少，查询性能也就越优秀


  • 全文索引

InnoDB支持对某个字段建立全文索引，比如创建表的时候指定列的全文索引：

CREATE TABLE opening_lines (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200),
       FULLTEXT idx (opening_line)
       ) ENGINE=InnoDB;

查询的时候使用match进行查询：

SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');

但是目前MySQL支持的全文索引并不是特别好，不像ES可以指定分词器，InnoDB的全文检索对于中文会将每一个字作为一个分词，这样查询起来效率就特别低，很多时候查询结果并不是我们想要的，所以一般也很少用它。


2.1.5.3、表空间

参考链接

  • 系统表空间

change buffer的存储空间


  • 独立表空间

为每个表开辟一个独立的空间


  • 通用表空间

多个表共享一个表空间


  • Undo表空间

undo logs的存储空间

由于undo log记录的是数据的变化，而MySQL操作数据的基础单元是page页，所以在8.0.23之前的版本，undo log的大小也就和page的大小相关，在page默认16k的情况下undo表空间的大小为10M，4k、8k、32k、64k的page页大小下分别是7M、8M、20M、40M，在8.0.23之后进行了优化，默认情况下是16M，会根据后续的操作情况而定


  • 临时表空间

临时表空间又有会话临时表空间和全局临时表空间：

① 会话临时表空间

一般就是存储用户自己创建的临时表和优化器在InnoDB引擎时创建的临时表

② 全局临时表空间

全局临时表空间存储用户创建的临时表的更改的回滚segement


2.1.5.4、doublewrite buffer

doublewrite buffer是为了保证数据的持久性而设计的一个缓存空间，主要就是存储数据在写入磁盘之前的更改，保证操作系统、存储系统等中间进程在正式将数据修改刷新到磁盘之前崩溃的数据恢复。

既然都是写磁盘，为什么它能保证数据写入？

因为doublewrite buffer是顺序写，它是一个比较大块的顺序块，通过操作系统的一次fsync 即可刷新到磁盘。

在8.0.20版本之前doublewrite buffer是在系统表空间的，而在这之后MySQL将其放到了单独的doublewrite的文件里，如最开始的InnoDB的架构图所示。


2.1.5.5、redolog

redolog 是一种基于磁盘的数据结构，用于在崩溃恢复期间纠正由不完整事务写入的数据，它也有自己的独立物理文件空间，写入重做日志文件的数据根据受影响的记录进行编码。


2.1.5.6、undolog

undolog是与单个读写事务关联的undolog记录的集合。undolog记录包含有关如何撤消事务对聚集索引记录的最新更改的信息。如果另一个事务需要将原始数据视为一致读取操作的一部分，则从undolog记录中检索未修改的数据。undolog存在于undolog segment中，undolog segment包含在rollback segment中。rollback segment位于undo表空间和全局临时表空间中。


2.2、MyISAM

官网链接

MyISAM存储引擎是在老牌存储引擎isam的基础之上扩展而来的。MyISAM存储引擎的表数据存储在两个文件中，一个以“.MYD”结尾（数据文件），一个以“.MYI”结尾（索引文件）。

MyISAM表的特性：

  • 所有数据优先低字节存储，所有数值key的value高字节存储
  • 文件系统和操作系统支持的情况下，支持最大63位的大文件存储
  • MyISAM表最大支持(232)2 (1.844E+19)行数据
  • MyISAM表最多支持64个索引，单个索引最多支持16列
  • MyISAM表的索引最长支持1000个字节长度
  • 支持每个表一个AUTO_INCREMENT列的内部处理

MyISAM会自动更新此列以进行INSERT和UPDATE操作。这使得AUTO_INCREMENT列更快（至少10%）。序列顶部的值在删除后不会重复使用。（当AUTO_INCREMENT列被定义为多列索引的最后一列时，确实会重复使用从序列顶部删除的值。）可以使用ALTER TABLE或myisamchk重置AUTO_INCREENT值。

  • BLOB和TEXT列也支持索引
  • 索引文件和数据文件可以指定放在不同的目录
  • null可以用作索引列，每个key占0或者1个字节长度
  • 每列可以设置不同的字符集


MyISAM的行格式：

  • static：默认

固定长度，只用于没有BLOB和TEXT列的表中

  • Dynamic

动态长度，如果表里边有任意一个类型：VARCHAR、VARBINARY、BLOB或者TEXT，则会被自动设置为Dynamic格式。亦或者显示指定ROW_FORMAT=DYNAMIC

特点：

① 除了4个长度一下的字符串类型字段长度都是可变的

② null列需要一个字节的额外长度来记录其值是否为空

③ 每一行需要多少空间就占用多少空间，这比固定长度所需的磁盘空间更小

④ 崩溃的时候会比固定长度格式更难恢复一点

  • Compressed

myisampack工具生成的只读格式，可以用myisamchk工具进行解压缩

特点：

① 占用空间特别小

② 每行都是独立压缩

② 可用于对固定长度或者动态长度的行进行压缩


MyISAM存储引擎的表在以下场景下可能会有问题：

  • mysqld进程被杀
  • 电脑非正常关机
  • 电脑硬件出问题
  • 用工具破坏
  • MySQL内部错误


2.3、MEMORY

官网链接

MEMORY存储引擎（以前称为HEAP）创建具有存储在内存中的内容的专用表。由于数据易受崩溃、硬件问题或停电的影响，因此只能将这些表用作从其他表中提取的数据的临时工作区或只读缓存。


2.4、CSV

官网链接

CSV存储引擎的表的数据文件都是用指定分隔符分隔的text文件，非常方便直接阅读。但是也会有如下限制：

  • 所有表不支持索引
  • 所有表不支持分区
  • 所有的列不能为空


2.5、ARCHIVE

官网链接

ARCHIVE存储引擎生成专用表，以非常小的占用空间存储大量未索引的数据


2.6、BLACKHOLE

官网链接

顾名思义的一个存储引擎，这个引擎的表就像一个黑洞，把数据放进去就没了，不会保存，每次查到的都是空，感觉没啥用，想不出什么场景会用到它


2.7、MERGE

官网链接

MERGE存储引擎，也称为MRG_MyISAM引擎，是可以作为一个整体使用的相同MyISAM表的集合。

基本不用，pass


2.8、FEDERATED

官网链接

FEDERATED存储引擎允许您从远程MySQL数据库访问数据，而无需使用复制或集群技术。查询本地FEDERATED表会自动从远程（联邦）表中提取数据。本地表上没有存储任何数据。

默认不启用，如果要启用的话在确保你使用的安装包支持这个存储引擎的条件下用，在程序启动时用“--federated”参数来启用

2.9、EXAMPLE

官网链接

示例存储引擎是一个什么都不做的存根引擎。它的目的是作为MySQL源代码中的一个例子，说明如何开始编写新的存储引擎



相关文章