mysql的Buffer Pool儲存及原理解析

一、前言

1、buffer pool是什麼

咱們在使用mysql的時候，比如很簡單的select * from table;這條語句，具體查詢資料其實是在儲存引擎中實現的，資料庫中的資料實際上最終都是要存放在磁碟檔案上的，如果每次查詢都直接從磁碟裡面查詢，這樣勢必會很影響效能，所以一定是先把資料從磁碟中取出，然後放在記憶體中，下次查詢直接從記憶體中來取。
但是一臺機器中往往不是隻有mysql一個程序在執行的，很多個程序都需要使用記憶體，所以mysql中會有一個專門的記憶體區域來處理這些資料，這個專門為mysql準備的區域，就叫buffer pool。
buffer pool是mysql一個非常關鍵的核心元件。

如下圖所示：

在對資料庫執行增刪改操作的時候，不可能直接更新磁碟上的資料的，因為如果你對磁碟進行隨機讀寫操作，那速度是相當的慢，隨便一個大磁碟檔案的隨機讀寫操作，可能都要幾百毫秒。如果要是那麼搞的話，可能你的資料庫每秒也就只能處理幾百個請求了！ 在對資料庫執行增刪改操作的時候，實際上主要都是針對記憶體裡的Buffer Pool中的資料進行的，也就是實際上主要是對**資料庫的記憶體(Buffer Pool)**裡的資料結構進行了增刪改。
如下圖所示：

操作記憶體的主要問題就是在資料庫的記憶體裡執行了一堆增刪改的操作，記憶體資料是更新了，但是這個時候如果資料庫突然崩潰了，那麼記憶體裡更新好的資料不是都沒了嗎？ MySQL就怕這個問題，所以引入了一個redo log機制，你在對記憶體裡的資料進行增刪改的時候，他同時會把增刪改對應的紀錄檔寫入redo log中。

如下圖：

萬一資料庫突然崩潰了，沒關係，只要從redo log紀錄檔檔案裡讀取出來你之前做過哪些增刪改操作，瞬間就可以重新把這些增刪改操作在你的記憶體裡執行一遍，這就可以恢復出來你之前做過哪些增刪改操作了。
當然對於資料更新的過程，它是有一套嚴密的步驟的，還涉及到undo log、binlog、提交事務、buffer pool髒資料刷回磁碟等等。

小結：

Buffer Pool就是資料庫的一個記憶體元件，裡面快取了磁碟上的真實資料，然後我們的系統對資料庫執行的增刪改操作，其實主要就是對這個記憶體資料結構中的快取資料執行的。通過這種方式，保證每個更新請求，儘量就是隻更新記憶體，然後往磁碟順序寫紀錄檔檔案。
更新記憶體的效能是極高的，然後順序寫磁碟上的紀錄檔檔案的效能也是比較高的，因為順序寫磁碟檔案，他的效能要遠高於隨機讀寫磁碟檔案。

2、buffer pool的工作流程

以查詢語句為例

• 在查詢的時候會先去buffer pool(記憶體)中看看有沒有對應的資料頁，如果有的話直接返回
• 如果buffer pool中沒有對應的資料頁，則會去磁碟中查詢，磁碟中如果找到了對應的資料，則會把該頁的資料直接copy一份到buffer pool中返回給使用者端
• 下次有同樣的查詢進來直接查詢buffer pool找到對應的資料返回即可。

3、buffer pool緩衝池和查詢快取(query cache)

在正式講解buffer pool 之前，我們先搞清楚buffer pool緩衝池和查詢快取(query cache)簡稱Qcache的區別。

如果將Mysql分為Server層和儲存引擎層兩大部分，那麼Qcache位於Server層，Buffer Pool位於儲存引擎層。

如果Mysql 查詢快取功能是開啟的，那麼當一個sql進入Mysql Server之後，Mysql Server首先會從查詢快取中檢視是否曾經執行過這個SQL，如果曾經執行過的話，曾經執行的查詢結果之前會以key-value的形式儲存在查詢快取中。key是sql語句，value是查詢結果。我們將這個過程稱為查詢快取。查詢快取會被所有的session共用。
如果查詢快取中沒有你要找的資料的話，MySQL才會執行後續的邏輯，通過儲存引擎將資料檢索出來。
MySQL查詢快取是查詢結果快取。它將以SEL開頭的查詢與雜湊表進行比較，如果匹配，則返回上一次查詢的結果。進行匹配時，查詢必須逐位元組匹配，例如 SELECT * FROM t1; 不等於select * from t1;，此外，一些不確定的查詢結果無法被快取，任何對錶的修改都會導致這些表的所有快取無效(只要有一個sql update了該表，那麼表的查詢快取就會失效)。因此，適用於查詢快取的最理想的方案是唯讀，特別是需要檢查數百萬行後僅返回數行的複雜查詢。如果你的查詢符合這樣一個特點，開啟查詢快取會提升你的查詢效能。
MySQL查詢快取的目的是為了提升查詢效能，但它本身也是有效能開銷的。需要在合適的業務場景下（讀寫壓力模型）使用，不合適的業務場景不但不能提升查詢效能，查詢快取反而會變成MySQL的瓶頸。

