新增或修改记录时,该表的普通索引(非唯一非主键)很多时候也会要做修改。
它的叶子节点的插入很大几率上是 离散随机的,非顺序操作,效率低。
通过 SHOW ENGINE INNODB STATUS 可以看到 Insert Buffer的相关信息:
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0merges
merged operations:insert 0, delete mark 0, delete 0discarded operations:insert 0, delete mark 0, delete 0
我们看看别人的例子:
Ibuf: size 1, free list len 7653, seg size 7655, 41697181merges
merged operations:insert 38494474, delete mark 24945714, delete 3144032discarded operations:insert 0, delete mark 0, delete 0
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 插入量特别大。没有起到很好的作用