InnoDB各版本功能对比

版本

功能

老版本 InnoDB（MySQL5.1之前）

支持 ACID 、行锁设计、 MVCC

InnoDB 1.0.x （MySQL5.1）

继承了上述版本所有，增加了 compress 和 dynamic 页格式

InnoDB 1.1.x （MySQL5.5之前）

继承了上述版本所有功能，增加了 Linux AIO 、多回滚段

InnoDB 1.2.x （MySQL5.6）

继承了上述版本所有功能，增加了全文索引支持、在线索引添加

InnoDB 5.7 （MySQL5.7）

继承了上述版本所有功能，支持buffer pool动态调整大小

图简单显示了 InnoDB 的存储引擎的体系架构，从图可见，InnoDB 存储引擎有多个内存块，可以认为这些内存块组成了一个大的内存池，负责如下工作：

• 维护所有进程／线程需要访问的多个内部数据结构。
• 缓存磁盘上的数据，方便快速地读取，同时在对磁盘文件的数据修改之前在这里缓存。
• 重做日志（redo log）缓冲。
• ......

后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下 InnoDB 能恢复到正常运行状态。

1、后台线程

InnoDB 存储引擎是多线程的模型，因此其后台有多个不同的后台线程，负责处理不同的任务。

1.1、Master Thread

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

1.2、IO Thread

在 InnoDB 存储引擎中大量使用了 AIO ( Async IO ）来处理写 IO 请求，这样可以极大提高数据库的性能。而IO Thread 的工作主要是负责这些IO请求的回调（call back) 处理。 InnoDB 1.0 版本之前共有 4 个IO Thread ，分别是 write 、 read 、 insert buffer 和 log IO thread 。在 Linux 平台下，IO Thread 的数量不能进行调整，但是在 Windows 平台下可以通过参数 innodb_file_io_ threads 来增大 IO Thread 。从 InnoDB 1.0.x 版本开始，read thread 和 write thread 分别增大到了 4 个，并且不再使用innodb_file_io_ threads 参数，而是分别使用 innodb_read_io_threads 和innodb_write_io_threads参数进行设置，如：

mysql> show variables like 'innodb_%io_threads';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_read_io_threads  | 4     |
| innodb_write_io_threads | 4     |
+-------------------------+-------+
2 rows in set (0.01 sec)

可以通过命令SHOW ENGINE INNODB STATUS来观察InnoDB 中的IO Threads：

mysql> SHOW ENGINE INNODB STATUS\G
......
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
......

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

1.3、Purge Thread

事务被提交后，其所使用的 undolog 可能不再需要，因此需要 Purge Thread 来回收已经使用并分配的 undo 页。在 InnoDB 1.1 版本之前， purge操作仅在 InnoDB 存储引擎的 Master Thread 中完成。而从 InnoDB 1.1 版本开始， purge 操作可以独立到单独的线程中进行，以此来减轻 Master Thread 的工作，从而提高 CPU 的使用率以及提升存储引擎的性能。用户可以在 MysQL 数据库的配置文件中添加如下命令来启用独立的Purge Thread :

[mysqld]innodb_purge_threads=1 

在 InnoDB 1.1 版本中，即使将innodb_purge_threads设为大于 1, InnoDB 存储引擎启动时也会将其设为 1 ，并在错误文件中出现如下类似的提示：

从 InnooB 1.2 版本开始， InnoDB 支持多个 Purge Thread ，这样做的目步加快undo页的回收。同时由于 Purgo Thread 需要离散地读取 undo 页，这样也能更进一步利用磁盘的随机读取性能。如用户可以设置 4 个 Purge Thrcad :

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.01 sec)
 
mysql> show variables like 'innodb_purge_threads';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_purge_threads | 4     |
+----------------------+-------+
1 row in set (0.00 sec)

1.4、Page Cleaner Thread

Page Cleaner Thread 是在 InnoDB 1.2.x 版本中引人的。其作用是将之前版本中脏页的刷新操作都放人到单独的线程中来完成。而其目的是为了减轻原 Master Thread 的工作及对于用户查询线程的阻塞，进一步提高 InnoDB 存储引擎的性能。

2、内存

2.1、缓冲池

