【MySQL精通之路】InnoDB(5)-內存結構

總目錄：

【MySQL精通之路】InnoDB存儲引擎-CSDN博客

【MySQL精通之路】InnoDB(4)-架構圖-CSDN博客

?編輯

1 緩存池（Buffer Pool）

1.1 緩存池LRU算法

1.2 緩存區配置

1.3 使用InnoDB標準監視器監視緩存池

2 更改緩存（Change Buffer）

2.1 配置更改緩存

2.2 配置更改緩沖區百分比

2.3 監控更改緩沖區

3 自適應哈希索引（Adaptive Hash Index）

4 日志緩沖區（Log Buffer）

圖片來自MySQL官方文檔

1 緩存池（Buffer Pool）

緩存池是主內存中的一個區域，InnoDB在訪問表和索引數據時會在該區域進行緩存。

緩存池允許直接從內存訪問頻繁使用的數據，這加快了處理速度。在專用服務器上，通常會將高達80%的物理內存分配給緩沖池。

為了提高高容量讀取操作的效率，緩存池被劃分為可能容納多行的頁面。為了提高緩存管理的效率，緩存池被實現為頁面的鏈表；很少使用的數據使用最近最少使用（LRU）算法的變體從高速緩存中淘汰。

了解如何利用緩存池將頻繁訪問的數據保存在內存中是MySQL調優的一個重要方面。

1.1 緩存池LRU算法

Buffer Pool LRU Algorithm

使用LRU算法的變體將緩沖池作為列表進行管理。

當需要空間將新頁面添加到緩存池時，會收回最近最少使用的頁面，并將新頁面增加到列表的中間。

此中點插入策略將列表視為兩個子列表：

頭部為最近訪問的新（“年輕”）頁面的子列表

尾部為最近訪問次數較少的舊頁面的子列表

圖17.2 緩存池列表

該算法將頻繁使用的頁面保留在新的子列表中。舊的子列表包含不太頻繁使用的頁面；這些頁面可能會被清除。

默認情況下，算法操作如下：

緩沖池的3/8用于舊的子列表。

列表的中點是新子列表的尾部與舊子列表的頭部相交的邊界。

當InnoDB將一個頁面讀取到緩沖池中時，它最初會將其插入中點（舊子列表的頭）。可以讀取頁面，因為它是用戶啟動的操作（如SQL查詢）所必需的，或者是InnoDB自動執行的預讀操作的一部分。

訪問舊子列表中的頁面會使其“年輕”，并將其移動到新子列表的頭部。如果由于用戶啟動的操作需要讀取頁面，則會立即進行第一次訪問，并使頁面年輕。如果頁面是由于預讀操作而讀取的，則第一次訪問不會立即發生，而且可能在頁面被收回之前根本不會發生。

當數據庫運行時，緩沖池中未被訪問的頁面會向列表的尾部移動，從而“老化”。新舊子列表中的頁面都會隨著其他頁面的更新而老化。舊子列表中的頁面也會隨著頁面插入中點而老化。最終，一個未使用的頁面到達舊子列表的尾部并被逐出。

默認情況下，查詢讀取的頁面會立即移動到新的子列表中，這意味著它們在緩存池中停留的時間更長。例如，為mysqldump操作或不帶WHERE子句的SELECT語句執行的表掃描可以將大量數據帶入緩存池，并收回等量的舊數據，即使新數據再也不用了。類似地，由預讀后臺線程加載并只訪問一次的頁面會移動到新列表的開頭。這些情況可能會將經常使用的頁面推送到舊的子列表中，在那里它們會被驅逐。有關優化此行為的信息，

請參閱“使緩存池抗掃描”和“配置InnoDB緩存池預取（預讀）”。
InnoDB標準監視器輸出包含緩存池和內存部分中關于緩存池LRU算法操作的幾個字段。有關詳細信息，請參閱使用InnoDB標準監視器監視緩存池。

1.2 緩存區配置

您可以配置緩存池的各個方面以提高性能

理想情況下，您可以將緩存池的大小設置為盡可能大的值，從而為服務器上的其他進程留下足夠的內存來運行，而不需要過多的分頁。

緩存池越大，InnoDB就越像內存中的數據庫，從磁盤讀取數據一次，然后在隨后的讀取過程中訪問內存中的數據。

參見第17.8.3.1節“配置InnoDB緩存池大小”。

