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

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

你的SQL語句為什么變“慢”了

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• 第一種是，還沒有使用的；
• 第二種是，使用了并且是干凈頁；
• 第三種是，使用了并且是臟頁。

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

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

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

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

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

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

InnoDB刷臟頁的控制策略

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

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

這就要用到innodb_io_capacity這個(gè)參數(shù)了，它會(huì)告訴InnoDB你的磁盤能力。這個(gè)值我建議你設(shè)置成磁盤的IOPS。磁盤的IOPS可以通過fio這個(gè)工具來測試，下面的語句是我用來測試磁盤隨機(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)闆]能正確地設(shè)置innodb_io_capacity參數(shù)，而導(dǎo)致的性能問題也比比皆是。之前，就曾有其他公司的開發(fā)負(fù)責(zé)人找我看一個(gè)庫的性能問題，說MySQL的寫入速度很慢，TPS很低，但是數(shù)據(jù)庫主機(jī)的IO壓力并不大。經(jīng)過一番排查，發(fā)現(xiàn)罪魁禍?zhǔn)拙褪沁@個(gè)參數(shù)的設(shè)置出了問題。

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

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

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

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

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

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

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

F1(M){  if M>=innodb_max_dirty_pages_pct then      return 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越大，算出來的值越大就好了。

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

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

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

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

其中，臟頁比例是通過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;

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

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

在InnoDB中，innodb_flush_neighbors 參數(shù)就是用來控制這個(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í)行完必要的刷臟頁操作，減少SQL語句響應(yīng)時(shí)間。

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

小結(jié)

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

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

文章最后，我給你留下一個(gè)思考題吧。

一個(gè)內(nèi)存配置為128GB、innodb_io_capacity設(shè)置為20000的大規(guī)格實(shí)例，正常會(huì)建議你將redo log設(shè)置成4個(gè)1GB的文件。

但如果你在配置的時(shí)候不慎將redo log設(shè)置成了1個(gè)100M的文件，會(huì)發(fā)生什么情況呢？又為什么會(huì)出現(xiàn)這樣的情況呢？