InnoDB 存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。因此可将其视为基于磁盘的数据库系统（Disk-base Database）。在数据库系统中，由于 CPU 速度与磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池技术来提高数据库的整体性能。缓冲池简单来说就是一块内存区域，通过内存的速度来弥补磁盘速度较慢对数据库性能的影响。在数据库中进行读取页的操作，首先将从磁盘读到的页存放在缓冲池中，这个过程称为将页 “FIX” 在缓冲池中。下一次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。对于数据库中页的修改操作，则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。这里需要注意的是，页从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为 Checkpoint 的机制刷新回磁盘。同样，这也是提高数据库的整体性。

综上所述，缓冲池的大小直接影响着数据库的整体性能。由于 32 位操作系统的限制，在该系统下最多将该值设置为 3G 。此外用户可以打开操作系统的PAE选项来获得 32 位操作系统下最大 64GB 内存的支持。随着内存技术的不断成熟，其成本也在不断下降。单条8GB 的内存变得非常普遍，而 PC 服务器已经能支持 512GB 的内存。因此为了让数据库使用更多的内存，强烈建议数据库服务器都采用 64 位的操作系统。对于 InnoDB 存储引擎而言，其缓冲池的配置通过参数 innodb_buffer_pool_size 来设置。下面显示一台 MySQL 数据库服务器，其将 InnoDB 存储引擎的缓冲池设置为128MB：

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

