MySQL存储引擎

目录

MySQL存储引擎

一、MyISAM

1.1 了解 MyISAM

1.2 面试必考——MyISAM 和 InnoDB 对比

1.3 面试必考——MVCC是什么？

二、InnoDB

2.1 了解 InnoDB

2.2 InnoDB 体系架构

2.3 InnoDB 内存池

2.4 InnoDB 后台线程

2.5 InnoDB 核心特性

三、其他存储引擎

---

MySQL存储引擎

一、MyISAM

不支持事务，性能更好，数据占用空间小。锁级别为表级锁，并发性差。

1.1 了解 MyISAM

创建一个存储引擎为 MyISAM 的数据表

CREATE TABLE `isam_table` (
  `id` int(11) DEFAULT NULL,
  `create_time` VARCHAR(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

每张表对应会生成三个文件

-rw-rw---- 1 mysql mysql    493 Oct 16 02:39 isam_table.frm
-rw-rw---- 1 mysql mysql      0 Oct 16 02:39 isam_table.MYD
-rw-rw---- 1 mysql mysql   1024 Oct 16 02:39 isam_table.MYI

frm 文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每一张表都会有一个 frm 文件。

MYD 文件：MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个 .MYD 文件。

MYI 文件：MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对应一个 .MYI 文件。

表修复

-- 检查表状态
check table `isam_table`;
-- 损坏表修复
repair table `isam_table`;

MySQL 还提供了一个工具

# 使用这个工具进行表修复必须停止 MySQL
myisamchk --help

表压缩

myisampack -b -f isam_table.MYI

压缩后的文件（.OLD 是压缩前的文件）：

-rw-rw---- 1 mysql mysql    493 Oct 16 02:39 isam_table.frm
-rw-rw---- 1 mysql mysql     50 Oct 16 02:39 isam_table.MYD
-rw-rw---- 1 mysql mysql   1024 Oct 16 02:56 isam_table.MYI
-rw-rw---- 1 mysql mysql      0 Oct 16 02:39 isam_table.OLD

TIPS

压缩后的表是只读的

表大小限制

MySQL 5.0 之前，默认 4 G。可修改 MAX_Rows 和 AVG_ROW_LENGTH 调整。

MySQL 5.0 时默认 256 TB。

适用场景

• 非事务型应用

• 只读类应用（可以压缩表）

• 空间类应用（存储空间数据，又称几何数据，如 gps）

1.2 面试必考——MyISAM 和 InnoDB 对比

• 事务和外键

  • InnoDB 支持事务，具有安全性和完整性，适合大量 insert、update 操作。

  • MyISAM 不支持事务，提供高速存储和检索，适合大量 select 操作。

• 锁机制

  • InnoDB 支持行级锁，基于索引实现。

  • MyISAM 只支持表级锁。

• 索引结构

  • InnoDB 适用聚集索引（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录。

  • MyISAM 使用非聚集索引（非聚簇索引），索引和记录分开。

• 并发处理能力

  • InnoDB 读写阻塞与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发。

  • MyISAM 使用表锁，会导致写操作并发率低，读写阻塞。

• 存储文件

  • InnoDB 对应两个文件： 表结构文件 .frm 、数据文件 .ibd；表最大为 64TB。

  • MyISAM 对应三个文件：表结构文件 .frm、表数据文件.MYD、索引文件.MYI；从 MySQL5.0 开始默认限制是 256TB。

存储引擎	适用场景
InnoDB	需要事务支持 并发要求高 写频繁 数据一致性要求高 硬件设备内存较大，可以利用 InnoDB 较好的缓存能力提高内存利用率，较少磁盘 IO
MyISAM	不需要事务支持 低并发 读频繁（写操作少或只读） 数据一致性要求不高

1.3 面试必考——MVCC是什么？

MVCC（Multi-Version Concurrency Control）多版本并发控制，为了实现读不加锁而引入的机制。

只在 READ COMMIT 和 REPEATABLE READ 这两个事务隔离级别下生效。

因为 READ UNCOMMITTED 总是读取最新数据行，而 SERIALIZABLE 对所有操作都加锁（串行）。

MVCC的实现是通过 保存某个时间点的快照（Read View） 来实现的。也就是说：

• 同一事务看到的数据都是一致的；

• 不同事务开启时间不同，同一时刻看到的数据可能不一样；

TIPS

MVCC 没有规范，各个存储引擎和数据库的实现都是不一样的。

InnoDB 的 MVCC 是通过在每行记录后面保存一些额外信息：

列	作用
DATA_TRX_ID [6bit]	最后一次更新这条记录的 transaction id，每处理一个事务，+1
DATA_ROLL_PTR [7bit]	指向当前记录项的 rollback segment 的 undo log 记录，找之前版本的数据就是通过这个指针，更新失败时回滚用。
DB_ROW_ID [6bit]	当由 InnoDB 自动产生聚集索引时，聚集索引包括这个 DB_ROW_ID 的值； 反之，聚集索引中不包括这个值，这个用于索引当中。
DELETE BIT	标识该记录是否被删除，这里的不是真正的删除数据，真正意义的删除是在 commit 的时候。

• SELECT

  • 只查找版本早于（包含）当前事务版本的数据行。

  • 只查找删除标识未定义的数据行。

• INSERT

  • 为新插入的每一行保存当前系统版本号作为行版本号。

• DELETE

  • 为删除的每一行保存当前系统版本号作为行删除标识。

• UPDATE

  • 插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

如何理解 "保存当前系统版本号到原来的行作为行删除标识" ?

更新操作本质上也是新增一行，只是更新了旧的数据行的删除标识，同时更新了版本号，当删除标识不为空时版本号也称为：删除版本号 or 过期版本号。

二、InnoDB

2.1 了解 InnoDB

表空间

InnoDB 使用表空间存储数据。

SHOW VARIABLES LIKE 'innodb_file_per_table';

• ON: 独立表空间 ，每张 InnoDB 表对应一个 tablename.ibd 文件。

• OFF: 系统（共享）表空间 ，所有表共同使用一个（或多个，自行配置）ibdata 文件。

使用独立表空间的优点：

• 可以通过 optimize table 命令收缩系统文件，大表或备份时节省空间。

• 可以同时向多个文件刷新数据，对于频繁写的应用来说 IO 瓶颈高。

表转移

把原来存在于系统表空间的表转移到独立表空间，步骤如下：

1. 使用 mysqldump导出所有数据库表数据；

2. 停止 MySQL 服务，修改参数，并删除 Innodb 相关文件；

3. 重启 MySQL 服务，重建 Innodb 系统表空间；

4. 重新导入数据。

2.2 InnoDB 体系架构

• 内存池

  • 维护多个内部数据结构。

  • 缓存磁盘上的数据，同时在对磁盘文件的数据修改之前在这里缓存。

  • 重做日志（redo log）缓冲。

  • ... ...

• 后台线程

  • 负责刷新内存池中的数据，保证缓存是最新的。

  • 将已修改的数据文件刷新到磁盘文件。

  • 保证数据库发生异常时 InnoDB 能恢复到正常运行状态。

  • ... ...

2.3 InnoDB 内存池

2.3.1 认识缓冲池

InnoDB 是基于磁盘存储的，并将其中的记录按照 "页" 的方式进行管理。

由于 CPU 与磁盘之间速度的鸿沟，数据库使用缓冲池技术来提高数据库的整体性能。

 

• 缓冲池大小

##################################
# 建议设置为总内存大小的 60%-80%
##################################
​
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

• 从 InnoDB 1.0.x 开始，允许有多个缓冲池实例

  • 优点：减少数据库内部资源竞争，增加并发处理能力；

  • 每个 Page 根据哈希值平均分配到不同缓冲池实例。

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| innodb_buffer_pool_instances | 1         |
+------------------------------+-----------+

• 查看缓冲池运行状态（show 命令）

mysql> SHOW ENGINE INNODB STATUS\G;
​
...
​
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 167772160
Dictionary memory allocated 1067424
Buffer pool size   8065
Free buffers       5625
Database pages     2440
Old database pages 920
Modified db pages  0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2309, created 131, written 0
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: 2440, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
​
...
​

• 查看缓冲池运行状态（information_schema.INNODB_BUFFER_POOL_STATS）

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
​
Database changed
MariaDB [information_schema]> SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES FROM INNODB_BUFFER_POOL_STATS\G;
*************************** 1. row ***************************
       POOL_ID: 0
     POOL_SIZE: 8065
  FREE_BUFFERS: 5625
DATABASE_PAGES: 2440

• 查看缓冲池配置参数

# page 大小，默认为 16K
mysql> SHOW VARIABLES LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
​
# 缓冲池参数
mysql> SHOW VARIABLES LIKE '%innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+

2.3.2 缓冲池管理

InnoDB 按照 Page（页）的方式管理数据，使用 LRU（Least Recently Used，最近最少使用）算法 进行 Page（页）的管理。

页的类型	说明	存在位置
free page	空闲页，未使用	Free 列表
clean page	干净页，未修改过，与磁盘中的数据一致	LRU 列表
dirty page	脏页，修改过，与磁盘中的数据不一致	LRU列表、Flush 列表

 

【普通 LRU 算法】

LRU 列表头部为使用最频繁的 Page，尾部为最少使用的 Page。当内存不足继续从磁盘读取 Page 时，释放尾部的 Page。

【存在的问题】

有一些操作需要访问表中的许多页，甚至全部页（如：索引 or 数据的扫描操作），这些页仅在这次查询操作中需要，并非热点数据；

如果直接把这些页放到 LRU 列表头部，那么就会有很多热点数据页被刷新出去，影响缓冲池效率，而且下次读取得重新访问磁盘。

【引入改良版 LRU 算法】

• 使用 中点插入策略（midpoint insertion strategy），最新访问的页放入 LRU 列表的 midpoint 位置。

• midpoint 可以通过参数 innodb_old_blocks_pct 设置，默认为 37，即 LRU 列表从尾部开始 37% 的位置（约 3/8）。

​

参数设置（非常重要） 

-- LRU 列表的 midpoint 位置（%），默认 37，热点数据越多，这个值就设置得越小
SHOW VARIABLES LIKE 'innodb_old_blocks_pct';
SET GLOBAL innodb_old_blocks_pct=20;
​
-- 页（Page）读取到 midpoint 位置后，无法进入 LRU 列表的热端
-- 必须等待 1000 毫秒（默认值）后，下一次对页的访问才会使其移动到 LRU 列表的热端
SHOW VARIABLES LIKE 'innodb_old_blocks_time';

2.3.3 日志缓存

日志信息会先放到缓冲区，然后按照一定频率刷新到日志文件。日志包含：

• InnoDB 引擎日志

• 数据库操作时产生的 redo 和 undo 日志

-- 日志缓存大小
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
​
-- 事务提交是日志刷新策略
-- 0：固定间隔， 1 秒执行一次。
-- 1：事务提交时，立刻写日志文件和刷新到磁盘，因为操作系统的“延迟写”特性，这里必须执行同步操作，才能保证事务提交成功的同时日志持久化到文件。
-- 2：事务提交时，立刻写日志文件，只是写入系统缓存，可能 1 秒后才会刷新到磁盘。
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

2.3.4 额外的内存池

• 每个缓冲池（innodb_buffer_pool）的帧缓冲（frame buffer）和缓冲控制对象（buffer control block）的内存需要从额外内存池中申请。

• 帧缓冲和缓冲控制对象记录了一些诸如 LRU、等待、锁等信息。

• 当申请的 InnoDB 缓冲池很大时，也要考虑适当地增加这个值。

2.4 InnoDB 后台线程

1) Master Thread

Master Thread 是 InnoDB 的核心主线程，主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性。

包含：脏页的刷新（page cleaner thread）、合并插入缓冲（INSERT BUFFER）、undo 页的回收（purge thread）、redo 日志刷新（log thread）等。

Master Thread 具有最高的线程优先级别。内部由多个循环组成：

• 主循环（loop）

• 后台循环（backgroup loop）

• 刷新循环（flush loop）

• 暂停循环（suspend loop）

循环通过 thread sleep 来实现，负载很大的时候可能会有延迟（delay）。

InnoDB 1.0.x 版本之前的 Master Thread

• 主循环（loop）—— 两大操作（每秒一次 & 每 10 秒一次）

  • 每秒一次

    • 日志缓冲刷新到磁盘，即使这个事务还没有提交（总是）；

    • 合并插入缓冲，IO 压力小才会执行（当前一秒发生的 IO 次数小于 5 次）；

    • 如果当前缓冲池中脏页的比例（buf_get_modified_ratio_pct）超过了配置文件中 innodb_max_dirty_pages_pct 这个参数（默认为 90，代表 90%）时，InnoDB 判定为需要磁盘同步，会将 100 个脏页写入磁盘中；

    • 如果当前没有用户活动，则切换到后台循环（backgroup loop）。

  • 每 10 秒一次

    • 如果过去 10 秒之内磁盘的 IO 操作小于 200 次，InnoDB 认为磁盘 IO 压力小，会将 100 个脏页刷新到磁盘；

    • 合并至多 4 个插入缓冲（总是）；

    • 将日志缓冲刷新到磁盘（总是）；

    • 删除无用的 Undo 页（总是）；

    • 如果脏页的比例（buf_get_modified_ratio_pct）大于 70%，则刷新 100 个脏页到磁盘；反之，刷新 10% 的脏页到磁盘。

• 后台循环（backgroup loop）—— 数据库空闲（没有用户活动）或关闭（shutdown）时，会切换到这个循环

  • 删除无用的 Undo 页（总是）；

  • 合并 20 个插入缓冲（总是）；

  • 跳回到主循环（总是）；

  • 不断刷新 100 个页直到符合条件（跳转到 flush loop 中完成）。

• 刷新循环（flush loop）

  • 脏页刷新到磁盘

• 暂停循环（suspend loop）

  • flush loop 中没有事情可以做了就会切换到这里，将 Master Thread 挂起，等待事件发生。

InnoDB 1.2.x 版本之前的 Master Thread

主要是对 1.0.x 版本之前的一些常量变成可配置参数，以适应实际的硬件性能

参数	含义
innodb_io_capacity	表示磁盘 IO 的吞吐量，默认值：200。 合并插入缓冲数量为 innodb_io_capacity 的 5%； 刷新脏页时，脏页数量为 innodb_io_capacity。
innodb_max_dirty_pages_pct	默认值从 92 调整为 75，脏页比例超过 75% 就同步到磁盘。
innodb_adaptive_flushing	自适应刷新，原本脏页刷新固定是 100 个；现在会根据产生 redo log 的速度来决定最合适的刷新脏页数量。
innodb_purge_batch_size	执行 full purge 是，原本最多回收 20 个 undo 页；现在按这个参数值回收，默认值：20。

InnoDB 1.2.x 版本的 Master Thread

原本每 10 秒一次的操作，调整为 InnoDB 空闲时才做；

刷新脏页的操作，从 Master Thread 线程分离到一个单独的 Page Cleaner Thread 中做；

减轻了 Master Thread 的工作，同时进一步提高了系统的并发性。

2) IO Thread