在具有足夠內存的64位系統上，可以將緩存池拆分為多個部分，以最大限度地減少并發操作之間對內存結構的爭用。

有關詳細信息，請參閱第17.8.3.2節“配置多個緩存池實例”。

您可以將頻繁訪問的數據保留在內存中，而不管操作會將大量不常訪問的數據帶入緩存池的活動突然激增。

有關詳細信息，請參閱第17.8.3.3節“使緩存池具有抗掃描性”。

您可以控制如何以及何時執行預讀請求，以異步方式將頁面預取到緩存池中，以應對即將到來的需求。

有關詳細信息，請參閱第17.8.3.4節“配置InnoDB緩存池預取（預讀）”。

您可以控制后臺刷新發生的時間，以及是否根據工作負載動態調整刷新速率。

有關詳細信息，請參閱第17.8.3.5節“配置緩存池刷新”。

您可以配置InnoDB如何保留當前緩存池狀態，以避免服務器重新啟動后的漫長預熱期。

有關詳細信息，請參閱第17.8.3.6節“保存和恢復緩沖池狀態”。

1.3 使用InnoDB標準監視器監視緩存池

可以使用SHOW ENGINE INNODB STATUS訪問的InnoDB Standard Monitor輸出提供了有關緩存池操作的指標。緩存池指標位于InnoDB標準監視器輸出的緩存池和內存部分：

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908
Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

下表描述了InnoDB Standard Monitor報告的緩存池指標。

InnoDB Standard Monitor輸出中提供的每秒平均值基于自上次打印InnoDB Standard監視器輸出以來的運行時間。

Name	Description
Total memory allocated	以字節為單位分配給緩存池的總內存。
Dictionary memory allocated	為InnoDB數據字典分配的總內存（以字節為單位）。
Buffer pool size	分配給緩存池的總大小（以頁為單位）。
Free buffers	緩存池可用列表的總大小（以頁為單位）。
Database pages	緩存池LRU列表的總大小（以頁面為單位）。
Old database pages	緩存池舊LRU子列表的總大小（以頁面為單位）。
Modified db pages	緩存池中當前修改的頁數。
Pending reads	等待讀取到緩存池中的緩存池頁面數。
Pending writes LRU	從LRU列表底部開始寫入緩存池中的舊臟頁數。
Pending writes flush list	列表在檢查點期間要刷新的緩沖池頁面數。
Pending writes single page	單頁緩存池中掛起的獨立頁寫入數。
Pages made young	緩存池LRU列表中變年輕的頁面總數（移動到“新”頁面的子列表的頭）。
Pages made not young	緩存池LRU列表中未年輕的頁面總數（保留在“舊”子列表中但未年輕的頁）。
youngs/s	每秒平均訪問緩存池LRU列表中導致頁面變年輕的舊頁面的次數。有關詳細信息，請參閱此表后面的注釋。
non-youngs/s	每秒平均訪問緩存池LRU列表中的舊頁面，導致頁面不年輕。有關詳細信息，請參閱此表后面的注釋。
Pages read	從緩存池中讀取的頁面總數。
Pages created	緩存池中創建的頁面總數。
Pages written	從緩存池中寫入的頁數。
reads/s	每秒平均讀取緩存池頁面的次數。
creates/s	每秒創建的緩存池頁面的平均數。
writes/s	每秒緩存池頁面寫入的平均次數。
Buffer pool hit rate	命中率從緩存池讀取的頁面與從磁盤存儲讀取的頁面的緩存池頁面命中率。
young-making rate	頁面訪問導致頁面年輕化的平均命中率。有關詳細信息，請參閱此表后面的注釋。
not (young-making rate)	頁面訪問未導致頁面年輕化的平均命中率。有關詳細信息，請參閱此表后面的注釋。
Pages read ahead	每秒預讀操作的平均值。
Pages evicted without access	每秒未經緩存池訪問而被移出的頁面的平均數。
Random read ahead	每秒隨機預讀操作的平均值。
LRU len	緩存池LRU列表的總大小（以頁面為單位）。
unzip_LRU len	緩存池unzip _ RU列表的長度（以頁為單位）。
I/O sum	訪問的緩存池LRU列表頁面的總數。
I/O cur	當前間隔內訪問的緩存池LRU列表頁的總數。
I/O unzip sum	解壓縮的緩存池unzip _LRU列表頁的總數。
I/O unzip cur	當前間隔內解壓縮的緩沖池unzip _LRU列表頁的總數。

