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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

我們?cè)趺粗滥男┚彺骓撌强臻e的？

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

你可能會(huì)想，當(dāng)冷數(shù)據(jù)區(qū)的緩存頁再次被訪問時(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)多線程訪問Buffer Pool中的各種鏈表時(shí)，需要加鎖保證線程安全，影響請(qǐng)求的處理速度，此時(shí)我們就可以將Buffer Pool分為多個(gè)，多線程訪問事不會(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來看一下，默認(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：讀的請(qǐng)求次數(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的大小

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

最新評(píng)論