MySQL：insert 将IOPS打满之insert buffer[change buffer]

背景：尊敬的xx您好 , 您的RDS实例xx MySQL(RDS) iops使用率于2018-03-08 10:05:09出现异常, 当前值100.0
1：IOPS

2:大量的输入流量：可能是由于DML造成的，那么我们通过show processlist 查看，全部都是 insert 语句

3：通过mysql_dml 查看

4：MySQL_ROWDML

5：存储使用量

6：表结构：此表是分库分表，通过客户了解这个表数据分布并不均匀，而且此表数据很大
CREATE TABLE xxx_xx_048 (
ID bigint(20) NOT NULL AUTO_INCREMENT,
GMT_CREATE datetime NOT NULL COMMENT '创建时间',
GMT_MODIFIED datetime DEFAULT NULL COMMENT '修改时间',
ROUTE_TYPE varchar(32) DEFAULT NULL COMMENT '路由类型',
SOURCE_ID varchar(64) DEFAULT NULL COMMENT '源id',
TARGET_ID varchar(64) DEFAULT NULL COMMENT '目的id',
PRIMARY KEY (ID),
UNIQUE KEY SOURCE_ID (SOURCE_ID,ROUTE_TYPE,TARGET_ID),
KEY idx_source_id (SOURCE_ID)
) ENGINE=InnoDB AUTO_INCREMENT=9526594 DEFAULT CHARSET=utf8
通过监控以及show processlist insert批量造成IOPS打满，那么insert 对数据有哪些操作呢？数据库哪些处理机制？会那么消耗IO呢？

那么我们来介绍下insert buffer：
1、概念：
在5.5加入了新特性目前称为 change buffer. change buffer是一种特殊的数据结构，当受到影响的行并没有在buffer pool中,缓存对辅助索引页面更改时，需要将page加载缓冲池进行合并[插入辅助索引相对是顺序发生的除和更新可能影响不邻近位于索引树中的辅助索引页。 随后在受影响的页面被其他操作读入缓冲池时合并缓存的更改，避免从磁盘读入辅助索引页所需的大量随机访问I / O ]。这些操作包含INSERT，UPDATE，DELETE。
2、目的：对于未非唯一索引，辅助索引的修改操作并非实时更新索引的叶子页，而是把若干对同一页面的更新缓存起来做合并为一次性更新操作，转换随机IO为顺序IO，这样可以避免IO带来的性能损耗，提高数据库的写性能。
3、特性：
insert buffer只适应于辅助索引

a.primary key:是按照递增的顺序插入是顺序IO
b.unique index:insert unique 时候需要检查这个记录是否存在,所以修改唯一索引之前，还需要将相关的索引页读取出来才知道是否唯一。如果使用唯一索引使用change buffer的话就会将索引数据切分成2部分，那么就是无法来保证唯一性了。那么这就不是insert buffer的要做的事情的，那么这么insert buffer也就没有意义了。insert unique的时候：随机IO

4、处理机制：

  为了解决这个问题，那么innodb就有了change buffer的技术，对辅助索引的insert,update 
  a.先判断这一页是否在缓冲池中，在：直接插入，不在，将索引页缓存到insert buffer 中。使用master thread 的调度处理进行将索引页合并。这时就可以将同一个索引页中的多个插入合并到一个IO操作中，大大提高的写的性能。

5、处理机制中设计到了master thread 的调成机制需要同学自行了解下。

4：那么我们可以通过show innodb status

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 7653, seg size 7655, 41697181 merges
merged operations:
insert 38494474, delete mark 24945714, delete 3144032
discarded operations:
insert 0, delete mark 0, delete 0
AHI PARTITION 1: Hash table size 2334679, node heap has 2 buffer(s)
AHI PARTITION 2: Hash table size 2334679, node heap has 308 buffer(s)
AHI PARTITION 3: Hash table size 2334679, node heap has 2 buffer(s)
AHI PARTITION 4: Hash table size 2334679, node heap has 303 buffer(s)
AHI PARTITION 5: Hash table size 2334679, node heap has 3 buffer(s)
AHI PARTITION 6: Hash table size 2334679, node heap has 318 buffer(s)
AHI PARTITION 7: Hash table size 2334679, node heap has 2 buffer(s)
AHI PARTITION 8: Hash table size 2334679, node heap has 306 buffer(s)
0.05 hash searches/s, 0.00 non-hash searches/s

解释：1.size 1 :正在使用的page
2.free list len 7653 ==》空闲的page
3.seg size 显示当前插入缓冲的大小 (1 + 7655 )*16KB
4.merges :41697181 合并数
5.merged operations:
insert 38494474, 插入的记录数。
delete mark 打上的标记 24945714，
delete 删除的次数3144032
6.insert buffer的效果 = merges/(insert + delete mark + delete)=
41697181/(38494474 + 24945714 + 3144032) = 62% 相当于每1.8次更新合并1次。也就是说明insert 插入量特别大。没有起到很好的作用

总结：
从上面的介绍我们知道insert table 为分库分表，分表接近1024个,那么数据在存储的时候，一个表会将所有的数据块链接在一起。此场景是分表太多，每个表就会链接各自的block 或page。各个表间存储是离散的，那么针对这么多的table进行批量插入时，需要找磁盘上各个表，再找具体的block，会产生大量的IO。而insert buffer只是对辅助索引cache。唯一索引会产生大量的IO，此次将IOPS打满的原因：1.分表太多insert 需要找各自相关的block，产生大量离散IO 2.unique key 产生离散IO 3.每1.8次更新和并1次操作说明insert插入量很大，并没有起到特别好的效果。

主要原因：还是数据比较离散，insert太大，才会造成上述的问题。

各个理解：欢迎一起讨论