InnoDB學習（二）之ChangeBuffer

御狐神發表於2021-12-08

ChangeBuffer是InnoDB快取區的一種特殊的資料結構，當使用者執行SQL對非唯一索引進行更改時，如果索引對應的資料頁不在快取中時，InnoDB不會直接載入磁碟資料到快取資料頁中，而是快取對這些更改操作。這些更改操作可能由插入、更新或刪除操作(DML)觸發。快取區的更改操作會在磁碟資料被其它讀操作載入到快取中時合併到對應的快取資料頁中。

ChangeBuffer

InnoDB ChangeBuffer的官方示意圖如下所示，從圖中可以看出以下資訊：

  1. ChangeBuffer用於儲存SQL變更操作，比如Insert/Update/Delete等SQL語句；
  2. ChangeBuffer中的每個變更操作都有其對應的資料頁，並且該資料頁未載入到快取中；
  3. 當ChangeBufferd中變更操作對應的資料頁載入到快取中後，InnoDB會把變更操作Merge到資料頁上；
  4. InnoDB會定期載入ChangeBuffer中操作對應的資料頁到快取中，並Merge變更操作；

InnoDB快取區結構

基於個人理解並參考官方的ChangeBuffer示例圖，我繪製了以下更為直觀的的ChangeBuffer示例圖：

InnoDB快取區結構

ChangeBuffer的作用

我們知道InnoDB推薦使用自增主鍵，插入時主鍵值時遞增的，可以順序訪問。與聚簇索引不同，二級索引通常是不是唯一的，並且以相對隨機的順序插入。類似的，二級索引的更新和刪除經常也會影響索引樹中不相鄰的二級索引資料頁。

對於二級索引資料變更引起的隨機訪問，如果每次都進行磁碟IO顯然會影響資料庫的效能。因此InnoDB不會立即執行資料頁不在快取中的二級索引的變更操作，而是先將變更操作快取起來，在某個時刻再將某一個資料頁上面的所有變更操作合併到該資料頁上，通過變更操作快取(ChangeBuffer)可合併同一個資料頁上的大量隨機訪問I/O。

ChangeBuffer工作流程

變更操作什麼時候放入ChangeBuffer

並不是資料庫中的所有操作都會進入ChangeBuffer，滿足以下條件的資料庫語句，在執行階段不會修改資料頁，而是會進入ChangeBuffer，

  1. SQL會修改資料庫中的資料；
  2. SQL語句不涉及唯一鍵的校驗；
  3. SQL語句不需要返回變更後的資料；
  4. 涉及的資料頁不在快取中；

ChangeBuffer合併到原資料頁

我們知道，ChangeBuffer中快取了變更操作，這些操作最終需要合併到資料庫的資料頁，合併過程稱為Merge，那麼在什麼場景下會觸發ChangeBuffer的Merge操作呢？

  1. 訪問變更操作對應的資料頁；
  2. InnoDB後臺定期Merge；
  3. 資料庫BufferPool空間不足；
  4. 資料庫正常關閉時；
  5. RedoLog寫滿時；

為什麼ChangeBuffer只快取非唯一索引資料

ChangeBuffer僅僅適用於變更的資料未為非唯一索引的情況，如果變更操作修改的資料為唯一索引或者主鍵資料，那麼InnoDB無法把變更操作快取到ChangeBuffer，這是為什麼呢？

以一張使用者表為例，使用者表包含主鍵ID、年齡、姓名和性別四個欄位，其中年齡新增了非唯一索引，初始資料及建表語句如下所示：
使用者ID 1 2 3 4 5 6 7 8 9
姓名 陳爾 張散 李思 王舞 趙流 孫期 周跋 吳酒 鄭史
性別 男 男 女 女 男 男 男 女 男
年齡 5 10 20 28 35 56 25 80 90 
create table user_info
(
    id   int primary key,
    age  int not null,
    name varchar(16),
    sex  bool,
    key(age)
)engine=InnoDB;

非唯一索引更新

假設我們使用SQL語句update user_info set age=6 where id=1修改ID=1的使用者的年齡為6，該操作會同時修改年齡索引以及行資料中的年齡，更新步驟如下：

  • 如果需要更改的年齡索引頁和行資料頁在快取中，直接更新快取中的資料，並把資料頁標記為髒頁；
  • 如果需要更改的年齡索引頁和行資料頁不在快取中，直接把SQL語句update user_info set age=6 where id=1儲存到ChangeBuffer；

唯一索引更新

假設我們使用SQL語句update user_info set id=2 where id=1修改ID=1的使用者的ID為2，該操作會同時修改聚簇索引和行資料，更新步驟如下：

  • 如果需要更改的聚簇索引和行資料頁在快取中，直接更新快取中的資料，並把資料頁標記為髒頁；
  • 如果需要更改的聚簇索引頁和行資料頁不在快取中，需要把對應的資料頁載入到快取中，判斷修改之後ID是不是符合唯一鍵約束，然後修改快取中的資料；

可以看到，由於唯一索引需要進行唯一性校驗，所以對唯一索引進行更新時必須將對應的資料頁載入到快取中進行校驗，從而導致ChangeBuffer失效。

普通索引還是唯一索引

通過以上分析，我們知道唯一索引無法使用ChangeBuffer，那麼我們實際使用過程中應該使用普通索引還是唯一索引呢？

從等值查詢效能角度來看：

  • 普通索引在查詢到第一個滿足條件的資料之後，需要繼續向後查詢滿足條件的資料；
  • 唯一索引在查詢到第一個滿足條件的資料之後，不需要再次向後查詢，因為索引具有唯一性；

二者之間只相差一條記錄，這個一條記錄會帶來多大的效能差距呢？答案是，微乎其微。因為InnoDB引擎是以頁為單位讀取資料的，讀取一條資料時，往往會將臨近的資料也讀到記憶體，所以多向後查詢幾條資料帶來的效能差別微乎其微。

從索引修改角度來看：

由於非唯一索引無法使用ChangeBuffer，對索引的修改會引起大量的磁碟IO，影響資料庫效能。

綜上可知，如果不是業務中要求資料庫對某個欄位做唯一性檢查，我們最好使用普通索引而不是唯一索引。

ChangeBuffer適用場景

什麼情況下ChangeBuffer會有較大的效能提升呢？

  1. 資料庫大部分索引是非唯一索引；
  2. 業務是寫多讀少，或者不是寫後立刻讀取；

不適合使用ChangeBuffer的場景與之對應：

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

  1. 資料庫都是唯一索引；
  2. 寫入資料後，會立刻讀取；

ChangeBuffer相關引數

  • innodb_change_buffer_max_size: 配置寫緩衝的大小，佔整個緩衝池的比例，預設值是25%，最大值是50%。
    寫多讀少的業務，才需要調大這個值。

  • innodb_change_buffering: 配置哪些寫操作啟用寫緩衝，可以設定成all/none/inserts/deletes等。

我是御狐神，歡迎大家關注我的微信公眾號:wzm2zsd

qrcode_for_gh_83670e17bbd7_344-2021-09-04-10-55-16

本文最先發布至微信公眾號，版權所有，禁止轉載！

相關文章