Innodb存储引擎之插入缓冲

Innodb存储引擎之插入缓冲

Innodb存储引擎特性之插入缓冲

01

基础知识

我们知道，innodb存储引擎是基于磁盘存储的，它同时利用缓冲池技术来提高数据库的整体性能，具体的利用方法为：innodb从磁盘中通过16KB数据页的形式，将磁盘中的数据加载到内存当中，通过内存的速度来弥补磁盘速度较慢对数据库带来的性能影响。当缓存池中的数据页被修改过后，通过将数据页从缓冲池刷新回磁盘的操作来确保数据所做的修改被永久保存。原理如下：

然而，缓冲池中不仅仅保存innodb的数据页，它还包含了很多内容，具体的内容如下：

这个innodb_buffer_pool就是所谓的缓冲池，你可以在MySQL中使用下面的语句来查看buffer pool的相关信息:

mysql--dba_admin@127.0.0.1:(none) 14:05:52>>show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 268435456      |
+-------------------------------------+----------------+
10 rows in set (0.03 sec)

innodb_buffer_pool是我们今天要了解的插入缓冲insert buffer，它只是buffer pool所有内容其中的一种。

02

插入类型

主键上的插入---聚集索引B+树插入

在Innodb存储引擎下，我们会为每一个表设置主键，主键一般设置为自增长的，这样，在我们进行数据插入的时候，如果设置主键列的值为null或者不设置主键列的值，那么主键值会自动增长，表现在数据页中就是数据页的序号是连续的，在这种插入的情况下，操作速度是非常快的。

普通索引---非聚集索引B+树插入

但是，一张表里面往往不是只有一个主键，还可能有普通的二级索引，在我们根据主键进行插入的时候，普通的二级索引往往是无序的，例如下面这个表以及插入语句：

create table test (
id int not null auto_increment primary key,
name varchar(10),
key `key_name`(name)
)engine=innodb default charset=utf8;

insert into test values ('Tom'),('Jenny'),('Karry');

id    name
1     Tom
2     Jenny
3     Karry

当我们进行插入的时候，插入的结果按照主键是有序的，所以在聚集索引的B+树上是顺序插入的，而在name列上的普通索引B+树上，索引是无序的（Tom、Jenny、Karry是无序的），这必然会造成普通索引的插入是无序的，而无序操作在磁盘上就需要随机的访问索引页，而随机的访问索引页会导致插入性能的下降。见下图：

我们看到，假设一个页面上保存5条记录，如果我们每间隔一段时间运行一次insert操作，如上图，前2条insert语句中的数据都插入到页号为1的数据页中了，最后2条insert语句都插入到页号为9的页面中了，在该模式下，几个insert语句，就会重复的进行几次页面的IO操作，因为每次insert 都会进行一次页面的IO操作，而且这种数据页之间没有任何关系，大量随机的在磁盘中寻找数据页，这样对MySQL的性能会产生极大的影响。

insert buffer就是为了解决这个问题而出现的，在引入insert buffer之后，对于非聚集索引（也就是普通索引）的插入或者更新操作，不是每一次都直接从磁盘上插入相关数据页，然后插入到数据页中，然后刷盘，而是先判断即将插入的普通索引页是否在缓冲池中，如果该普通索引页已经存在于缓冲池中，则直接进行插入操作；如果该普通索引页不存在于缓冲池中，则innodb会将这些insert操作先存放到缓冲池中，然后再以一定的频率将缓冲池中的insert操作和数据页进行合并。如下图：

步骤一：假设一个页面上保存5条记录，页号为1的数据页此时在缓冲池中，那么一次性插入编号1和编号2两条insert语句，页号为9的数据页不在缓冲池中，那么编号7和编号8的insert语句不着急插入，此时存在于insert buffer中：

步骤二：此时页号为9的数据页存在于缓冲池中，那么编号7~10的insert语句将会和页号为9的数据页进行合并，如下：

将多个在相同索引页中进行的insert操作同时合并到一个操作中，这样就大大减少了IO的次数，如上例子，我们要插入编号1~2,7~10的总计6条记录，本来需要6次的IO操作，因为有了insert buffer，只需要2次IO操作便可以实现，提高了对于普通索引插入的性能。

关于上图的几点疑问：

1、为什么后续的第二个数据页的编号是9而不是2,？

因为在MySQL中，数据页的编号是不连续的，不同的数据页之间是按照双向链表来进行关联的，每个数据页保存了它上一个数据页和下一个数据页的指针，所以数据页号之间不用保持连续性。