查詢快取的開銷主要有：

• 讀查詢在開始前必須先檢查是否命中快取；
• 如果這個讀查詢可以被快取，那麼當完成執行後，MySQL若發現查詢快取中沒有這個查詢，會將其結果存入查詢快取，這會帶來額外的系統消耗；
• 當向某個表寫入資料的時候，MySQL必須將對應表的所有快取都設定失效。如果查詢快取非常大或者碎片很多，這個操作就可能帶來很大的系統消耗。

查詢快取的缺點：
首先，查詢快取的效果取決於快取的命中率，只有命中快取的查詢效果才能有改善，因此無法預測其效能。只要有一個sql update了該表，那麼表的查詢快取就會失效，所以當你的業務對錶CRUD的比例不相上下，那麼查詢快取會影響應用的吞吐效率。
其次，查詢快取的另一個大問題是它受到單個互斥鎖的保護。在具有多個核心的伺服器上，大量查詢會導致大量的互斥鎖爭用。

注意：在mysql8.0的版本中，已經將查詢快取模組刪除了。

二、buffer pool的記憶體資料結構

1、資料頁概念

我們先了解一下資料頁這個概念。它是 MySQL 抽象出來的資料單位，磁碟檔案中就是存放了很多資料頁，每個資料頁裡存放了很多行資料。
預設情況下，資料頁的大小是 16kb。
所以對應的，在 Buffer Pool 中，也是以資料頁為資料單位，存放著很多資料。但是我們通常叫做快取頁，因為 Buffer Pool 畢竟是一個緩衝池，並且裡面的資料都是從磁碟檔案中快取到記憶體中。它和磁碟檔案中資料頁是一一對應的。

假設我們要更新一行資料，此時資料庫會找到這行資料所在的資料頁，然後從磁碟檔案裡把這行資料所在的資料頁直接給載入到Buffer Pool裡去。如下圖。

2、那麼怎麼識別資料在哪個快取頁中

每個快取頁都會對應著一個描述資料塊，裡面包含資料頁所屬的表空間、資料頁的編號，快取頁在 Buffer Pool 中的地址等等。
描述資料塊本身也是一塊資料，它的大小大概是快取頁大小的5%左右。假設你設定的buffer pool大小是128MB，實際上Buffer Pool真正的最終大小會超出一些，可能有個130多MB的樣子，因為他裡面還要存放每個快取頁的描述資料。
在Buffer Pool中，每個快取頁的描述資料放在最前面，然後各個快取頁放在後面。

所以Buffer Pool實際看起來大概如下：

3、buffer pool的初始化與設定

3.1、初始化

• MySQL 啟動時，會根據引數 innodb_buffer_pool_size 的值來為 Buffer Pool 分配記憶體區域。
• 然後會按照快取頁的預設大小 16k 以及對應的描述資料塊大小，在 Buffer Pool 中劃分中一個個的快取頁和一個個的描述資料庫塊。
• 注意：此時的快取頁和描述資料塊都是空的，畢竟才剛啟動 MySQL。

3.2、buffer pool的設定

buffer pool通常由數個記憶體塊加上一組控制結構體物件組成。記憶體塊的個數取決於buffer pool instance的個數，不過在5.7版本中開始預設以128M(可設定)的chunk單位分配記憶體塊，這樣做的目的是為了支援buffer pool的線上動態調整大小。
Buffer Pool預設情況下是128MB，還是有一點偏小了，我們實際生產環境下完全可以對Buffer Pool進行調整。 比如我們的資料庫如果是16核32G的機器，那麼你就可以給Buffer Pool分配個2GB的記憶體。

主要設定引數如下：

• innodb_buffer_pool_size：這個值是設定 InnoDB Buffer Pool 的總大小；
• innodb_buffer_pool_chunk_size：當增加或減少innodb_buffer_pool_size時，操作以塊（chunk）形式執行，預設是128MB。
• innodb_buffer_pool_instances：設定 InnoDB Buffer Pool 範例的個數，每一個範例都有自己獨立的 list 管理Buffer Pool；
• innodb_old_blocks_pct：預設 InnoDB Buffer Pool 中點的位置，預設值是37，最大100，也就是我們所謂的3/8的位置，可以自己設定。

