時(shí)的工作中，不知道你有沒(méi)有遇到過(guò)這樣的場(chǎng)景，一條SQL語(yǔ)句，正常執(zhí)行的時(shí)候特別快，但是有時(shí)也不知道怎么回事，它就會(huì)變得特別慢，并且這樣的場(chǎng)景很難復(fù)現(xiàn)，它不只隨機(jī)，而且持續(xù)時(shí)間還很短。

看上去，這就像是數(shù)據(jù)庫(kù)“抖”了一下。今天，我們就一起來(lái)看一看這是什么原因。

你的SQL語(yǔ)句為什么變“慢”了

在前面第2篇文章我為你介紹了WAL機(jī)制?，F(xiàn)在你知道了，InnoDB在處理更新語(yǔ)句的時(shí)候，只做了寫日志這一個(gè)磁盤操作。這個(gè)日志叫作redo?log（重做日志），也就是《孔乙己》里咸亨酒店掌柜用來(lái)記賬的粉板，在更新內(nèi)存寫完redo?log后，就返回給客戶端，本次更新成功。

做下類比的話，掌柜記賬的賬本是數(shù)據(jù)文件，記賬用的粉板是日志文件（redo?log），掌柜的記憶就是內(nèi)存。

掌柜總要找時(shí)間把賬本更新一下，這對(duì)應(yīng)的就是把內(nèi)存里的數(shù)據(jù)寫入磁盤的過(guò)程，術(shù)語(yǔ)就是flush。在這個(gè)flush操作執(zhí)行之前，孔乙己的賒賬總額，其實(shí)跟掌柜手中賬本里面的記錄是不一致的。因?yàn)榭滓壹航裉斓馁d賬金額還只在粉板上，而賬本里的記錄是老的，還沒(méi)把今天的賒賬算進(jìn)去。

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

不論是臟頁(yè)還是干凈頁(yè)，都在內(nèi)存中。在這個(gè)例子里，內(nèi)存對(duì)應(yīng)的就是掌柜的記憶。

接下來(lái)，我們用一個(gè)示意圖來(lái)展示一下“孔乙己賒賬”的整個(gè)操作過(guò)程。假設(shè)原來(lái)孔乙己欠賬10文，這次又要賒9文。

圖1?“孔乙己賒賬”更新和flush過(guò)程

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

那么，什么情況會(huì)引發(fā)數(shù)據(jù)庫(kù)的flush過(guò)程呢？

我們還是繼續(xù)用咸亨酒店掌柜的這個(gè)例子，想一想：掌柜在什么情況下會(huì)把粉板上的賒賬記錄改到賬本上？

• 第一種場(chǎng)景是，粉板滿了，記不下了。這時(shí)候如果再有人來(lái)賒賬，掌柜就只得放下手里的活兒，將粉板上的記錄擦掉一些，留出空位以便繼續(xù)記賬。當(dāng)然在擦掉之前，他必須先將正確的賬目記錄到賬本中才行。 這個(gè)場(chǎng)景，對(duì)應(yīng)的就是InnoDB的redo?log寫滿了。這時(shí)候系統(tǒng)會(huì)停止所有更新操作，把checkpoint往前推進(jìn)，redo?log留出空間可以繼續(xù)寫。我在第二講畫了一個(gè)redo?log的示意圖，這里我改成環(huán)形，便于大家理解。

圖2?redo?log狀態(tài)圖

checkpoint可不是隨便往前修改一下位置就可以的。比如圖2中，把checkpoint位置從CP推進(jìn)到CP’，就需要將兩個(gè)點(diǎn)之間的日志（淺綠色部分），對(duì)應(yīng)的所有臟頁(yè)都flush到磁盤上。之后，圖中從write?pos到CP’之間就是可以再寫入的redo?log的區(qū)域。

