什么是Mysql的next-key、插入缓冲、二次写、自适应哈希索引和预读

楔子

在《Mysql技术内幕InnoDB存储引擎》第二版的书中有这么一句话：“InnoDB通过使用多版本并发控制（MVCC）来获得高并发性，并且实现类SQL标准的4种隔离级别，默认为可重复读（REPEATABLE）级别。同时，使用一种被称为next-key locking的策略来避免幻读（phantom）现象的产生。除此之外，InnoDB存储殷勤还提供了插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、预读（read ahead）等高性能和高可用的功能。”
于是，本着初次学习不太了解的原因，整理了一下关于上述几个专有名词的解析供自己学习复习。

一、间隙锁（Next-Key锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。就是说你有的我要“独占”，没有的我也要“占有”，没有的还要占有，这就是Next-key。

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：

Select * from  emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！

在如表20-13所示的例子中，假如emp表中只有101条记录，其empid的值分别是1,2,…,100,101。

• update 不存在的值时，也会升级全表锁！！
• update使用唯一索引的时候，不会升级全表锁，但是如果索引值有重复，则会升级全表锁！！！

二、插入缓冲(Insert Buffer)

2.1、什么是insert buffer？

插入缓冲，也称之为insert buffer，它是innodb存储引擎的关键特性之一，我们经常会理解插入缓冲是缓冲池的一个部分，这样的理解是片面的，insert buffer的信息一部分在内存中，另外一部分像数据页一样，存在于物理页中。

在innodb中，我们知道，如果一个表有自增主键，那么对于这个表的默认插入是非常快的，注意，这里的主键是自增的，如果不是自增的，那么这个插入将会变成随机的，就可能带来数据页分裂的开销，这样，插入就不是顺序的，就会变慢。还有一种情况，就是如果我们插入的id不是顺序的，而是随机的，那么即使有自增主键，那么插入的速度也不会特别快。

如果我们定义了一个表，包含一个主键和一个非聚集索引，如下：

create table t(

a int auto_increment,

b varchar(30),

primary key(a),

key (b)

);

当我们按照主键a进行插入的时候，对于非聚集索引，也就是常说的二级索引b,它的插入不是顺序的，插入性能必然会下降。

Innodb存储引擎针对这种情况，设计了Insert Buffer，对于非聚集索引的插入或者更新操作，不是每一次插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在，则直接插入，如果不在，则先放入一个insert buffer中，告诉数据库这个非聚集的索引已经插入到了叶子节点，实际上并没有插入，只是存放在另外一个位置，然后再以一定的频率和情况进行Insert buffer和辅助索引叶子节点合并操作。这种时候，经常能将多条记录的插入合并到一个操作中，这样就大大提高了非聚集索引离散插入的性能。

2.2、insert buffer的触发条件？

insert buffer需要满足两个条件才能被使用，第一，索引是辅助索引，也就是二级索引，第二，索引不是唯一的。当满足上述两个条件的时候，就可以使用insert buffer，从而提高数据库的插入操作性能。

这里需要注意，如果在程序进行了大量操作的时候发生了MySQL数据库的宕机，那么肯定有大量的insert buffer没有合并到实际的非聚集索引中去，恢复可能会造成很长的时间。

2.3、为什么不能是唯一索引？

之所以不支持唯一索引，是因为如果辅助索引是唯一索引，那么在插入时需要校验唯一性，校验唯一性的时候就会发生离散读取，从而又增加了开销，那么insert buffer得不偿失。

我们可以通过show engine innodb status来查看insert buffer的使用情况，如下：

mysql--root@localhost:dms_alimetadata 20:35:24>>show engine innodb status\G
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0

其中size代表了已经合并记录页的数量，free list len代表了空闲列表的长度，seg size显示了当前insert buffer的大小为2*16KB

2.4、insert buffer的升级版change buffer

最新的MySQL5.7已经支持change buffer，事实上，它在innodb 1.0.x版本已经引入，这个change buffer 可以理解为insert buffer的升级，也就是对常见的DML语言都可以进行缓冲，包含insert delete以及update，对应的分别是insert buffer，delete buffer以及purge buffer。

