InnoDB緩沖池揭秘：MySQL中的資料緩存利器

配置大量記憶體最大的原因其實不是因為可以在記憶體中儲存大量資料：最終目的是避免磁盤I/O，因為磁盤I/O 比在記憶體中通路資料要慢得多。關鍵是要平衡記憶體和磁盤的大小、 速度、成本和其他因素，以便為工作負載提供高性能的表現。

MySQL 需要為緩存配置設定更多的記憶體。它使用緩存來避免磁盤通路，磁盤通路比記憶體通路資料要慢得多。作業系統可能會緩存一些資料，這對 MySQL 有些好處（尤其是對 MyISAM)，但是 MysQL 自身也需要大量記憶體。

下面是我們認為對大部分情說來說最重要的緩存：

• InnoDB 緩沖池
• InnoDB 日志檔案和 MyISAM資料的作業系統緩存
• MyISAM 鍵緩存
• 查詢緩存
• 無法手工配置的緩存，例如二進制日志和表定義檔案的作業系統緩存

接下來我們重點學習 InnoDB 緩沖池。

介紹

InnoDB是基于磁盤存儲的，并将其中的資料按頁的方式進行管理。是以InnoDB可視為基于磁盤的資料庫系統。為了緩解 CPU 與磁盤速度之間的沖突，基于磁盤的資料庫系統通常使用緩沖池技術來提高資料庫的整體性能。

緩沖池其實就是一塊記憶體區域，沒什麼特别的。緩沖池（Buffer Pool）的預設大小為 128M，可通過 innodb_buffer_pool_size 參數來配置。

如果大部分都是 InnoDB表，InnoDB 緩沖池或許比其他任何東西更需要記憶體。關于緩沖池的架構圖如下所示：

由圖可知，緩沖池中緩存的資料頁類型有：索引頁、資料頁、undo 頁、插入緩存（insert buffer）、自适應哈希索引（adaptive hash index）、鎖資訊、資料字典資訊等。

InnoDB 還使用緩沖池來幫助延遲寫入，這樣就能合并多個寫入操作，然後一起順序地寫回。總之，InnoDB 嚴重依賴緩沖池，你必須确認為它配置設定了足夠的記憶體。但不是說緩沖池的記憶體大小越大越好，如果資料量不大，且資料增長緩慢，就沒必要給緩沖池配置設定過多的記憶體；如果資料量增長迅速，則可以提前規劃好緩沖池大小。

執行下述指令可以檢視緩沖池的狀态資訊：

SQL複制代碼show engine innodb status;
           

往下定位到 BUFFER POOL AND MEMORY，相關字段含義如下：

• Total large memory allocated: 這是指為InnoDB存儲引擎配置設定的總記憶體大小，機關為位元組。在此例中，總共配置設定了 274,726,912 位元組的記憶體。
• Dictionary memory allocated: 這是指為InnoDB字典（例如表結構和索引資訊）配置設定的記憶體大小，機關為位元組。在此例中，配置設定了 23,120,009 位元組的記憶體。
• Buffer pool size: 這是指緩沖池的大小，表示用于存儲資料頁的記憶體大小，機關為頁（通常為 16KB）。在此例中，緩沖池大小為 16,384 頁。
• Free buffers: 這是指目前緩沖池中空閑的緩沖區數量。在此例中，有 1,018 個空閑緩沖區。
• Pending reads: 這是指目前正在等待讀取的資料庫頁數。在此例中，沒有任何等待讀取的資料庫頁。
• Pending writes: LRU 0, flush list 0, single page 0: 這是指目前正在等待寫入到磁盤的資料庫頁數。在此例中，沒有任何等待寫入的資料庫頁。
• Buffer pool hit rate: 這是指緩沖池的命中率，表示從緩沖池中讀取資料頁時的命中次數與總的讀取次數的比率。在此例中，命中率為 980/1000，即為98%。

資料頁

在Innodb的B+樹中，我們常說的節點被稱之為 頁(page)，每個頁當中存儲了使用者資料，所有的頁合在一起組成了一顆B+樹。

