change buffer是在其他資料庫中沒有的一個概念，說白了就是一塊系統表空間分配的空間，針對的對象是輔助索引的葉子節點（為什麼不是主鍵索引？因為主鍵索引是聚集索引，在磁碟上的排列是有序的，磁碟的順序IO的性能很高，而隨機IO的性能卻很低）。當輔助索引的值有更新時，將這些更新先緩存起來，當有其他 ...
change buffer是在其他資料庫中沒有的一個概念，說白了就是一塊系統表空間分配的空間，針對的對象是輔助索引的葉子節點（為什麼不是主鍵索引？因為主鍵索引是聚集索引，在磁碟上的排列是有序的，磁碟的順序IO的性能很高，而隨機IO的性能卻很低）。當輔助索引的值有更新時，將這些更新先緩存起來，當有其他應用對相同的頁做更新操作後，對該頁進行整合，最後將整合後的值一起更新到磁碟文件中，減少了磁碟的I/O. change buffer是由InnoDB的系統表空間分配的，雖然叫buffer，但和double write buffer一樣，都是表空間的空間，可以和其他數據頁一樣緩存在buffer pool中。
以下是change buffer相關文檔的翻譯，來自官方文檔。
14.9.4 Configuring InnoDB Change Buffering
When INSERT, UPDATE, and DELETE operations are performed on a table, the values of indexed columns (particularly the values of secondary keys) are often in an unsorted order, requiring substantial I/O to bring secondary indexes up to date. InnoDB has a change buffer that caches changes to secondary index entries when the relevant page is not in the buffer pool, thus avoiding expensive I/O operations by not immediately reading in the page from disk. The buffered changes are merged when the page is loaded to the buffer pool, and the updated page is later flushed to disk. The InnoDB main thread merges buffered changes when the server is nearly idle, and during a slow shutdown.
當對錶進行增刪改操作的時候，由於索引列（尤其是複製索引列）的值總是無序的，更新輔助索引往往需要大量的隨機I/O操作。InnoDB的change buffer會存放一些輔助索引條目，當有DML操作對輔助索引修改時，會先對這些索引頁進行整合，之後一塊刷新到磁碟上。InnoDB的主線程會在系統I/O空閑時或關機時整合這些索引頁。
Because it can result in fewer disk reads and writes, the change buffer feature is most valuable for workloads that are I/O-bound, for example applications with a high volume of DML operations such as bulk inserts.
因為change buffer可以減少磁碟的讀寫，在有大量讀寫綁定的操作上會更體現它的價值，比如說對於批量插入操作的應用來說。
However, the change buffer occupies a part of the buffer pool, reducing the memory available to cache data pages. If the working set almost fits in the buffer pool, or if your tables have relatively few secondary indexes, it may be useful to disable change buffering. If the working set fits entirely within the buffer, change buffering does not impose extra overhead, because it only applies to pages that are not in the buffer pool.
然而，change buffer會占用buffer pool的記憶體空間，這樣就會減少數據頁在記憶體中的緩存。因此對於有輔助索引相關的表操作的時候，change buffer可能會有用，對於工作集都在緩衝池中的操作，change buffer就不會起作用，因為它只應用於索引頁不完全在記憶體中的情況。
You can control the extent to which InnoDB performs change buffering using the innodb_change_buffering configuration parameter. You can enable or disable buffering for inserts, delete operations (when index records are initially marked for deletion) and purge operations (when index records are physically deleted). An update operation is a combination of an insert and a delete. In MySQL 5.5 and higher, the default innodb_change_buffering value is changed from inserts to all.
可以使用參數innodb_change_buffering來對是否使用change buffer來進行控制，5.5及以上的版本中預設值是all.
Permitted innodb_change_buffering values include:
該參數的參數值包含一下：
all
The default value: buffer inserts, delete-marking operations, and purges.
none
Do not buffer any operations.
inserts
Buffer insert operations.
deletes
Buffer delete-marking operations.
changes
Buffer both inserts and delete-marking operations.
purges
Buffer the physical deletion operations that happen in the background.
You can set the innodb_change_buffering parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege. Changing the setting affects the buffering of new operations; the merging of existing buffered entries is not affected.
innodb_change_buffering參數的值可以在配置文件(my.cnf or my.ini)中設置，或者具有SUPER許可權的用戶使用SET GLOBAL命令動態的修改，修改只對以後的操作生效。
可以通過以下命令來監控change buffer。
mysql> SHOW ENGINE INNODB STATUS\G;
-------------------------------------
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 276707, node heap has 1 buffer(s)
15.81 hash searches/s, 46.33 non-hash searches/s
seg size 指segment總的分配大小，以頁為單位；
free list 指空閑數據頁；
size 指已經合併數據頁數量。