注意：

youngs/s指標

僅適用于舊頁面。它基于頁面訪問次數。一個給定的頁面可以有多次訪問，所有訪問都會被計算在內。如果在沒有進行大型掃描的情況下看到非常低的youngs/s值，請考慮減少延遲時間或增加用于舊子列表的緩存池的百分比。增加百分比會使舊的子列表更大，因此該子列表中的頁面移動到尾部所需的時間更長，這會增加這些頁面再次被訪問并變得年輕的可能性。參見第17.8.3.3節“使緩沖池具有抗掃描性”。

non-youngs/s指標僅適用于舊頁面。它基于頁面訪問次數。一個給定的頁面可以有多次訪問，所有訪問都會被計算在內。如果在執行大型表掃描時沒有看到更高的non-youngs/s值（以及更高的youngs/s值），請增加延遲值。參見第17.8.3.3節“使緩沖池具有抗掃描性”。

年輕的生成率考慮了所有緩沖池頁面訪問，而不僅僅是舊子列表中頁面的訪問。年輕頁的命中率和非命中率通常不會加起來成為整個緩沖池的命中率。舊的子列表中的頁面命中會導致頁面移動到新的子列表，但只有當頁面與列表頭相距一定距離時，新子列表中頁面命中才會導致頁面移動到頭。

not（young makeing rate）是由于不滿足innodb_old_block_time定義的延遲，或者由于新子列表中的頁面命中沒有導致頁面移動到頭部，因此頁面訪問沒有導致頁面年輕的平均命中率。此速率將考慮所有緩沖池頁面訪問，而不僅僅是舊子列表中頁面的訪問。

2 更改緩存（Change Buffer）

交換緩存區是一種特殊的數據結構，當輔助索引頁不在緩存池中時，它會緩存對這些頁的更改。緩存的更改可能由INSERT、UPDATE或DELETE操作（DML）引起，稍后當頁面通過其他讀取操作加載到緩存池中時，這些更改將被合并。

圖17.3更改緩存區

與聚集索引不同，二級索引通常是非唯一的，并且插入二級索引的順序相對隨機。類似地，刪除和更新可能會影響索引樹中不相鄰的輔助索引頁。

然后，當其他操作將受影響的頁讀取到緩存池中時，合并緩存的更改可以避免從磁盤將輔助索引頁讀取到緩存池中所需的大量隨機訪問I/O。

在系統大部分空閑或緩慢關閉時運行的清洗操作會定期將更新的索引頁寫入磁盤。

與立即將每個值寫入磁盤相比，清洗操作可以更有效地寫入一系列索引值的磁盤塊。
當有許多受影響的行和許多要更新的輔助索引時，交換緩沖區合并可能需要幾個小時。在此期間，磁盤I/O會增加，這可能會導致磁盤綁定查詢的速度顯著減慢。在提交事務之后，甚至在服務器關閉和重新啟動之后，更改緩存區合并也可能繼續發生

（有關更多信息，請參閱第17.213.3節“強制InnoDB恢復”）。

在內存中，更改緩存區占據了緩存池的一部分。在磁盤上，更改緩存區是系統表空間的一部分，當數據庫服務器關閉時，索引更改將在其中緩存。

緩存在更改緩存區中的數據類型由innodb_change_buffering變量控制。

如果輔助索引包含降序索引列或主鍵包含降序索引列則不支持交換緩存。

有關更改緩沖區的常見問題解答，

請參閱第A.16節“MySQL 8.0常見問題解答：InnoDB更改緩存區”。

2.1 配置更改緩存

在表上執行INSERT、UPDATE和DELETE操作時，索引列的值（尤其是輔助關鍵字的值）通常按未排序的順序排列，需要大量I/O才能使輔助索引保持最新。

當相關頁面不在緩沖池中時，更改緩存區緩存對輔助索引項的更改，從而避免了昂貴的I/O操作，因為不會立即從磁盤讀取頁面。當頁面加載到緩沖池中時，緩存的更改會合并，更新后的頁面稍后會刷新到磁盤。當服務器接近空閑時，以及在緩慢關閉期間，InnoDB主線程合并緩存的更改。

因為它可以減少磁盤讀取和寫入，所以更改緩存對于I/O綁定的工作負載來說是最有價值的；例如，具有大量DML操作（如大容量插入）的應用程序受益于更改緩存。

