InnoDB 記憶體結構之更改緩衝區

更改緩衝區（Change Buffer）是一種特殊的數據結構，用於緩存不在緩衝池中的二級索引（secondary index）頁的更改。可能來自於 INSERT、UPDATE 或 DELETE 操作（數據操作語言，DML）的緩衝更改，會在後續通過其他讀操作將這些頁載入到緩衝池時被合併。 ...

更改緩衝區（Change Buffer）是一種特殊的數據結構，用於緩存不在緩衝池中的二級索引（secondary index）頁的更改。可能來自於INSERT、UPDATE或DELETE操作（數據操作語言，DML）的緩衝更改，會在後續通過其他讀操作將這些頁載入到緩衝池時被合併。

與聚簇索引（clustered indexes）不同，二級索引通常是不唯一的，並且對二級索引的插入操作以相對隨機的順序發生。同樣地，刪除和更新操作可能會影響索引樹中不相鄰的二級索引頁。隨後當受影響的頁被其他操作讀入緩衝池時，合併緩存中的更改可以避免從磁碟讀取二級索引頁到緩衝池中所需的大量隨機訪問I/O。

在系統大部分處於空閑狀態或慢速關閉期間，會運行清理（purge）操作，定期將更新的索引頁寫入磁碟。與立即將每個值寫入磁碟相比，清理操作可以更高效地將索引值批量寫入磁碟。

當有大量受影響的行和需要更新的二級索引時，變更緩衝區的合併過程可能需要幾個小時。在此期間，磁碟 I/O 會增加，這可能導致磁碟密集型查詢明顯減慢。提交事務之後，甚至在伺服器關閉並重新啟動之後，變更緩衝區合併也可能會持續發生(請參閱“第 14.22.2 節“強制 InnoDB 恢復”瞭解更多信息)。

在記憶體中，變更緩衝區占用了緩衝池的一部分空間。在磁碟上，變更緩衝區是系統表空間的一部分，當資料庫伺服器關閉時，索引更改將存儲在其中。

變更緩衝區中緩存的數據類型由innodb_change_buffering變數控制。要瞭解更多信息，請參閱下文的”配置變更緩衝區“。您還可以配置最大變更緩衝區大小。要瞭解更多信息，請參閱下文的”配置最大變更緩衝區的大小“。

如果二級索引包含降序索引列，或者主鍵包含降序索引列，那麼變更緩衝區不支持對該二級索引進行緩衝。

有關變更緩衝區的常見問題的解答，請參見第 A.16 節“ MySQL 5.7 FAQ: InnoDB 變更緩衝區”。

配置變更緩衝區

當對錶執行INSERT、UPDATE和DELETE操作時，索引列的值（尤其是二級鍵的值）通常是無序的，需要大量的 I/O 操作來更新二級索引。變更緩衝區會在相關頁面不在緩衝池中時緩存對二級索引條目的更改，從而通過不會立即從磁碟讀取頁面來避免昂貴的 I/O 操作。當頁面被載入到緩衝池時，緩衝中的更改將合併，更新後的頁面隨後會刷新到磁碟。在伺服器幾乎空閑或慢速關閉時，InnoDB主線程會合併緩衝中的更改。

由於變更緩衝區可以減少磁碟讀寫操作，因此它對於 I/O 密集型的工作負載最為有價值。例如，變更緩衝可以給頻繁進行 DML 操作（如批量插入）的應用程式帶來好處。

但是，變更緩衝區占用了緩衝池的一部分空間，從而減少了可用於緩存數據頁面的記憶體。如果工作集幾乎完全適應緩衝池，或者您的表具有相對較少的二級索引，禁用變更緩衝可能是有益的。如果工作數據集完全適合緩衝池，變更緩衝不會增加額外開銷，因為它僅適用於不在緩衝池中的頁面。

innodb_change_buffering變數控制著InnoDB執行變更緩衝的程度。您可以啟用或禁用插入操作、刪除操作（最初將索引記錄標記為刪除時）和清理操作（當索引記錄被物理刪除時）的緩衝。更新操作是插入操作和刪除操作的組合。innodb_change_buffering的預設值為all。

all

預設值：緩衝區插入，刪除標記操作和清除。
none

不緩衝任何操作。
inserts

緩衝插入操作。
deletes

緩衝刪除標記操作。
changes

緩衝插入和刪除標記操作。
purges

緩衝後臺發生的物理刪除操作。

您可以在 MySQL 選項文件(my.cnf或my.ini)中設置innodb_change_buffering參數，或使用SET GLOBAL語句動態更改它，該語句需要足夠的許可權來設置全局系統變數。參見第 5.1.8.1 節“系統變數特權”。更改設置會影響新操作的緩衝；現有緩衝條目的合併不受影響。

您可以在 MySQL 的選項文件（my.cnf或my.ini）中設置innodb_change_buffering參數，或者使用SET GLOBAL語句動態更改它，該語句需要足夠的許可權來設置全局系統變數。請參閱第 5.1.8.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

有關更多信息，請參閱第 14.18.3 節 “InnoDB 標準監視器和鎖監視器輸出”。

Information Schema的INNODB_METRICS表提供了InnoDB標準監視器輸出中的大部分數據點以及其他數據點。要查看變更緩衝區指標及其描述，請執行以下查詢：
```
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G
```
有關`INNODB_METRICS表用法情況的信息，請參見第 14.16.6 節“ InnoDB INFORMATION_SCHEMA Metrics Table”。
Information Schema的INNODB_BUFFER_PAGE表提供了關於緩衝池中每個頁面的元數據，包括變更緩衝區索引和變更緩衝區點陣圖頁面。變更緩衝區頁面通過PAGE_TYPE進行標識。IBUF_INDEX是變更緩衝區索引頁的頁面類型，IBUF_BITMAP是變更緩衝區點陣圖頁的頁面類型。

Waring：查詢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表提供的其他數據的信息，請參閱第 24.4.2 節 “INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table”。有關相關使用信息，請參閱第 14.16.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互斥鎖等待的信息，請參閱第 14.17.2 節 “使用 Performance Schema 監視 InnoDB 互斥等待”。

註：原文來自 MySQL 5.7 官方文檔，閱讀 MySQL 中文文檔時有些語句理解不順暢，便結合中文文檔使用 ChatGPT 進行了翻譯，如有不正請指出。