2.4 Innodb存储引擎特性（Insert Buffer、Double Write、Adaptive Hash Index等）

2.6 InnoDB 关键特性

• InnoDB存储引擎的关键特性包括∶

1. 插入缓冲（Insert Buffer）
2. 两次写（Double Write）
3. 自适应哈希索引（Adaptive Hash Index）
4. 异步 IO（Async IO）
5. 刷新邻接页（Flush Neighbor Page）
   上述这些特性为 InnoDB 存储引擎带来更好的性能以及更高的可靠性。

2.6.1 插入缓冲

1. Insert Buffer

• InnoDB 缓冲池中有 Insert Buffer 信息，但是 Insert Buffer 和数据页—样，也是物理页的一个组成部分。
• 在 InnoDB 存储引擎中，主键是行唯一的标识符。插入聚集索引（Primary Key）一般是顺序的，不需要磁盘的随机读取。比如按下列 SOL 定义表∶

CREATE TABLE t (
a INT AUTO_INCREMENT, b VARCHAR (30), PRIMARY KEY (a));

• 其中a 列是自增长的，若对 a 列插入 NULL 值，则由于其具有 AUTO_INCREMENT属性，其值会自动增长。同时页中的行记录按 a 的值进行顺序存放。在一般情况下，不需要随机读取另一个页中的记录。因此，对于这类情况下的插入操作，速度是非常快的。

并不是所有的主键插入都是顺序的。若主键类是 UUID 这样的类，那么插入和辅助索引一样，同样是随机的。即使主键是自增类型，但是插入的是指定的值，而不是 NULL 值，那么同样可能导致插入并非连续的情况。

• 但是不可能每张表上只有一个聚集索引，更多情况下，一张表上有多个非聚集的辅助索引（secondary index）。比如，用户需要按照b 这个字段进行查找，并且b这个字段不是唯一的，即表是按如下的 SQL 语句定义的∶

CREATE TABLE t (
a INT AUTO_INCREMENT, b VARCHAR (30), PRIMARY KEY (a), key (b));

• 在这样的情况下产生了一个非聚集的且不是唯一的索引。在进行插入操作时，数据页的存放还是按主键 a 进行顺序存放的，但是对于非聚集索引叶子节点的插入不再是顺序的了，这时就需要离散地访问非聚集索引页， 由于随机读取的存在而导致了插入操作性能下降。当然这并不是这个 b 字段上索引的错误，而是因为 B+ 树的特性决定了非聚集索引插入的离散性。

需要注意的是，在某些情况下，辅助索引的插入依然是比较顺序的，比如用户购买表中的时间字段。在通常情况下，用户购买时间是一个辅助索引，用来根据时间条件进行查询。但是在插入时却是根据时间的递增而插入的，因此插入也是"较为"顺序的。

• InnoDB存储引擎开创性地设计了Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入;若不在，则先放入到一个 Insert Buffer 对象中，好似欺骗。数据库以为这个非聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge （合并）操作，这时通常能将多个插入合并到一个操作中（因为在一个索引页中），这就大大提高了对干非聚集索引插入的性能。
• 然而 Insert Buffer 的使用需要同时满足以下两个条件;

1. 索引是辅助索引（secondary index）;
2. 索引不是唯一（unique）的。

• 若此时 MySOL 数据库发生了宕机，这时势必有大量的 Insert Buffer 并没有合并到实际的非聚集索引中去，因此这时恢复可能需要很长的时间。
• 辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生，从而导致 Insert Buffer失去了意义。
• 用户可以通过命令 SHOW ENGINE INNODB STATUS 来查看插入缓冲的信息∶

mysql>SHOW ENGINE INNODB STATUS\G;

• seg size 显示了当前 Insert Buffer 的大小为11336×16KB，大约为 177MB;
• free list len 代表了空闲列表的长度;
• size 代表了已经合并记录页的数量。
• 黑体部分的第 2行它显示了插入性能的提高。
• Inserts 代表了插入的记录数;
• merged recs 代表了合并的插入记录数量;
• merges 代表合并的次数，也就是实际读取页的次数。
• merges∶merged recs 大约为1∶3，代表了插入缓冲将对于非聚集索引页的离散 IO逻辑请求大约降低了2/3。

---

• 正如前面所说的，目前 Insert Buffer 存在一个问题是∶在写密集的情况下，插入缓冲会占用过多的缓冲池内存（innodb_buffer_pool），默认最大可以占用到 1/2 的缓冲池内存。以下是 InnoDB 存储引擎源代码中对于 insert buffer 的初始化操作∶

