mysql change buffer小結

首先：正常insert的時候，也是首先要判斷哪個物理頁(包括資料頁和索引頁)可以insert，然後把這個物理塊讀取到buffer pool，然後再在記憶體執行insert 操作，然後再重新整理到磁碟;

接下來介紹下我對change buffer的認識，知識來自於網路文章+innodb 儲存引擎書籍+官方文件

0)Change Buffer為何提高效能

它快取的是一個索引頁的 dml的操作，而不是具體的資料頁，具體的資料頁直接就修改了，對非聚集非唯一的索引頁修改的時候才可能用到change buffer。當 buffer pool中沒有某個索引頁，並且需要dml操作這個索引頁的資料的時候，不需要先從磁碟讀取該索引頁到buffer pool，直接把該操作記錄到cahnge buffer 中，這裡就減少了一次隨機io，然後等下次業務需要訪問這個索引頁的時候，再把這個頁讀取到buffer poo中，然後change buffer記錄的操作和buffer pool中剛讀進來的索引頁進行merge 操作。

提高效能的地方還有一點是：對於非聚集非唯一索引的更新或者插入操作，不是每次插入或者更新操作完， 都直接插入到索引頁中，而是先判斷插入的非聚集索引頁是否在緩衝池中，如果在，則直接插入，不在則放到change buffer中，然後以一定的頻率和情況進行change buffer和輔助索引葉子的merge(合併)操作，這時通常可以將多個索引葉子的dml操作合併到一個操作裡(因為在一個索引頁中)，這裡也提高了效能

綜上所述 cahnge buffer 效能的提升在於兩點：

一：當需要修改的索引頁不在buffer pool的時候，不需要先把索引頁讀取到buffer pool,這裡減少了io

二：將多個索引葉子的dml操作合併到一個操作裡(因為在一個索引頁中)，批次merge，這裡也提高了效能;

1)change buffer的內容

Change Buffer是一種特殊的資料結構，快取對二級索引頁面的更改並且這些頁面不在Buffer Pool中(注意快取的是對索引葉子的修改，而不是具體的索引葉子)。快取的changes可能由 Insert 、Delete 和 Update的結果導致。稍後在頁面被其他讀取操作載入到Buffer Pool的時候合併,

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

3)change buffer 只針對輔助索引而言

對主鍵索引無效;對輔助索引葉子節點的更改才可能借助change buffer ,如果該列上沒有索引，那麼就不會藉助change buffer了!

4)緩衝池有change buffer 資訊固然不錯，但他不是單純是快取池的一部分，change buffer 和資料頁一樣，change buffer 是一個B+樹，放在共享表空間，預設也就是idbata1，向change buffer 寫資料也是需要隨機io,但有change buffer後，當buffer 中沒有需要修改的葉子塊的時候，你不需要把葉子塊讀進innodb_buffer_cahce中(減少的就是這裡的io)只需要把改變寫進cahnge buffer即可，然後cahnge buffer中排序最後等待和真正的葉子節點資料合併!批次合併!所以如果更新操作不頻繁的時候，並且更新後立馬需要讀取該頁的時候，change buffer 的機制會增加部分io消耗! change buffer 減少了從記憶體讀取硬碟的隨機讀IO(資料頁)操作，換做成批次順序merge，redo log減少了隨機寫log操作

5).之所以需要滿足索引是輔助索引，並且是非唯一的

因為在插入緩衝時，資料庫並不去查詢索引頁來判斷插入的記錄的唯一性，如果去查詢肯定又會有離散讀取的情況發生(隨機io)，從而導致change buffer 失去意義!

6) change buffer的相關引數

1 innodb_change_buffering 預設是all支援所有DML操作

2 innodb_change_buffer_max_size，預設是25，即緩衝池的1/4。最大可設定為50,採用預設即可

7).合併的操作是在 buffer pool裡面進行的

當下一次需要載入這個頁面的時候，也就是這個頁面有需求的時候，會將Change Buffer內的更改合併到Buffer Pool，隨後當伺服器在空閒的時候，這個更改會刷到disk(磁碟)上, 或者在不繁忙的時候進行merge，這時候merger操作也是發生在buffer pool;

8).為什麼change buffer 存的是 buffer pool中沒有的索引頁的操作?

因為如果在buffer pool中，那麼就直接dml操作對應的索引頁了，change buffer 存在就是為了當需要操作的索引頁不在buffer pool中，然後不需要立馬去磁碟讀取該索引頁，只有當buffer pool中不存在的時候才可能用到change buffer;

9) 除了資料頁被訪問，還有哪些場景會觸發刷寫緩衝中的資料呢?

還有這麼幾種情況，會刷寫緩衝中的資料：

(1)有一個後臺執行緒，會認為資料庫空閒時;

(2)資料庫緩衝池不夠用時;

(3)資料庫正常關閉時;

(4)redo log寫滿時;

畫外音：幾乎不會出現redo log寫滿，此時整個資料庫處於無法寫入的不可用狀態。

10)什麼業務場景，適合開啟InnoDB的寫緩衝機制?

先說什麼時候不適合，如上文分析，當：

(1)資料庫都是唯一索引;

(2)或者，寫入一個資料後，會立刻讀取它;

這兩類場景，在寫操作進行時(進行後)，本來就要進行進行頁讀取，本來相應頁面就要入緩衝池，此時寫快取反倒成了負擔，增加了複雜度。

什麼時候適合使用寫緩衝，如果：

(1)資料庫大部分是非唯一索引;

(2)業務是寫多讀少，或者不是寫後立刻讀取;

可以使用寫緩衝，將原本每次寫入都需要進行磁碟IO的SQL，最佳化定期批次寫磁碟。

畫外音：例如，賬單流水業務。