全網最清楚的：MySQL的insert buffer和change buffer 串講

一、前言

終於《為研發同學同學定製的MySQL面試指南》第30篇更新來啦～

說來話長，都說Baidu是養老廠，結果偏偏幹出了pdd的感覺。最近工作確實比較忙，然後週六日又想放鬆一下接連好多週六日都和同學出去遊玩。

立個flag吧！後續的更新進度做到每週至少一更。歡迎關注白日夢，乾貨分享不斷～

好！開始啦，做了這麼久研發的你，有沒有聽別人說過、或者在哪裡見過insert buffer 、change buffer呢？這篇文章我們一起閒聊一下MySQL的insert buffer、change buffer，徹底揭開這兩個名詞的面紗！

二、問題引入

在白日夢看來，如果你想更好的理解 insert buffer、change buffer。首先你的先掌握一些前置的知識，比如MySQL索引的相關知識。所以不要著急，我們一點點展開話題，從你資料的知識過度到insert buffer、change buffer上去，你會發現豁然開朗。

2.1、聚簇索引

首先我們回顧一下MySQL的聚簇索引，這個東西大家肯定不陌生吧！我打賭在做的各位面試前都會背一背什麼是聚簇索引。

大家可以看下面這張圖，它就是對B+tree的抽象。它有很多特性，在這篇文章中只需要知道如下幾個就好了

它是一個B+tree。
我們管這棵樹的葉子結點叫做資料頁。
葉子結點中儲存的資料行是一個全集，怎麼解釋這個全集呢？比如資料表就3列，id、name、age。所謂的全集就是說：每行資料都有id、name、age這3列。
我們管非葉子結點叫做索引頁。

而且我跟大家講哦，這棵B+tree是會被儲存在Disk中的。如果你不能很好的理解的話，可以讀一下下面的兩端話：

比如一條update sql想修改id = 999的資料行，那它會怎麼運算元據頁呢？簡單來說就是：首先會檢查一下buffer pool中有沒有包含這條資料的資料頁。如果有的話，直接update。如果沒有的話進行一次磁碟IO把該資料頁載入進記憶體，然後將其update。然後這時的資料頁也就變成了髒頁。等後續其它機制將該資料頁重新整理回Disk。完成記憶體和資料。

讀上面的這段話，你要重點感受一下：資料頁從磁碟到Buffer Pool中的這個過程（最終會被掛載在B+tree的葉子結點上）

其實你類比著資料頁來看，對於B+tree的非葉子結點來說也是一樣的。上面我說了，我們管非葉子結點叫做索引頁。為啥這樣說呢？其實本質上非葉子結點也是資料頁，只不過它裡面儲存的資料是索引資料。而且和普通的資料頁一樣，當你需要它而且它還不在記憶體中時，進行磁碟操作將其讀取記憶體中。

2.2、普通索引

普通的索引也就是我們常說的二級索引、聯合索引等等。

比如我們將name列設定成index的話，那麼MySQL就會為我們這個索引單獨建立一個B+Tree。（是的！它是獨立於主鍵索引之外的另一顆B+Tree）。而且你注意一下如下幾點：

和聚簇索引一樣的是，我們管它的非葉子結點叫做：索引頁
它的葉子結點中儲存的並不是所有的列的全集。比如我們對name列建立索引，那麼它的葉子節點中儲存的就是id、name兩列。並會按照name排序。

三、change buffer存在的意義

瞭解了上面的索引相關的前置知識點再來看insert buffer和change buffer那其實就很簡單了。

我們這一小節來看一下change buffer存在的意義：

其實說白了其實insert buffer也好，還是change buffer也好，它們其實就是MySQL在我們對非唯一的二級索引進行DML（刪除行、寫入行、修改行）操作時作出的優化邏輯。目的就是讓MySQL的效能更好。

比如還是我們這個例子：表裡面有3列。id、name、age。然後id是主鍵、name是非唯一的二級索引。

一條update sql：update xx set name = "賜我白日夢" where name = “白日夢”打過來之後，執行流程大概就像下面這樣：

1、檢查需要被update的資料是否在buffer pool中。

