MySQL變更緩沖區(qū)作用、主要配置及查看方法

更新時間：2025年05月20日 10:05:48 作者：JhonKI

變更緩沖區(qū)用來緩存對二級索引數據的修改,當數據頁沒有被回載到內存中時,先把修改緩存起來,等到其他查詢操作發(fā)生時,數據頁被加載到內存后,再直接修改內存中的數據頁,從而達到減少磁盤I/0的目的,下面給大家介紹MySQL變更緩沖區(qū)及查看方法,感興趣的朋友一起看看吧

???‍??一、變更緩沖區(qū)的作用?

變更緩沖區(qū)用來緩存對 二級索引數據 的修改，當數據頁沒有被回載到內存中時，先把修改緩存起來，等到其他查詢操作發(fā)生時，數據頁被加載到內存后，再直接修改內存中的數據頁，從而達到減少磁盤I/0的目的。

變更緩沖區(qū)占用 Buffer Pool 的一部分空間，具體如圖所示

場景：如果要修改一條數據，內存與磁盤之間的交互過程是怎么樣的

在磁盤中找到對應的數據行數據行所在的數據頁加載到內存中在內存中完成對數據行的修改把修改過后的數據頁在寫回到磁盤中

即，需要兩次磁盤IO才能完成一次數據修改操作

變更緩沖區(qū)用來緩存對 二級索引數據 的修改，是一個特殊的數據結構

當使用 INSERT 、UPDATE 或 DELETE 語句修改二級索引對應的數據時，如果對應的數據頁在緩沖池中則直接更新，如果不在緩沖池中，那么就把修改操作緩存到變更緩沖區(qū)，這樣就不用立即從磁盤讀取對應的數據頁了，當之后的讀操作將對應的數據頁從磁盤加載到緩沖池中時，變更緩沖區(qū)中緩存的修改操作再批量合并到緩沖池，從而達到減少磁盤I/0的目的。執(zhí)行流程如圖所示:

1.1 為什么是二級索引?

關于索引在數據庫初階已經做了介紹，我們知道索引分為 聚集索引(主鍵) 和 二級索引(自定義)
由于聚集索引具有唯一性，我們分析一下聚集索引為什么不能被放入變更緩存，假設表中有一個主鍵(ID)，現在有兩條 INSER 語句，都在插入數據時ID的值相同(id=1)，那么在變更緩沖區(qū)中就存在
兩個修改操作，如果以后要合并到緩沖池中，這時就會出現重復的主鍵值，所以聚集索引的修改不能被加入到變更緩沖區(qū);
與聚集索引不同，二級索引通常是不唯一的，并且向二級索引中插入數據時由于數據列不同，所以位置相對隨機，同樣對于刪除和更新操作可能會影響不相鄰的二級索引頁，如果每次都從磁盤讀取
數據就會發(fā)生大量的隨機I/0，以變更緩沖區(qū)的方式先將修改緩存起來，當真正的讀取數據時再把修改合并到緩沖池中可以提升效率。

1.2 Merge的觸發(fā)時機有哪些?

讀取對應的數據頁時;
當系統(tǒng)空閑或者 Slow Shutdown 時，主線程發(fā)起 merge;
Change buffer 的內存空間即將耗盡時;
Redo Log 寫滿時。

???‍??二、變更緩沖區(qū)的主要配置項都有哪些?

主要的配置項有 緩沖類型 和 更改緩沖區(qū)的最大大小

2.1 緩沖類型

在修改二級索引數據時變更緩沖區(qū)可以減少磁盤I/0從而提高效率，但是變更緩沖區(qū)占用了緩沖池的一部分空間，從而減少了可用于緩存數據頁的內存，如果業(yè)務場景讀多寫少，或者表中的二級索引相對較少，那么可以考慮禁用更改緩沖從而提高緩沖池空間。

可以通過選項文件或 SET GLOBAL 語句對系統(tǒng)變量 innodb_change_buffering 進行設置，來控制變更緩沖區(qū)對于插入、刪除操作(索引記錄被標記為刪除) 和清除操作(當索引記錄被物理刪除時)的開啟或禁用:

刪除操作: 索引記錄被標記為刪除
清除操作: 索引記錄被物理刪除時
更新操作: 是插入和刪除操作的組合

all: 默認值，緩存插入、刪除標記操作和清除
none:不緩存任何操作
inserts: 只緩存插入操作
deletes: 只緩存刪除標記操作
changes: 緩存插入和刪除標記操作
purges: 緩存發(fā)生在后臺的物理刪除操作

2.2 更改緩沖區(qū)的最大大小

通過 innodb_change_buffer_max_size 系統(tǒng)變量可以設置更改緩沖區(qū)的最大大小，默認為25，最大為50，表示更改緩沖區(qū)占緩沖池內存總大小的百分比。
在有大量插入、更新和刪除的業(yè)務場景中，可以考慮增加 innodb_change_buffer_max_size 的值，在大部分是讀多寫少，比如用于報表的靜態(tài)數據場景中考慮減小 innodb_change_buffer_max_size 的值
需要注意的是，如果更改緩沖區(qū)占了緩沖池太多的內存空間，會導致緩沖池中的數據頁更快地淘汰。

???‍??三、怎么查看當前變更緩沖區(qū)的信息?

通過使用 SHOW ENGINE InnODB STATUS 訪問 InnoDB 標準監(jiān)視器輸出中 INSERTBUFFER AND ADAPTIVE HASH INDEX 部分查看有關更改緩沖區(qū)狀態(tài)的信息。

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
... # 省略
-------------------------------------
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
... # 省略