頁 是InnoDB存儲引擎管理資料庫的最小磁盤機關，我們常說每個節點16KB，其實就是指每頁的大小為16KB。

在 Buffer Pool 中，也是以資料頁為資料機關，存放着很多資料。但是我們通常叫做緩存頁，因為 Buffer Pool 畢竟是一個緩沖池，并且裡面的資料都是從磁盤檔案中緩存到記憶體中。

緩沖池和磁盤之間的資料交換的機關是資料頁，包括從磁盤中讀取資料到緩沖池和緩沖池中資料刷回磁盤中，如圖所示：

當MySQL服務重新開機和關閉時，通常需要進行緩沖池的預熱和關閉操作。接下來我們具體學習了解一下預熱和關閉操作做了哪些事。

預熱

MySQL 5.6 引入了資料預熱機制。innodb_buffer_pool_dump_at_shutdown 和 innodb_buffer_pool_load_at_startup 兩個參數控制了預熱，不過預設都是關閉的，需要開啟。MySQL 5.7則是預設開啟。

InnoDB Buffer Pool 預熱機制原理

1、關閉MySQL，執行導出 InnoDB Buffer Pool 資料到檔案

• 在關閉MySQL時，将 InnoDB Buffer Pool 的資料儲存到磁盤上，并且導出的檔案是經過壓縮的。
• ib_buffer_pool 是InnoDB Buffer Pool中資料儲存到磁盤上的檔案名，它的名字和路徑受 innodb_buffer_pool_filename 控制。該檔案預設儲存在InnoDB的資料目錄下。
• ib_buffer_pool 檔案中儲存了 tablespace IDs 和 page IDs 。

開啟 “關閉MySQL導出 InnoDB Buffer Pool 資料”功能，如果需要永久執行，請加到my.cnf 。

SQL複制代碼SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON;
           

2、啟動MySQL，執行ib_buffer_pool檔案恢複到 InnoDB Buffer Pool

• 根據ib_buffer_pool 檔案中的tablespace IDs 和 page IDs ，将資料恢複到 InnoDB Buffer Pool。tablespace IDs和page IDs資訊來自INNODB_BUFFER_PAGE_LRU表。
• ib_buffer_pool 檔案過舊沒有關閉，MySQL會比對資料的新老，如果磁盤中page最近有過DML操作（如update），那麼ib_buffer_pool中的資料不會加載到 InnoDB Buffer Pool中。
• 如果MySQL中有的page已經不存在了，那麼加載機制會跳過這個page，不會把加載。

開啟 “啟動MySQL，InnoDB Buffer Pool曆史資料導入”功能，建議直接加入到my.cnf 。

SQL複制代碼mysqld --innodb_buffer_pool_load_at_startup=ON;
           

3、Online儲存和恢複InnoDB Buffer Pool資料

在MySQL運作時，可以将InnoDB Buffer Pool資料儲存到磁盤，或者恢複到InnoDB Buffer Pool。

SQL複制代碼SET GLOBAL innodb_buffer_pool_dump_now=ON;
SET GLOBAL innodb_buffer_pool_load_now=ON;
           

4、檢視執行儲存、恢複 InnoDB Buffer Pool 的進展狀态。主要用于Online儲存和恢複場景。

<1>顯示執行 InnoDB Buffer Pool 資料儲存到磁盤的進展狀态

SQL複制代碼mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 170112 17:26:02 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.01 sec)
           

<2>顯示恢複 InnoDB Buffer Pool資料時的進展狀态

SQL複制代碼mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170112 17:31:22 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)
           

5、終止 InnoDB Buffer Pool 恢複操作

SQL複制代碼SET GLOBAL innodb_buffer_pool_load_abort=ON;
           

6、其他

如果MySQL 版本小于MySQL 5.6，可以手動對資料進行預熱。對經常被使用的表進行count(*) 操作，也能起到資料預熱的效果。

SQL複制代碼select count(*) t1;
select count(*) t2;
           

上文涉及到的參數，可以執行下面這些查詢語句來看目前狀态值。

