MySQL Buffer Pool如何提高頁的存取速度

2023-03-05 14:03:43

如何提高SQL執行速度？

當我們想更新某條資料的時候，難道是從磁碟中載入出來這條資料，更新後再持久化到磁碟中嗎？

如果這樣搞的話，那一條sql的執行過程可太慢了，因為對一個大磁碟檔案的讀寫操作是要耗費幾百萬毫秒的

真實的執行過程是，當我們想更新或者讀取某條資料的時候，會把對應的頁載入到Buffer Pool緩衝池中（Buffer Pool本質上就是一塊連續的記憶體空間）

預設為128m，當然為了提高系統的並行度，你可以把這個值設大一點

之所以載入頁到Buffer Pool中，是考慮到當你使用這個頁的資料時，這個頁的其他資料使用到的概率頁很大，隨機IO的耗時很長，所以多載入一點資料到Buffer Pool

Buffer Pool的資料結構是怎樣的？

Buffer Pool中主要分為2部分，快取頁和描述資料，MySQL從磁碟載入的資料頁會放入快取頁中

對於每個快取頁都有對應的描述資訊，比如資料頁所屬於表空間，資料頁的編號等

Buffer Pool中的描述資料大概相當於快取頁大小的5%左右，這部分記憶體是不包含在Buffer Pool中的

當更新資料的時候，如果對應的頁在Buffer Pool中，則直接更新Buffer Pool中的頁即可，對應的頁不在Buffer Pool中時，才會從磁碟載入對應的頁到Buffer Pool，然後再更新，此時Buffer Pool中的頁和磁碟中的頁資料是不一致的，被稱為髒頁。這些髒頁是要被刷回到磁碟中的

這些髒頁是多會刷回到磁碟中的？ 有如下幾個時機

Buffer Pool不夠用了，要給新載入的頁騰位置了，所以會利用改進的後的LRU演演算法，將一些髒頁刷回磁碟後臺執行緒會在MySQL不繁忙的時候，將髒頁刷到磁碟中redolog寫滿時（redolog的作用後面會提到）資料庫關閉時會將所有髒頁刷回到磁碟

這樣搞，效率是不是高很多了？

當需要更新的資料所在的頁已經在Buffer Pool中時，只需要操作記憶體即可，效率不是一般的高

我們怎麼知道哪些快取頁是空閒的？

MySQL為Buffer Pool設計了一個free連結串列，它是一個雙向連結串列，每個節點就是一個空閒快取頁的描述資料

我們如何知道快取頁是否被載入到記憶體了？

很簡單啊，建立一個雜湊表不就行了，key為表空間號+頁號，value為對應的快取頁

當把資料頁讀取到快取頁的時候，對應的描述資料會從free連結串列放到flush連結串列

當不停的把磁碟上的資料頁載入到快取頁，free連結串列不停的移除空閒快取頁，當free連結串列上沒有空閒快取頁，當你還要載入資料頁到快取頁時，該怎麼辦呢？

如果要淘汰一些資料，該淘汰誰呢？

引入LRU連結串列來判斷哪些快取頁是不常用的？

快取淘汰策略在很多中介軟體中會被用到，其中用的最多的就是LRU演演算法，當每存取一個快取頁的時候就把快取頁移到連結串列的頭部

我們只需要把連結串列尾部的快取頁刷到記憶體中，然後載入新的資料頁即可。

這樣的方式看似很完美，但是在實際執行過程中會存在巨大的隱患

首先就是mysql的預讀，

哪些情況會觸發MySQL的預讀

當發生全表掃描的時候（比如 select * from users），會導致表裡的資料頁都載入到 Buffer Pool 中去。這樣有可能導致LRU連結串列前面一大串資料頁都是全表掃描載入進來的資料頁，但是如果這次全表掃描過後後續幾乎沒用到這個表裡面的資料呢？

這樣就會導致經常被掃描的快取頁被淘汰了，留下的都是全表掃描載入進來的快取頁

為了解決這個問題，LRU連結串列改進了一下，採用了冷熱分離的思想。

即LRU連結串列會被拆分為2部分，一部分是冷資料，一部分是熱資料

改進後的連結串列是如何工作的？

當資料頁第一次被載入到快取的時候，快取頁會被放到冷資料區域的連結串列頭部。

那麼冷資料區的快取頁多會放到熱資料區呢？

你可能會想，當冷資料區的快取頁再次被存取時，就放到熱資料區可以不？

mysql> SHOW VARIABLES LIKE 'innodb_old_blocks_pct';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+
1 row in set (0.02 sec)

當多執行緒存取Buffer Pool中的各種連結串列時，需要加鎖保證執行緒安全，影響請求的處理速度，此時我們就可以將Buffer Pool分為多個，多執行緒存取事不會互相影響，提高了請求的處理速度

在MySQL 5.7.5之前，Buffer Pool不能動態擴充套件，動態擴充套件。為了增加動態擴充套件就增加了chunk機制，有興趣的小夥伴可以看看其他資料，就不多做分析了

Buffer Pool的相關引數

學習了這麼多理論知識，那麼Buffer Pool應該調多大呢？

執行如下命令可以得到Buffer Pool的大小，名字，以及chunk的大小

SHOW VARIABLES LIKE '%innodb_buffer%'

innodb_buffer_pool_size的單位是位元組，我們轉成MB來看一下，預設是128M

-- 128m
SELECT @@innodb_buffer_pool_size / 1024 / 1024

執行如下命令可以得到buffer_pool的當前使用狀態

SHOW STATUS LIKE '%buffer_pool%';

我們挑一些重要的引數來分析一下

Innodb_buffer_pool_read_requests：讀的請求次數
Innodb_buffer_pool_reads：從物理磁碟中讀取資料的次數
Innodb_buffer_pool_pages_data：有資料的快取頁
Innodb_buffer_pool_pages_free：空閒快取頁
Innodb_buffer_pool_pages_total：總共的快取頁

Buffer Pool 讀快取命中率：

(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / （Innodb_buffer_pool_read_requests） *100%

Buffer Pool 髒頁比率：

Innodb_buffer_pool_pages_dirty / （Innodb_buffer_pool_pages_data）*100%

Buffer Pool 使用率：

innodb_buffer_pool_pages_data / ( innodb_buffer_pool_pages_data + innodb_buffer_pool_pages_free ) * 100%

快取命中率比較低可以增大Buffer Pool的大小

使用率比較高時可以增大Buffer Pool的大小

你也可以執行如下命令獲取一些關於Buffer Pool的其他引數，本篇文章就不多做介紹了

show engine innodb status;

參考部落格

[1]https://www.cnblogs.com/FengGeBlog/p/10283095.html
[2]https://m.starcto.com/mysql/128.html

到此這篇關於MySQL Buffer Pool如何提高頁的存取速度的文章就介紹到這了,更多相關MySQL Buffer Pool存取速度內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！