常用存储引擎

存储引擎是 MySQL 区别于其他数据库的一个最重要特性。每个存储引擎都有各自的特点，能够根据具体的应用建立不同存储引擎表。可以通过 SHOW ENGINES 语句查看当前使用的 MySQL 所支持的存储引擎：
image.png
其中 Support 列表示该存储引擎是否可用，DEFAULT 值代表是当前服务器程序的默认存储引擎。Transactions 列代表该存储引擎是否支持事务处理。XA 列代表着该存储引擎是否支持分布式事务。Savepoints 代表着该列是否支持部分事务回滚。

以下是一些对存储引擎的简单介绍：

1. InnoDB

从 MySQL 5.5.8 版本开始，InnoDB 存储引擎是默认的存储引擎。InnoDB 存储引擎支持事务，其设计目标主要面向在线事务处理（OLTP）的应用。其特点是行锁设计，支持外键，并支持非锁定读，即默认读取操作不会产生锁。InnoDB 通过使用多版本并发控制（MVCC）来获得高并发性，并实现了 SQL 标准的四种隔离级别，默认为可重复读（REPEATABLE）级别。同时，使用 next-key locking 策略来避免幻读现象的产生。此外，InnoDB 存储引擎还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等高性能和高可用的功能。

对于表中数据的存储，InnoDB 存储引擎采用了聚集的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显示地在表定义时指定主键，InnoDB 存储引擎会为每一行生成一个 6 字节的 ROWID 并以此为主键。

2. MyISAM

在 MySQL 5.5.8 版本之前 MyISAM 存储引擎是默认的存储引擎。MyISAM 存储引擎不支持事务，只支持表级锁，主要面向一些 OLAP 数据库应用。MyISAM 存储引擎与 InnoDB 存储引擎最大的区别是对事务的支持。此外，MyISAM 存储引擎的另一个与众不同的地方是它的缓冲池只缓存索引文件，而不缓存数据文件，数据文件的缓存交由操作系统本身来完成，这与其他使用 LRU 算法缓存数据的大部分数据库大不相同。

3. Memory

Memory 存储引擎将表中的数据存放在内存中，如果数据库重启或发生崩溃，表中的数据都将消失。它非常适合用于存储临时数据的临时表。Memory 存储引擎默认使用哈希索引，而不是 B+ 树索引。并且 Memory 存储引擎只支持表级锁，并发性能差，并且不支持 TEXT 和 BLOB 列类型。

MySQL 数据库使用 Memory 存储引擎作为临时表来存放查询的中间结果集。如果中间结果集大于 Memory 存储引擎表的容量设置，又或者中间结果含有 TEXT 或 BLOB 列类型字段，则 MySQL 会把其转换到 MyISAM 存储引擎表而存放到磁盘中。由于 MyISAM 不缓存数据文件，因此这时产生的临时表会对查询有一定的性能损失。

InnoDB 组成

InnoDB 主要包括了内存池、后台线程以及存储文件。内存池又是由多个内存块组成的，主要包括缓存磁盘数据、redo log 缓冲等；后台线程则包括了 Master Thread、IO Thread 以及 Purge Thread 等；由 InnoDB 存储引擎实现的表的存储结构文件一般包括表结构文件（.frm）、共享表空间文件（ibdata1）、独占表空间文件（ibd）以及日志文件（redo 文件等）等。
f26b2fad64a9a527b5ac0e8c7f4be992.webp

1. 后台线程

1）Master Thread
Master Thread 是一个非常核心的后台线程，主要负责将缓冲池中的数据异步刷新到磁盘中，来保证数据的一致性。此外，还包括合并插入缓存（INSERT BUFFER）、undo 页的回收等。

