理解change buffer(insert buffer)
Change Buffer的作用：
- 作用于不在缓存池中的非唯一索引页
- 对该页进行写操作后并不会立刻将该页从磁盘中加载到缓存池，而是在change buffer中记录页的修改，等该页被读取时，再将修改的数据merger到缓存池中；
- 以此来缓解写操作多次随机读的IO消耗；
- 当索引中包含降序索引或者主键中包含降序索引时不会用到change buffer；
- 当change buffer大小超过最大阈值后不会再使用change buffer；
merge策略：
- 被动merge：当用户线程主动发起change buffer中索引页的读取操作时，会被动的将页的修改记录merge到原始索引页中；
- 主要merge：master thread定期将change buffer中的记录随机刷回索引页，如果每秒master thread有空闲I/O能力，则刷5个缓存索引页，每10秒不管I/O压力都会刷5个缓存的索引页记录；
相关参数：
innodb_change_buffer_max_size = 25 #默认25表示change buffer最大可以占用innodb buffer的25%，最大可设置的值为50
innodb_change_buffering = all #默认all表示所有的非唯一普通索引页写入都使用change buffer
all
none #关闭change buffer
inserts #缓冲插入操作
deletes #缓冲删除操作
changes #缓冲插入和删除操作
purges #缓冲在后台发生的物理删除操作
相关监控信息：
show engine innodb status\G
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3077, seg size 3079, 3338271 merges
merged operations:
insert 3679942, delete mark 333798, delete 1737
discarded operations:
insert 0, delete mark 0, delete 0
#size 1 正在使用的page
#free list len 3077 空闲的page
#seg size 3079 当前change buffer大小3079*16K
#3338271 merges 合并的数目
#insert 3679942 通过change buffer插入的数目
#delete mark 333798 通过change buffer删除的数目
#delete 1737 通过change buffer purge的数目
#change buffer的效果=merges/(insert+delete mark+delete)结果越小说明change buffer对性能提升越有利
mysql> select NAME,COUNT,MAX_COUNT,MIN_COUNT,AVG_COUNT,COMMENT from information_schema.innodb_metrics where name like '%ibuf%';
+-----------------------------------------+---------+-----------+-----------+--------------------------+-------------------------------------------------------------+
| NAME | COUNT | MAX_COUNT | MIN_COUNT | AVG_COUNT | COMMENT |
+-----------------------------------------+---------+-----------+-----------+--------------------------+-------------------------------------------------------------+
| buffer_page_read_index_ibuf_leaf | 0 | NULL | NULL | NULL | Number of Insert Buffer Index Leaf Pages read |
| buffer_page_read_index_ibuf_non_leaf | 0 | NULL | NULL | NULL | Number of Insert Buffer Index Non-Leaf Pages read |
| buffer_page_read_ibuf_free_list | 0 | NULL | NULL | NULL | Number of Insert Buffer Free List Pages read |
| buffer_page_read_ibuf_bitmap | 0 | NULL | NULL | NULL | Number of Insert Buffer Bitmap Pages read |
| buffer_page_written_index_ibuf_leaf | 0 | NULL | NULL | NULL | Number of Insert Buffer Index Leaf Pages written |
| buffer_page_written_index_ibuf_non_leaf | 0 | NULL | NULL | NULL | Number of Insert Buffer Index Non-Leaf Pages written |
| buffer_page_written_ibuf_free_list | 0 | NULL | NULL | NULL | Number of Insert Buffer Free List Pages written |
| buffer_page_written_ibuf_bitmap | 0 | NULL | NULL | NULL | Number of Insert Buffer Bitmap Pages written |
| ibuf_merges_insert | 3681684 | 3681684 | NULL | 0.9494142111810469 | Number of inserted records merged by change buffering |
| ibuf_merges_delete_mark | 333962 | 333962 | NULL | 0.08612044618561636 | Number of deleted records merged by change buffering |
| ibuf_merges_delete | 1740 | 1740 | NULL | 0.0004487024762187688 | Number of purge records merged by change buffering |
| ibuf_merges_discard_insert | 0 | 0 | NULL | 0 | Number of insert merged operations discarded |
| ibuf_merges_discard_delete_mark | 0 | 0 | NULL | 0 | Number of deleted merged operations discarded |
| ibuf_merges_discard_delete | 0 | 0 | NULL | 0 | Number of purge merged operations discarded |
| ibuf_merges | 3340164 | 3340164 | NULL | 0.8613447458487284 | Number of change buffer merges |
| ibuf_size | 1 | 699 | NULL | 0.0000002578749863326257 | Change buffer size in pages |
| innodb_ibuf_merge_usec | 0 | NULL | NULL | NULL | Time (in microseconds) spent to process change buffer merge |
+-----------------------------------------+---------+-----------+-----------+--------------------------+-------------------------------------------------------------+
change buffer局限：
- 需要作用于非唯一索引，因为非唯一索引不要校验数据重复；
- change buffer会占用一定的innodb buffer空间；
