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

ChangeBuffer

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

ChangeBuffer用于存盤SQL變更操作，比如Insert/Update/Delete等SQL陳述句；
ChangeBuffer中的每個變更操作都有其對應的資料頁，并且該資料頁未加載到快取中；
當ChangeBufferd中變更操作對應的資料頁加載到快取中后，InnoDB會把變更操作Merge到資料頁上；
InnoDB會定期加載ChangeBuffer中操作對應的資料頁到快取中，并Merge變更操作；

InnoDB快取區結構

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

InnoDB快取區結構

ChangeBuffer的作用

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

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

ChangeBuffer作業流程

變更操作什么時候放入ChangeBuffer

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

SQL會修改資料庫中的資料；
SQL陳述句不涉及唯一鍵的校驗；
SQL陳述句不需要回傳變更后的資料；
涉及的資料頁不在快取中；

ChangeBuffer合并到原資料頁

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

訪問變更操作對應的資料頁；
InnoDB后臺定期Merge；
資料庫BufferPool空間不足；
資料庫正常關閉時；
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會有較大的性能提升呢？

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

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

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

資料庫都是唯一索引；
寫入資料后，會立刻讀取；

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