如何提高SQL執(zhí)行速度？

當我們想更新某條數據的時候，難道是從磁盤中加載出來這條數據，更新后再持久化到磁盤中嗎？

如果這樣搞的話，那一條sql的執(zhí)行過程可太慢了，因為對一個大磁盤文件的讀寫操作是要耗費幾百萬毫秒的

真實的執(zhí)行過程是，當我們想更新或者讀取某條數據的時候，會把對應的頁加載到Buffer Pool緩沖池中（Buffer Pool本質上就是一塊連續(xù)的內存空間）

默認為128m，當然為了提高系統(tǒng)的并發(fā)度，你可以把這個值設大一點

之所以加載頁到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鏈表，它是一個雙向鏈表，每個節(jié)點就是一個空閑緩存頁的描述數據

我們如何知道緩存頁是否被加載到內存了？

很簡單啊，建立一個哈希表不就行了，key為表空間號+頁號，value為對應的緩存頁

當把數據頁讀取到緩存頁的時候，對應的描述數據會從free鏈表放到flush鏈表

當不停的把磁盤上的數據頁加載到緩存頁，free鏈表不停的移除空閑緩存頁，當free鏈表上沒有空閑緩存頁，當你還要加載數據頁到緩存頁時，該怎么辦呢？

如果要淘汰一些數據，該淘汰誰呢？

引入LRU鏈表來判斷哪些緩存頁是不常用的？

緩存淘汰策略在很多中間件中會被用到，其中用的最多的就是LRU算法，當每訪問一個緩存頁的時候就把緩存頁移到鏈表的頭部

我們只需要把鏈表尾部的緩存頁刷到內存中，然后加載新的數據頁即可。

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

首先就是mysql的預讀，

哪些情況會觸發(fā)MySQL的預讀

當發(fā)生全表掃描的時候（比如 select * from users），會導致表里的數據頁都加載到 Buffer Pool 中去。這樣有可能導致LRU鏈表前面一大串數據頁都是全表掃描加載進來的數據頁，但是如果這次全表掃描過后后續(xù)幾乎沒用到這個表里面的數據呢？

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

為了解決這個問題，LRU鏈表改進了一下，采用了冷熱分離的思想。

即LRU鏈表會被拆分為2部分，一部分是冷數據，一部分是熱數據

改進后的鏈表是如何工作的？

當數據頁第一次被加載到緩存的時候，緩存頁會被放到冷數據區(qū)域的鏈表頭部。

那么冷數據區(qū)的緩存頁多會放到熱數據區(qū)呢？

你可能會想，當冷數據區(qū)的緩存頁再次被訪問時，就放到熱數據區(qū)可以不？

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不能動態(tài)擴展，動態(tài)擴展。為了增加動態(tài)擴展就增加了chunk機制，有興趣的小伙伴可以看看其他資料，就不多做分析了

Buffer Pool的相關參數

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

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

SHOW VARIABLES LIKE '%innodb_buffer%'

innodb_buffer_pool_size的單位是字節(jié)，我們轉成MB來看一下，默認是128M

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

執(zhí)行如下命令可以得到buffer_pool的當前使用狀態(tài)

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的大小

你也可以執(zhí)行如下命令獲取一些關于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訪問速度內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！