mysql写缓冲_MySQL插入缓冲

上一篇文章中讲解了MySQL Innodb存储引擎的聚集索引和非聚集索引的区别以及优缺点(MySQL聚集索引和非聚集索引)；Innodb数据表本身就是一个聚集索引表，表中的记录按照聚集索引顺序存储，插入时按聚集索引自增插入，顺序写磁盘，速度是有保证的；但对于非聚集索引，插入操作就不那么顺利了，非聚集索引并非按顺序插入，因此在插入非聚集索引叶节点时，为随机插入，性能不高；

举个例子：

create table student (

`id` INT UNSIGNED AUTO_INCREMENT,

`name` VARCHAR(255),

PRIMARY KEY(`id`),

KEY(`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入两条记录：

insert into student (name) values('Alice');

insert into student (name) values('Jack');

这两条记录id是连续自增的，append操作，因此插入聚集索引页(真实记录存储页)很快；但是name作为非聚集索引，也要将非聚集索引记录插入到非聚集索引页，'Alice'和'Jack'两条记录在非聚集索引中并不相邻，随机插入磁盘，效率很低，影响数据库插入性能。但这并不是name字段上非聚集索引的错误，而是B+树的特性决定了非聚集索引插入的离散性；

插入缓冲机制

为此Innodb引入了大名鼎鼎的插入缓冲机制，虽然该缓冲不止针对插入操作，对于删除、更新操作同样有效。对于非聚集索引的插入或更新操作，不是每一次操作都直接插入索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入；如果不在，则先放入一个插入缓冲区中，返回插入成功的结果。然后由master thread以一定的频率执行插入缓冲和非聚集索引叶子节点的合并操作，这时通常能将多个插入合并到一个IO操作中(因为多个插入在同一个索引叶节点中)，提高了对非聚集索引的插入和更新性能，MySQL官方手册给出的优化结果，采用插入缓冲性能提升15倍；类似于RocksDB的compact操作(RocksDB学习之compaction)

插入缓冲条件

插入缓冲的使用需要满足以下两个条件：

(1)索引是非聚集索引；

(2)索引并非唯一索引；

解释下为什么必须是非唯一的副主索引，如果是唯一索引，在插入时，需要查找辅助索引树，判断该记录是否存在以确保唯一性，查找过程就是随机读的过程，因此插入缓冲就没意义了。

插入缓冲带来的问题

(1)应用程序执行大量的插入和更新操作时，如果这个过程中数据库发生了宕机，会存在大量的插入缓冲未合并到实际的非聚集索引页中，如果这样，导致实例恢复时间变长，并且恢复会占用大量的磁盘IO。

(2)写密集的情况下，插入缓冲占用过多的缓冲池内存，默认情况下最大可以占用1/2的缓冲池内存，可能会对其他操作带来影响。

参考文章：

《MySQL技术内幕：InnoDB存储引擎》