• 第二種場(chǎng)景是，這一天生意太好，要記住的事情太多，掌柜發(fā)現(xiàn)自己快記不住了，趕緊找出賬本把孔乙己這筆賬先加進(jìn)去。 這種場(chǎng)景，對(duì)應(yīng)的就是系統(tǒng)內(nèi)存不足。當(dāng)需要新的內(nèi)存頁(yè)，而內(nèi)存不夠用的時(shí)候，就要淘汰一些數(shù)據(jù)頁(yè)，空出內(nèi)存給別的數(shù)據(jù)頁(yè)使用。如果淘汰的是“臟頁(yè)”，就要先將臟頁(yè)寫到磁盤。 你一定會(huì)說(shuō)，這時(shí)候難道不能直接把內(nèi)存淘汰掉，下次需要請(qǐng)求的時(shí)候，從磁盤讀入數(shù)據(jù)頁(yè)，然后拿redo?log出來(lái)應(yīng)用不就行了？這里其實(shí)是從性能考慮的。如果刷臟頁(yè)一定會(huì)寫盤，就保證了每個(gè)數(shù)據(jù)頁(yè)有兩種狀態(tài)：

• 一種是內(nèi)存里存在，內(nèi)存里就肯定是正確的結(jié)果，直接返回；

• 另一種是內(nèi)存里沒(méi)有數(shù)據(jù)，就可以肯定數(shù)據(jù)文件上是正確的結(jié)果，讀入內(nèi)存后返回。 這樣的效率最高。

• 第三種場(chǎng)景是，生意不忙的時(shí)候，或者打烊之后。這時(shí)候柜臺(tái)沒(méi)事，掌柜閑著也是閑著，不如更新賬本。 這種場(chǎng)景，對(duì)應(yīng)的就是MySQL認(rèn)為系統(tǒng)“空閑”的時(shí)候。當(dāng)然，MySQL“這家酒店”的生意好起來(lái)可是會(huì)很快就能把粉板記滿的，所以“掌柜”要合理地安排時(shí)間，即使是“生意好”的時(shí)候，也要見縫插針地找時(shí)間，只要有機(jī)會(huì)就刷一點(diǎn)“臟頁(yè)”。

• 第四種場(chǎng)景是，年底了咸亨酒店要關(guān)門幾天，需要把賬結(jié)清一下。這時(shí)候掌柜要把所有賬都記到賬本上，這樣過(guò)完年重新開張的時(shí)候，就能就著賬本明確賬目情況了。 這種場(chǎng)景，對(duì)應(yīng)的就是MySQL正常關(guān)閉的情況。這時(shí)候，MySQL會(huì)把內(nèi)存的臟頁(yè)都flush到磁盤上，這樣下次MySQL啟動(dòng)的時(shí)候，就可以直接從磁盤上讀數(shù)據(jù)，啟動(dòng)速度會(huì)很快。

接下來(lái)，你可以分析一下上面四種場(chǎng)景對(duì)性能的影響。

其中，第三種情況是屬于MySQL空閑時(shí)的操作，這時(shí)系統(tǒng)沒(méi)什么壓力，而第四種場(chǎng)景是數(shù)據(jù)庫(kù)本來(lái)就要關(guān)閉了。這兩種情況下，你不會(huì)太關(guān)注“性能”問(wèn)題。所以這里，我們主要來(lái)分析一下前兩種場(chǎng)景下的性能問(wèn)題。

第一種是“redo?log寫滿了，要flush臟頁(yè)”，這種情況是InnoDB要盡量避免的。因?yàn)槌霈F(xiàn)這種情況的時(shí)候，整個(gè)系統(tǒng)就不能再接受更新了，所有的更新都必須堵住。如果你從監(jiān)控上看，這時(shí)候更新數(shù)會(huì)跌為0。

第二種是“內(nèi)存不夠用了，要先將臟頁(yè)寫到磁盤”，這種情況其實(shí)是常態(tài)。InnoDB用緩沖池（buffer?pool）管理內(nèi)存，緩沖池中的內(nèi)存頁(yè)有三種狀態(tài)：

• 第一種是，還沒(méi)有使用的；

• 第二種是，使用了并且是干凈頁(yè)；

• 第三種是，使用了并且是臟頁(yè)。

InnoDB的策略是盡量使用內(nèi)存，因此對(duì)于一個(gè)長(zhǎng)時(shí)間運(yùn)行的庫(kù)來(lái)說(shuō)，未被使用的頁(yè)面很少。

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

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

1. 一個(gè)查詢要淘汰的臟頁(yè)個(gè)數(shù)太多，會(huì)導(dǎo)致查詢的響應(yīng)時(shí)間明顯變長(zhǎng)；

1. 日志寫滿，更新全部堵住，寫性能跌為0，這種情況對(duì)敏感業(yè)務(wù)來(lái)說(shuō)，是不能接受的。

