有時會遇到這樣的場景，一條SQL語句，正常執(zhí)行的時候特別快，但是有時也不知道怎么回事，它就會變得很慢，并且這樣的場景很難復現(xiàn)，它不只隨機，而且持續(xù)時間還很短。看上去，就像是數(shù)據(jù)庫“抖”了一下。

1、為什么變慢了

前面我們知道了數(shù)據(jù)庫的WAL機制，InnoDB 在處理更新數(shù)據(jù)的時候，只做了寫日志這一個磁盤操作。這個日志叫做 redo log（重做日志），在更新內(nèi)存寫完redo log 后，就返回給客戶端，本次更新成功。

以上操作，只是更新了內(nèi)存中的數(shù)據(jù)，那么肯定在某個時機將內(nèi)存里的數(shù)據(jù)寫入磁盤中。這個操作刷內(nèi)存數(shù)據(jù)到磁盤的操作叫做 flush。

當內(nèi)存數(shù)據(jù)頁和磁盤數(shù)據(jù)頁內(nèi)容不一致的時候，我們稱這個內(nèi)存頁為“臟頁”。內(nèi)存數(shù)據(jù)寫入到磁盤后，內(nèi)存和磁盤上的數(shù)據(jù)頁的內(nèi)容就一致了，稱為“干凈頁”。

不論是臟頁還是干凈頁，都在內(nèi)存中。

回到文章開頭的問題，不難想象，平時執(zhí)行很快的更新操作，其實就是在寫內(nèi)存和日志，而MySQL偶爾“抖”一下的那個瞬間，可能就是在刷臟頁（flush）。

引起 flush 操作的可能原因：

1. InnoDB 的redo log 寫滿了，這時候系統(tǒng)會停止所有更新操作，把checkpoint 往前推進，redo log 留出空間可以繼續(xù)寫。

redo log

如上圖中的 write pos 要追上 check point。

2. 內(nèi)存不足時。當需要新的內(nèi)存頁，而內(nèi)存不夠用的時候，就需要淘汰一些數(shù)據(jù)頁，空出內(nèi)存給別的數(shù)據(jù)頁使用。如果淘汰的是“臟頁”，就要先將臟頁寫到磁盤。
3. 系統(tǒng)空閑時。MySQL認為空閑的時候，就會找機會刷一點“臟頁”。
4. MySQL正常關閉的時候，這時候會把內(nèi)存的臟頁都flush到磁盤上。

上面四種場景對性能的影響：

第一種：

“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)：

1. 還沒有使用的
2. 使用了并且是干凈頁
3. 使用了并且是臟頁

InnoDB的策略是盡量使用內(nèi)存，因此對于一個長時間運行的庫來說，未被使用的頁很少。

而當要讀入的數(shù)據(jù)頁沒有在內(nèi)存的時候，就必須到緩沖池中申請一個數(shù)據(jù)頁。這時候只能把最久不使用的數(shù)據(jù)頁從內(nèi)存中淘汰掉；如果要淘汰的是一個干凈頁，就直接釋放出來復用；但如果是臟頁，就必須先把臟頁刷到磁盤，變成干凈頁后才能復用。

第三種：

屬于MySQL空閑時的操作，系統(tǒng)沒有壓力。

第四種：

數(shù)據(jù)庫正常關閉，不會關注性能問題。

所以，刷臟頁雖然是常態(tài)，但是出現(xiàn)以下情況時，都是會明顯影響性能的：

1. 一個查詢要淘汰的臟頁個數(shù)太多，會導致查詢的響應時間明顯變長
2. 日志寫滿，更新全部堵住，寫性能跌為0，這種情況對敏感業(yè)務來說，是不能接受的。

所以，InnoDB 需要有控制臟頁比例的機制，來盡量避免上面的這兩種情況。

2、InnoDB 刷臟頁的控制策略

首先，需要正確地告訴InnoDB所在主機的IO能力，這樣InnoDB 才能直到需要全力刷臟頁的時候，可以刷多快。

這就要用到innodb_io_capacity這個參數(shù)了，它會告訴InnoDB 你的磁盤能力。這個值建議設置成磁盤的IOPS。可以用fio 這個工具來測試，下面是命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

其實，因為沒能正確地設置innodb_io_capacity參數(shù)，而導致的性能問題也比比皆是。

現(xiàn)在InnoDB知道了主機的IO能力，我們再看看InnoDB怎么控制引擎按照“全力”的百分比來刷臟頁。如果刷太慢，會導致內(nèi)存臟頁太多，其次是redo log寫滿。如果刷太快，WAL機制就體現(xiàn)不出來優(yōu)勢。

所以，InnoDB 的刷盤速度要參考兩個因素：一是臟頁比例，二是 redo log 寫盤速度。

InnoDB 會根據(jù)這兩個因素先單獨算出兩個數(shù)字。

參數(shù) innodb_max_dirty_pages_pct 是臟頁比例上限，默認值是75%。InnoDB會根據(jù)當前的臟頁比例（假設為M），算出一個范圍在 0 到 100 之間的數(shù)字，計算公式記為F1(M)。

InnoDB 每次寫入的日志都有一個序號，當前寫入的序號跟 checkpoint 對應的序號之間的差值，我們假設為N。InnoDB會根據(jù)這個N算出一個范圍在 0 到 100之間的數(shù)字，這個計算公式可以記為F2(N)，N越大，算出來的值就越大。

然后，根據(jù)上述算得的F1(M)和F2(N) 兩個值，取其中較大的值記為 R，之后引擎就可以按照 innodb_io_capacity 定義的能力乘以 R%來控制刷臟頁的速度。

現(xiàn)在我們知道，InnoDB 會在后臺刷臟頁，而刷臟頁的過程是要將內(nèi)存頁寫入磁盤。所以，無論是查詢語句在需要內(nèi)存的時候可能要淘汰一個臟頁，還是由于刷臟頁的邏輯會占用IO資源并可能影響到更新語句，都可能造成MySQL“抖”了一下。

要盡量避免這種情況，就需要合理地設置innodb_io_capacity的值，并且平時要多關注臟頁比例，不要讓它接近75%。

其中臟頁比例可以通過臟頁數(shù)/總頁數(shù)來計算。下面是sql：

select VARIABLE_VALUE into @a from performance_schema.global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty'; 
select VARIABLE_VALUE into @b from performance_schema.global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'; 
select @a/@b;

另外，說一個有趣的策略。

一旦一個查詢請求需要在執(zhí)行過程中先flush掉一個臟頁時，這個查詢就可能比平時慢了。

而MySQL中的一個機制，可能讓它更慢：在準備刷一個臟頁的時候，如果這個數(shù)據(jù)頁旁邊的數(shù)據(jù)頁剛好是臟頁，就會把這個“鄰居”頁帶著一起刷掉；而且這個“鄰居”還會拖它的鄰居下水，會一直蔓延下去。

在InnoDB中，innodb_flush_neighbors參數(shù)就是用來控制這個行為的，值為1的時候會有上述的“連坐”機制，值為0時表示不找鄰居。

在機械硬盤時代，這個參數(shù)是有意義的，可以減少很多隨機IO。但是在SSD時代，建議設置 innodb_flush_neighbors 的值為0。

在MySQL 8.0 中，innodb_flush_neighbors參數(shù)的默認值已經(jīng)是0了。