【MySQL笔记】InnoDB的插入缓存+非聚簇索引插入的离散性理解

为什么需要插入缓存Insert Buffer

磁盘中的主键索引由于天然自增，无须磁盘的随机 I/O，只需不断追加。但普通索引大概率无序，默认情况下需要进行随机磁盘 I/O 操作，效率极差

磁盘随机IO：B+数索引是按页存储的，随机IO就是随机访问页中的数据

因此需要插入缓存（Insert Buffer）解决普通索引插入效率低下的问题

非聚簇索引插入的离散性

《MySQL技术内幕》中有这样一句话”B+树的特性决定了非聚集索引插入的离散性“，下面先稍微理解下。

create table tb_user ( 
		id int auto_increment, 
		name varchar(20),
		age int unsigned,
		primary key (id),
		key(age)
 ) engine=innodb  default charset=utf8;

上面建的表中有三个字段：

• id：自增主键
• age：普通key，等于是非聚集索引
• name：普通字段

往里面插入几条数据后如下：

此时，如果插入主键索引（递增），由于主键索引是有序递增的，连续插入的操作是连续的，比如id=6，7，8都依次插入到最后就行，B+树结构也不太会受影响（因为主键索引叶子节点也是按主键顺序存储），也不需要额外的磁盘随机IO，比较快。

如果插入非聚簇索引（二级索引）age的值，由于age列的数据是无序的，无法做到连续的索引插入到连续的行中，比如age=21， 22， 23，只能一条一条地定位和插入，这就是“离散”。在非聚簇索引的B+树中，叶子节点存储的是非聚簇索引与主键索引的值，虽然排序是按照age的大小，但插入操作并不是只考虑这个顺序，还是需要搜索定位到特定的**数据页（innoDB 数据页就是主键索引b+树的叶子节点，按主键索引顺序存储）**再插入，插入后还要考虑数据页容量大小决定是否分裂。

总之，主键索引由于自增特性，插入操作也是连续的，比较快；非聚簇索引字段在数据页中可能是无序，插入需要定位对应的数据页（主键索引B+树叶子结点），因此插入操作是离散的，相对较慢。

Insert Buffer

对于满足以下条件的索引：

• 非聚簇索引：因为聚簇索引，或者说主键索引一般是自增的且唯一的，插入操作连续，不存在非聚簇索引的问题
• 索引不唯一：索引唯一的话，每次插入还需要通过磁盘随机I/O判断插入索引值是否已存在

不直接插入到磁盘的索引叶中，先判断对应的非聚簇索引是否在Buffer Pool中，如果在则直接插入，否则先放入Insert Buffer中，然后以一定频率和辅助索引页子节点进行merge insert buffer，此时通常能将多个插入合并到一个操作中，因此能提高插入性能。

查看Insert Buffer信息

SHOW ENGINE INNODB STATUS

INSERT BUFFER AND ADAPTIVE HASH INDEX 下面就是insert buffer的信息，包括：

• seg size ：当前Insert Buffer的大小，就是多少个数据页（insert buffer本身就是一个B+树，一个数据页16KB）
• free listlen ：空闲列表的长度
• size ：已经合并记录页的数量
• Inserts ：插入的记录数
• merged recs ：合并的插入记录数量
• merges ：合并的次数，也就是实际读取页的次数

Insert Buffer的问题

因为Insert Buffer信息也存储在buffer pool中，在写密集的情况下，可能会占用过多的buffer pool（默认最大可以占用到1/2），影响其他使用buffer pool的操作

Change Buffer

就是MySQL 5.5 之后的升级版本，可以理解为对所有的表更改操作（INSERT、DELETE、UPDATE）都做buffer，包括：

• Insert Buffer
• Delete Buffer
• Purgebuffer

原来的Insert Buffer只对插入操作做buffer

总结

Insert Buffer 就是用于提升非聚集索引页的插入性能的，其数据结构类似于数据页的一个B+树，物理存储在共享表空间ibdata1中 。

Reference

https://cloud.tencent.com/developer/article/1735580

https://segmentfault.com/q/1010000013687775

https://zhuanlan.zhihu.com/p/333167012