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

版本 8.4

8.0 日文

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

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 8.4 常見問題：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 緩衝池資料表」。

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

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 節，「使用效能架構監控 InnoDB 互斥鎖等待」。

上一個首頁上一層下一個