SQL複制代碼SELECT @@innodb_buffer_pool_dump_now;
SELECT @@innodb_buffer_pool_load_now;
SELECT @@innodb_buffer_pool_load_at_startup;
SELECT @@innodb_buffer_pool_dump_at_shutdown;
           

緩存頁管理

緩沖池 buffer pool 除了資料頁的加載和導出，還需要對資料頁進行管理。MySQL會使用 Free 連結清單、Flush 連結清單和 LRU連結清單來管理緩沖池中的資料頁，我們來學習一下。

緩沖池中的頁不僅需要被讀取，還需要進行修改操作。修改的頁肯定發生在 LRU 連結清單中，當 LRU連結清單中的頁被修改後，則稱該頁為髒頁（dirty page)，即緩沖池中的頁和磁盤上的頁資料産生了不一緻。這時資料庫會通過 checkpoint 機制将髒頁重新整理回磁盤。而 flush 連結清單中的頁即為髒頁。需要注意的是，髒頁既存在于 LRU連結清單中，也存在于 flush 連結清單中。LRU連結清單用于管理緩沖池中頁的可用性，flush連結清單則用于管理将頁重新整理回磁盤，兩者互不影響。下圖顯示了 free 連結清單、LRU 連結清單、flush 連結清單之間的關系：

Free 連結清單記錄空閑緩存頁

Free 連結清單，它是一個雙向連結清單，連結清單的每個節點就是一個個空閑的緩存頁對應的描述資料塊。每個描述資料塊裡都有兩個指針，一個是 free_pre 指針，一個是 free_next 指針，分别指向自己的上一個 free 連結清單的節點，以及下一個 free 連結清單的節點。

Free 連結清單用于跟蹤空閑的資料頁，即未被任何資料占用的頁。這些頁可以用來存儲新讀取或修改的資料頁。

當需要從磁盤讀取新的資料頁時，MySQL會從Free連結清單中擷取可用的空閑頁，并将其加載到緩沖池中。

當 InnoDB 存儲引擎啟動後，其緩沖池是空的，所有頁都在 free 連結清單中。由于資料庫的所有讀/寫操作都需要首先在緩沖池中完成，故緩沖池的首要任務就是将外存中的頁讀取到緩沖池中，一般也稱作頁的實體讀取 （physical read)。

在高并發環境下，如果有多個線程進行并發通路同一個頁時，隻有第一個通路讀實體頁的線程進行讀取操作，其他線程需要等待該I/O操作完成，MySQL 會對此進行并發控制的保護。

Flush 連結清單記錄髒緩存頁

Flush連結清單用于跟蹤已被修改但尚未寫入磁盤的資料頁，也稱為髒頁（Dirty Pages）。

當資料頁在緩沖池中被修改後，它們會被添加到Flush連結清單中，表示需要将其重新整理（Flush）到磁盤中以確定資料的持久性。

Flush連結清單通常使用一個連結清單資料結構進行管理，每個節點表示一個髒頁。這些節點包含了指向下一個髒頁的指針。

在背景或适當的時機，MySQL會将Flush連結清單中的髒頁寫入到磁盤，進而保持資料的一緻性和持久性。

LRU 連結清單記錄緩存頁的命中率

MySQL會使用LRU（最近最少使用）等算法來管理緩沖池中的資料頁。如果緩沖池已滿，MySQL可能會根據算法的規則将一些較早未使用的資料頁從記憶體中淘汰出去，以為新的預讀資料騰出空間。

InnoDB 記憶體管理用的是最近最少使用 (Least Recently Used, LRU) 算法，這個算法的核心就是淘汰最久未使用的資料。

下圖是一個 LRU 算法的基本模型。

InnoDB 管理 Buffer Pool 的 LRU 算法，是用連結清單來實作的。

1. 在上圖 的state 1 裡，連結清單頭部是 P1，表示 P1 是最近剛剛被通路過的資料頁；
2. 假設記憶體裡隻能放下這麼多資料頁；這時候有一個讀請求通路 P3，是以變成state 2，P3 被移到最前面；
3. state 3 表示，這次通路的資料頁是不存在于連結清單中的，是以需要在 Buffer Pool 中新申請一個資料頁 Px，加到連結清單頭部。但是由于記憶體已經滿了，不能申請新的記憶體。于是，會清空連結清單末尾 Pm 這個資料頁的記憶體，存入 Px 的内容，然後放到連結清單頭部。
4. 從效果上看，就是最久沒有被通路的資料頁 Pm，被淘汰了。