2、编号为3~6的语句为什么没有标出？

编号为3~6的语句可能是insert，也可能是update语句，这里的编号不一定非要连续，我们仅仅是为了说明这个insert buffer 的处理过程。

03

使用条件

原理说完之后，我们说说insert buffer的使用需要满足的条件：

1、索引必须是普通索引。

2、索引不是唯一的。

第一条容易理解，如果索引是主键索引，也就是聚集索引，那么由于自增列的存在，插入的时候往往是顺序插入的，索引不会用到插入缓存，第二条，索引不能是唯一的，这是因为如果索引唯一，那么每一次在将数据插入到数据页的时候，都要查询该数据页内的记录以保证记录的唯一性，而这个查找的过程又会有随机读取的情况发生，这样，我们要解决随机读取的问题，又引入了随机读取的问题，那么insert buffer就失去了它应有的意义。

关于insert buffer的信息，我们可以使用下面的语句来查看：

mysql--dba_admin@127.0.0.1 15:53:16>>show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 11, seg size 13, 14 merges
merged operations:
 insert 9, delete mark 11, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 69239, node heap has 11 buffer(s)
Hash table size 69239, node heap has 17 buffer(s)
Hash table size 69239, node heap has 4 buffer(s)
Hash table size 69239, node heap has 39 buffer(s)
Hash table size 69239, node heap has 13 buffer(s)
Hash table size 69239, node heap has 4 buffer(s)
Hash table size 69239, node heap has 5 buffer(s)
Hash table size 69239, node heap has 13 buffer(s)
0.35 hash searches/s, 0.04 non-hash searches/s

关于上面的参数，其中seg size显示了当前insert buffer的大小为13*16K，14merges代表进行了14次合并次数，insert 9代表了插入的记录数。注意，这个结果是截止具体某一时间点的结果。

这里存在一个问题，就是这个insert buffer到底应该设置多大，如果设置的小了，那么在大量插入的情况下，就起不到效果，如果设置的过大，则会影响其他的操作。在MySQL中，默认最大可以占用到1/2的缓冲池内存，在percona mysql中，可以通过调整源码中的ibuf_pool_size_per_max_size的值来进行调整。

04

其他buffer

那么既然有insert buffer，有没有其他的DML操作的buffer呢？答案是有的。InnoDB从1.0.x版本开始，引入了insert buffer，delete buffer和purge buffer，它们统称之为change buffer，当然，这些buffer的适用对象都是非唯一的普通索引。当我们对一条记录进行delete的操作时，具体的过程可以分为两个步骤，第一步是将记录标记为已经删除，第二步是将记录真正删除，其中delete buffer对应的是delete的第一个过程，purge buffer 对应的是delete操作的第二个过程。

Innodb存储引擎提供了参数innodb_change_buffering，这个参数可选的值为inserts，deletes，purges，changes，all、none，其中

changes代表启用了inserts和deletes，all表示启动了所有buffer，none表示都没有启动，默认的参数是all：

mysql--dba_admin 16:01:14>>show variables like '%buffering%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_change_buffering | all   |
+-------------------------+-------+
1 row in set (0.00 sec)

05

insert buffer的具体实现

我们现在大概知道了insert buffer是用来干什么的，以及如何使用的，关于insert buffer具体是怎么实现的，这又是另外一个问题。

insert buffer的本质其实是一棵B+树，而且在5.X中，全局只有一棵B+树，负责对实例中所有的表的普通索引进行插入缓冲，它在物理磁盘上是存放在ibdata中的，这也就是为什么我们使用独立表空间ibd文件恢复表数据的时候，check table会失败了，因为表的索引数据可能还存在于insert buffer中，所以一般情况下，通过ibd文件进行数据恢复之后，还需要使用repair table来重建表上的普通索引。

在下面的情况下，缓冲池中的insert buffer和索引页将会发生merge：

1、当索引页被读取到缓冲池的时候，

2、当插入其他记录的时候发现索引页中已经没有剩余空间的时候，此时会强制进行索引合并

3、Master Thread的定时刷新动作

其他两个注意的点：

第一：当要进行merge的时候，如果表已经被drop掉了，那么会直接丢弃已经被缓存的insert记录。

第二：当insert buffer中的记录很多时，后台线程会将内存中的insert buffer数据临时的刷新到ibdata中进行保存。