2、如果在buffer pool中直接將其update。

3、如果不在buffer pool中，進行磁碟的IO操作，將其讀取記憶體中，再把它update。

現在的問題是，name列是個索引列。上文也說了，既然是索引列就意味著需要為它單獨建立一顆B+Tree。

那你的update sql要做修改，那是不是會分成兩個大的步驟

1、Step1: 對buffer pool中的資料頁中的資料進行update。

2、Step2: 維護為name單獨建立的B+Tree。

你想呀既然MySQL要優化我們對非唯一的二級索引的DML操作，肯定要有個需要優化的點吧！

而這裡的Step2，就是insert buffer和change buffer 存在的意思所在！

為啥這樣說呢？因為在本篇文章的開頭我們提到了，B+tree也是儲存在Disk中的，那它肯定就難免發生隨機磁碟IO。

或者你想一下：你只是想update 幾條資料。假設運氣很不好這幾條都沒有在buffer pool中。那沒辦法，我們只能去讀磁碟。但是更不巧的，涉及到的二級索引頁竟然也沒有在記憶體中，我們竟然還要同步等待這一次隨機磁碟IO！！！

四、再看change buffer

change buffer的本質上其實也是一塊記憶體。

比如你的：insert、delete、update等DML操作需要用到的二級索引頁（注意是二級索引頁，具體就比如說為name列這個二級索引建立的B+Tree的葉子節點，而不是Buffer pool中的普通資料頁）

就是當這些二級索引頁不在記憶體中時，你對它們的操作會被快取在change buffer中（目的是省去這次隨機的磁碟IO）。等之後MySQL空閒了、或者是MySQL關閉前、或者是有讀取操作時再將這部分快取操作merge到B+Tree中。

五、change buffer 的限制

這個現實其實已經說過了

1、首先得要求是二級索引。如果不是二級索引到話，那前面change buffer存在意義又是什麼呢？沒有啥可優化的地方。那不如不要這個change buffer

2、要求二級索引不能唯一。這個很好理解。如果name列是唯一的。那我每次insert 之前是不是都必須去看下記憶體、Disk上到底有沒有已經存在的相同值的索引。這也就意味著這個insert 操作其實是不能被快取的！必須立即知道到底能否insert 成功。對吧！不這樣的話，你打算返回給客戶端什麼結果呢？

六、change buffer 相關引數

引數：innodb_change_buffer_max_size

作用：控制change buffer能佔用buffer pool總記憶體的比例

範圍：預設25（表示change buffer最大能佔用其25%的記憶體），最大50。

引數：innodb_change_buffering

作用：控制change buffer對哪些dml起作用

可選引數：all（insert、delete、update）、none（不快取任何操縱）、inserts、deletes、purges

七、檢視你的MySQL的change buffer

# 命令
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 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hash searches/s, 202.47 non-hash searches/s

# insert：insert buffer
# delete mask：delete buffer
# delete ：purge buffer
# discarded operations:當change buffer發生merge時，資料表被刪除了！無需再merge

八、靈魂拷問

如果你能回答上這個問題，說明你真的理解了change buffer！

問：

我開啟change buffer 之後，現在要刪除一個非唯一的二級輔助索引資料行，比如就刪除name=Tom的行，並且這個索引頁不在記憶體中……接下來會發生什麼？

按照change buffer的作用來說，是不是當索引頁不在記憶體中時，不去讀盤，而是會把這個刪除操作寫到change buffer 中？

那問題又來了，既然你是把這個操作寫到了change buffer中，那你返回給客戶端的影響行數怎麼算出來的呢？你都沒有讀讀磁碟，萬一磁碟上都沒你要刪除的資料呢…… 你告訴客戶端，刪除成功了，影響行數為1？

答：其實客戶端每次都能得到正確的影響行數！不錯，change buffer中是把快取了你的delete操作，但是buffer pool是沒有被影響的呀，如果buffer pool中沒有這個name=Tom的行，它依然會去讀磁碟的！你品一品，buffer pool和change buffer是兩塊快取哦～

九、參考

https://dev.mysql.com/doc/refman/5.7/en/innodb-change-buffer.html