所以，InnoDB需要有控制臟頁(yè)比例的機(jī)制，來(lái)盡量避免上面的這兩種情況。

InnoDB刷臟頁(yè)的控制策略

接下來(lái)，我就來(lái)和你說(shuō)說(shuō)InnoDB臟頁(yè)的控制策略，以及和這些策略相關(guān)的參數(shù)。

首先，你要正確地告訴InnoDB所在主機(jī)的IO能力，這樣InnoDB才能知道需要全力刷臟頁(yè)的時(shí)候，可以刷多快。

這就要用到innodb_io_capacity這個(gè)參數(shù)了，它會(huì)告訴InnoDB你的磁盤能力。這個(gè)值我建議你設(shè)置成磁盤的IOPS。磁盤的IOPS可以通過(guò)fio這個(gè)工具來(lái)測(cè)試，下面的語(yǔ)句是我用來(lái)測(cè)試磁盤隨機(jī)讀寫的命令：

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

其實(shí)，因?yàn)闆](méi)能正確地設(shè)置innodb_io_capacity參數(shù)，而導(dǎo)致的性能問(wèn)題也比比皆是。之前，就曾有其他公司的開發(fā)負(fù)責(zé)人找我看一個(gè)庫(kù)的性能問(wèn)題，說(shuō)MySQL的寫入速度很慢，TPS很低，但是數(shù)據(jù)庫(kù)主機(jī)的IO壓力并不大。經(jīng)過(guò)一番排查，發(fā)現(xiàn)罪魁禍?zhǔn)拙褪沁@個(gè)參數(shù)的設(shè)置出了問(wèn)題。

他的主機(jī)磁盤用的是SSD，但是innodb_io_capacity的值設(shè)置的是300。于是，InnoDB認(rèn)為這個(gè)系統(tǒng)的能力就這么差，所以刷臟頁(yè)刷得特別慢，甚至比臟頁(yè)生成的速度還慢，這樣就造成了臟頁(yè)累積，影響了查詢和更新性能。

雖然我們現(xiàn)在已經(jīng)定義了“全力刷臟頁(yè)”的行為，但平時(shí)總不能一直是全力刷吧？畢竟磁盤能力不能只用來(lái)刷臟頁(yè)，還需要服務(wù)用戶請(qǐng)求。所以接下來(lái)，我們就一起看看InnoDB怎么控制引擎按照“全力”的百分比來(lái)刷臟頁(yè)。

根據(jù)我前面提到的知識(shí)點(diǎn)，試想一下，如果你來(lái)設(shè)計(jì)策略控制刷臟頁(yè)的速度，會(huì)參考哪些因素呢？

這個(gè)問(wèn)題可以這么想，如果刷太慢，會(huì)出現(xiàn)什么情況？首先是內(nèi)存臟頁(yè)太多，其次是redo?log寫滿。

所以，InnoDB的刷盤速度就是要參考這兩個(gè)因素：一個(gè)是臟頁(yè)比例，一個(gè)是redo?log寫盤速度。

InnoDB會(huì)根據(jù)這兩個(gè)因素先單獨(dú)算出兩個(gè)數(shù)字。

參數(shù)innodb_max_dirty_pages_pct是臟頁(yè)比例上限，默認(rèn)值是75%。InnoDB會(huì)根據(jù)當(dāng)前的臟頁(yè)比例（假設(shè)為M），算出一個(gè)范圍在0到100之間的數(shù)字，計(jì)算這個(gè)數(shù)字的偽代碼類似這樣：

F1(M)
{if?M>=innodb_max_dirty_pages_pct?thenreturn?100;return?100*M/innodb_max_dirty_pages_pct;
}

InnoDB每次寫入的日志都有一個(gè)序號(hào)，當(dāng)前寫入的序號(hào)跟checkpoint對(duì)應(yīng)的序號(hào)之間的差值，我們假設(shè)為N。InnoDB會(huì)根據(jù)這個(gè)N算出一個(gè)范圍在0到100之間的數(shù)字，這個(gè)計(jì)算公式可以記為F2(N)。F2(N)算法比較復(fù)雜，你只要知道N越大，算出來(lái)的值越大就好了。

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