具体来看，缓冲池中缓存的数据页类型有：索引页、数据页、 undo 页、插入缓冲 (insert buffer）、自适应哈希索引（adaptive hash index）、 InnoDB 存储的锁信息（lock info）、数据字典信息（data dictionary）等。不能简单地认为，缓冲池只是缓存索引页和数据页，它们只是占缓冲池很大的一部分而已。下图很好地显示了 InnoDB 存储引擎中内存的结构情况。

从 InnoDB 1.0.x 版本开始，允许有多个缓冲池实例。每个页根据哈希值平均分配到不同缓冲池实例中。这样做的好处是减少数据库内部的资源竟争，增加数据库的并发处理能力。可以通过参数 innodb_buffer_pool_instances来进行配置，该值默认为1。

mysql> show variables like 'innodb_buffer_pool_instances';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| innodb_buffer_pool_instances | 1     |
+------------------------------+-------+
1 row in set (0.00 sec)

在配置文件中将innodb_buffer_pool_instances设置为大于 1 的值就可以得到多个缓冲池实例。再通过命令 SHOW ENGINE INNODB STATUS 可以观察到如下的内容：

从 MySQL 5.6 版本开始，还可以通过 information_schema 架构下的表innodb_buffer_pool_stats来观察缓冲的状态，如运行下列命令可以看到各个缓冲池的使用状态：

mysql> select pool_id,pool_size, free_buffers,database_pages from innodb_buffer_pool_stats\G

2.2、LRU List 、Free List 和Flush List

我们知道了缓冲池是一个很大的内存区域，其中存放各种类型的页。那么 InnoDB 存储引擎是怎么对这么大的内存区域进行管理的呢？通常来说，数据库中的缓冲池是通过 LRU (Latest Recent Used ，最近最少使用）算法来进行管理的。即最频繁使用的页在 LRU 列表的前端，而最少使用的页在 LRU 列表的尾端。当缓冲池不能存放新读取到的页时，将首先释放 LRU 列表中尾端的页。在 InnoDB 存储引擎中，缓冲池中页的大小默认为 16KB ，同样使用 LRU 算法对缓冲池进行管理。稍有不同的是 InnoDB ”存储引擎对传统的 LRU 算法做了一些优化。在InnoDB 的存储引擎中，LRU 列表中还加人了 midpoint 位置。新读取到的页，虽然是最新访问的页，但并不是直接放人到 LRU 列表的首部，而是放人到 LRU 列表的 midpoint 位置。这个算法在 InnoDB 存储引擎下称为 midpoint insertion strategy 。在默认配置下，该位置在 LRU列表长度的 5/8 处。 midpnint 位置可由参数 innodb_old_blocks_pct控制，如：

mysql> show variables like 'innodb_old_blocks_pct';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.01 sec)

从上面的例子可以看到，参数innodb_old_blocks_pct默认值为 37 ，表示新读取的页插人到 LRU 列表尾端的 37％的位置（差不多 3/8 的位置）。在 InnoDB 存储引擎中，把 midpoint 之后的列表称为 old 列表，之前的列表称为 new 列表。可以简单地理解为 new 列表中的页都是最为活跃的热点数据。那为什么不采用朴素的 LRU 算法，直接将读取的页放人到 LRU 列表的首部呢？这是因为若直接将读取到的页放人到 LRU 的首部，那么某些 SQL 操作可能会使缓冲池中的页被刷新出，从而影响缓冲池的效率。常见的这类操作为索引或数据的扫描操作。这类操作需要访问表中的许多页，甚至是全部的页，而这些页通常来说又仅在这次查询操作中需要，并不是活跃的热点数据。如果页被放人 LRU 列表的首部，那么非常可能将所需要的热点数据页从 LRU 列表中移除，而在下一次需要读取该页时， InnoDB 存储引擎需要再次访问磁盘。

为了解决这个问题， InnoDB 存储引擎引人了另一个参数来进一步管理 LRU 列表，这个参数是 innodb_old_blocks_time ，用于表示页读取到 mid 位置后需要等待多久才会被加入到 LRU 列表的热端。因此当需要执行上述所说的 SQL 操作时，可以通过下面的方法尽可能使 LRU 列表中热点数据不被刷出：

mysql> set global innodb_old_blocks_time=1000;
Query OK, 0 rows affected (0.01 sec)

如果用户预估自己活跃的热点数据不止 63% ，那么在执行 SQL 语句前，还可以通过下面的语句来减少热点页可能被刷出的概率。

mysql > SET GLOBAL innodb_old_blocks_pct = 20;

LRU 列表用来管理已经读取的页，但当数据库刚启动时， LRU 列表是空的，即没有任何的页。这时页都存放在 Free 列表中。当需要从缓冲池中分页时，首先从 Free 列表中查找是否有可用的空闲页，若有则将该页从 Free 列表中删除，放人到 LRU 列表中。否则，根据 LRU 算法，淘汰 LRU 列表末尾的页，将该内存空间分配给新的页。当页从 LRU 列表的 old 部分加人到 new 部分时，称此时发生的操作为 page made young ，而因为 innodb_old_blocks_time 的设置而导致页没有从 old 部分移动到 new 部分的操作称为 page not made young 。可以通过命令 SHOW ENGINE INNODB STATUS 来观察 LRU 列表及 Free 列表的使用情况和运行状态。

通过命令show engine innodb status可以看到：当前 Buffer poolsize共有327679 个页，即 327679 * 16K ，总共5GB 的缓冲池。 Free buffers 表示当前 Free 列表中页的数量， Database pages 表示 LRU 列表中页的数量。可能的情况是 Free buffers 与 Database pages 的数量之和不等于 Buffer pool size。因为缓冲池中的页还可能会被分配给自适应哈希索引、 Lock 信息、 Insert Buffer 等页，而这部分页不需要 LRU 算法进行维护，因此不存在于 LRU 列表中。

pages made young 显示了 LRU 列表中页移动到前端的次数，因为该服务器在运行阶段没有改变 innodb_old_blocks_time的值，因此 not young 为 0。 youngs/s 、non-youngs/s 表示每秒这两类操作的次数。这里还有一个重要的观察变量 ― Buffer pool hit rate ，表示缓冲池的命中率，这个例子中为 100 % ，说明缓冲池运行状态非常良好。通常该值不应该小于 95 ％。若发生 Buffer pool hit rate 的值小于 95 ％这种情况，用户需要观察是否是由于全表扫描引起的 LRU 列表被污染的问题。

注意执行命令 SHOW ENGINE INNODB STATUS 显示的不是当前的状态，而是过去某个时间范围内 InnoDB 存储引擎的状态。从上面的例子可以发现， Per second averages calculated from the last 24 seconds 代表的信息为过去 24 秒内的数据库状态。从 InnoDB 1.2 版本开始，还可以通过表 INNODB BUFFER POOL STATS 来观察缓冲池的运行状态，如：

mysql> select pool_id,hit_rate,pages_made_young,pages_not_made_young from information_schema.innodb_buffer_pool_stats;
+---------+----------+------------------+----------------------+
| pool_id | hit_rate | pages_made_young | pages_not_made_young |
+---------+----------+------------------+----------------------+
|       0 |        0 |                0 |                    0 |
+---------+----------+------------------+----------------------+
1 row in set (0.01 sec)

此外具体信息还可以通过表 INNODB_BUFFER_PAGE_LRU 来观察每个 LRU 列表中每个页的例如通过下面的语句可以看到缓冲池 LRU 列表中 SPACE 为 1 的表的页类型：

mysql> SELECT TABLE_NAME,SPACE,PAGE_NUMBER,PAGE_TYPE FROM INNODB_BUFFER_PAGE_LRU WHERE SPACE = 1;

Inno OB 存储引擎从 1.0.x 版本开始支持压缩页的功能，即将原本 16 KB 的页压缩为1KB 、2KB 、4KB 和8KB 。而由于页的大小发生了变化， LRU 列表也有了些许的改变。对于非 16KB 的页，是通过 unzip _ LRU 列表进行管理的。通过命令 show engine innodb status可以观察到如下内容：

可以看到 LRU 要注意的是， LRU 列表中一共有 1539 个页，而 unzip_LRU 列表中有 156 个页。这里需中的页包含了 unzip_LRU 列表中的页。对于压缩页的表，每个表的压缩比率可能各不相同。可能存在有的表页大小为8KB , 有的表页大小为2KB 的情况。 unzip_LRU 是怎样从缓冲池中分配内存的呢？首先，在 unzip_LRU 列表中对不同压缩页大小的页进行分别管理。其次，通过伙伴算法进行内存的分配。例如对需要从缓冲池中申请页为 4KB 的大小，其过程如下：

1. 检查4KB 的unzip_LRU 列表，检查是否有可川的空闲页；
2. 若有则直接使用
3. 否则，检查8KB 的unzip_LRU 列表
4. 若能够得到空闲页将页分成 2 个 4KB 页，存放到 4KB 的 unzip_LRU 列表
5. 若不能得到空闲页，从 LRU 列表中申请一个16KB 的页,将页分为1个8KB的页、2个4KB 的页，分别存放到对应的unzip_LRU 列表中

同样可以通过 information_ schema 架构下的表 INNODB_BUFFER_PAGE_LRU 来观察unzip_LRU 列表中的页，如：

mysql> select table_name,space,page_number,compressed_size from innodb_buffer_page_lru where compressed_size <>0;

在 LRU 列表中的页被修改后，称该页为脏页（dirty page) ，即缓冲池中的页和磁盘上的页的数据产生了不一致。这时数据库会通过 CHECKPOINT 机制将脏页刷新回磁盘，而 Flush 列表中的页即为脏页列表。需要注意的是，脏页既存在于 LRU 列表中，也存在于 Flush 列表中。 LRU 列表用来管理缓冲池中页的可用性， Flush 列表用来管理将页刷新回磁盘，二者互不影响。同 LRU 列表一样， Flush 列表也可以通过命令 SHOW ENGINE INNODB STATUS 来查看，前面例子中 Modified db pages 24673 就显示了脏页的数量。 information_schema 架构下并没有类似 INNODB_BUFFER_PAGE_LRU 的表来显示脏页的数量及脏页的类型，但正如前面所述的那样，脏页同样存在于 LRU 列表中，故用户可以通过元数据表 INNODB_BUFFER_PAGE_LRU 来查看，唯一不同的是需要加人 OLDEST_MODIFICATION 大于 0 的 SQL 查询条件，如：