/** Buffer pool size per the maximum insert buffer size */
#define IBUF POOL SIZE PER MAX SIZE   2
ibuf->max_size = buf_pool_get_curr_size()/ UNIV_PAGE SIZE
/ IBUF_POOL_SIZE_PER_MAX_SIZE;

• 这对于其他的操作可能会带来一定的影响。修改IBUF_POOL_SIZE_PER_MAX_SIZE 就可以对插入缓冲的大小进行控制。比如将 IBUF_POOL_SIZE_PER_MAX_SIZE 改为 3，则最大只能使用 1/3 的缓冲池内存。

2.Change Buffer

• InnoDB 从 1.0.x版本开始引入了 Change Buffer，可将其视为 Insert Buffer 的升级（适用的对象依然是非唯一的辅助索引）。从这个版本开始，InnoDB存储引擎可以对 DML 操作——INSERT、DELETE、UPDATE都进行缓冲，他们分别是∶ Insert Buffer、Delete Buffer、Purge buffer。
• 对一条记录进行 UPDATE 操作可能分为两个过程∶

1. 将记录标记为已删除;
2. 真正将记录删除。

• 因此 Delete Buffer 对应 UPDATE操作的第一个过程，即将记录标记为删除。Purge Buffer 对UPDATE 操作的第二个过程，即将记录真正的删除。InnoDB 存储引擎提供了参数innodb_change_buffering，用来开启各种 Buffer 的选项。该参数可选的值为∶ inserts、deletes、purges、changes、all、none。inserts、deletes、purges 就是前面讨论过的三种情况。changes 表示启用 inserts 和 deletes，all 表示启用所有，none 表示都不启用。该参数默认值为 all。
• 从 InnoDB 1.2.x 版本开始，可以通过参数 innodb_change_buffer_max_size 来控制 Change Buffer 最大使用内存的数量∶

mysql> SHOW VARIABLES LIKE 'innodb_change_buffer_max_size'\G;
*************************** 1.rOW***************************
 Variable_name: innodb_change_buffer_max_size
		Value: 25
1 row in set (0.00 sec)

• innodb_change_buffer_max_size 值默认为 25，表示最多使用 1/4 的缓冲池内存空间。而需要注意的是，该参数的最大有效值为 50。
• 在 MySQL5.5版本中通过命令 SHOWENGINE INNODB STATUS，可以观察到类似如下的内容∶

mysql> SHOW ENGINE INNODB STATUS\G;
*************************** 1.rOW***************************
	Type: InnoDB .
	......
	---------------------------------------------------------------------
	INSERT BUFFER AND ADAPTIVE HASH INDEX
	---------------------------------------------------------------------
	Ibuf: size 1,free list len 34397,seg size 34399,10875 merges
	 merged operations:
		insert 20462,delete mark 20158,delete 4215 
	discarded operations:
		insert 0, delete mark 0, delete 0 ....

• 可以看到这里显示了merged operations 和 discarded operation，并且下 面具体显示 Change Buffer 中每个操作的次数。insert 表示 Insert Buffer; delete mark 表示 Delete Buffer; delete 表示 Purge Buffer; discarded operations 表示当 Change Buffer发生 merge时，表已经被删除，此时就无需再将记录合并（merge）到辅助索引中了。

3. Insert Buffer 的内部实现

• Insert Buffer 的数据结构是一棵 B+ 树。在 MySQL 4.1之前的版本中每张表有一棵 Insert Buffer B+ 树。而在现在的版本中，全局只有一棵 Insert Buffer B+ 树，负责对所有的表的辅助索引进行 Insert Buffer。而这棵 B+ 树存放在共享表空间中，默认也就是ibdata1中。因此，试图通过独立表空间 ibd 文件恢复表中数据时，往往会导致 CHECK TABLE 失败。这是因为表的辅助索引中的数据可能还在 Insert Buffer 中，也就是共享表空间中，所以通过 ibd 文件进行恢复后，还需要进行 REPAIR TABLE 操作来重建表上所有的辅助索引。
• Insert Buffer 是一棵 B+ 树，因此其也由叶节点和非叶节点组成。非叶节点存放的是查询的 search key（键值）