上述的計(jì)算流程比較抽象，不容易理解，所以我畫了一個(gè)簡(jiǎn)單的流程圖。圖中的F1、F2就是上面我們通過(guò)臟頁(yè)比例和redo?log寫入速度算出來(lái)的兩個(gè)值。

圖3?InnoDB刷臟頁(yè)速度策略

現(xiàn)在你知道了，InnoDB會(huì)在后臺(tái)刷臟頁(yè)，而刷臟頁(yè)的過(guò)程是要將內(nèi)存頁(yè)寫入磁盤。所以，無(wú)論是你的查詢語(yǔ)句在需要內(nèi)存的時(shí)候可能要求淘汰一個(gè)臟頁(yè)，還是由于刷臟頁(yè)的邏輯會(huì)占用IO資源并可能影響到了你的更新語(yǔ)句，都可能是造成你從業(yè)務(wù)端感知到MySQL“抖”了一下的原因。

要盡量避免這種情況，你就要合理地設(shè)置innodb_io_capacity的值，并且平時(shí)要多關(guān)注臟頁(yè)比例，不要讓它經(jīng)常接近75%。

其中，臟頁(yè)比例是通過(guò)Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的，具體的命令參考下面的代碼：

mysql>?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;

接下來(lái)，我們?cè)倏匆粋€(gè)有趣的策略。

一旦一個(gè)查詢請(qǐng)求需要在執(zhí)行過(guò)程中先f(wàn)lush掉一個(gè)臟頁(yè)時(shí)，這個(gè)查詢就可能要比平時(shí)慢了。而MySQL中的一個(gè)機(jī)制，可能讓你的查詢會(huì)更慢：在準(zhǔn)備刷一個(gè)臟頁(yè)的時(shí)候，如果這個(gè)數(shù)據(jù)頁(yè)旁邊的數(shù)據(jù)頁(yè)剛好是臟頁(yè)，就會(huì)把這個(gè)“鄰居”也帶著一起刷掉；而且這個(gè)把“鄰居”拖下水的邏輯還可以繼續(xù)蔓延，也就是對(duì)于每個(gè)鄰居數(shù)據(jù)頁(yè)，如果跟它相鄰的數(shù)據(jù)頁(yè)也還是臟頁(yè)的話，也會(huì)被放到一起刷。

在InnoDB中，innodb_flush_neighbors?參數(shù)就是用來(lái)控制這個(gè)行為的，值為1的時(shí)候會(huì)有上述的“連坐”機(jī)制，值為0時(shí)表示不找鄰居，自己刷自己的。

找“鄰居”這個(gè)優(yōu)化在機(jī)械硬盤時(shí)代是很有意義的，可以減少很多隨機(jī)IO。機(jī)械硬盤的隨機(jī)IOPS一般只有幾百，相同的邏輯操作減少隨機(jī)IO就意味著系統(tǒng)性能的大幅度提升。

而如果使用的是SSD這類IOPS比較高的設(shè)備的話，我就建議你把innodb_flush_neighbors的值設(shè)置成0。因?yàn)檫@時(shí)候IOPS往往不是瓶頸，而“只刷自己”，就能更快地執(zhí)行完必要的刷臟頁(yè)操作，減少SQL語(yǔ)句響應(yīng)時(shí)間。

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

小結(jié)

今天這篇文章，我延續(xù)第2篇中介紹的WAL的概念，和你解釋了這個(gè)機(jī)制后續(xù)需要的刷臟頁(yè)操作和執(zhí)行時(shí)機(jī)。利用WAL技術(shù)，數(shù)據(jù)庫(kù)將隨機(jī)寫轉(zhuǎn)換成了順序?qū)?#xff0c;大大提升了數(shù)據(jù)庫(kù)的性能。

但是，由此也帶來(lái)了內(nèi)存臟頁(yè)的問(wèn)題。臟頁(yè)會(huì)被后臺(tái)線程自動(dòng)flush，也會(huì)由于數(shù)據(jù)頁(yè)淘汰而觸發(fā)flush，而刷臟頁(yè)的過(guò)程由于會(huì)占用資源，可能會(huì)讓你的更新和查詢語(yǔ)句的響應(yīng)時(shí)間長(zhǎng)一些。在文章里，我也給你介紹了控制刷臟頁(yè)的方法和對(duì)應(yīng)的監(jiān)控方式。