系列文章
六、五分鐘，讓你明白MySQL是怎麼選擇索引《死磕MySQL系列 六》
專案中將MySQL的報錯、異常、執行時間長的都打到了釘釘群中，這樣有利於平時及時處理。今天要聊的是無法復現的慢查詢。
一、為什會出現無法復現的“慢”SQL
在一生摯友redo log、binlog《死磕MySQL系列 二》中詳細的說明了redo log、binlog。此時你知道了在更新時當事務提交後，並非直接修改資料庫的資料，而是先更新記憶體並在 redo log中記錄相關的操作。
總歸是要把記憶體的資料刷入磁碟中，也可以稱之為刷髒頁（flush）。
什麼是髒頁、乾淨頁
大多數資料都提及到髒頁，那麼髒頁到底是什麼呢？髒頁時記憶體資料頁的資料跟磁碟資料不一致時，就稱這個記憶體頁為髒頁。
當記憶體頁寫入磁碟後，記憶體和磁碟的資料頁就一致了，此時稱這個記憶體頁為乾淨頁。
什麼時候髒頁會變為乾淨頁
第一種
Innodb的redo log寫滿了，也就是下圖的write pos 追上了check point了，此時系統所有的更新操作都會停止。
直至check point推進了，對應的髒頁都flush到磁碟了，redo log才可以繼續寫。
一般情況下這個redo log日誌在開發前期根據innodb_log_file_size引數設定好後就不會出現redo log寫滿的情況。
第二種
記憶體不足導致，更新一條語句會先更新記憶體再更新到redo log，若記憶體不足就無法申請新的記憶體就需要淘汰一些資料頁。就需要把髒頁flush到磁碟。
有沒有想過既然更新操作給記憶體和redo log都存了一份，那麼能不能直接把記憶體頁淘汰掉，再有請求時從磁碟讀入資料頁再把redo log拿出來應用不行嗎？
記憶體滿時不刷髒頁而直接淘汰掉，那下次請求磁碟中的乾淨頁到記憶體時，還需要額外的判斷redo log中是否有對該頁的修改，有的話還需要對它應用redo log。這個髒頁始終都是要刷盤的，但現在缺額外多了應用redo log的操作。所以不能直接淘汰記憶體，而是記憶體滿時直接flush。
另外，redo log是迴圈寫的，若想應用redo log那麼redo log就要一直存在，不能刪除。違背了系統設計。
第三種
MySQL在系統低峰期時進行刷髒頁
第四種
MySQL正常關閉時會把記憶體的髒頁都刷到磁碟中，重啟後從磁碟直接讀資料，啟動速度會很快。
結論
到這裡你就應該明白，莫名其妙的慢SQL就是因為flush造成的，那麼這四種情況都是怎麼影響MySQL的呢？
二、四種flush對效能的影響
第三、四種情況不會因為flush而導致MySQL執行慢，一個是系統空閒時段，另一個是資料庫本來就要關閉了。
redo log寫滿了，需要flush髒頁
這種情況在第二期文章中就已經給了方案，redo log一旦寫滿整個系統就不再接受更新操作了， 所有的更新操作都得停滯，直到check point推進了。
擴充套件
在MySQL中提供了innodb_log_file_size引數來優化redo log日誌。
對於innodb_log_file_size的設定也是有一些計算規則的，下面將為你介紹。
若innodb_log_file_size設定太小，將導致redo log檔案頻繁切換，頻繁的觸發資料庫的檢查點（check point），導致記錄更新到資料檔案的次數增加，從而影響IO效能。
同樣，如果有一個大的事務，並且所有 redo log日誌都已寫滿，但是還沒有完成，將導致日誌無法切換，從而導致 MySQL直接堵死。
innodb_log_file_size設定太大，雖然極大地提高了 IO效能，但是在 MySQL重啟或當機時，恢復時間會因為 redo log檔案過大而延長。而這種恢復時間通常是無法控制的。
如何合理的設定innodb_log_file_size？
用一個指令碼定時執行，記錄對應時間的sequenumber再取平均值，計算出的誤差將減至最小。sequenumber是當每個 binlog生成時，該值從1開始，然後遞增，每增加一個事務， sequenumber就加上1。
系統記憶體不足，要刷髒頁
Innodb中管理記憶體的是buffer pool，記憶體頁在上文可得知存在三種狀態，未使用的、使用了是乾淨頁、使用了是髒頁。
對於一個長時間執行的庫來說，未被使用的頁非常少，當記憶體不足時，就只能把最久不使用的資料頁從記憶體中淘汰掉。
若淘汰的是一個乾淨頁，就直接釋放使用，但如果是髒頁就必須先把髒頁刷盤，變為乾淨頁進行復用。
查詢的資料沒有在記憶體中，就需要把資料從磁碟中讀入資料，若讀的資料太多就需要淘汰多個髒頁，會導致查詢時間邊長。
redo log日誌寫滿，所有的更新系統都不執行，對於大多數業務來說都不能接受。
為了防止這種情況的發生就需要控制刷髒頁的頻率。
三、如何設定刷髒頁的速度
刷髒頁到磁碟的快慢必定跟系統的IO能力有關，在MySQL中innodb_io_capacity是控制刷髒頁的速度。
在從緩衝區重新整理髒頁時（check point）,每秒重新整理髒頁的數量就等於innodb_io_capacity的值。
這個值可以設定成磁碟的IOPS，可以使用fio工具來測試，具體使用這裡就不聊了。
刷髒頁的速度也要根據髒頁比例、redo log寫盤速度來決定。
引數innodb_max_dirty_pages_pct是髒頁比例上限，在MySQL8.0這個比例預設為90%，MySQL5.6還是75%。
一般情況下對於innodb_io_capacity的值設定為髒頁比例上限與寫redo log日誌時的日誌序號減去checkpoint的值，倆個值取最大的即可。
髒頁比例的計算公式是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;
在這個SQL語句中可以看到使用的是global_status這張表在performance_schema這個庫裡邊。執行命令前需要執行use performance_schema。
當你的MySQL寫入速度很慢，TPS很低，IO壓力不大時需要排查的地方
出現這個問題時就考慮下一下innodb_io_capacity這個引數值設定是否合理。
在1核2G的伺服器預設值是200，在公司伺服器上看是2000，也是跟伺服器配置有關係的。
四、有趣引數
在MySQL8.0中引數innodb_flush_neighbors預設值為0。
當一個查詢需要在執行過程中先flush掉一個髒頁時，如果這個資料頁旁邊的資料頁剛好是髒頁，就會把這個資料頁一同刷掉，而這個連帶的邏輯會持續下去。會使SQL的查詢變的更慢。
“堅持學習、堅持寫作、堅持分享是咔咔從業以來所秉持的信念。願文章在偌大的網際網路上能給你帶來一點幫助，我是咔咔，下期見。
”