面試高頻：為什么MySQL會抖一下？
比較喜歡的一段話：不經(jīng)一番寒徹骨，怎得梅花撲鼻香。閱讀這篇文章大概需要20分鐘！
大家好前面我們大概了解了如何巧妙的給字符串字段加索引提高查詢性能。今天我們介紹一下為什么MySQL在查詢數(shù)據(jù)的時候，有些時候會 "抖" 一下
先解釋一下抖這個字。有些時候的SQL執(zhí)行非常快，有些時候執(zhí)行非常慢。通過explain查看SQL的執(zhí)行計劃還是無用。該走的索引也走了，該優(yōu)化的細節(jié)也優(yōu)了。那么到底是因為什么所以才導(dǎo)致卡頓一下的呢?
首先介紹一下什么是干凈頁跟臟頁。先把路鋪好。
干凈頁
內(nèi)存上的數(shù)據(jù)和磁盤上的數(shù)據(jù)頁的內(nèi)容一致時，稱為 “干凈頁”。
臟頁
內(nèi)存上的數(shù)據(jù)和磁盤上的數(shù)據(jù)頁的內(nèi)容不一致時，稱為 “臟頁”。
舉個例子
舉一個干凈頁跟臟頁的例子，這個例子也是前幾篇文章舉的。一家農(nóng)村型超市，這個超市沒有大城市那種不賒賬的規(guī)矩。所以農(nóng)村的超市一般會涉及到賒賬。那么如何進行有效的記賬呢？我們不可能因為賒了一個人就記一次，那這樣的話做事的效率是非常低的。因為每次在記賬本上找到那個人的名字都要找很久。所以一般采用的是，會有一個臨時紙條，這個紙條夾在一個固定的地方，每當晚上打樣的時候，統(tǒng)一把所有紙條上的數(shù)據(jù)一一歸到記賬本上。
這樣才是大多數(shù)超市的真實場景，那么數(shù)據(jù)庫里這些是如何實現(xiàn)的呢？紙條就是redo log！記賬本就是磁盤！
如下圖所示
第一個小方格中的內(nèi)容：當前賒賬的數(shù)據(jù)還沒有轉(zhuǎn)移到記賬本上。所以內(nèi)存與磁盤上的數(shù)據(jù)不一致。這樣就是一開始介紹的臟頁。
第二個小方格中的內(nèi)容：到了晚上或者不忙的時候，掌柜或者老板就把紙條上的數(shù)據(jù)轉(zhuǎn)移到記賬本上。這個過程在數(shù)據(jù)庫中就是flush數(shù)據(jù)的過程。
綜上所述：干凈頁，臟頁大概就是這么一個東西。數(shù)據(jù)庫之所以抖一下，就是因為數(shù)據(jù)庫的內(nèi)部正在進行flush操作
圖1 超市賒賬紙條轉(zhuǎn)移到記賬本的過程
那么數(shù)據(jù)庫什么時候才會抖一下呢？什么時候開始flush呢？今天我們詳細介紹一下
內(nèi)存場景
我們介紹場景的時候，還是按照上述例子，先從簡單易懂的真實場景，再到數(shù)據(jù)庫抽象的過度！下面四個場景看不懂沒關(guān)系，我們一一介紹。這篇文章還是比較偏底層介紹的，所以耐心一點，看完之后再回味一下。不懂的可以微信關(guān)注【歡少的成長之路】。
紙條滿了
第一種場景就是，所有紙條都記滿了，這個時候必須放下手里的工作。把紙條上的所有賬都轉(zhuǎn)移到記賬本上。對應(yīng)的數(shù)據(jù)庫過程就是。redo log滿了。需要把redo log日志上的操作轉(zhuǎn)移到磁盤中。變成真正的數(shù)據(jù)。
一張紙條滿了
第二種場景就是，一張紙條滿了，掌柜的不得不再找一張紙條過來，繼續(xù)記賬。對應(yīng)的數(shù)據(jù)庫流程就是，一頁數(shù)據(jù)寫滿了，MySQL不得不再開一頁進行存放數(shù)據(jù)。
紙條沒滿，不忙的時候
第三種場景就是，在超市低峰期的時候，都是比較悠閑的。所以這個時候老板閑著也是閑著，不如把紙條上的賬，歸到記賬本上。對應(yīng)的數(shù)據(jù)庫流程就是，一段時間沒人使用數(shù)據(jù)庫的時候，數(shù)據(jù)庫就會自己把redo log日志更新到磁盤上。
打樣時
第四種場景就是，超市晚上打樣時，完全沒人。掌柜的收尾一天工作的時候把賬歸到記賬本上。對應(yīng)的數(shù)據(jù)庫流程就是，用戶關(guān)閉會話的時候。沒有連接使用了。數(shù)據(jù)庫也會把redo log更新到磁盤上。
分析
第三種情況與第四種情況都是屬于空閑時，系統(tǒng)沒什么壓力。我們就不做分析了。這里著重分析一下第一種與第二鐘情況。
紙條滿了：“redo log 寫滿了，要 flush 臟頁”，這種情況是 InnoDB 要盡量避免的。因為出現(xiàn)這種情況的時候，整個系統(tǒng)就不能再接受更新了，所有的更新都必須堵住。如果你從監(jiān)控上看，這時候更新數(shù)會跌為 0。
一張紙條滿了：“內(nèi)存不夠用了，要先將臟頁寫到磁盤”，這種情況其實是常態(tài)。InnoDB 用緩沖池（buffer pool）管理內(nèi)存，緩沖池中的內(nèi)存頁有三種狀態(tài)：還沒有使用的，使用了并且是干凈頁，使用了并且是臟頁。
innodb的策略是盡量使用內(nèi)存，提高自身性能，因此對于一個長時間運行的庫來說，未被使用的頁面很少。
而當要讀入的數(shù)據(jù)頁沒有在內(nèi)存的時候，就必須到緩沖池中申請一個數(shù)據(jù)頁。這時候只能把最久不使用的數(shù)據(jù)頁從內(nèi)存中淘汰掉：如果要淘汰的是一個干凈頁，就直接釋放出來復(fù)用；但如果是臟頁呢，就必須將臟頁先刷到磁盤，變成干凈頁后才能復(fù)用。
所以刷臟頁是數(shù)據(jù)庫運行的常態(tài)了。那么下面兩種情況會影響數(shù)據(jù)庫的運行。也就是一開始我們說的數(shù)據(jù)庫為什么會抖音一下的詳細原因：
一個查詢要淘汰的臟頁個數(shù)太多，會導(dǎo)致查詢的響應(yīng)時間明顯變長；
日志寫滿，更新全部堵住，寫性能跌為 0，這種情況對敏感業(yè)務(wù)來說，是不能接受的。
所以MySQL想用的穩(wěn)定，用的爽就必須控制，刷臟頁的時機。以及何時刷臟頁，刷多塊？
innodb刷臟頁策略
這里解決一下何時刷臟頁，刷多塊的問題
首先，你要正確地告訴 InnoDB 所在主機的 IO 能力，這樣 InnoDB 才能知道需要全力刷臟頁的時候，可以刷多快。下列是數(shù)據(jù)庫用到的參數(shù)，通過他來設(shè)置。怎么設(shè)置我們下面會介紹的。
show variables like 'innodb_io_capacity'
innodb_io_capacity 不能過高，也不能過低。過高的話會占用大量內(nèi)存，會嚴重影響MySQL的日常使用。如果過低會導(dǎo)致臟頁太多。數(shù)據(jù)跟不上使用。導(dǎo)致寫入只能等。
如何設(shè)置
首先主要考慮兩個因素。一個是臟頁比例，一個是redo log寫盤速度。
下列參數(shù)是控制臟頁比例。默認值為75。也就是75%
show variables like 'innodb_max_dirty_pages_pct'
InnoDB 會根據(jù)當前的臟頁比例（假設(shè)為 M），算出一個范圍在 0 到 100 之間的數(shù)字
InnoDB 每次寫入的日志都有一個序號，當前寫入的序號跟 checkpoint 對應(yīng)的序號之間的差值，我們假設(shè)為 N。InnoDB 會根據(jù)這個 N 算出一個范圍在 0 到 100 之間的數(shù)字，這個計算公式可以記為 F2(N)。F2(N) 算法比較復(fù)雜，你只要知道 N 越大，算出來的值越大就好了。
然后，根據(jù)上述算得的 F1(M) 和 F2(N) 兩個值，取其中較大的值記為 R，之后引擎就可以按照 innodb_io_capacity 定義的能力乘以 R% 來控制刷臟頁的速度。
如下圖，圖中的 F1、F2 就是上面我們通過臟頁比例和 redo log 寫入速度算出來的兩個值。
現(xiàn)在你知道了，InnoDB 會在后臺刷臟頁，而刷臟頁的過程是要將內(nèi)存頁寫入磁盤。所以，無論是你的查詢語句在需要內(nèi)存的時候可能要求淘汰一個臟頁，還是由于刷臟頁的邏輯會占用 IO 資源并可能影響到了你的更新語句，都可能是造成你從業(yè)務(wù)端感知到 MySQL“抖”了一下的原因。
要盡量避免這種情況，你就要合理地設(shè)置 innodb_io_capacity 的值，并且平時要多關(guān)注臟頁比例，不要讓它經(jīng)常接近 75%。
其中，臟頁比例是通過 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的，具體的命令參考下面的代碼：
select VARIABLE_VALUE into @a
from global_status
where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b
from global_status
where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;
下面科普一個有趣的策略。一旦一個查詢請求需要在執(zhí)行過程中先 flush 掉一個臟頁時，這個查詢就可能要比平時慢了。而 MySQL 中的一個機制，可能讓你的查詢會更慢：在準備刷一個臟頁的時候，如果這個數(shù)據(jù)頁旁邊的數(shù)據(jù)頁剛好是臟頁，就會把這個“鄰居”也帶著一起刷掉；而且這個把“鄰居”拖下水的邏輯還可以繼續(xù)蔓延，也就是對于每個鄰居數(shù)據(jù)頁，如果跟它相鄰的數(shù)據(jù)頁也還是臟頁的話，也會被放到一起刷。
緊接著相關(guān)聯(lián)的參數(shù)是set innodb_flush_neighbors=1 允許連坐機制，set innodb_flush_neighbors=0 不允許連坐機制。
這個策略還是比較好的。這個優(yōu)化在機械硬盤時代是很有意義的，可以減少很多隨機 IO。機械硬盤的隨機 IOPS 一般只有幾百，相同的邏輯操作減少隨機 IO 就意味著系統(tǒng)性能的大幅度提升。
而如果使用的是 SSD 這類 IOPS 比較高的設(shè)備的話，我就建議你把 innodb_flush_neighbors 的值設(shè)置成 0。因為這時候 IOPS 往往不是瓶頸，而“只刷自己”，就能更快地執(zhí)行完必要的刷臟頁操作，減少 SQL 語句響應(yīng)時間。
在 MySQL 8.0 中，innodb_flush_neighbors 參數(shù)的默認值已經(jīng)是 0 了。