這裡面有個關係要確定一下，最好按照這個設定 innodb_buffer_pool_size=innodb_buffer_pool_chunk_size * innodb_buffer_pool_instancesN（N>=1）;
當buffer pool比較大的時候（超過1G），innodb會把buffer pool劃分成幾個instances，這樣可以提高讀寫操作的並行，減少競爭。讀寫page都使用hash函數分配給一個instances。
當增加或者減少buffer pool大小的時候，實際上是操作的chunk。buffer pool的大小必須是innodb_buffer_pool_chunk_sizeinnodb_buffer_pool_instances的整數倍，如果不是的話，innodb會自動調整的。
比如：
如果指定的buffer pool size大小是9G，instances的個數是16，chunk預設的大小是128M，那麼buffer會自動調整為10G。因為9216MB/16/128MB = 4.5不是整數倍，會自動調整為10240MB/16/128MB=5整數倍

3.3、Buffer Pool Size 設定和生效過程

理想情況下，在給伺服器的其他程序留下足夠的記憶體空間的情況下，Buffer Pool Size 應該設定的儘可能大。當 Buffer Pool Size 設定的足夠大時，整個資料庫就相當於儲存在記憶體當中，當讀取一次資料到 Buffer Pool Size 以後，後續的讀操作就不用再存取磁碟。

設定方式：
當資料庫已經啟動的情況下，我們可以通過線上調整的方式修改 Buffer Pool Size 的大小。
通過以下語句：SET GLOBAL innodb_buffer_pool_size=402653184;
當執行這個語句以後，並不會立即生效，而是要等所有的事務全部執行成功以後才會生效；新的連線和事務必須等其他事務完全執行成功以後，Buffer Pool Size 設定生效以後才能夠連線成功，不然會一直處於等待狀態。
期間，Buffer Pool Size 要完成碎片整理，去除快取 page 等等操作。在執行增加或者減少 Buffer Pool Size 的操作時，操作會作為一個執行塊執行，innodb_buffer_pool_chunk_size 的大小會定義一個執行塊的大小，預設的情況下，這個值是128M。
Buffer Pool Size 的大小最好設定為 innodb_buffer_pool_chunk_size/ innodb_buffer_pool_instances 的整數倍，而且是大於等於1。
如果我們要查 Buffer Pool 的狀態的話，可以使用一下sql:
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
可以幫我們檢視到狀態。我們可以看一下增加 Buffer Pool 的時候的一個過程，再看一下減少的時候的紀錄檔，其實還是很好理解的，我們可以看成每次增大或者減少 Buffer Pool 的時候就是進行 innodb_buffer_pool_chunk 的增加或者釋放，按照 innodb_buffer_pool_chunk_size 設定值的大小增加或者釋放執行塊。

• 增加的過程： 增加執行塊，指定新地址，將新加入的執行塊加入到 free list（控制執行塊的一個列表）。
• 減少的過程： 重新整理 Buffer Pool 和空閒頁，將資料從塊中移除，指定新地址。

3.4、Buffer Pool Instances

在64位元作業系統的情況下，可以拆分緩衝池成多個部分，這樣可以在高並行的情況下最大可能的減少爭用。
設定多個 Buffer Pool Instances 能在很大程度上能夠提高 MySQL 在高並行的情況下處理事物的效能，優化不同連線讀取緩衝頁的爭用。
我們可以通過設定 innodb_buffer_pool_instances 來設定 Buffer Pool Instances。當 InnoDB Buffer Pool 足夠大的時候，你能夠從記憶體中讀取時候能有一個較好的效能，但是也有可能碰到多個執行緒同時請求緩衝池的瓶頸。這個時候設定多個 Buffer Pool Instances 能夠儘量減少連線的爭用。
這能夠保證每次從記憶體讀取的頁都對應一個 Buffer Pool Instances，而且這種對應關係是一個隨機的關係。並不是熱資料存放在一個 Buffer Pool Instances下，內部也是通過 hash 演演算法來實現這個亂數的。每一個 Buffer Pool Instances 都有自己的 free lists，LRU 和其他的一些 Buffer Pool 的資料結構，各個 Buffer Pool Instances 是相對獨立的。
innodb_buffer_pool_instances 的設定必須大於1才算得上是多設定，但是這個功能起作用的前提是innodb_buffer_pool_size 的大小必須大於1G，理想情況下 innodb_buffer_pool_instances 的每一個 instance 都保證在1G以上。

3.5、SHOW ENGINE INNODB STATUS