• search key 一共占用9个字节，其中 space 表示待插入记录所在表的表空间 id，在 InnoDB 存储引擎中，每个表有一个唯一的 space id，可以通过 space id 查询得知是哪张表。space 占用4字节。marker 占用1字节，它是用来兼容老版本的 Insert Buffer。offset表示页所在的偏移量，占用 4 字节。
• 当一个辅助索引要插入到页（space，offset）时，如果这个页不在缓冲池中，那么InnoDB存储引擎首先根据上述规则构造一个search key，接下来查询 Insert Buffer 这棵 B+ 树，然后再将这条记录插入到 Insert Buffer B+ 树的叶子节点中。
• 对于插入到Insert Buffer B+树叶子节点的记录（如图 2-4 所示），并不是直接将待插入的记录插入，而是需要根据如下的规则进行构造∶
  
• space、marker、page_no 字段和之前非叶节点中的含义相同，一共占用9字节。第 4个字段 metadata 占用4 字节，其存储的内容如表 2-2 所示。
  
• IBUF_REC_OFFSET_COUNT是保存两个字节的整数，用来排序每个记录进入 Insert Buffer 的顺序。因为从 InnoDB1.0.x开始支持 Change Buffer，所以这个值同样记录进入 Insert Buffer 的顺序。通过这个顺序回放（replay）才能得到记录的正确值。
• 从Insert Buffer 叶子节点的第 5 列开始，就是实际插入记录的各个字段了。因此较之原插入记录，Insert Buffer B+ 树的叶子节点记录需要额外 13 字节的开销。
• 因为启用 Insert Buffer索引后，辅助索引页（space，page_no）中的记录可能被插入到Insert Buffer B+树中，所以为了保证每次 Merge Insert Buffer 页必须成功，还需要有一个特殊的页用来标记每个辅助索引页（space，page_no）的可用空间。这个页的类型为 Insert Buffer Bitmap。
• 每个Insert Buffer Bitmap 页 用来追 踪 16384个辅 助索引页，也就是 256个区（Extent）。每个Insert Buffer Bitmap 页都在16384个页的第二个页中。关于Insert Buffer Bitmap 页的作用会在下一小节中详细介绍。
• 每个辅助索引页在 Insert Buffer Bitmap 页中占用4 位（bit），由表2-3 中的三个部分组成。
  

4. Merge Insert Buffer

• 概括地说，Merge Insert Buffer 的操作可能发生在以下几种情况下∶

1. 辅助索引页被读取到缓冲池时;
2. Insert Buffer Bitmap 页追踪到该辅助索引页已无可用空间时;
3. Master Thread。

• 第一种情况为当辅助索引页被读取到缓冲池中时，例如这在执行正常的 SELECT 查询操作，这时需要检查 Insert Buffer Bitmap 页，然后确认该辅助索引页是否有记录存放于 Insert Buffer B+ 树中。若有，则将 Insert Buffer B+ 树中该页的记录插入到该辅助索引页中。可以看到对该页多次的记录操作通过—次操作合并到了原有的辅助索引页中，因此性能会有大幅提高。
• Insert Buffer Bitmap 页用来追踪每个辅助索引页的可用空间，并至少有 1/32 页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于 1/32 页，则会强制进行一个合并操作，即强制读取辅助索引页，将 Insert Buffer B+ 树中该页的记录及待插入的记录插入到辅助索引页中。这就是上述所说的第二种情况。
• 还有一种情况，之前在分析 Master Thread 时曾讲到，在 Master Thread 线程中每秒或每 10 秒会进行一次 Merge Insert Buffer 的操作，不同之处在于每次进行 merge 操作的页的数量不同。
• 在 Master Thread 中，执行 merge操作的不止是一个页，而是根据 srv_innodb_io capactiy 的百分比来决定真正要合并多少个辅助索引页。但 InnoDB 存储引擎又是根据怎样的算法来得知需要合并的辅助索引页呢?
• 在Insert Buffer B+树中，辅助索引页根据（space，offset）都已排序好，故可以根据（space，offset）的排序顺序进行页的选择。然而，对于Insert Buffer 页的选择， InnoDB存储引擎并非采用这个方式，它随机地选择 Insert Buffer B+ 树的一个页，读取该页中的 space 及之后所需要数量的页。该算法在复杂情况下应有更好的公平性。同时，若进行 merge 时，要进行 merge 的表已经被删除，此时可以直接丢弃已经被 Insert/ Change Buffer 的数据记录。

2.6.2 两次写

