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

更改緩沖區（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 進行了翻譯，如有不正請指出，

標籤：其他

上一篇：華為云GaussDB為MetaERP“成本核算”產品“保駕護航”

下一篇：返回列表