當你的資料庫啟動之後，你隨時可以通過上述命令，去檢視當前innodb裡的一些具體情況，執行:
SHOW ENGINE INNODB STATUS
就可以了。此時你可能會看到如下一系列的東西：

下面解釋一下這裡的東西，主要講解這裡跟buffer pool相關的一些東西。

• Total memory allocated，這就是說buffer pool最終的總大小是多少
• Buffer pool size，這就是說buffer pool一共能容納多少個快取頁
• Free buffers，這就是說free連結串列中一共有多少個空閒的快取頁是可用的
• Database pages和Old database pages，就是說lru連結串列中一共有多少個快取頁，以及冷資料區域裡的快取頁數量
• Modified db pages，這就是flush連結串列中的快取頁數量
• Pending reads和Pending writes，等待從磁碟上載入進快取頁的數量，還有就是即將從lru連結串列中刷入磁碟的數量、即將從flush連結串列中刷入磁碟的數量
• Pages made young和not young，這就是說已經lru冷資料區域裡存取之後轉移到熱資料區域的快取頁的數 量，以及在lru冷資料區域裡1s內被存取了沒進入熱資料區域的快取頁的數量
• youngs/s和not youngs/s，這就是說每秒從冷資料區域進入熱資料區域的快取頁的數量，以及每秒在冷資料區域裡被存取了但是不能進入熱資料區域的快取頁的數量
• Pages read xxxx, created xxx, written xxx，xx reads/s, xx creates/s, 1xx writes/s，這裡就是說已經讀取、建立和寫入了多少個快取頁，以及每秒鐘讀取、建立和寫入的快取頁數量
• Buffer pool hit rate xxx / 1000，這就是說每1000次存取，有多少次是直接命中了buffer pool裡的快取的
• young-making rate xxx / 1000 not xx / 1000，每1000次存取，有多少次存取讓快取頁從冷資料區域移動到了熱資料區域，以及沒移動的快取頁數量
• LRU len：這就是lru連結串列裡的快取頁的數量
• I/O sum：最近50s讀取磁碟頁的總數
• I/O cur：現在正在讀取磁碟頁的數量

三、buffer pool的空間管理

緩衝池也是有大小限制的，那麼既然緩衝池有大小限制的，每次都讀入的資料頁怎麼來管理呢？這裡我們來聊聊緩衝池的空間管理，其實對緩衝池進行管理的關鍵部分是如何安排進池的資料並且按照一定的策略淘汰池中的資料，保證池中的資料不溢位，同時還能保證常用資料留在池子中。

1、傳統 LRU 淘汰法

緩衝池是基於傳統的 LRU 方法來進行快取頁管理的，我們先來看下如果使用 LRU 是如何管理的。
LRU，全稱是 Least Recently Used，中文名字叫作「最近最少使用」。從名字上就很容易理解了。
這裡分兩種情況：

1.1、快取頁已在緩衝池中

這種情況下會將對應的快取頁放到 LRU 連結串列的頭部，無需從磁碟再進行讀取，也無需淘汰其它快取頁。
如下圖所示，如果要存取的資料在 6 號頁中，則將 6 號頁放到連結串列頭部即可，這種情況下沒有快取頁被淘汰。

1.2、快取頁不在緩衝池中

快取頁不在緩衝中，這時候就需要從磁碟中讀入對應的資料頁，將其放置在連結串列頭部，同時淘汰掉末尾的快取頁
如下圖所示，如果要存取的資料在 60 號頁中，60 號頁不在緩衝池中，此時載入進來放到連結串列的頭部，同時淘汰掉末尾的 17 號快取頁。

簡單的LRU演演算法會帶來幾個問題：

• **預讀失效：**上面我們提到了緩衝池的預讀機制可能會預先載入相鄰的資料頁。假如載入了 20、21 相鄰的兩個資料頁，如果只有頁號為 20 的快取頁被存取了，而另一個快取頁卻沒有被存取。此時兩個快取頁都在連結串列的頭部，但是為了載入這兩個快取頁卻淘汰了末尾的快取頁，而被淘汰的快取頁卻是經常被存取的。這種情況就是預讀失效，被預先載入進緩衝池的頁，並沒有被存取到，這種情況是不是很不合理。
• **緩衝池汙染 **：還有一種情況是當執行一條 SQL 語句時，如果掃描了大量資料或是進行了全表掃描，此時緩衝池中就會載入大量的資料頁，從而將緩衝池中已存在的所有頁替換出去，這種情況同樣是不合理的。這就是緩衝池汙染，並且還會導致 MySQL 效能急劇下降。

2、冷熱資料分離

