怎么理解MySQL change buffer

本篇內(nèi)容介紹了“怎么理解MySQL change buffer”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠?qū)W有所成！

change buffer是MySQL5.5加入的新特性，change buffer是insert buffer的加強，insert buffer只針對insert有效，change buffer對insert、delete、update(delete+insert)、purge都有效。當修改一個索引塊(secondary index)時的數(shù)據(jù)時，索引塊在buffter pool中不存在，修改信息就會被cache在change buffer中，當通過索引掃描把需要的索引塊讀取到buffer pool時，會和change buffer中修改信息合并，再擇機寫回disk。目的還是為了減少隨機IO帶來性能損耗，說明白了：把隨機IO盡量變成順序IO。
Change buffer的主要目的是將對二級索引的數(shù)據(jù)操作緩存下來，以此減少二級索引的隨機IO，并達到操作合并的效果。
change buffer是存放二級索引的沒有在buffer pool的變更頁的緩存區(qū)，變更的buffer是由insert，update,delete等操作導致的。等頁被加載進buffer pool中后會將change buffer中的頁合并。 
二級索引通常是非唯一的，插入也是很隨機的順序，更新刪除也都不是在鄰近的位置，所以change buffer就避免了很多的隨機io的產(chǎn)生。puge操作會在系統(tǒng)空閑或慢關閉的時候定時將變更頁寫入到磁盤上去。 
change buffer合并在有大量的二級索引頁更新或有很多影響行的情況下會花費很長的時間。change buffer會占用innodb buffer pool的部分空間，在磁盤上，change buffer會占用系統(tǒng)表空間，所以在數(shù)據(jù)庫重啟后，索引變更仍然被緩存。在change buffer中被緩存的數(shù)據(jù)可以使用innodb_change_buffering控制，我們也可以調(diào)整innodb_change_buffer_max_size配置change buffer的大小。

索引對insert的影響

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

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

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

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

將對索引的更新記錄存入insert buffer中，而不是直接調(diào)入索引頁進行更新；擇機進行merge insert buffer的操作，將insert buffer中的記錄合并(merge)到真正的輔助索引中。
解決了insert表數(shù)據(jù)產(chǎn)生過多物理讀的問題。
Insert Buffer 何時 merge
有2種情況innodb會merge Insert Buffer 到磁盤 
1)，master loop 線程主動merge
若過去1s之內(nèi)發(fā)生的I/O，小于系統(tǒng)I/O能力的5%，則主動進行一次Insert buffer的merge操作。Merge的頁面數(shù)為系統(tǒng)I/O能力的5%，每10s，必定觸發(fā)一次insert buffer merge動作.
Merge的頁面數(shù)仍舊為系統(tǒng)I/O能力的5% (系統(tǒng)能力指innodb_io_capacity)

2),讀取數(shù)據(jù)發(fā)現(xiàn)該page在Insert Buffer還沒有被merge,那么innodb首先會做一個merge操作，所以此時讀取的速度會降低

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

merge insert buffer的操作可能發(fā)生在什么情況下：
　　在merge insert buffer之前，insert buffer數(shù)據(jù)是存在內(nèi)存中，為了防止數(shù)據(jù)庫意外宕機導致數(shù)據(jù)丟失，系統(tǒng)會周期性將insert buffer數(shù)據(jù)寫入共享表空間中。
　　1、輔助索引頁被讀取到buffer pool中
　　　　例如這在執(zhí)行正常的select查詢操作，索引頁被調(diào)入內(nèi)存，該索引頁對應在insert buffer中的索引更改記錄就會發(fā)生merge操作。
　　2、insert buffer bitmap頁追蹤到該輔助索引頁已無可用空間時
　　　　存于ibd文件中(表數(shù)據(jù)文件)
　　　　記錄每一個索引頁在insert buffer中對應的行數(shù)

如何看insert buffer的效果
　　1、insert buffer所占空間，占比太高就影響緩沖性能
　　2、每次merge處理的數(shù)據(jù)量
　　　　1)、merges如果很高，說明insert buffer調(diào)小了，也說明索引建多了；
　　　　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。

調(diào)整建議：
　　1、如果系統(tǒng)中有嚴重的insert、update并且還有活躍的delete時，就增大max_size；
　　2、針對不更改數(shù)據(jù)的純報表系統(tǒng)，可以減小該參數(shù)值。

“怎么理解MySQL change buffer”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識可以關注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！