当然，change buffer的使用对象仍然是非唯一的辅助索引。

这里我们以update操作为例，update的过程可以拆分为两个部分：

第一个部分是将记录的delete_mask标记为删除，如果你不了解delete_mask，可以在4月9号的文章中去看。第二个部分是真正的将记录删除。

而delete buffer对应的是update的第一个过程，purge buffer对应的是第二个部分。

在innodb中，我们可以通过参数innodb_change_buffering来开启buffer的各种选项，该参数可选的值为inserts，deletes，purges，changes，all，none等，其中inserts，deletes和purges就是前面讨论过的情况，changes表示开启inserts和deletes，all表示开启所有，默认的参数如下：

mysql--root@localhost:dms_alimetadata 21:13:37>>show variables like '%buffering%';        
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_change_buffering | all   |
+-------------------------+-------+
1 row in set (0.01 sec)

我们还可以通过innodb_change_buffer_max_size来控制change_buffer的最大使用内存数量，该参数的默认值是25，也就是1/4，示例如下：

mysql--root@localhost:dms_alimetadata 21:20:52>>show variables like '%innodb_change_buffer_max_size%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
+-------------------------------+-------+
1 row in set (0.00 sec)

在上面的show engine innodb status命令的输出结果中，显示了merged operation和discarded operation，其中insert 表示insert buffer的操作次数，delete mark表示delete buffer的操作次数，而delete表示purge buffer的操作次数，discarded operation表示当change buffer发生merge时，表已经被删除，此时就无需进行合并。

2.5、Insert Buffer的实现

insert buffer的数据结构是一棵B+树，类似聚集索引一样，全局只有一棵insert buffer B+树，它负责对所有的表进行insert buffer，而这棵B+树放在共享表空间中，也就是ibdata1文件中，因此，试图通过ibd文件恢复表数据的时候可能会出现check table失败，原因是表的辅助索引中的数据可能还在insert buffer中，所以通过ibd文件恢复文件之后，还需要进行repair table操作来重建表上的辅助索引。

insert buffer既然是一棵树，那么必定有叶子节点和非叶子节点，非叶子节点存放的是查询的search key值，它的构造如下：

+---------+------------+-------+
| space   |   marker   | Value |
+---------+------------+-------+

这个结构一共占用9个字节，其中，space表示待插入的记录所在的表的表空间id，这个id是每个表都要有的唯一的id，其中space占用4个字节，marker占用1个字节，用来兼容老版本的insert buffer，offset占用4个字节，表示页所在的偏移量。

2.6、辅助索引的插入过程

当一个辅助索引要插入到数据页的时候，如果这个数据页不在缓冲池中，那么innodb会根据规则构造一个search key，接下来将这个记录插入到insert buffer的B+树里面去，插入的过程中，需要对这个记录进行一些构造，最终插入的结果是类似下面这样的一条记录：

+---------+------------+-------+------------+------+-------+------+-------+
| space   |   marker   | Value | metadata   |      |       |      |       |
+---------+------------+-------+------------+------+-------+------+-------+

可以发现，最后面多了一个metadata的字段和4个其他的字段，先来说说metadata的字段，它占用4个字节，它用来排序每个记录进入insert buffer的顺序，从第5列开始，就是实际插入记录的各个字段的值了，因此和单纯的数据记录相比，insert buffer需要额外13个字节的开销。

为了保证每次merge insert buffer成功，需要设置一个特殊的数据页来标记每个辅助索引页的可用空间，这个数据页的类型为insert buffer bitmap，这个页可以追踪很多辅助索引页的可用空间。这里简单了解一下，下面会解释它的用法。

2.7、Merged Insert Buffer的时机

我们前面已经知道，当插入记录的辅助索引页不在缓冲池中的时候，需要将辅助索引记录插入到这棵B+树中，后续会从insert buffer中往真正的辅助索引中进行合并，那么什么时候进行合并呢？

1. 辅助索引页被读取到缓冲池的时候
2. insert buffer Bitmap追踪到该辅助索引页已经没有足够的可用空间时，一般的阈值是辅助索引页空间的1/32
3. master thread每秒执行一次merge insert buffer的操作