傳統的 LRU 方法並不能滿足緩衝池的空間管理。因此，Msyql 基於 LRU 設計了冷熱資料分離的處理方案。
也就是將 LRU 連結串列分為兩部分，一部分為熱資料區域，一部分為冷資料區域。

當資料頁第一次被載入到緩衝池中的時候，先將其放到冷資料區域的連結串列頭部，1s（由 innodb_old_blocks_time 引數控制） 後該快取頁被存取了再將其移至熱資料區域的連結串列頭部。

為什麼要等 1s 後才將其移至熱資料區域呢？

如果資料頁剛被載入到冷資料區就被存取了，之後再也不存取它了呢？這不就造成熱資料區的浪費了嗎？要是 1s 後不存取了，說明之後可能也不會去頻繁存取它，也就沒有移至熱緩衝區的必要了。當快取頁不夠的時候，從冷資料區淘汰它們就行了。

另一種情況，當我的資料頁已經在熱緩衝區了，是不是快取頁只要被存取了就將其插到連結串列頭部呢？不用我說你肯定也覺得不合理。熱資料區域裡的快取頁是會被經常存取的，如果每存取一個快取頁就插入一次連結串列頭，那整個熱緩衝區裡就異常騷動了，所以，Mysql 中優化為熱資料區的後 3/4 部分被存取後才將其移動到連結串列頭部去，對於前 1/4 部分的快取頁被存取了不會進行移動。

四、Buffer Pool預讀機制

預讀是mysql提高效能的一個重要的特性。預讀就是 IO 非同步讀取多個頁資料讀入 Buffer Pool 的一個過程，並且這些頁被認為是很快就會被讀取到的。InnoDB使用兩種預讀演演算法來提高I/O效能：線性預讀（linear read-ahead）和隨機預讀（randomread-ahead）
為了區分這兩種預讀的方式，我們可以把線性預讀放到以extent為單位，而隨機預讀放到以extent中的page為單位。線性預讀著眼於將下一個extent提前讀取到buffer pool中，而隨機預讀著眼於將當前extent中的剩餘的page提前讀取到buffer pool中。

Linear線性預讀

線性預讀的單位是extend，一個extend中有64個page。線性預讀的一個重要引數innodb_read_ahead_threshold，是指在連續存取多少個頁面之後，把下一個extend讀入到buffer pool中，不過預讀是一個非同步的操作。當然這個引數不能超過64，因為一個extend最多隻有64個頁面。
例如，innodb_read_ahead_threshold = 56，就是指在連續存取了一個extend的56個頁面之後把下一個extend讀入到buffer pool中。在新增此引數之前，InnoDB僅計算當它在當前範圍的最後一頁中讀取時是否為整個下一個範圍發出非同步預取請求。

Random隨機預讀

隨機預讀方式則是表示當同一個extent中的一些page在buffer pool中發現時，Innodb會將該extent中的剩餘page一併讀到buffer pool中。由於隨機預讀方式給innodb code帶來了一些不必要的複雜性，同時在效能也存在不穩定性，在5.5中已經將這種預讀方式廢棄，預設是OFF。若要啟用此功能，即將設定變數設定innodb_random_read_ahead為ON。

五、Buffer Pool的三種Page和連結串列

Buffer Pool 是Innodb 記憶體中的的一塊佔比較大的區域，用來快取表和索引資料。眾所周知，從記憶體存取會比從磁碟存取快很多。為了提高資料的讀取速度，Buffer Pool 會通過三種Page 和連結串列來管理這些經常存取的資料，保證熱資料不被置換出Buffer Pool。

1、三種Page

• Free Page（空閒頁）： 表示此Page 未被使用，位於 Free 連結串列。
• Clean Page（乾淨頁）： 此Page 已被使用，但是頁面未發生修改，位於LRU 連結串列。
• Dirty Page（髒頁）： 此Page 已被使用，頁面已經被修改，其資料和磁碟上的資料已經不一致。當髒頁上的資料寫入磁碟後，記憶體資料和磁碟資料一致，那麼該Page 就變成了乾淨頁。髒頁同時存在於LRU 連結串列和Flush 連結串列。

2、三種連結串列

2.1、LRU 連結串列

如上圖所示，是Buffer Pool裡面的LRU(least recently used)連結串列。LRU連結串列是被一種叫做最近最少使用的演演算法管理。
LRU連結串列被分成兩部分：

• New Sublist(Young 連結串列)，用來存放經常被讀取的頁的地址。
• Old Sublist(Old 連結串列)，用來存放較少被使用的頁面。每部分都有對應的頭部和尾部。

預設情況下

