MySQL必知必會：用十一張圖講清楚，當你CRUD時BufferPool中發生了什麼！以及BufferPool的優化！

賜我白日夢發表於2020-11-22

MySql優化

一、收到了大佬們的建議

1、篇幅偏短，建議稍微加長一點。

這點說的確實挺對，有的篇幅確實比較短，針對這個提議我會考慮將相似的話題放在一篇文章中。但是這可能會導致我中斷每天更新的步調，換成隔幾天發一篇的步調（但是這個系列的文章一定會寫完的！）

2、Buffer Pool、LRU List、Flush List、Free List相輔相成，建議放在一起串講。

說的沒錯，是應該一起串講。於是有了這篇加餐的文章：下面讓我們就一起看下，當你執行CURD時，InnoDB的Buffer Pool中都發生了什麼！以及Buffer Pool的優化！

二、Let‘s go

你知道的，MySQL對資料的增刪改查都是記憶體中完成的，這塊記憶體就是Buffer Pool。

你可以像下面這樣檢視下你的MySQL的Buffer的Buffer Pool的預設大小

上圖中的0.125單位為GB，轉換成MB就是 1024* 1/8 = 128MB

總結來說，就是MySQL啟動後就會為我們初始化好這塊Buffer Pool。如下圖：

你可以看著上圖，然後讀下面這段話：

MySQL以資料頁為單位，從磁碟中讀取資料。資料頁被讀取到記憶體中，所謂的記憶體其實就是Buffer Pool。

Buffer Pool中維護的資料結構是快取頁，而且每個快取頁都有它對應的描述資訊。

由於MySQL剛啟動，還沒有從磁碟中讀取任何資料頁到記憶體（Buffer Pool）中，那此時Buffer Pool中所有的快取頁其實都是空的。

除了快取頁之外，你還能看到Buffer Pool中存在三個雙向連結串列。分別是FreeList、LRUList以及FlushList。這三個雙向連結串列中維護著快取頁的描述資訊。

三、好，假設你讀取出來了1個資料頁

當你通過select讀取出一個資料頁之後，是需要將這個資料頁載入進Buffer Pool中的快取頁中的。

那問題來了，MySQL怎麼知道該將你讀取出來的資料頁存放在那個快取頁中呢？相信你看了上圖應該也能想到答案了。FreeList這個雙向連結串列不是存放了空閒的快取頁的描述資訊嗎？那從FreeList中取出一個空間快取頁的描述資訊不就好了？於是得到了下面這張圖：

囉嗦一點：對這張圖稍微做一下解讀：

InnoDB會將你讀取出來的資料頁載入進Buffer Pool中的快取頁中，然後快取頁的描述資訊也會被維護進LRU連結串列中。連結串列做了冷熱資料分離優化，5/8的區域是熱資料區域，3/8的區域算是冷資料區域。（本質上它們都是雙向連結串列），而你新讀取的資料頁會被放在冷資料區的靠前的位置上。

如果你將該資料頁讀取出來載入進快取頁中後，間隔沒到1s，就使用該快取頁。那麼InnoDB是不會將這個描述資訊移動到5/8的熱資料區域的。

但是當超過1s後，你又去讀這個資料頁。那這個資料頁的描述資訊就會被放到熱資料區域。如下圖：

四、假設你一次性讀取出來了好多資料頁

白日夢在第 6 篇文章中跟大家分享過，MySQL是存在預讀機制的，感興趣可關注公眾號閱讀。

假設觸發了MySQL的預讀機制。一次性從磁碟中讀取來N多個快取頁。會得到下面這張圖:

因為發生了預讀，所以你的一次磁碟IO讀出了大量的資料頁，但是這些資料頁中很可能是有一些是你根本不需要的，僅僅是預讀把它們級聯查出來了。這時按老規矩，從FreeList中找到空閒的快取頁資訊，然後將其從FreeList中移除。根據找到的空閒快取頁的描述資訊，將從磁碟中讀取出來的資料頁載入進去。相應的該快取頁的描述資訊也會被維護進LRU連結串列的冷資料區域。

這時你就會發現這種冷熱資料分離的機制多麼妙！即使發生了預讀又怎麼樣？根本沒有機會將熱資料區的描述資訊1擠下去。當記憶體不夠用了需要將部分快取頁重新整理到磁碟中時，那就從冷資料區域開始重新整理好了，反正他們本來就不經常被使用。

同樣的，當你超過1s後又訪問了冷資料區的快取頁，比如訪問了快取頁66和資料頁67，該快取頁對應的描述資訊是會被提升到熱資料區，於是有了下面這張圖：

那，如果你訪問上圖中的資料頁67，它會移動到描述資訊66所在節點的前面去嗎？

其實MySQL的LRU連結串列做了優化，資料67是不會往前跑的。

五、假設你修改了某資料頁

