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

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

如果這樣搞的話，那一條sql的執(zhí)行過(guò)程可太慢了，因?yàn)閷?duì)一個(gè)大磁盤(pán)文件的讀寫(xiě)操作是要耗費(fèi)幾百萬(wàn)毫秒的

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

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

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

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

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

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

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

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

這些臟頁(yè)是多會(huì)刷回到磁盤(pán)中的？ 有如下幾個(gè)時(shí)機(jī)

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

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

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

我們?cè)趺粗滥男┚彺骓?yè)是空閑的？

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

我們?nèi)绾沃谰彺骓?yè)是否被加載到內(nèi)存了？

很簡(jiǎn)單啊，建立一個(gè)哈希表不就行了，key為表空間號(hào)+頁(yè)號(hào)，value為對(duì)應(yīng)的緩存頁(yè)

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

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

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

引入LRU鏈表來(lái)判斷哪些緩存頁(yè)是不常用的？

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

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

這樣的方式看似很完美，但是在實(shí)際運(yùn)行過(guò)程中會(huì)存在巨大的隱患

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

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

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

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

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

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

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

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

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

你可能會(huì)想，當(dāng)冷數(shù)據(jù)區(qū)的緩存頁(yè)再次被訪問(wèn)時(shí)，就放到熱數(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)多線程訪問(wèn)Buffer Pool中的各種鏈表時(shí)，需要加鎖保證線程安全，影響請(qǐng)求的處理速度，此時(shí)我們就可以將Buffer Pool分為多個(gè)，多線程訪問(wèn)事不會(huì)互相影響，提高了請(qǐng)求的處理速度

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

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

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

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

SHOW VARIABLES LIKE '%innodb_buffer%'

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

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

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

SHOW STATUS LIKE '%buffer_pool%';

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

• Innodb_buffer_pool_read_requests：讀的請(qǐng)求次數(shù)
• Innodb_buffer_pool_reads：從物理磁盤(pán)中讀取數(shù)據(jù)的次數(shù)
• Innodb_buffer_pool_pages_data：有數(shù)據(jù)的緩存頁(yè)
• Innodb_buffer_pool_pages_free：空閑緩存頁(yè)
• Innodb_buffer_pool_pages_total：總共的緩存頁(yè)

Buffer Pool 讀緩存命中率：

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

Buffer Pool 臟頁(yè)比率：

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

使用率比較高時(shí)可以增大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如何提高頁(yè)的訪問(wèn)速度的文章就介紹到這了,更多相關(guān)MySQL Buffer Pool訪問(wèn)速度內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論