MySQL change buffer介紹和相關引數調整建議

change buffer是MySQL5.5加入的新特性，change buffer是insert buffer的加強，insert buffer只針對insert有效，change buffer對insert、delete、update(delete+insert)、purge都有效。當修改一個索引塊(secondary index)時的資料時，索引塊在buffter pool中不存在，修改資訊就會被cache在change buffer中，當透過索引掃描把需要的索引塊讀取到buffer pool時，會和change buffer中修改資訊合併，再擇機寫回disk。目的還是為了減少隨機IO帶來效能損耗，說明白了：把隨機IO儘量變成順序IO。

Change buffer的主要目的是將對二級索引的資料操作快取下來，以此減少二級索引的隨機IO，並達到操作合併的效果。

change buffer是存放二級索引的沒有在buffer pool的變更頁的快取區，變更的buffer是由insert，update,delete等操作導致的。等頁被載入進buffer pool中後會將change buffer中的頁合併。 
二級索引通常是非唯一的，插入也是很隨機的順序，更新刪除也都不是在鄰近的位置，所以change buffer就避免了很多的隨機io的產生。puge操作會在系統空閒或慢關閉的時候定時將變更頁寫入到磁碟上去。 
change buffer合併在有大量的二級索引頁更新或有很多影響行的情況下會花費很長的時間。change buffer會佔用innodb buffer pool的部分空間，在磁碟上，change buffer會佔用系統表空間，所以在資料庫重啟後，索引變更仍然被快取。在change buffer中被快取的資料可以使用innodb_change_buffering控制，我們也可以調整innodb_change_buffer_max_size配置change buffer的大小。



索引對insert的影響


　　1、表insert，對應表上的所有索引都需要insert；


　　2、假設這些索引不常使用，容易產生物理讀；


　　3、索引的順序和表的順序完全不一致；


　　原則：一個表上的索引最好不超過6個


將對索引的更新記錄存入insert buffer中，而不是直接調入索引頁進行更新；擇機進行merge insert buffer的操作，將insert buffer中的記錄合併(merge)到真正的輔助索引中。


解決了insert表資料產生過多物理讀的問題。

Insert Buffer 何時 merge


有2種情況innodb會merge Insert Buffer 到磁碟 


1)，master loop 執行緒主動merge


若過去1s之內發生的I/O，小於系統I/O能力的5%，則主動進行一次Insert buffer的merge操作。Merge的頁面數為系統I/O能力的5%，每10s，必定觸發一次insert buffer merge動作.


Merge的頁面數仍舊為系統I/O能力的5% (系統能力指innodb_io_capacity)


2),讀取資料發現該page在Insert Buffer還沒有被merge,那麼innodb首先會做一個merge操作，所以此時讀取的速度會降低


在系統負載較高時，merge操作會很少，而在系統比較空閒時，merge操作會非常頻繁，所以當你的系統突然負載變低時，DB還有可能會進行很長一段時間的IO操作。


merge insert buffer的操作可能發生在什麼情況下：

　　在merge insert buffer之前，insert buffer資料是存在記憶體中，為了防止資料庫意外當機導致資料丟失，系統會週期性將insert buffer資料寫入共享表空間中。


　　1、輔助索引頁被讀取到buffer pool中

　　　　例如這在執行正常的select查詢操作，索引頁被調入記憶體，該索引頁對應在insert buffer中的索引更改記錄就會發生merge操作。

　　2、insert buffer bitmap頁追蹤到該輔助索引頁已無可用空間時

　　　　存於ibd檔案中(表資料檔案)

　　　　記錄每一個索引頁在insert buffer中對應的行數


如何看insert buffer的效果

　　1、insert buffer所佔空間，佔比太高就影響緩衝效能

　　2、每次merge處理的資料量

　　　　1)、merges如果很高，說明insert buffer調小了，也說明索引建多了；

　　　　2)、對錶進行批次IDU的時候，可能會導致insert buffer迅速增加。



關注change buffer在innodb buffer pool中的佔比

mysql> show variables like '%change_buffer%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering       | all   |
+-------------------------------+-------+
2 rows in set (0.01 sec)


　　1、innodb_change_buffer_max_size：表示change buffer在buffer pool中的最大佔比，預設25%，最大50%

　　2、innodb_change_buffering：表示索引列merge物件，all表示對IDU索引列都起作用，都進行merge，如果只想對insert索引列進行merge，就把all改為inserts。


調整建議：

　　1、如果系統中有嚴重的insert、update並且還有活躍的delete時，就增大max_size；

　　2、針對不更改資料的純報表系統，可以減小該引數值。