MySQL :: MySQL 9.0 參考手冊 :: 17.5.2 變更緩衝區

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 變更緩衝區

17.5.2 變更緩衝區

變更緩衝區是一種特殊的資料結構，用於在次要索引頁面不在緩衝池中時，快取對這些頁面的變更。緩衝的變更可能來自INSERT、UPDATE 或 DELETE 操作 (DML)，稍後當其他讀取操作將頁面載入到緩衝池時，會合併這些變更。

圖 17.3 變更緩衝區

Content is described in the surrounding text.

與叢集索引不同，次要索引通常是非唯一索引，而且插入次要索引的發生順序相對隨機。同樣地，刪除和更新可能會影響索引樹中非相鄰位置的次要索引頁面。當其他操作將受影響的頁面讀入緩衝池時，在稍後合併快取的變更，可以避免大量的隨機存取 I/O，否則需要從磁碟將次要索引頁面讀入緩衝池。

系統大部分處於閒置狀態時，或在緩慢關閉期間執行的清除操作，會定期將更新的索引頁面寫入磁碟。與立即將每個值寫入磁碟相比，清除操作可以更有效率地寫入一系列索引值的磁碟區塊。

當有許多受影響的列和許多要更新的次要索引時，變更緩衝區合併可能需要幾個小時。在這段時間內，磁碟 I/O 會增加，這可能會導致受限於磁碟的查詢速度明顯變慢。變更緩衝區合併也可能在交易提交後，甚至在伺服器關閉並重新啟動後繼續發生（如需更多資訊，請參閱第 17.20.3 節「強制 InnoDB 還原」）。

在記憶體中，變更緩衝區會佔用緩衝池的一部分。在磁碟上，變更緩衝區是系統表空間的一部分，當資料庫伺服器關閉時，索引變更會緩衝在此處。

變更緩衝區中快取的資料類型由 innodb_change_buffering 變數控制。如需更多資訊，請參閱設定變更緩衝區。您也可以設定變更緩衝區的最大大小。如需更多資訊，請參閱設定變更緩衝區最大大小。

如果索引包含遞減索引欄位，或者主鍵包含遞減索引欄位，則不支援對次要索引進行變更緩衝。

如需有關變更緩衝區的常見問題解答，請參閱第 A.16 節，「MySQL 9.0 常見問題解答：InnoDB 變更緩衝區」。

設定變更緩衝區

當對表格執行 INSERT、UPDATE 和 DELETE 操作時，索引欄位的值（尤其是次要鍵的值）通常是未排序的，需要大量的 I/O 才能將次要索引更新為最新狀態。變更緩衝區會在相關頁面不在緩衝池中時，快取對次要索引項目的變更，從而避免了立即從磁碟讀取頁面的昂貴 I/O 操作。當頁面載入到緩衝池時，會合併緩衝的變更，並且更新後的頁面稍後會刷新到磁碟。InnoDB 主執行緒會在伺服器接近閒置時以及在慢速關機期間合併緩衝的變更。

由於它可以減少磁碟讀取和寫入次數，因此變更緩衝對於受 I/O 限制的工作負載最有價值；例如，具有大量 DML 操作（如大量插入）的應用程式受益於變更緩衝。

但是，變更緩衝區佔用緩衝池的一部分，從而減少了可用於快取資料頁面的記憶體。如果工作集幾乎可以放入緩衝池中，或者您的表格相對較少次要索引，則停用變更緩衝可能會有用。如果工作資料集完全適合緩衝池，則變更緩衝不會產生額外的開銷，因為它僅適用於不在緩衝池中的頁面。

innodb_change_buffering 變數控制 InnoDB 執行變更緩衝的程度。您可以啟用或停用插入、刪除操作（當索引記錄最初被標記為刪除時）和清除操作（當索引記錄被實際刪除時）的緩衝。更新操作是插入和刪除的組合。預設的 innodb_change_buffering 值為 none，允許的值在 innodb_change_buffering 文件中描述。

