快捷導(dǎo)航

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

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

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

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

變更緩沖區(qū)用來(lái)緩存對(duì) 二級(jí)索引數(shù)據(jù) 的修改，當(dāng)數(shù)據(jù)頁(yè)沒(méi)有被回載到內(nèi)存中時(shí)，先把修改緩存起來(lái)，等到其他查詢(xún)操作發(fā)生時(shí)，數(shù)據(jù)頁(yè)被加載到內(nèi)存后，再直接修改內(nèi)存中的數(shù)據(jù)頁(yè)，從而達(dá)到減少磁盤(pán)I/0的目的。

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

場(chǎng)景：如果要修改一條數(shù)據(jù)，內(nèi)存與磁盤(pán)之間的交互過(guò)程是怎么樣的

在磁盤(pán)中找到對(duì)應(yīng)的數(shù)據(jù)行數(shù)據(jù)行所在的數(shù)據(jù)頁(yè)加載到內(nèi)存中在內(nèi)存中完成對(duì)數(shù)據(jù)行的修改把修改過(guò)后的數(shù)據(jù)頁(yè)在寫(xiě)回到磁盤(pán)中

即，需要兩次磁盤(pán)IO才能完成一次數(shù)據(jù)修改操作

變更緩沖區(qū)用來(lái)緩存對(duì) 二級(jí)索引數(shù)據(jù) 的修改，是一個(gè)特殊的數(shù)據(jù)結(jié)構(gòu)

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

1.1 為什么是二級(jí)索引?

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

1.2 Merge的觸發(fā)時(shí)機(jī)有哪些?

讀取對(duì)應(yīng)的數(shù)據(jù)頁(yè)時(shí);
當(dāng)系統(tǒng)空閑或者 Slow Shutdown 時(shí)，主線程發(fā)起 merge;
Change buffer 的內(nèi)存空間即將耗盡時(shí);
Redo Log 寫(xiě)滿(mǎn)時(shí)。

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

主要的配置項(xiàng)有 緩沖類(lèi)型 和 更改緩沖區(qū)的最大大小

2.1 緩沖類(lèi)型

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

可以通過(guò)選項(xiàng)文件或 SET GLOBAL 語(yǔ)句對(duì)系統(tǒng)變量 innodb_change_buffering 進(jìn)行設(shè)置，來(lái)控制變更緩沖區(qū)對(duì)于插入、刪除操作(索引記錄被標(biāo)記為刪除) 和清除操作(當(dāng)索引記錄被物理刪除時(shí))的開(kāi)啟或禁用:

刪除操作: 索引記錄被標(biāo)記為刪除
清除操作: 索引記錄被物理刪除時(shí)
更新操作: 是插入和刪除操作的組合

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

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

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

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

通過(guò)使用 SHOW ENGINE InnODB STATUS 訪問(wèn) InnoDB 標(biāo)準(zhǔn)監(jiān)視器輸出中 INSERTBUFFER AND ADAPTIVE HASH INDEX 部分查看有關(guān)更改緩沖區(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
... # 省略