三、double write（二次写）

3.1、什么是double write

MySQL的数据页默认是16K，而文件系统的数据页是4K，IO操作是按页为单位就行读写的。这就可能出现数据库对一个16k的数据页修改后，操作系统开始进行写磁盘，但是在这个过程中数据库宕机导致没有完全将16K数据页写到磁盘上。数据库重启后，校验数据页，发现有数据页不完整，就起不来了（redo是基于完整数据页进行的恢复）。

为了解决这个问题，MySQL引入了double write这个特性。double write针对的是脏数据，提高innodb的可靠性，用来解决部分写失败(partial page write）。为了数据的持久性，脏数据需要刷新到磁盘上，而double write就产生在将脏数据刷盘的过程中。刷盘是一份脏数据写到共享表空间ibdata中，一份写到真正的数据文件永久的保存。写了两次脏数据，就叫double write。

3.2、double write原理

简单来说，double write就是将修改后的脏页先放到double write buffer区，这个区占用2M内存空间，buffer空间满或其他条件触发，使double write buffer存的脏页先写到共享表空间（在MySQL 8.0.20之前，doublewrite缓冲区存储区位于InnoDB系统表空间中。从MySQL 8.0.20开始，doublewrite缓冲区存储区位于doublewrite文件中），之后在写入数据文件。这个时候如果写了不完整的页，可以用共享表空间中完整的页加以覆盖，数据页完整了，数据库也就可以拉起了，之后的各种恢复就看redo log的了。redo log是按数据块的方式记录日志的，它是根据偏移量来记录修改。尽管数据被写入两次，但双写缓冲区不需要两倍的I / O开销或两倍的I / O操作。只需一次fsync()调用操作系统。数据就可以按较大的顺序块写入doublewrite缓冲区（除非 innodb_flush_method设置为 O_DIRECT_NO_FSYNC）。

8.0.20后，在默认情况下，为每个缓冲池实例创建两个双写文件：刷新列表双写文件和LRU列表双写文件。

• 刷新列表双写文件用于从缓冲池刷新列表中刷新的页面。刷新列表doublewrite文件的默认大小是InnoDB page size * doublewrite page bytes.。

• LRU列表双写文件用于从缓冲池LRU列表中刷新的页面。它还包含用于单页刷新的插槽。LRU列表双写文件的默认大小为InnoDB page size * (doublewrite pages + (512 / the number of buffer pool instances))，其中512是为单页刷新保留的插槽总数。

至少有两个双写文件。双写文件的最大数量是缓冲池实例数量的两倍。
redolog写入的单位就是512字节，也就是磁盘IO的最小单位，所以无所谓数据损坏。

3.3、double write恢复流程

数据恢复有三种情况

1. 脏数据写磁盘成功
刷盘成功，找检查点，进行前滚、回滚就行了。
2. 共享表空间ibdata写失败
如果是写共享表空间失败，那么这些数据不会被写到数据文件，数据库会认为这次刷盘从没发生过，MySQL此时会从磁盘载入原始的数据，然后找检查点，redo log前滚、回滚就行了。
3. 脏数据刷数据文件失败
写共享表空间成功，但是写数据文件失败，在恢复的时候，MySQL直接比较页面的checksum，如果不对的话，直接从共享表空间的double write中找到该页的一个最近的副本，将其复制到表空间文件，再应用redo log，就完成了恢复过程。因为有副本所以也不担心表空间中数据页是否损坏。

3.4、doublewrite的负载

double write是一个buffer, 但其实它是开在物理文件上的一个buffer, 其实也就是file, 所以它会导致系统有更多的fsync操作, 而硬盘的fsync性能是很慢的, 所以它会降低mysql的整体性能。

3.4.1、监控double write工作负载

mysql> show global status like '%dblwr%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Innodb_dblwr_pages_written | 44    |
| Innodb_dblwr_writes        | 8     |
+----------------------------+-------+
2 rows in set (0.00 sec)

关注点：Innodb_dblwr_pages_written / Innodb_dblwr_writes

开启doublewrite后，每次脏页刷新必须要先写doublewrite，而doublewrite存在于磁盘上的是两个连续的区，每个区由连续的页组成，一般情况下一个区最多有64个页，所以一次IO写入应该可以最多写64个页。

3.4.2、适合关闭double write

1. 海量DML
2. 不惧怕数据损坏和丢失
3. 系统写负载成为主要负载

3.5、相关参数

mysql> show variables like '%double%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_doublewrite            | ON    |
| innodb_doublewrite_batch_size | 0     |
| innodb_doublewrite_dir        |       |
| innodb_doublewrite_files      | 2     |
| innodb_doublewrite_pages      | 8     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

#innodb_doublewrite：是否开启doublewrite
#innodb_doublewrite_batch_size：定义要批量写入的双写页面数
#innodb_doublewrite_dir：定义双写缓冲区文件目录
#innodb_doublewrite_files：定义双写文件的数量
#innodb_doublewrite_pages：定义批量写入时每个线程的最大双写页数

四、MySQL自适应Hash索引

4.1、Hash索引

Hash是一种查找数据非常快的数据结构，在正常情况下这种查找的时间复杂度为O(1)，即一般仅需要一次查找就能定位数据，正常情况是指不存在哈希冲突的情况；而B+树的查找次数，取决于B+树的高度，B+树的高度一般为3、4层，所以一般最少需要3、4次的查询。

4.2、InnoDB的Hash索引

InnoDB存储引擎会监控对表上各索引页的查询，如果监控到某个索引页被频繁查询，并诊断后发现如果为这一页的数据创建Hash索引会带来更大的性能提升，则会自动为这一页的数据创建Hash索引，并称之为自适应Hash索引。自适应Hash是通过缓冲池中B+树的页进行构建的，建立速度很快，不需要对整张表的数据都构建Hash索引，所以我们又可以把自适应Hash索引看成是索引的索引，。注意一点就是InnoDB只会对热点页构建自适应索引，且是由InnoDB自动创建和删除的，所以不能人为干预是否在一张InnoDB的表中创建Hash索引。

4.3、自适应Hash索引

官方有告诉我们每一种存储引擎所支持的索引结构，在https://dev.mysql.com/doc/refman/5.7/en/create-index.html中可以查看。我们找寻关于自适应Hash索引的部分，对他总结一下就是：自适应Hash索引特征能使InnoDB在具有适当的工作负载和足够缓冲池内存的系统上执行的更像内存中的数据库的操作，且不会牺牲事务特性或可靠性，MySQL能基于监视到的搜索规则，使用索引键的前缀构建Hash索引，前缀可以是任意长度，并且可能只有b+树中的某些值出现在Hash索引中，Hash索引其实就是对经常访问的索引页进行构建的。

这又说明其实InnoDB是支持Hash索引的，但并不是真正意义上的Hash，而是通过自己的监视情况自动对某些热点索引值构建的内存Hash。

4.4、开启和关闭

默认情况下自适应索引是开启状态，毕竟是可以提升性能的嘛，我们也可以通过命令开启和关闭，并可以查看自适应索引的

• 开启
  默认就是开启的，可以通过命令show variables like ‘innodb_adaptive_hash_index’;查看自适应哈希索引的状态，并可以在命令行通过show engine innodb status\G查看自适应Hash索引的使用信息(AHI的大小，使用情况，每秒使用AHI搜索的情况等等)
• 关闭
  负载较重的情况下，就不太适合开启自适应Hash索引了，因为这样可以避免额外的索引维护带来的开销，可以在启动的时候通过参数–skip-innodb-adaptive-hash-index关闭

五、MySQL的预读机制

5.1、预读机制

InnoDB在I/O的优化上有个比较重要的特性为预读，预读请求是一个i/o请求，它会异步地在缓冲池中预先回迁多个页面，预计很快就会需要这些页面，这些请求在一个范围内引入所有页面。InnoDB以64个page为一个extent，那么InnoDB的预读是以page为单位还是以extent？

数据库请求数据的时候，会将读请求交给文件系统，放入请求队列中；相关进程从请求队列中将读请求取出，根据需求到相关数据区(内存、磁盘)读取数据；取出的数据，放入响应队列中，最后数据库就会从响应队列中将数据取走，完成一次数据读操作过程。

接着进程继续处理请求队列，(如果数据库是全表扫描的话，数据读请求将会占满请求队列)，判断后面几个数据读请求的数据是否相邻，再根据自身系统IO带宽处理量，进行预读，进行读请求的合并处理，一次性读取多块数据放入响应队列中，再被数据库取走。(如此，一次物理读操作，实现多页数据读取，rrqm>0（# iostat -x），假设是4个读请求合并，则rrqm参数显示的就是4)

5.2、两种预读算法

InnoDB使用两种预读算法来提高I/O性能：线性预读（linear read-ahead）和随机预读（randomread-ahead）

为了区分这两种预读的方式，我们可以把线性预读放到以extent为单位，而随机预读放到以extent中的page为单位。线性预读着眼于将下一个extent提前读取到buffer pool中，而随机预读着眼于将当前extent中的剩余的page提前读取到buffer pool中。

5.2.1、线性预读（linear read-ahead）

线性预读方式有一个很重要的变量控制是否将下一个extent预读到buffer pool中，通过使用配置参数innodb_read_ahead_threshold，控制触发innodb执行预读操作的时间。

如果一个extent中的被顺序读取的page超过或者等于该参数变量时，Innodb将会异步的将下一个extent读取到buffer pool中，innodb_read_ahead_threshold可以设置为0-64的任何值(因为一个extent中也就只有64页)，默认值为56，值越高，访问模式检查越严格。

mysql> show variables like 'innodb_read_ahead_threshold';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| innodb_read_ahead_threshold | 56    |
+-----------------------------+-------+

例如，如果将值设置为48，则InnoDB只有在顺序访问当前extent中的48个pages时才触发线性预读请求，将下一个extent读到内存中。如果值为8，InnoDB触发异步预读，即使程序段中只有8页被顺序访问。

可以在MySQL配置文件中设置此参数的值，或者使用SET GLOBAL需要该SUPER权限的命令动态更改该参数。

在没有该变量之前，当访问到extent的最后一个page的时候，innodb会决定是否将下一个extent放入到buffer pool中。

5.2.2、随机预读（randomread-ahead）

随机预读方式则是表示当同一个extent中的一些page在buffer pool中发现时，Innodb会将该extent中的剩余page一并读到buffer pool中。

mysql> show variables like 'innodb_random_read_ahead';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_random_read_ahead | OFF   |
+--------------------------+-------+

由于随机预读方式给innodb code带来了一些不必要的复杂性，同时在性能也存在不稳定性，在5.5中已经将这种预读方式废弃，默认是OFF。若要启用此功能，即将配置变量设置innodb_random_read_ahead为ON。

5.3、监控Innodb的预读

5.3.1、可以通过show engine innodb status\G显示统计信息

mysql> show engine innodb status\G
----------------------
BUFFER POOL AND MEMORY
----------------------
……
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
……

• 1、Pages read ahead：表示每秒读入的pages；
• 2、evicted without access：表示每秒读出的pages；
• 3、一般随机预读都是关闭的，也就是0。

5.3.2、通过两个状态值，评估预读算法的有效性

mysql> show global status like '%read_ahead%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 2303  |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
+---------------------------------------+-------+
3 rows in set (0.01 sec)

• 1、Innodb_buffer_pool_read_ahead：通过预读(后台线程)读入innodb buffer pool中数据页数
• 2、Innodb_buffer_pool_read_ahead_evicted：通过预读来的数据页没有被查询访问就被清理的pages，无效预读页数

六、参考链接

1. https://blog.csdn.net/xiaobluesky/article/details/50412069
2. https://www.jb51.net/article/209147.htm
3. https://blog.csdn.net/qq_42979842/article/details/108043605
4. https://blog.csdn.net/luxiaoruo/article/details/106637298
5. https://blog.csdn.net/qq9808/article/details/105846413