您可以在 MySQL 選項檔案（my.cnf 或 my.ini）中設定 innodb_change_buffering 變數，或者使用 SET GLOBAL 陳述式動態變更它，這需要足夠的權限來設定全域系統變數。請參閱第 7.1.9.1 節，「系統變數權限」。變更設定會影響新操作的緩衝；現有緩衝項目的合併不受影響。

設定變更緩衝區最大大小

innodb_change_buffer_max_size 變數允許將變更緩衝區的最大大小設定為緩衝池總大小的百分比。預設情況下，innodb_change_buffer_max_size 設定為 25。最大設定為 50。

在具有大量插入、更新和刪除活動的 MySQL 伺服器上，如果變更緩衝區合併速度跟不上新的變更緩衝區項目，導致變更緩衝區達到其最大大小限制，請考慮增加 innodb_change_buffer_max_size。

如果 MySQL 伺服器使用靜態資料進行報表，或者如果變更緩衝區消耗太多與緩衝池共用的記憶體空間，導致頁面比預期更快從緩衝池中老化，請考慮減少 innodb_change_buffer_max_size。

使用代表性的工作負載測試不同的設定，以確定最佳設定。innodb_change_buffer_max_size 變數是動態的，允許在不重新啟動伺服器的情況下修改設定。

監控變更緩衝區

以下選項可用於變更緩衝區監控

InnoDB 標準監控輸出包括變更緩衝區狀態資訊。若要檢視監控資料，請發出 SHOW ENGINE INNODB STATUS 陳述式。

mysql> SHOW ENGINE INNODB STATUS\G

變更緩衝區狀態資訊位於 INSERT BUFFER AND ADAPTIVE HASH INDEX 標題下，外觀類似於以下內容

-------------------------------------
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

如需更多資訊，請參閱第 17.17.3 節，「InnoDB 標準監控和鎖定監控輸出」。

Information Schema INNODB_METRICS 表格提供了 InnoDB 標準監控輸出中找到的大部分資料點，以及其他資料點。若要檢視變更緩衝區度量以及每個度量的描述，請發出以下查詢
```
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
```
請參閱第 17.15.6 節，「InnoDB INFORMATION_SCHEMA 度量表格」。
Information Schema INNODB_BUFFER_PAGE 表格提供了有關緩衝池中每個頁面的中繼資料，包括變更緩衝區索引和變更緩衝區位圖頁面。變更緩衝區頁面由 PAGE_TYPE 識別。IBUF_INDEX 是變更緩衝區索引頁面的頁面類型，而 IBUF_BITMAP 是變更緩衝區位圖頁面的頁面類型。

警告

查詢 INNODB_BUFFER_PAGE 表格會導致明顯的效能開銷。為避免影響效能，請在測試執行個體上重現您想要調查的問題，並在測試執行個體上執行您的查詢。

例如，您可以查詢 INNODB_BUFFER_PAGE 表格，以確定 IBUF_INDEX 和 IBUF_BITMAP 頁面數量的近似值，以總緩衝池頁面的百分比表示。
```
mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
       (SELECT ((change_buffer_pages/total_pages)*100))
       AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                  25 |        8192 |                        0.3052 |
+---------------------+-------------+-------------------------------+
```
如需有關 INNODB_BUFFER_PAGE 表格提供的其他資料的資訊，請參閱第 28.4.2 節，「INFORMATION_SCHEMA INNODB_BUFFER_PAGE 表格」。如需相關的使用資訊，請參閱第 17.15.5 節，「InnoDB INFORMATION_SCHEMA 緩衝池表格」。

Performance Schema 提供了變更緩衝區互斥鎖等待檢測，以進行進階效能監控。若要檢視變更緩衝區檢測，請發出以下查詢

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
+-------------------------------------------------------+---------+-------+
| NAME                                                  | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
+-------------------------------------------------------+---------+-------+

如需有關監控 InnoDB 互斥鎖等待的資訊，請參閱第 17.16.2 節，「使用 Performance Schema 監控 InnoDB 互斥鎖等待」。