2）IO Thread
在 InnoDB 存储引擎中大量使用了 AIO（Async IO）来处理写 IO 请求，这样可以极大提高数据库的性能。而 IO Thread 的工作主要是负责这些 IO 请求的回调处理。InnoDB 1.0 版本之前共有4 个 IO Thread，分别是 write thread、read thread、insert buffer 和 log IO thread。从 InnoDB 1.0.x 版本开始，read thread 和 write thread 分别增大到了四个，可以通过 innodbread_io_threads 和 innodb write_io_threads 参数进行设置，如：
image.png
此外，还可以通过 SHOW ENGINE INNODB STATUS 命令来观察 InnoDB 中的 IO Thread 状态：

  1. mysql> show engine innodb status;
  2. ......
  3. --------
  4. FILE I/O
  5. --------
  6. I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
  7. I/O thread 1 state: waiting for completed aio requests (log thread)
  8. I/O thread 2 state: waiting for completed aio requests (read thread)
  9. I/O thread 3 state: waiting for completed aio requests (read thread)
  10. I/O thread 4 state: waiting for completed aio requests (read thread)
  11. I/O thread 5 state: waiting for completed aio requests (read thread)
  12. I/O thread 6 state: waiting for completed aio requests (write thread)
  13. I/O thread 7 state: waiting for completed aio requests (write thread)
  14. I/O thread 8 state: waiting for completed aio requests (write thread)
  15. I/O thread 9 state: waiting for completed aio requests (write thread)

可以看到 IO Thread 0 为 insert buffer thread。IO Thread 1 为 log thread。之后就是根据参数 innodb_read_io_threads 及 innodb_write_io_threads 来设置的读写线程，并且读线程的 ID 总是小于写线程。

3）Purge Thread
事务被提交后，其所使用的 undo log 可能不再需要，因此需要 Purge Thread 来回收已经使用并分配的 undo 页。在 InnoDB 1.1 版本之前，purge 操作仅在 InnoDB 存储引擎的 Master Thread 中完成，从 InnoDB 1.1 版本开始，purge 操作可以独立到单独的线程中进行，以此来减轻 Master Thread 的工作，从而提高 CPU 的使用率以及提升存储引擎的性能。

从 InnoDB 1.2 版本开始，InnoDB 支持多个 Purge Thread，这样做的目的是为了进一步加快 undo 页的回收。同时由于 Purge Thread 需要离散地读取 undo 页，这样也能更进一步利用磁盘的随机读取性能。用户可以在 MySQL 配置文件中添加如下命令来配置 Purge Thread 的数量：

  1. [mysqld]
  2. innodb_purge_threads=4

通过如下语句可以查看 Purge Thread 配置：
image.png

4）Pager Cleaner Thread
Pager Cleaner Thread 是在 InnoDB 1.2.x 版本中引入的，其作用是协助 Master Thread 刷新脏页到磁盘，它可以减轻 Master Thread 的工作压力及对于用户查询线程的阻塞，进一步提高 InnoDB 存储引擎的性能。

2. 内存

InnoDB 存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统。在数据库系统中，由于 CPU 速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。

1）Buffer Pool
缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为 Checkpoint 的机制刷新回磁盘。同样，这也是为了提高数据库的整体性能。

具体来看，缓冲池中缓存的数据页类型有：索引页、数据页、undo 页、插入缓冲（insert buffer）、自适应哈希索引（adaptive hash index）、InnoDB 存储的锁信息（lock info）等。不能简单地认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大的一部分而已。

2）redo log buffer
InnoDB 存储引擎的内存区域除了有缓冲池外，还有 redo log 缓冲（redo log bufter）。InnoDB 存储引擎首先将重做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置得很大，因为一般情况下每隔一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。该值可由系统参数 innodb_log_buffer_size 控制，默认为 16 MB。

3. 存储文件

在 MySQL 中建立一张表都会生成一个 .frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。在 InnoDB 中，存储数据都是按表空间进行存放的，默认为共享表空间，存储的文件即为共享表空间文件（ibdata1）。若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，因此也会产生一个独占表空间文件（ibd）。而日志文件则主要是重做日志文件，主要记录事务产生的重做日志，保证事务的一致性。