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

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

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

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

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

之所以加載頁到Buffer Pool中，是考慮到當(dāng)你使用這個頁的數(shù)據(jù)時，這個頁的其他數(shù)據(jù)使用到的概率頁很大，隨機IO的耗時很長，所以多加載一點數(shù)據(jù)到Buffer Pool

Buffer Pool的數(shù)據(jù)結(jié)構(gòu)是怎樣的？

Buffer Pool中主要分為2部分，緩存頁和描述數(shù)據(jù)，MySQL從磁盤加載的數(shù)據(jù)頁會放入緩存頁中

對于每個緩存頁都有對應(yīng)的描述信息，比如數(shù)據(jù)頁所屬于表空間，數(shù)據(jù)頁的編號等

Buffer Pool中的描述數(shù)據(jù)大概相當(dāng)于緩存頁大小的5%左右，這部分內(nèi)存是不包含在Buffer Pool中的

當(dāng)更新數(shù)據(jù)的時候，如果對應(yīng)的頁在Buffer Pool中，則直接更新Buffer Pool中的頁即可，對應(yīng)的頁不在Buffer Pool中時，才會從磁盤加載對應(yīng)的頁到Buffer Pool，然后再更新，此時Buffer Pool中的頁和磁盤中的頁數(shù)據(jù)是不一致的，被稱為臟頁。這些臟頁是要被刷回到磁盤中的

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

Buffer Pool不夠用了，要給新加載的頁騰位置了，所以會利用改進(jìn)的后的LRU算法，將一些臟頁刷回磁盤后臺線程會在MySQL不繁忙的時候，將臟頁刷到磁盤中redolog寫滿時（redolog的作用后面會提到）數(shù)據(jù)庫關(guān)閉時會將所有臟頁刷回到磁盤

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

當(dāng)需要更新的數(shù)據(jù)所在的頁已經(jīng)在Buffer Pool中時，只需要操作內(nèi)存即可，效率不是一般的高

我們怎么知道哪些緩存頁是空閑的？

MySQL為Buffer Pool設(shè)計了一個free鏈表，它是一個雙向鏈表，每個節(jié)點就是一個空閑緩存頁的描述數(shù)據(jù)

我們?nèi)绾沃谰彺骓撌欠癖患虞d到內(nèi)存了？

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

當(dāng)把數(shù)據(jù)頁讀取到緩存頁的時候，對應(yīng)的描述數(shù)據(jù)會從free鏈表放到flush鏈表

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

如果要淘汰一些數(shù)據(jù)，該淘汰誰呢？

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

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

我們只需要把鏈表尾部的緩存頁刷到內(nèi)存中，然后加載新的數(shù)據(jù)頁即可。

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

首先就是mysql的預(yù)讀，

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

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

這樣就會導(dǎo)致經(jīng)常被掃描的緩存頁被淘汰了，留下的都是全表掃描加載進(jìn)來的緩存頁

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

即LRU鏈表會被拆分為2部分，一部分是冷數(shù)據(jù)，一部分是熱數(shù)據(jù)

改進(jìn)后的鏈表是如何工作的？

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

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

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

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

當(dāng)多線程訪問Buffer Pool中的各種鏈表時，需要加鎖保證線程安全，影響請求的處理速度，此時我們就可以將Buffer Pool分為多個，多線程訪問事不會互相影響，提高了請求的處理速度

在MySQL 5.7.5之前，Buffer Pool不能動態(tài)擴展，動態(tài)擴展。為了增加動態(tài)擴展就增加了chunk機制，有興趣的小伙伴可以看看其他資料，就不多做分析了

Buffer Pool的相關(guān)參數(shù)

學(xué)習(xí)了這么多理論知識，那么Buffer Pool應(yīng)該調(diào)多大呢？

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

SHOW VARIABLES LIKE '%innodb_buffer%'

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

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

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

SHOW STATUS LIKE '%buffer_pool%';

我們挑一些重要的參數(shù)來分析一下

• Innodb_buffer_pool_read_requests：讀的請求次數(shù)
• Innodb_buffer_pool_reads：從物理磁盤中讀取數(shù)據(jù)的次數(shù)
• Innodb_buffer_pool_pages_data：有數(shù)據(jù)的緩存頁
• 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í)行如下命令獲取一些關(guān)于Buffer Pool的其他參數(shù)，本篇文章就不多做介紹了

show engine innodb status;

參考博客

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

到此這篇關(guān)于MySQL Buffer Pool如何提高頁的訪問速度的文章就介紹到這了,更多相關(guān)MySQL Buffer Pool訪問速度內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論