• Old 連結串列佔整個LRU 連結串列的比例是3/8。該比例由innodb_old_blocks_pct控制，預設值是37（3/8*100），該值取值範圍為5~95，為全域性動態變數。
• 當新的頁被讀取到Buffer Pool裡面的時候，和傳統的LRU演演算法插入到LRU連結串列頭部不同，Innodb LRU演演算法是將新的頁面插入到Yong 連結串列的尾部和Old 連結串列的頭部中間的位置，這個位置叫做Mid Point。如上圖所示。
• 頻繁存取一個Buffer Pool的頁面( innodb_old_blocks_time間隔時間設定)，會促使頁面往Young連結串列的頭部移動。如果一個Page在被讀到Buffer Pool後很快就被存取，那麼該Page會往Young List的頭部移動，但是如果一個頁面是通過預讀的方式讀到Buffer Pool，且之後短時間內沒有被存取，那麼很可能在下次存取之前就被移動到Old List的尾部，而被驅逐了。
• 隨著資料庫的持續執行，新的頁面被不斷的插入到LRU連結串列的Mid Point，Old 連結串列裡的頁面會逐漸的被移動Old連結串列的尾部。同時，當經常被存取的頁面移動到LRU連結串列頭部的時候，那些沒有被存取的頁面會逐漸的被移動到連結串列的尾部。最終，位於Old 連結串列尾部的頁面將被驅逐。

如果一個資料頁已經處於Young 連結串列，當它再次被存取的時候，只有當其處於Young 連結串列長度的1/4(大約值)之後，才會被移動到Young 連結串列的頭部。這樣做的目的是減少對LRU 連結串列的修改，因為LRU 連結串列的目標是保證經常被存取的資料頁不會被驅逐出去。

移動到young連結串列的時間設定：
innodb_old_blocks_time 控制的Old 連結串列頭部頁面的轉移策略。該Page需要在Old 連結串列停留超過innodb_old_blocks_time 時間，之後再次被存取，才會移動到Young 連結串列。這麼操作是避免Young 連結串列被那些只在innodb_old_blocks_time時間間隔內頻繁存取，之後就不被存取的頁面塞滿，從而有效的保護Young 連結串列。
在全表掃描或者全索引掃描的時候，Innodb會將大量的頁面寫入LRU 連結串列的Mid Point位置，並且只在短時間記憶體取幾次之後就不再存取了。設定innodb_old_blocks_time的時間視窗可以有效的保護Young List，保證了真正的頻繁存取的頁面不被驅逐。
innodb_old_blocks_time 單位是毫秒，預設值是1000。調大該值提高了從Old連結串列移動到Young連結串列的難度，會促使更多頁面被移動到Old 連結串列，老化，從而被驅逐。
當掃描的表很大，Buffer Pool都放不下時，可以將innodb_old_blocks_pct設定為較小的值，這樣唯讀取一次的資料頁就不會佔據大部分的Buffer Pool。例如，設定innodb_old_blocks_pct = 5，會將僅讀取一次的資料頁在Buffer Pool的佔用限制為5％。
當經常掃描一些小表時，這些頁面在Buffer Pool移動的開銷較小，我們可以適當的調大innodb_old_blocks_pct，例如設定innodb_old_blocks_pct = 50。

在SHOW ENGINE INNODB STATUS 裡面提供了Buffer Pool一些監控指標，有幾個我們需要關注一下：

• youngs/s：該指標表示的是每秒存取Old 連結串列中頁面，使其移動到Young連結串列的次數。如果MySQL範例都是一些小事務，沒有大表全掃描，且該指標很小，就需要調大innodb_old_blocks_pct 或者減小innodb_old_blocks_time，這樣會使得Old List 的長度更長，Old頁面被移動到Old List 的尾部消耗的時間會更久，那麼就提升了下一次存取到Old List裡面的頁面的可能性。如果該指標很大，可以調小innodb_old_blocks_pct，同時調innodb_old_blocks_time保護熱資料。
• non-youngs/s：該指標表示的是每秒存取Old 連結串列中頁面，沒有移動到Young連結串列的次數，因為其不符合innodb_old_blocks_time。如果該指標很大，一般情況下是MySQL存在大量的全表掃描。如果MySQL存在大量全表掃描，且這個指標又不大的時候，需要調大innodb_old_blocks_time，因為這個指標不大意味著全表掃描的頁面被移動到Young 連結串列了，調大innodb_old_blocks_time時間會使得這些短時間頻繁存取的頁面保留在Old 連結串列裡面。

髒頁清除執行緒：

• 每隔1秒鐘，Page Cleaner執行緒執行LRU List Flush的操作，來釋放足夠的Free Page。
• innodb_lru_scan_depth 變數控制每個Buffer Pool範例每次掃描LRU List的長度，來尋找對應的髒頁，執行Flush操作。