• 如果说 Insert Buffer 带给 InnoDB 存储引擎的是性能上的提升，那么 doublewrite（两次写）带给InnoDB 存储引擎的是数据页的可靠性。
• 当发生数据库宕机时，可能 InnoDB存储引擎正在写入某个页到表中，而这个页只写了一部分，比如 16KB 的页，只写了前 4KB，之后就发生了宕机，这种情况被称为部分写失效（partial page write）。在 InnoDB存储引擎未使用 doublewrite技术前，曾经出现过因为部分写失效而导致数据丢失的情况。
• 有经验的 DBA 也许会想，如果发生写失效，可以通过重做日志进行恢复。这是一个办法。但是必须清楚地认识到，重做日志中记录的是对页的物理操作，如偏移量 800，写’aaaa’记录。如果这个页本身已经发生了损坏，再对其进行重做是没有意义的。这就是说，在应用（apply）重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是 doublewrite。在 InnoDB 存储引擎中 doublewrite 的体系架构如图 2-5 所示。
• doublewrite 由两部分组成，一部分是内存中的 doublewrite buffer，大小为 2MB，另一部分是物理磁盘上共享表空间中连续的 128个页，即 2 个区（extent），大小同样为 2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy 函数将脏页先复制到内存中的 doublewrite buffer，之后通过 doublewrite buffer 再分两次，每次 1MB 顺序地写入共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为 doublewrite 页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成 doublewrite 页的写入后，再将 doublewrite buffer 中的页写入各个表空间文件中，此时的写入则是离散的。可以通过以下命令观察到 doublewrite运行的情况∶
  
• 可以看到，doublewrite 一共写了6325194个页，但实际的写入次数为 100399，基本上符合 64∶1。如果发现系统在高峰时的 Innodb_dblwr_pages_written∶Innodb_dblwr_ writes 远小于64∶1，那么可以说明系统写入压力并不是很高。
• 如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB 存储引擎可以从共享表空间中的 doublewrite 中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。下面显示了一个由 doublewrite 进行恢复的情况∶
  
• 若查看MySQL官方手册，会发现在命令 SHOW GLOBAL STATUS中Innodb_ buffer_pool_pages_flushed变量表示当前从缓冲池中刷新到磁盘页的数量。根据之前的介绍，用户应该了解到，在默认情况下所有页的刷新首先都需要放入到 doublewrite 中，因此该变量应该和 Innodb_ dblwr_pages_written一致。然而在 MySQL 5.5.24 版本之前，Innodb_buffer_pool_pages_flushed 总是为 Innodb_dblwr_pages_written 的2倍，而此 Bug直到 MySQL5.5.24 才被修复。因此用户若需要统计数据库在生产环境中写入的量，最安全的方法还是根据 Innodb_dblwr_pages_written 来进行统计，这在所有版本的 MySQL 数据库中都是正确的。
• 参数 skip_innodb_doublewrite 可以禁止使用doublewrite 功能，这时可能会发生前面提及的写失效问题。不过如果用户有多个从服务器（slave server），需要提供较快的性能（如在 slaves erver 上做的是 RAID0），也许启用这个参数是一个办法。不过对于需要提供数据高可靠性的主服务器（master server），任何时候用户都应确保开启doublewrite 功能。

有些文件系统本身就提供了部分写失效的防范机制，如ZFS 文件系统。在这种情况下，用户就不要启用 doublewrite 了。

2.6.3 自适应哈希索引

• 哈希（hash）是一种非常快的查找方法，在一般情况下这种查找的时间复杂度为 O（1），即一般仅需要一次查找就能定位数据。而 B+ 树的查找次数，取决于 B+ 树的高度，在生产环境中，B+ 树的高度一般为 3 ～ 4 层，故需要 3～ 4 次的查询。
• InnoDB 存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index，AHI）。 AHI 是通过缓冲池的 B+ 树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB 存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。
• AHI 有一个要求，即对这个页的连续访问模式必须是一样的。例如对于（a，b）这样的联合索引页，其访问模式可以是以下情况∶

1. WHERE a=xxx
2. WHERE a=xxx and b=xxx

• 访问模式一样指的是查询的条件一样，若交替进行上述两种查询，那么 InonDB 存储引擎不会对该页构造 AHI。此外 AHI 还有如下的要求∶

1. 以该模式访问了100 次
2. 页通过该模式访问了N次，其中 N= 页中记录*1/16