在 InnoDB 存储引擎中，大量使用了 AIO 来处理写请求，极大提高数据库的性能。

IO Thread 主要负责这些 IO 请求的回调处理。

SHOW VARIABLES LIKE 'innodb_%io_threads';

线程	线程个数	作用
read thread	4	将数据从磁盘加载到缓存 page
write thread	4	将缓存脏页刷新到磁盘
insert buffer thread	1	将写缓冲区内容刷新到磁盘
log thread	1	将日志缓冲区内容刷新到磁盘

3) Purge Thread

事务被提交后，其所使用的 undolog 可能不再需要，PurgeThread 用来回收已经使用并分配的 undo 页。

PurgeThread 是 InnoDB 1.1 版本引入的，减轻了 Master Thread 的工作，提高 CPU 的使用率以及存储引擎的性能。

• 查看 PurgeThread 线程数

mysql> SHOW VARIABLES LIKE 'innodb_purge_threads';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_purge_threads | 4     |
+----------------------+-------+

• 设置 PurgeThread 线程数【配置文件】

[mysqld]
innodb_purge_threads=4

4) Page Cleaner Thread

InnoDB 1.2.x 引入的，将脏页的刷新操作都交给这个线程来完成，减轻了原 Master Thread 的工作及对于用户查询线程的阻塞。