2.2、Flush 連結串列

• Flush 連結串列裡面儲存的都是髒頁，也會存在於LRU 連結串列。
• Flush 連結串列是按照oldest_modification排序，值大的在頭部，值小的在尾部
• 當有頁面訪被修改的時候，使用mini-transaction，對應的page進入Flush 連結串列
• 如果當前頁面已經是髒頁，就不需要再次加入Flush list，否則是第一次修改，需要加入Flush 連結串列
• 當Page Cleaner執行緒執行flush操作的時候，從尾部開始scan，將一定的髒頁寫入磁碟，推進檢查點，減少recover的時間

SQL 的增刪改查都在 Buffer Pool 中執行，慢慢地，Buffer Pool 中的快取頁因為不斷被修改而導致和磁碟檔案中的資料不一致了，也就是 Buffer Pool 中會有很多個髒頁，髒頁裡面很多髒資料。
所以，MySQL 會有一條後臺執行緒，定時地將 Buffer Pool 中的髒頁刷回到磁碟檔案中。但是，後臺執行緒怎麼知道哪些快取頁是髒頁呢，不可能將全部的快取頁都往磁碟中刷吧，這會導致 MySQL 暫停一段時間。

MySQL 是怎麼判斷髒頁的

我們引入一個和 free 連結串列類似的 flush 連結串列。他的本質也是通過快取頁的描述資料塊中的兩個指標，讓修改過的快取頁的描述資料塊能串成一個雙向連結串列，這兩指標大家可以認為是 flush_pre 指標和 flush_next 指標。

下面我用虛擬碼來描述一下：

DescriptionDataBlock{
block_id = block1; // free 連結串列的
free_pre = null;
free_next = null; // flush 連結串列的
flush_pre = null;
flush_next = block2;
}

flush 連結串列也有對應的基礎節點，也是包含連結串列的頭節點和尾節點，還有就是修改過的快取頁的數量。

FlushListBaseNode{
start = block1;
end = block2;
count = 2;
}

到這裡，我們都知道，SQL 的增刪改都會使得快取頁變為髒頁，此時會修改髒頁對應的描述資料塊的 flush_pre 指標和 flush_next 指標，使得描述資料塊加入到 flush 連結串列中，之後 MySQL 的後臺執行緒就可以將這個髒頁刷回到磁碟中。

2.3、Free 連結串列

• Free 連結串列存放的是空閒頁面，初始化的時候申請一定數量的頁面，當 MySQL 啟動後，會不斷地有 SQL 請求進來，此時空先的快取頁就會不斷地被使用。
• 在執行SQL的過程中，每次成功load 頁面到記憶體後，會判斷Free 連結串列的頁面是否夠用。如果不夠用的話，就flush LRU 連結串列和Flush 連結串列來釋放空閒頁。如果夠用，就從Free 連結串列裡面刪除對應的頁面，在LRU 連結串列增加頁面，保持總數不變。

使用原理

free 連結串列，它是一個雙向連結串列，連結串列的每個節點就是一個個空閒的快取頁對應的描述資料塊。他本身其實就是由 Buffer Pool 裡的描述資料塊組成的，你可以認為是每個描述資料塊裡都有兩個指標，一個是 free_pre 指標，一個是 free_next 指標，分別指向自己的上一個 free 連結串列的節點，以及下一個 free 連結串列的節點。
通過 Buffer Pool 中的描述資料塊的 free_pre 和 free_next 兩個指標，就可以把所有的描述資料塊串成一個 free 連結串列。
下面我們可以用虛擬碼來描述一下 free 連結串列中描述資料塊節點的資料結構：

DescriptionDataBlock{
block_id = block1;
free_pre = null;
free_next = block2;
}

free 連結串列有一個基礎節點，他會參照連結串列的頭節點和尾節點，裡面還儲存了連結串列中有多少個描述資料塊的節點，也就是有多少個空閒的快取頁。

下面我們也用虛擬碼來描述一下基礎節點的資料結構：

FreeListBaseNode{
start = block01;
end = block03;
count = 2;
}

到此，free 連結串列就介紹完了。上面我們也介紹了 MySQL 啟動時 Buffer Pool 的初始流程，接下來，我會將結合剛介紹完的 free 連結串列，講解一下 SQL 進來時，磁碟資料頁讀取到 Buffer Pool 的快取頁的過程。
但是，我們先要了解一下一個新概念：資料頁快取雜湊表，它的 key 是表空間+資料頁號，而 value 是對應快取頁的地址。