如果做全表掃描一個 200G 的曆史資料表，平時不怎麼通路，按照上述的算法就會把目前的 Buffer Pool 裡的資料全部淘汰掉，存入掃描過程中通路到的資料頁的内容。也就是說 Buffer Pool 裡面主要放的是這個曆史資料表的資料。

對于一個正在做業務服務的庫，這可不妙。你會看到，Buffer Pool 的記憶體命中率急劇下降，磁盤壓力增加，SQL 語句響應變慢。

是以，InnoDB 不能直接使用這個 LRU 算法。是以，InnoDB 對 LRU 算法做了改進。

在 InnoDB 實作上，按照 5:3 的比例把整個 LRU 連結清單分成了 young 區域和 old 區域。圖中 LRU_old 指向的就是 old 區域的第一個位置，是整個連結清單的 5/8 處。也就是說，靠近連結清單頭部的 5/8 是 young 區域，靠近連結清單尾部的 3/8 是 old 區域。

具體來說就是：新增了midPoint位置。新讀取到的頁并沒有直接放在LRU列的首部，而是放在距離尾部37%的位置。這個算法稱之為midpoint insertion stategy。

• midPoint在整體清單的 5/8 處
• midpoint之前的是new區域（熱資料）
• midpoint之後的資料是不活躍資料，old區域。
• midpoint處，是新子清單的尾部與舊子清單的頭相交的邊界

檢視midpoint

SQL複制代碼mysql> show variables like 'innodb_old_blocks_pct';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.04 sec)
           

改進後的 LRU 算法執行流程變成了下面這樣。

1. 上圖中state 1，要通路資料頁 P3，由于 P3 在 young 區域，是以和優化前的 LRU 算法一樣，将其移到連結清單頭部，變成state 2。
2. 之後要通路一個新的不存在于目前連結清單的資料頁，這時候依然是淘汰掉資料頁 Pm，但是新插入的資料頁 Px，是放在 LRU_old 處。
3. 處于 old 區域的資料頁，每次被通路的時候都要做下面這個判斷： 若這個資料頁在 LRU 連結清單中存在的時間超過了 1 秒，就把它移動到連結清單頭部； 如果這個資料頁在 LRU 連結清單中存在的時間短于 1 秒，位置保持不變。1 秒這個時間，是由參數 innodb_old_blocks_time 控制的。其預設值是 1000，機關毫秒。

這個政策，就是為了處理類似全表掃描的操作量身定制的。還是以剛剛的掃描 200G 的曆史資料表為例，我們看看改進後的 LRU 算法的操作邏輯：

1. 掃描過程中，需要新插入的資料頁，都被放到 old 區域 ;
2. 一個資料頁裡面有多條記錄，這個資料頁會被多次通路到，但由于是順序掃描，這個資料頁第一次被通路和最後一次被通路的時間間隔不會超過 1 秒，是以還是會被保留在 old 區域；
3. 再繼續掃描後續的資料，之前的這個資料頁之後也不會再被通路到，于是始終沒有機會移到連結清單頭部（也就是 young 區域），很快就會被淘汰出去。

可以看到，這個政策最大的收益，就是在掃描這個大表的過程中，雖然也用到了 Buffer Pool，但是對 young 區域完全沒有影響，進而保證了 Buffer Pool 響應正常業務的查詢命中率。

總結

InnoDB緩沖池是MySQL中一項關鍵技術，它扮演着資料緩存的重要角色。本文深入介紹了InnoDB緩沖池的核心概念，從緩沖池中資料頁的存儲結構到預熱機制的工作原理，一一揭示了其神奇之處。而除了加載和導出資料頁，緩沖池還通過Free連結清單、Flush連結清單和LRU連結清單對資料頁進行精準管理。