Innodb三大特性之自适应哈希索引、double write&change buffer

来源：mysql官方手册整理

自适应哈希索引

自适应哈希索引特性能够使MySQL在不牺牲事务的特性和可靠性的前提下，在具有特性的工作负载和缓存配置时表现的更像内存数据库。

可以通过启用innodb_adaptive_hash_index来开启自适应哈希索引特性或在服务器启动时通过指定--skip-innodb-adaptive-hash-index关闭。

mysql> show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+

基于观察到的搜索模式，使用索引键的前缀构建哈希索引，前缀可以是任意长度，而且哈希索引可能只有b-tree的一部分值。哈希索引基于经常访问的索引页建立。

如果一张表几乎完全在内存中，哈希索引可以将键值转化为指针，通过启用对任意元素的直接查找来加快查询的效率。innodb拥有能够监控索引搜索的机制，如果发现能够通过建立哈希索引来使查询变快，它会自动建立哈希索引。

在某些工作负载下，哈希索引带来查询速度的提高远远超过监控索引查找和维护哈希索引结构的代价。在较高的工作负载下，哈希索引也可能称为一部分争用的来源，比如多个并发的连接，使用like和%通配符这样的查询也不能从哈希索引中受益，在这种情况下可以关闭该特性以减轻不必要的工作负载。因为很难精确的预测在特定的系统和工作负载下哈希索引能否带来正面的影响，可以通过运行基准测试来决定关闭或启用。MySQL5.6中的特性修改使得相较之前的版本禁用哈希索引更适合。

可以通过show engine innodb status中SEMAPHORS部分的信息来查看哈希索引的使用和争用情况。如果存在大量的线程等待在btr0sea.c创建的RW-latch，禁用哈希索引特性可能会更适合。

SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2746
OS WAIT ARRAY INFO: signal count 2666
RW-shared spins 0, rounds 1093, OS waits 542
RW-excl spins 0, rounds 213, OS waits 9
RW-sx spins 2, rounds 60, OS waits 0
Spin rounds per wait: 1093.00 RW-shared, 213.00 RW-excl, 30.00 RW-sx

Doublewrite Buffer

doublewrite buffeer是MySQL系统表空间的一块内存区域，innodb先将页刷新到该区域，然后再将页面写回到数据文件的相应位置。只有当innodb将页面刷新并写到doublewrite buffer后才会刷新页面到磁盘。如果操作系统，存储或mysqld进程在页面写入的中途崩溃，innodb就可以在实例恢复时从doublewrite buffer中找到页的拷贝。

尽管在doublewrite的机制下，数据总是需要写两次，但它并不需要两倍的IO开销或IO操作，在该过程中数据会作为连续块写入doublewrite buffer,并对操作系统进行一个fsync()调用。

doublewrite buffer特性默认是启用的，可以通过将innodb_doublewrite设置为0来禁用。

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

change buffer

changebuffer是一种特殊的数据结构，用来缓存对未在缓冲区的二级索引页的更改。由INSERT,UPDATE或者DELETE操作产生的操作将会在页面被加载到buffer pool时候由innodb的main thread进行合并。

不用于聚簇索引，辅助索引通常是不唯一的，并且插入辅助索引的顺序是相对随机的。类似的，delete和update操作页可能会影响在索引树中不相邻的索引页。当受影响的页面被读入buffer pool时再做缓冲区更改的合并，可以避免建辅助索引从磁盘读入buffer pool可能会产生的大量随机IO。

周期性的，当系统处于空闲状态或正常关闭时做的清除操作将会将对索引页的更改写入磁盘。与立即将每个值写入磁盘相比，清除操作可以更有效把一系列索引值写入磁盘。

当需要更新大量的行和辅助索引时，change buffer的合并可能会花费几个小时，在此期间，磁盘IO会增加，因而可能会导磁盘绑定的查询显著变慢。在事务提交甚至服务器关闭或重启时，都有可能发生chane buffer的合并。

在内存中，change buffer作为buffer pool的一部分，而在磁盘上，change buffer是系统表空间的一部分，当服务器关闭时，索引的更改将会缓存到磁盘上的区域。

由于change buffer可以减少磁盘读写，该特性对于IO板顶的工作负载最有价值，例如具有大量DML操作(如批量插入)的应用程序。

mysql> show variables like 'innodb_change_buffering';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_change_buffering | all   |
+-------------------------+-------+

然而，chane buffer会占用buffer pool的一部分空间，而导致可用来缓存的内存减小，如果数据能够完全存放在buffer pool中或很少使用辅助索引，禁用change buffer可能会更合适。

可以通过设置innodb_change_buffering来控制innodb使用change buffer的程度，可以为插入操作，删除操作(索引最初被标记为删除)和清除操作(索引记录被物理删除)启用或禁用。update操作被认为是delete和insert操作的组合。默认的innodb_change_buffering值为all，下面为可选配置的值：

all: 默认值，缓存Insert，delete-marking和purges
none：不缓存任何操作
inserts：缓存insert操作
deletes：缓存delete-marking
changes：缓存inserts和delete-marking
purges：缓存后台发生的物理删除操作

innodb_change_buffering可以在my.cnf中设置或使用set global语句动态地更改(需要set global variables的权限)，更改该设置只影响新产生的操作，已缓存在change buffer中的条目不受影响。

innodb_change_buffer_max_size变量配置为change buffer大小可占用buffer pool的最大比例，默认值为25，最大可调整为50.

可以通过show engine innodb status查看change buffer的状态信息。

INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 33, seg size 35, 6 merges
merged operations:
 insert 20, delete mark 1, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
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 42 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

查看缓冲区指标及其描述：

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G;