• 根据 InnoDB 存储引擎官方的文档显示，启用 AHI后，读取和写入速度可以提高 2倍，辅助索引的连接操作性能可以提高 5倍。毫无疑问，AHI是非常好的优化模式，其设计思想是数据库自优化的（self-tuning），即无需 DBA 对数据库进行人为调整。
• 通过命令 SHOW ENGINE INNODB STATUS 可以看到当前 AHI 的使用状况;
  
• 现在可以看到 AHI 的使用信息了，包括 AHI 的大小、使用情况、每秒使用 AH 搜索的情况。值得注意的是，哈希索引只能用来搜索等值的查询，如 SELECT*FROM table WHERE index_col=‘xxx’。而对于其他查找类型，如范围查找，是不能使用哈希索引的，因此这里出现了 non-hash searches/s 的情况。通过 hash searches∶non-hash searches 可以大概了解使用哈希索引后的效率。
• 由于 AHI 是由 InnoDB存储引擎控制的，因此这里的信息只供用户参考。不过用户可以通过观察 SHOW ENGINE INNODB STATUS的结果及参数 innodb_adaptive_hash_index 来考虑是禁用或启动此特性，默认 AHI为开启状态。

2.6.4 异步 IO

• 为了提高磁盘操作性能，当前的数据库系统都采用异步 IO（Asynchronous IO， AIO）的方式来处理磁盘操作。InnoDB 存储引擎亦是如此。
• 与 AIO 对应的是 Sync IO，即每进行一次 IO操作，需要等待此次操作结束才能继续接下来的操作。但是如果用户发出的是一条索引扫描的查询，那么这条 SQL 查询语句可能需要扫描多个索引页，也就是需要进行多次的 IO 操作。在每扫描一个页并等待其完成后再进行下一次的扫描，这是没有必要的。用户可以在发出一个 IO 请求后立即再发出另一个IO 请求，当全部 IO 请求发送完毕后，等待所有 IO操作的完成，这就是 AIO。
• AIO 的另一个优势是可以进行 IO Merge 操作，也就是将多个 IO 合并为1个 IO，这样可以提高 IOPS 的性能。例如用户需要访问页的（space，page_no）为∶
  (8,6)、(8,7),(8,8)
• 每个页的大小为 16KB，那么同步 IO需要进行 3 次 IO操作。而 AIO 会判断到这三个页是连续的（显然可以通过（space，page_no）得知）。因此 AIO 底层会发送一个IO请求，从（8，6）开始，读取 48KB 的页。
• 若通过Linux操作系统下的 iostat命令，可以通过观察 rram/s 和 wram/s。例如∶
  
• 在 InnoDB1.1.x 之前，AIO 的实现通过 InnoDB存储引擎中的代码来模拟实现。而从 InnoDB 1.1.x开始（InnoDB Plugin 不支持），提供了内核级别 AIO的支持，称为Native AIO。因此在编译或者运行该版本 MySOL 时，需要 libaio 库的支持。若没有则会出现如下的提示∶
  
• 提供Native AIO 支持，而 Mac OSX系统则未提供。因此在这些系统下，依旧只能使用原模拟的方式。在选择 MySQL 数据库服务器的操作系统时，需要考虑这方面的因素。
• 参数innodb_use_native_aio 用来控制是否启用Native AIO，在 Linux操作系统下，默认值为 ON∶
  
• 用户可以通过开启和关闭Native AIO 功能来比较 InnoDB 性能的提升。官方的测试显示，启用 Native AIO，恢复速度可以提高 75%。
• 在 InnoDB存储引擎中，read ahead方式的读取都是通过 AIO完成，脏页的刷新，即磁盘的写入操作则全部由 AIO完成。

2.6.5 刷新邻接页

• InnoDB 存储引擎还提供了Flush Neighbor Page（刷新邻接页）的特性。其工作原理为;当刷新一个脏页时，InnoDB 存储引擎会检测该页所在区（extent）的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过 AIO 可以将多个IO写入操作合并为一个IO 操作，故该工作机制在传统机械磁盘下有着显著的优势。但是需要考虑到下面两个问题∶

1. 是不是可能将不怎么脏的页进行了写入，而该页之后又会很快变成脏页?
2. 固态硬盘有着较高的 IOPS，是否还需要这个特性?

• 为此，InnoDB存储引擎从 1.2.x 版本开始提供了参数innodb_flush_neighbors，用来控制是否启用该特性。对于传统机械硬盘建议启用该特性，而对于固态硬盘有着超高 IOPS 性能的磁盘，则建议将该参数设置为 0，即关闭此特性。