mysql> SHOW VARIABLES LIKE '%innodb_page_cleaners%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_page_cleaners | 1     |
+----------------------+-------+

2.5 InnoDB 核心特性

2.5.1 Change Buffer（提升存储性能）

TIPS

• 聚集索引（Primary Key）

• 非聚集索引（secondary index），又称非主键索引、辅助索引、二级索引

• 非聚集索引叶子节点的插入不是顺序的，需要离散地访问非聚集索引页，性能较差；

• 对于 非聚集、非唯一 的辅助索引，更新操作不会直接写入索引页；

• 而是先放入 Change Buffer 中，然后以一定的频率和情况进行 Change Buffer 和辅助索引页子节点的 merge（合并）操作；

• 通常会将多个操作合并成一个（因为在一个索引页中），大大提高了非聚集索引的写性能；

唯一索引为什么不适用 Change Buffer？

唯一索引写入时，需要做唯一性校验，就必须离散地访问索引页（Change Buffer 就没有意义了）。

​参数配置（非常重要）

## change buffer 默认占缓冲池的 25%，最多可调整至 50%
mysql> SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
​
## 开启 buffer 的选项：inserts、deletes、purges、changes、all、none
mysql> SHOW VARIABLES LIKE 'innodb_change_buffering';

以 UPDATE 为例，更新操作分为两个过程：