假設你執行了update xxx set xxx where id in (xxx,xxx,xxx,xxx)；

而符合條件的資料行恰巧就在描述資訊1、描述資訊66、描述資訊67所指向的快取頁中，那BufferPool中會發生什麼呢？

如下圖：

你會看到，被你修改了的快取頁的描述資訊，被新增到了FlushList這個雙向連結串列中。

想必看到這裡你已經知道了，原來FlushList中的節點存放就是被修改了髒資料頁的描述資訊塊。

隨著MySQL被使用的時間越來越長，BufferPool的大小就越來越小。等它不夠用的時候，就會將部分LRU中的資料頁描述資訊移除出去，這時如果發現被移除出來的資料頁在FLushList中，就會觸發fsync的操作，觸發隨機寫磁碟。如果該資料頁是乾淨的，那移除出去就好了。其他也不用幹啥。

舉個例子：假設需要將描述資訊66、描述資訊67指向的快取頁落盤。會得到下面這張腦圖：

描述資訊66、67指向的快取頁被重新整理進磁碟。同時從FlushList中將其移除，然後存入FreeList中。完成一個迴圈

當然，將髒資料頁重新整理進磁碟的時機除了上圖中說的還有好多種情況，白日夢在上一篇文章中有分享。可關注公眾號檢視哦

下面再看一下關於Buffer Pool的設定和相關的優化。

六、配置Buffer Pool的大小

buffer pool越大，MySQL的效能就越強悍。你可以像下面這樣配置Buffer Pool的大小。

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

七、配置多個Buffer Pool的例項

你可以為MySQL例項配置多個Buffer Pool，每個Buffer Pool各自負責管理一部分快取頁，並且有自己獨立的LRU、Free、Flush連結串列。

當有多執行緒併發請求過來時，執行緒可以在不同的Buffer Pool中執行自己的操作，MySQL效能就會得到很大的提升

在my.d中進行配置

[server]
innodb_buffer_pool_size = xxx
innodb_buffer_pool_instances = 4

意思是將總容量為xxx的buffer pool劃分成4個例項。每個例項都有 xxx/4 的容量。

引數innodb_buffer_pool_instances的最大值為64，並且想讓該引數生效，innodb_buffer_pool_size容量至少是1G。

可以像下面這樣檢視你的MySQL的Buffer Pool例項狀態。

八、揭祕BufferPool的真實結構

現實中Buffer Pool動輒就佔用好幾G的記憶體，相對於直接申請幾G的記憶體完成擴容，MySQL有更優雅的實現方式。

為了實現動態調整Buffer Pool的大小。MySQL設計了chunk 機制。

可以看上圖腦補一下Buffer Pool 以及 Chunk長什麼樣。

總的來說：就是將每一個 Buffer Pool Instance 更加細力度化。將Buffer Pool拆分成更小的獨立單元。

每個Buffer Pool劃分成多個chunnk，每個chunk中維護一部分快取頁、快取頁的描述資訊。同屬於一個Buffer Pool的chunk共享該Buffer Pool的lru、free、flush連結串列。

塊大小由引數innodb_buffer_pool_chunk_size控制，預設值為 128M

該引數可以像下面這樣修改：

shell> mysqld --innodb-buffer-pool-chunk-size=134217728

或者通過配置檔案自定義

[mysqld]
innodb_buffer_pool_chunk_size=134217728

九、看一看Buffer Pool相關的引數

執行命令

> mysql show engine innodb status

十、如何規劃你的Buffer Pool大小

推薦將Buffer Pool的總大小設定為伺服器記憶體的 50%～60%左右

BufferPool總大小 = （chunkSize * bufferPoolInstanceNum)*2

十一、Buffer Pool的預熱機制

這種機制實際上是想讓重啟後的MySQL快速適應大規模的流量請求。

InnoDB 在伺服器關閉時為每個緩衝池儲存一部分最近高頻使用的頁面，並在伺服器啟動時恢復這些頁面。儲存多大比例的快取頁由引數innodb_buffer_pool_dump_pct控制。

在啟動時還原緩衝池，實際上會縮短預熱的時間。

你可以通過下面的方式配置該引數

# 通過命令
SET GLOBAL innodb_buffer_pool_dump_pct=40;

# 通過檔案
[mysqld]
innodb_buffer_pool_dump_pct=40

引數innodb_buffer_pool_dump_at_shutdown控制 MySQL關閉時儲存緩衝池的狀態，預設為on的狀態。

啟動引數--innodb-buffer-pool-load-at-startup 表示啟動MySQL的時候恢復緩衝池中的狀態，預設也是開啟的。

關注送書！《Netty實戰》

文章公號首發！連載中！關注微信公號回覆：“抽獎” 還可參加抽?活動

誠信第一，活動真實有效

參考：

https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-preload-buffer-pool.html