描述如圖所示：

磁碟資料頁讀取到 Buffer Pool 的快取頁的過程

• 首先，SQL 進來時，判斷資料對應的資料頁能否在資料頁快取雜湊表裡找到對應的快取頁。如果找到，將直接在 Buffer Pool 中進行增刪改查。
• 如果找不到，則從 free 連結串列中找到一個空閒的快取頁，然後從磁碟檔案中讀取對應的資料頁的資料到快取頁中，並且將資料頁的資訊和快取頁的地址寫入到對應的描述資料塊中，然後修改相關的描述資料塊的 free_pre 指標和 free_next 指標，將使用了的描述資料塊從 free 連結串列中移除。
• 記得，還要在資料頁快取雜湊表中寫入對應的 key-value 對。最後也是在 Buffer Pool 中進行增刪改查。

3、LRU 連結串列和Flush連結串列的區別

LRU連結串列：

• LRU 連結串列 ，由使用者執行緒觸發(MySQL 5.6.2之前)；
• LRU 連結串列，其目的是為了寫出LRU 連結串列尾部的髒頁，釋放足夠的空閒頁，當Buffer Pool滿的時候，使用者可以立即獲得空閒頁面，而不需要長時間等待；
• LRU 連結串列，其寫出的髒頁，需要從LRU連結串列中刪除，移動到Free 連結串列。
• Flush 連結串列由MySQL資料庫InnoDB儲存引擎後臺srv_master執行緒處理。(在MySQL 5.6.2之後，都被遷移到Page Cleaner執行緒中)。
• LRU 連結串列 flush，每次flush的髒頁數量較少，基本固定，只要釋放一定的空閒頁即可；

Flush連結串列：

• Flush 連結串列，其目的是推進Checkpoint LSN，使得InnoDB系統崩潰之後能夠快速的恢復。
• Flush List flush，不需要移動page在LRU連結串列中的位置。
• Flush 連結串列 flush，根據當前系統的更新繁忙程度，動態調整一次flush的髒頁數量，量很大。
• 在Flush 連結串列上的頁面一定在LRU 連結串列上，反之則不成立。

4、觸發刷髒頁的條件

• REDO紀錄檔快用滿的時候。由於MySQL更新是先寫REDO紀錄檔，後面再將資料Flush到磁碟，如果REDO紀錄檔對應髒資料還沒有重新整理到磁碟就被覆蓋的話，萬一發生Crash，資料就無法恢復了。此時會從Flush 連結串列裡面選取髒頁，進行Flush。
• 為了保證MySQL中的空閒頁面的數量，Page Cleaner執行緒會從LRU 連結串列尾部淘汰一部分頁面作為空閒頁。如果對應的頁面是髒頁的話，就需要先將頁面Flush到磁碟。
• MySQL中髒頁太多的時候。innodb_max_dirty_pages_pct 表示的是Buffer Pool最大的髒頁比例，預設值是75%，當髒頁比例大於這個值時會強制進行刷髒頁，保證系統有足夠可用的Free Page。innodb_max_dirty_pages_pct_lwm引數控制的是髒頁比例的低水位，當達到該引數設定的時候，會進行preflush，避免比例達到innodb_max_dirty_pages_pct 來強制Flush，對MySQL範例產生影響。
• MySQL範例正常關閉的時候，也會觸發MySQL把記憶體裡面的髒頁全部重新整理到磁碟。

Innodb 的策略是在執行過程中儘可能的多佔用記憶體，因此未被使用的頁面會很少。當我們讀取的資料不在Buffer Pool裡面時，就需要申請一個空閒頁來存放。如果沒有足夠的空閒頁時，就必須從LRU 連結串列的尾部淘汰頁面。如果該頁面是乾淨的，可以直接拿來用，如果是髒頁，就需要進行刷髒操作，將記憶體資料Flush到磁碟。

所以，如果出現以下情況，是很容易影響MySQL範例的效能：

• 一個SQL查詢的資料頁需要淘汰的頁面過多
• 範例是個寫多型的MySQL，checkpoint跟不上紀錄檔產生量，會導致更新全部堵塞，TPS跌0。

innodb_io_capacity 引數定義了Innodb 後臺任務的IO能力，例如刷髒操作還有Change Buffer的merge操作等。

Innodb 的三種Page和連結串列的設計，保證了我們需要的熱資料常駐在記憶體，及時淘汰不需要的資料，提升了我們的查詢速度，同時不同的刷髒策略也提高了我們的恢復速度，保證了資料安全。

到此這篇關於mysql的Buffer Pool儲存及原理的文章就介紹到這了,更多相關mysql的Buffer Pool原理內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！