什么是Change Buffer？
Change Buffer是一种用于缓存二级索引页变化的特殊数据结构，是缓冲池中一块独立的区域。当需要修改的二级索引页不在缓冲池中而在磁盘中时，会将这些索引页的变化缓存在change buffer中。
Change Buffer
为什么二级索引需要Change Buffer？
-
减少随机读取IO
对于聚集索引页，DML操作带来的数据磁盘读取和缓存修改往往是顺序的、集中的，这就很好地利用了聚集索引的优点，但随着聚集索引页的顺序IO，各个二级索引页的读取和修改通常是随机IO的。而Change buffer将分散的、未缓存的二级索引页的变化进行缓存而非直接从磁盘中读取索引页，待到其他操作需要访问二级索引时，只需将change buffer中的变化缓存合并然后加入缓冲池就行了，这样就大大地减少了磁盘随机读取的IO。
-
定期批量写入索引变化
为了避免change buffer使用过多，二级索引页的变化也必须定期地写入磁盘，这个操作叫做purge，相较于立即将索引页变化写入磁盘而言，这种批量的写入自然是更加高效。InnoDB主线程会在服务器空闲时进行purge操作
但需要注意的是，当需要更新的二级索引页过多时，change buffer的purge操作可能需要花费几个小时，期间，磁盘IO会增加，可能会严重影响磁盘IO密集的查询。合理配置change buffer可避免这种情况的发生。
如何配置change buffer？
可以用innodb_change_buffering系统变量控制change buffer相关的行为。可以控制change buffer只对部分类操作生效。该变量的值和含义如下：
-
all
默认值，表示缓存所有DML语句和物理purge操作的索引页变化。
-
none
不对任何操作缓存，相当于禁用
-
inserts
只缓存insert
-
deletes
只缓存标记性delete操作
-
changes
只缓存写入和标记性delete
-
purges
只缓存后台物理删除操作
注意：
innodb_change_buffering参数可动态修改，只对修改后发生的操作生效，修改前已缓存的索引页不受影响
可以用innodb_change_buffer_max_size变量控制change buffer区域的最大大小，表示占缓冲池总大小的百分点数。默认为25。最大可以设置为50。
当DML语句量较多时，可以适当扩大innodb_change_buffer_max_size.反则反之。
如何监控Change buffer的使用？
-
show engine innodb status命令可用于查看change buffer的使用情况，包含于INSERT BUFFER AND ADAPTIVE HASH INDEX部分：
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hash searches/s, 202.47 non-hash searches/s
Ibuf: size 1, free list len 0, seg size 2, 0 merges
size为当前使用的change buffers页数，seg size表示change buffer分配的总页数。
- 也可以用下面的SQL查看change buffer更详细的使用统计
select * from information_schema.innodb_metrics
where subsystem like 'change_buffer'\G
*************************** 1. row ***************************
NAME: ibuf_merges_insert
SUBSYSTEM: change_buffer
COUNT: 198469
MAX_COUNT: 198469
MIN_COUNT: NULL
AVG_COUNT: 0.13473886448652062
COUNT_RESET: 198469
MAX_COUNT_RESET: 198469
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2019-07-30 16:04:56
TIME_DISABLED: NULL
TIME_ELAPSED: 1472990
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of inserted records merged by change buffering
*************************** 2. row ***************************
NAME: ibuf_merges_delete_mark
SUBSYSTEM: change_buffer
COUNT: 35361
MAX_COUNT: 35361
MIN_COUNT: NULL
AVG_COUNT: 0.02400627295500988
COUNT_RESET: 35361
MAX_COUNT_RESET: 35361
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2019-07-30 16:04:56
TIME_DISABLED: NULL
TIME_ELAPSED: 1472990
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of deleted records merged by change buffering
*************************** 3. row ***************************
NAME: ibuf_merges_delete
SUBSYSTEM: change_buffer
COUNT: 875
MAX_COUNT: 875
MIN_COUNT: NULL
AVG_COUNT: 0.0005940298304808587
COUNT_RESET: 875
MAX_COUNT_RESET: 875
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2019-07-30 16:04:56
TIME_DISABLED: NULL
TIME_ELAPSED: 1472990
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of purge records merged by change buffering
*************************** 4. row ***************************
NAME: ibuf_merges_discard_insert
SUBSYSTEM: change_buffer
COUNT: 0
MAX_COUNT: 0
MIN_COUNT: NULL
AVG_COUNT: 0
COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2019-07-30 16:04:56
TIME_DISABLED: NULL
TIME_ELAPSED: 1472990
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of insert merged operations discarded
*************************** 5. row ***************************
NAME: ibuf_merges_discard_delete_mark
SUBSYSTEM: change_buffer
COUNT: 0
MAX_COUNT: 0
MIN_COUNT: NULL
AVG_COUNT: 0
COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2019-07-30 16:04:56
TIME_DISABLED: NULL
TIME_ELAPSED: 1472990
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of deleted merged operations discarded
*************************** 6. row ***************************
NAME: ibuf_merges_discard_delete
SUBSYSTEM: change_buffer
COUNT: 0
MAX_COUNT: 0
MIN_COUNT: NULL
AVG_COUNT: 0
COUNT_RESET: 0
MAX_COUNT_RESET: 0
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2019-07-30 16:04:56
TIME_DISABLED: NULL
TIME_ELAPSED: 1472990
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of purge merged operations discarded
*************************** 7. row ***************************
NAME: ibuf_merges
SUBSYSTEM: change_buffer
COUNT: 178009
MAX_COUNT: 178009
MIN_COUNT: NULL
AVG_COUNT: 0.12084874982179106
COUNT_RESET: 178009
MAX_COUNT_RESET: 178009
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2019-07-30 16:04:56
TIME_DISABLED: NULL
TIME_ELAPSED: 1472990
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Number of change buffer merges
*************************** 8. row ***************************
NAME: ibuf_size
SUBSYSTEM: change_buffer
COUNT: 1
MAX_COUNT: 1
MIN_COUNT: NULL
AVG_COUNT: 0.0000006788912348352671
COUNT_RESET: 1
MAX_COUNT_RESET: 1
MIN_COUNT_RESET: NULL
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2019-07-30 16:04:56
TIME_DISABLED: NULL
TIME_ELAPSED: 1472990
TIME_RESET: NULL
STATUS: enabled
TYPE: status_counter
COMMENT: Change buffer size in pages
8 rows in set (0.01 sec)
-
information_schema.innodb_buffer_page系统表中存放的是每个缓冲池中的页的元数据，也可以用于统计change buffer的使用情况：
mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
(SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
(SELECT ((change_buffer_pages/total_pages)*100))
AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
| 25 | 8192 | 0.3052 |
+---------------------+-------------+-------------------------------+