• 将记录标记为已删除（对应 Delete Buffer）

• 真正将记录删除（对应 Purge Buffer）

以 INSERT 为例，插入操作分为两个过程：

• 将记录标记为插入（对应 Insert Buffer）

• 真正将记录插入（对应 Buffer Pool）

2.5.2 doublewrite（两次写）

当 Page 发生部分写失败（partial page write）时，用来还原 Page 的副本。

数据库发生宕机时，可以通过 redo log 进行恢复。

如果宕机发生时，InnoDB 正在将某个页写到表中（比如 16KB 的页，只写了 4KB），那就要先通过【页的副本】还原这个页，再进行重做。

【页的副本】就存在 doublewrite 中，doublewrite 由两部分组成：

• 内存中的 doublewrite buffer（2MB）

• 物理磁盘上共享表空间中连续的 128 个页（64*2），一个页 16KB，128 个页正好 2MB

缓冲池的脏页进行刷新时：

• 会将脏页先复制到内存的 doublewrite buffer

• 通过 doublewrite buffer 分两次，每次 1 MB 顺序地写入共享表空间的物理磁盘上

• 然后马上调用 fsync 函数，同步到磁盘【假设一个页还没写完，突然宕机】

• 重启 MySQL ，从共享表空间中的 doublewirte 找到【页的副本】，将其复制到表空间文件

• redo log 恢复丢失数据

211017 14:54:30  mysqld restarted
211017 14:54:31  InnoDB: Database was not shutdown normylly!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Crash recovery may have failed for some .ibd files!
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...

2.5.3 自适应哈希索引

自适应哈希索引（Adaptive Hash Index, AHI）：

InnoDB 存储引擎会监控对表上各索引页的查询，根据访问频率和模式来自动地为某些热点页建立哈希索引。

-- 查看自适应哈希索引是否开启
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';

比如，连续 100 次以同样的模式（查询条件）访问同一个页，就会建立 AHI；不一定是 100 次，达到页中记录数的 1/16 即可。

三、其他存储引擎