筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》 （本篇內圖片均來自丁奇老師的講解，如有侵權，請聯繫我刪除） 12） --為什麼我的MySQL會“抖”一下？ 斷更了一段時間，因為這幾周實在是太忙了，周末加班兩天那種。。。 有時你會遇到這樣的問題，一條SQL語句，正常執行的時候很快，但是有時候會 ...
筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》
（本篇內圖片均來自丁奇老師的講解，如有侵權，請聯繫我刪除）
12） --為什麼我的MySQL會“抖”一下？
斷更了一段時間，因為這幾周實在是太忙了，周末加班兩天那種。。。
有時你會遇到這樣的問題，一條SQL語句，正常執行的時候很快，但是有時候會變得特別慢。並且這種場景很難復現，不只隨機而且持續時間很短。這其實與MySQL的臟頁以及它的刷新機制有關。之前我們有過一個關於《孔乙己》裡面酒館老闆記賬的比喻。在這個比喻里，掌柜的賬本就是我們的數據文件，掌柜的臨時寫下的粉板就是我們的日誌文件(redo log)，而掌柜的記憶就相當於我們的記憶體。
粉板可以記錄的內容有限（redo log迴圈寫），掌柜的記憶也是有限的，因此他總要找時間把這兩部分的內容記錄到賬本里去。這個術語就是flush.
當記憶體數據頁跟磁碟數據頁內容不一致的時候，我們稱這個記憶體頁為“臟頁”。記憶體數據寫入到磁碟後，記憶體和磁碟上的數據頁的內容就一致了，稱為“乾凈頁”。
大致上有四種情景來進行刷“臟頁”：
- 粉板滿了。（redo log寫滿），redo log寫滿以後，如果又有了新的數據，就不得不去除一些之前的數據。當然，在去除之前，要先把正確的數據記錄下來才行。
- 生意太好，短時間內有很多客人，掌柜的記不下來了。(系統記憶體不足)。記憶體不足時，需要先淘汰掉一些數據頁，如果這些數據頁中有“臟頁”就必須要把這些記錄到磁碟才行。你可能會有疑問，為什麼不直接淘汰掉這些頁呢，如果再有相關的記錄，就利用redo log來還原數據呢？（掌柜的疑問一部分腦子裡的記憶，下次再有的話，先看賬本，然後再看粉板，倒退出最新的記錄），這其實是從性能方面考慮的。如果我們保證每次刷臟頁一定會寫磁碟，那麼下次再讀的時候就有兩種情況。一是記憶體中有記錄，那麼直接返回。二是，記憶體中沒有，那麼先從磁碟讀，讀到了就直接返回。對應掌柜的就是，掌柜的有印象，直接返回。掌柜的沒有印象查看賬本，找到了就直接返回。
- 生意不太忙的時候，掌柜的閑著沒事，就更新賬本。（系統空閑）,閑著沒事，Mysql就自己刷臟頁玩。
- 年底放假，酒店關門幾天。(MySQL正常關閉)，正常關閉時MySQL會把記憶體的臟頁都flush到磁碟上。
回到我們開始的問題，什麼情況會抖一下呢？首先排除後兩種，空閑或是要關閉資料庫時。來看看前兩種情況：
先是redo log寫滿了，要flush臟頁，這種情況是InnoDB要儘量避免的。因為這種情況下，整個系統就不能再接受更新了，所有的更新都必須堵住。如果你從監控上看，這時的更新數會跌為0.然後是第二種情況：“記憶體不夠用了，要先將臟頁寫到磁碟”。這種情況是常態。InnoDB用緩衝池（buffer pool）管理記憶體，緩衝池中的記憶體頁有三種狀態：
- 還沒使用的
- 使用了並且是乾凈頁的
- 使用了並且是臟頁的
InnoDB的策略是儘量使用記憶體，因此對於一個長時間運行的庫來說，未被使用的頁面很少。當要讀入的數據頁沒有在記憶體的時候，就必須到緩衝池中申請一個數據頁。這時候只能把最久不使用的數據頁從記憶體中淘汰掉。如果淘汰掉的是一個乾凈頁，可以直接釋放出來複用。如果淘汰掉的是一個臟頁，就必須先將臟頁刷到磁碟，變成乾凈頁後才能復用。所以，刷臟頁雖然是常態，但是出現以下兩種情況，都會明顯的影響性能：
- 一個查詢要淘汰的臟頁太多，會導致查詢的響應時間明顯變長
- 日誌寫滿，更新全部堵住，寫性能跌為0，這個情況對敏感業務來說，是不能接受的。
所以，InnoDB需要有控制臟頁比例的機制，來儘量避免上面的這兩種情況。
InnoDB刷臟頁的控制策略：
首先，你要正確地告訴InnoDB所在主機的IO能力，這樣InnoDB才能知道需要全力刷臟頁的時候，可以刷多快。這個就要用到innodb_io_capacity這個參數了，它會告訴InnoDB你的磁碟能力。這個值建議你設置為磁碟的IOPS，磁碟的IOPS可以通過fio（linux）來進行測試。（Linux下fio，window下可以使用iometer）
InnoDB使用兩個因素來進行計算刷盤速度。一個是臟頁比例，一個是redo log的寫盤速度。參數innodb_max_dirty_pages_pct是臟頁比例上限，預設值是75%。InnoDB會根據當前的臟頁比例M，算出一個範圍在0到100之間的數字。InnoDB每次寫入日誌都有一個序號，當前寫入的序號跟checkpoint對應的序號之間的差值，我們假設為N，同樣，InnoDB會根據這個N算出一個0到100之間的數字。N越大這個計算出來的值就越大。然後，根據上述兩個計算出的數據f(M)和f(N),取其中較大的值記為R，之後引擎就可以按照innodb_io_capacity定義的能力乘以R%來控制刷臟頁的速度了。
因此要合理的設置innodb_io_capacity的值，平時要多關註臟頁的比例，不要讓它經常接近75%。
還有一個有趣的策略：一旦一個請求涉及到刷臟頁，在準備進行刷臟頁的時候，會把這個數據頁旁邊的數據頁也檢查一下，如果這個鄰居也是臟頁，那麼就會把鄰居一起刷掉。InnoDB使用參數innodb_flush_neighbors參數來控制這個行為，為1的時候就會觸發這個“連坐”機制。在MySQL8.0中，這個預設值已經是0了。
上期問題:
如果你要維護學生信息的資料庫，學生登錄名統一是"學號@gamail.com"，學號的規則是十五位的數字，前三位是城市編號，四到六位是學校編號，七到十位是入學年份，最後五位是順序編號，只考慮登錄驗證的話，你會怎麼設計這個登錄名的索引呢？
由於這個學號的規則，無論是正向還是反向的首碼索引，重覆度都很高。前三位是城市編號，四到六位是學校編號，這六位數字其實是相對固定的。而郵箱尾碼也是確定的，因此可以只存入學年份加順序編號，它們的長度是9位。在此基礎上，可以用數字類型來存儲這個9位數字，這樣只需占用4個位元組，這其實是一種hash,只是用了最簡單的轉換規則。
另有別的思路是，一個學校總人數不會很大，這點數據量，這個表必然是個小表。直接存原來的字元串，可以使業務更簡單。這也是一種很好的回答，結合實際業務。
問題：
一個記憶體配置為128GB，innodb_io_capacity設置為20000的大規格實例，正常會建議你將redo log設置成4個1GB的文件，但是如果你配置時不小心將redo log設置為了4個100M的文件，會發生什麼情況呢？為什麼呢？