mysql> select table_name,space,page_number,page_type from innodb_buffer_page_lru where oldest_modification>0;

可以看到当前共有 5个脏页及它们对应的表和页的类型。 TABLE_NAME 为 NULL表示该页属于系统表空间。

2.3、重做日志缓冲

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

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.04 sec)

• Master Thread 每一秒将重做日志缓冲刷新到重做日志文件；
• 每个事务提交时会将重做日志缓冲刷新到重做日志文件；
• 当重做日志缓冲池剩余空间小于 1/2 时，重做日志缓冲刷新到重做日志文件。

2.4、额外的内存池

额外的内存池通常被DBA忽略，他们认为该值并不十分重要，事实恰恰相反，该值同样十分重要。在IrmoDB存储引擎中，对内存的管理是通过一种称为内存堆（heap)的方式进行的。在对一些数据结构本身的内存进行分配时，需要从额外的内存池中进行申请，当该区域的内存不够时，会从缓冲池中进行申请。例如，分配了缓冲池（innodb_buffer_pool),但是每个缓冲池中的帧缓冲（frame buffer)还有对应的缓冲控制对象(buffer control block),这些对象记录了一些诸如LRU、锁、等待等信息，而这个对象的内存需要从额外内存池中申请。因此，在申请了很大的InnoDB缓冲池时，也应考虑相应的增加这个值。