但是，更改緩沖區占用了緩沖池的一部分，從而減少了可用于緩存數據頁的內存。如果工作集幾乎適合緩存池，或者表的輔助索引相對較少，則禁用更改緩存可能會很有用。如果工作數據集完全適合緩存池中，則更改緩存不會帶來額外的開銷，因為它只適用于不在緩存池中的頁。

innodb_change_buffering變量控制innodb執行更改緩存的程度。您可以啟用或禁用插入、刪除操作（當索引記錄最初標記為刪除時）和清除操作（當物理刪除索引記錄時）的緩存。更新操作是插入和刪除的組合。innodb_change_buffering的默認值為all。

允許的innodb_change_buffering值包括：

all

默認值：緩存區插入、刪除標記操作和清除。

none

不要緩存任何操作。

inserts

緩存區插入操作。

deletes

緩存區刪除標記操作。

changes

緩存插入和刪除標記操作。

purges

緩存后臺發生的物理刪除操作。

您可以在MySQL選項文件（my.cnf或my.ini）中設置innodb_change_buffering變量，也可以使用SET GLOBAL語句動態更改它，這需要足夠的權限來設置全局系統變量。

參見第7.1.9.1節“系統變量權限”。更改設置會影響新操作的緩沖；

現有緩沖條目的合并不受影響。

2.2 配置更改緩沖區百分比

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變量是動態的，允許在不重啟服務器的情況下修改設置。

2.3 監控更改緩沖區

以下選項可用于更改緩沖區監控：

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

?有關更多信息：

【MySQL精通之路】SHOW ENGINE INNODB STATUS指標解釋-CSDN博客

Information Schema庫中的 INNODB_METRICS表：

提供了INNODB Standard Monitor輸出中的大部分數據點以及其他數據點。要查看更改緩沖區指標及其描述，請發出以下查詢：

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G

關于INNODB_METRICS表的內容參考：

【MySQL精通之路】InnoDB-INFORMATION_SCHEMA庫Metrics表-CSDN博客

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

Performance Schema表為高級性能監視提供了更改緩沖區互斥等待檢查。若要查看更改緩沖區檢測，請發出以下查詢：

mysql> SELECT * FROM performance_schema.setup_instrumentsWHERE 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 mutex等待”。

3 自適應哈希索引（Adaptive Hash Index）

????????自適應哈希索引使InnoDB能夠在具有適當的工作負載組合和足夠的緩沖池內存的系統上執行更像內存中的數據庫，而不會犧牲事務特性或可靠性。

自適應哈希索引由innodb_adaptive_hash_index變量啟用

或在服務器啟動時由--skip-innodb-adaptive-hash-index關閉。

根據觀察到的搜索模式，使用索引鍵的前綴構建哈希索引。

前綴可以是任何長度，并且可能只有B樹中的一些值出現在哈希索引中。

哈希索引是根據經常訪問的索引頁的需要構建的。

如果一個表幾乎完全適合主內存，那么哈希索引可以通過直接查找任何元素來加快查詢速度，將索引值變成某種指針。

InnoDB有一個監視索引搜索的機制。如果InnoDB注意到查詢可以從構建哈希索引中受益，它會自動這樣做。

博主PS：

我們知道表的數據是通過頁組成為一顆B+樹存放的，自適應哈希索引的意義就是監控哪些頁經常被訪問到，那么就把這些經常訪問到的頁使用hash索引，鍵值對的方式存放下來，下一次要找某頁數據時，就不從B+樹尋找了，直接hash判斷頁的位置。我們知道B+樹本質是二分查找法。二分查找是永遠沒有hash算法直接計算數據位置來得快的。

對于某些工作負載，哈希索引查找的速度大大超過了監視索引查找和維護哈希索引結構的額外工作。在繁重的工作負載（例如多個并發聯接）下，對自適應哈希索引的訪問有時會成為爭用的來源。使用LIKE運算符和%通配符的查詢也往往沒有好處。

對于沒有從自適應哈希索引中獲益的工作負載，關閉它可以減少不必要的性能開銷。

由于很難提前預測自適應哈希索引是否適用于特定的系統和工作負載，請考慮在啟用和禁用它的情況下運行基準測試。

對自適應哈希索引功能進行了分區。每個索引都綁定到一個特定的分區，每個分區都由一個單獨的鎖存器保護。分區由innodb_adaptive_hash_index_parts變量控制。