周杰倫的所有歌曲中，我最喜歡的歌就是《聽媽媽的話》，其中有這么一句歌詞：小朋友，你是否有很多問號，為什么別人在那看漫畫，我卻在學(xué)畫畫。

應(yīng)用在現(xiàn)在的場景就是：小伙伴，你是否有很多問號，為什么別人只需要簡單用一下MySQL，你卻要對MySQL深入淺出。

實際上每天的進步都是為了自己能接受頂尖大佬的技術(shù)熏陶，雖然我們不能親自聆聽他們的聲音，但是他們已經(jīng)將自己的思路寫在了他們的開源項目里，這就是我們學(xué)習(xí)開源項目的意義所在。

比如今天，我們的話題是：MySQL可以存儲上億級別的數(shù)據(jù)，但是卻幾乎不會丟失數(shù)據(jù)，這里面到底是因為什么？

先給出結(jié)論：MySQL的數(shù)據(jù)不丟失就需要保證binlog和redo log都持久化到磁盤，因此，為了保證數(shù)據(jù)不丟失，就需要了解兩個日志的寫入機制。

1 binlog寫入機制

1.1 寫入原則

binlog的寫入邏輯為：事務(wù)執(zhí)行過程中，先把日志寫到binlog cache，事務(wù)提交的時候，再把binlog cache寫到binlog文件中。

同時，一個事務(wù)的Binlog是不能拆開的，因此，無論事務(wù)多大，也要確保一次性寫入，這就涉及到binlog cache的保存問題。原因在于：binlog寫入的前提條件是事務(wù)被提交，事務(wù)至少進入prepare狀態(tài)，若此時一個事務(wù)的binlog拆分寫，意味著備庫執(zhí)行時，可能將還沒有提交的事務(wù)執(zhí)行，導(dǎo)致主備數(shù)據(jù)不一致。

系統(tǒng)給binlog cache分配了一塊內(nèi)存，每個線程一個，參數(shù)binlog_cache_size用于控制單個線程內(nèi)binlog cache所占內(nèi)存大小。如果超過了這個參數(shù)規(guī)定大小，就要暫存到磁盤。可以通過語句show status like 'Binlog_cache_disk_use';判斷默認大小32KB是否滿足大小，如果語句的值遠大于0，需要增加binlog_cache_size的值；

圖片

事務(wù)提交時，執(zhí)行器把binlog cache里的完整事務(wù)寫入到binlog，并清空binlog cache。實際上，在第一段提交狀態(tài)變?yōu)閜repare狀態(tài)時，就可以把binlog cache寫入binlog，因此，即使之后crash，也能恢復(fù)數(shù)據(jù)。

1.2 寫入流程

圖片

如圖所示，每個線程有自己binlog cache，但是共用同一份binlog文件。執(zhí)行流程為：

• 事務(wù)執(zhí)行過程中先把日志寫到binlog cache ，事務(wù)提交的時候再把binlog cache 寫入到binlog文件中，并清空binlog cache；
• 系統(tǒng)為每個線程分配了一片binlog cache內(nèi)存，參數(shù)binlog_cache_size控制單個線程內(nèi)binlog cache大小。如果超過這個大小就要暫存到磁盤；
• 事務(wù)提交的時候，執(zhí)行器把binlog cache里完整的事務(wù)寫入binlog中。并清空binlog cache。 
• 每個線程都有自己的binlog cache，共用一份binlog文件 
• write，是把日志寫入到文件系統(tǒng)的page cache內(nèi)存中，沒有持久化到磁盤，所以速度比較快。fsync是將數(shù)據(jù)持久化到磁盤，因此說，fsync才會占用磁盤的IOPS；

Page Cache是OS關(guān)于磁盤IO的緩存，位于內(nèi)核中，不適用于大文件傳輸，因為大文件傳輸page cache的命中率比較低，這個時候page cache不僅沒有起到作用還增加了一次數(shù)據(jù)從磁盤buffer到內(nèi)核page cache的開銷；

高版本的Linux系統(tǒng)中已經(jīng)把Buffer跟虛擬文件系統(tǒng)的page cache合并在一起了，因此也就沒有從磁盤buffer拷貝到內(nèi)核page cache的開銷；

write和fsync的時機，由參數(shù)sync_binlog控制（與redis的appendfsync相似）：

• sync_binlog=0，每次提交事務(wù)都只write，不做fysnc；
• sync_binlog=1 的時候，表示每次提交事務(wù)都會執(zhí)行 fsync；
• sync_binlog=N(N>1) 的時候，表示每次提交事務(wù)都 write，但累積 N 個事務(wù)后才 fsync。

因此，在出現(xiàn) IO 瓶頸的場景里，將 sync_binlog 設(shè)置成一個比較大的值，可以提升性能。在實際的業(yè)務(wù)場景中，考慮到丟失日志量的可控性，一般不建議將這個參數(shù)設(shè)成 0，比較常見的是將其設(shè)置為 100~1000 中的某個數(shù)值。

但是，將 sync_binlog 設(shè)置為 N，對應(yīng)的風(fēng)險是：如果主機發(fā)生異常重啟，會丟失最近 N 個事務(wù)的 binlog 日志。

2 redo log機制

2.1 redo log三種狀態(tài)

圖片

如圖所示的三種顏色就是redo log的三種狀態(tài)：

• 紅色部分：存在redo log buffer中，物理上是在MySQL進程內(nèi)存中；
• 黃色部分：寫到磁盤（write），但是沒有持久化（fsync），物理上是在文件系統(tǒng)的page cache中；
• 綠色部分：持久化到磁盤，對應(yīng)的是hard disk；

fsync函數(shù)同步內(nèi)存中所有已修改的文件數(shù)據(jù)到儲存設(shè)備。一般情況下，對硬盤（或者其他持久存儲設(shè)備）文件的write操作，更新的只是內(nèi)存中的頁緩存（page cache），而臟頁面不會立即更新到硬盤中，而是由操作系統(tǒng)統(tǒng)一調(diào)度，如由專門的flusher內(nèi)核線程在滿足一定條件時（如一定時間間隔、內(nèi)存中的臟頁達到一定比例）內(nèi)將臟頁面同步到硬盤上（放入設(shè)備的IO請求隊列）。 因為write調(diào)用不會等到硬盤IO完成之后才返回，因此如果OS在write調(diào)用之后、硬盤同步之前崩潰，則數(shù)據(jù)可能丟失。

如果事務(wù)執(zhí)行過程中MySQL發(fā)生異常重啟，這部分日志丟了，也不會有損失，因為事務(wù)還沒有提交， 因此，redo log buffer不需要每次生成都直接持久化磁盤。

2.2 redo log寫入策略

由于都是內(nèi)存操作，因此日志寫入redo log buffer，以及write到page cache都很快，但是持久化磁盤的速度比較慢。

為控制寫入策略，InnoDB提供了innodb_flush_log_at_trx_commit參數(shù)：

• 0：每次事務(wù)提交都只是把redo log留在redo log buffer；
• 1：每次事務(wù)提交都將redo log直接持久化到磁盤；【innodb的默認值】
• 2：每次事務(wù)提交時都只是把redo log寫到page cache；

2.3 刷盤時機

1）定時任務(wù)：InnoDB有一個后臺線程，每隔1秒，就會把redo log buffer日志調(diào)用write寫入到文件系統(tǒng)的page cache，然后調(diào)用fsync持久化到磁盤。

事務(wù)執(zhí)行過程中的redo log也是直接寫入到buffer中，這些redo log也會被后臺線程一起持久化到磁盤，因此，一個沒有提交的事務(wù)的redo log也可能已經(jīng)持久化到磁盤。

2）空間不足：redo log buffer占用的空間即將到達innodb_log_buffer_size一半時，后臺線程會主動寫盤。注意，此時由于這個事務(wù)還沒有提交，所以這個寫盤動作只是write，沒有調(diào)用fsync，即：只是寫入到page cache中。

圖片

3）其他事務(wù)提交：并行事務(wù)提交時，順帶將這個事務(wù)的redo log buffer持久化到磁盤。假設(shè)一個事務(wù) A 執(zhí)行到一半，已經(jīng)寫了一些 redo log 到 buffer 中，這時候有另外一個線程的事務(wù) B 提交，如果 innodb_flush_log_at_trx_commit 設(shè)置的是 1，那么按照這個參數(shù)的邏輯，事務(wù) B 要把 redo log buffer 里的日志全部持久化到磁盤。這時候，就會帶上事務(wù) A 在 redo log buffer 里的日志一起持久化到磁盤。

2.4 配置說明

兩階段提交，時序上是redo log先prepare，再寫binlog，最后再把redo log commit。

• 在redo log執(zhí)行prepare階段MySQL異常重啟，redo log沒有fsync，內(nèi)存丟失，直接回滾，不影響數(shù)據(jù)一致性；
• 當(dāng)redo log執(zhí)行fsync成功，但是binlog持久化異常，此時MySQL異常重啟，此時檢查redo log在prepare狀態(tài)，但是Binlog寫入失敗，則直接回滾即可；
• 當(dāng)binlog持久化后，但是redo log commit失敗，此時的redo log一定是prepare狀態(tài)，并且binlog完成，則添加commit標(biāo)記，進而提交執(zhí)行持久化，滿足數(shù)據(jù)一致性；
• binlog完成且提交，redo log也commit成功，此時數(shù)據(jù)滿足一致性。

如果把innodb_flush_log_at_trx_commit設(shè)置為1，那么redo log在prepare階段就要持久化一次，因為crash-safe依賴于prepare狀態(tài)的redo log + binlog恢復(fù)。

每秒一次后臺輪詢刷盤，再加上crash-safe，InnoDB認為redo log在commit時只需要write到文件系統(tǒng)的page cache就可以了，因為只要binlog寫盤成功，就算redo log狀態(tài)還是prepare狀態(tài)也會被認為事務(wù)已經(jīng)執(zhí)行成功，所以只需要write到page cache就OK了，沒必要浪費IO主動執(zhí)行一次fsync。

• redo log prepare && binlog commit：事務(wù)提交；
• redo log prepare && binlog uncommitted：事務(wù)回滾；

MySQL的“雙1”配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit設(shè)置為1.即：一個事務(wù)完整提交前，需要等待兩次刷盤，一次是redo log的prepare階段，一個是Binlog。

這里需要注意的是，在事務(wù)中有兩次commit，第一次commit是事務(wù)語句的commit，這里說的commit主要是第二次commit，即：redo log的commit。

兩個commit的不是一個東西，在事務(wù)提交時，commit語句可以稱為commit1, 這時就會將redolog和 binlog fsync到磁盤, 這里寫入的redolog是prepare狀態(tài)（此時是語句的commit事務(wù)），如果這個prepare狀態(tài)的redolog和binlog都fsync成功的話，這個數(shù)據(jù)就不會丟失了。 然后后續(xù)把redolog的狀態(tài)從prepare的狀態(tài)變成commit狀態(tài)，這里稱為commit2，這里的改變狀態(tài)是后臺線程刷的，和數(shù)據(jù)不丟就沒啥關(guān)系，只是為了讓redolog狀態(tài)完整。

2.5 組提交（group commit）

2.5.1 LSN

LSN(Log Sequence Number，日志邏輯序列號）是單調(diào)遞增的，用來對應(yīng)redo log的一個個寫入點，每次寫入長度為length的redo log，LSN的值就會加上length。

LSN可以看成是事務(wù)提交的序號，這個序號是在事務(wù)提交寫盤的時候生成的，因此可以說LSN反映了事務(wù)提交的順序。

LSN也會寫到InnoDB的數(shù)據(jù)頁中，確保數(shù)據(jù)頁中不會被多次執(zhí)行重復(fù)的redo log。

圖片

如圖所示三個并發(fā)事務(wù) (trx1, trx2, trx3) 在 prepare 階段，都寫完 redo log buffer，持久化到磁盤的過程，對應(yīng)的 LSN 分別是 50、120 和 160。

• trx1是第一個到達的，會被選擇這組leader；
• 等trx1開始寫盤時，組內(nèi)有三個事務(wù)，LSN變成了160；
• trx1寫盤時，攜帶的LSN為160，因此等trx1返回時，所有LSN小于等于160的redo log都已經(jīng)被持久化到磁盤；
• 這時候trx2和trx3可以直接返回；

MySQL當(dāng)多個線程在提交完prepare，redo log寫入到redo log buffer中，此時，redo log buffer存在多個線程的日志，并同步更新了LSN。第一個寫完的線程帶著LSN去刷盤，寫完后，別的線程發(fā)現(xiàn)自己的redo log已經(jīng)寫完了（LSN大于線程的LSN），直接就返回。

如上所示，一個組提交的事務(wù)越多，節(jié)約磁盤的IOPS效果越好。在并發(fā)場景，即使innodb_flush_log_at_trx_commit設(shè)置為1，事務(wù)每次prepare都要執(zhí)行刷盤，此時可能有其他的線程也在執(zhí)行事務(wù)，也可以將他們組成一個組實現(xiàn)組提交。

2.5.2 兩階段優(yōu)化

圖片

兩階段提交可以簡化為如下兩步：

• 先把binlog從binlog cache寫到磁盤的binlog文件；
• 調(diào)用fsync持久化；

既然組提交能夠優(yōu)化磁盤的IOPS，那就有了如下的優(yōu)化：

圖片

如圖所示，把redo log做fysnc的時間拖到了步驟1之后，采用交叉fsync的方式，就是為了收集更多的“提交”，這樣的組提交效果更好一些。

這么一來，binlog也可以組提交了。在執(zhí)行第4步把binlog fsync到磁盤時，如果有多個事務(wù)的 binlog 已經(jīng)寫完了，也是一起持久化的，這樣也可以減少 IOPS 的消耗。

不過通常情況下第 3 步執(zhí)行得會很快（redo log順序?qū)?，相對較快），所以 binlog 的 write 和 fsync 間的間隔時間短，導(dǎo)致能集合到一起持久化的 binlog 比較少，因此 binlog 的組提交的效果通常不如 redo log 的效果那么好。

如果想提升 binlog 組提交的效果，可以通過設(shè)置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 來實現(xiàn)。

• binlog_group_commit_sync_delay 參數(shù)，表示延遲多少微秒后才調(diào)用 fsync;
• binlog_group_commit_sync_no_delay_count 參數(shù)，表示累積多少次以后才調(diào)用 fsync。

這兩個條件是或的關(guān)系，也就是說只要有一個滿足條件就會調(diào)用 fsync，當(dāng) binlog_group_commit_sync_delay 設(shè)置為 0 的時候，binlog_group_commit_sync_no_delay_count 也無效了。

之前我們多次提及的WAL能夠減少磁盤寫，主要是得益于：

• redo log和binlog都是順序?qū)?，磁盤的順序?qū)懕入S機寫要快；
• 組提交機制，大大降低磁盤的IOPS消耗；

3 MySQL的IO瓶頸優(yōu)化

分析到這里，我們再來回答這個問題：如果你的 MySQL 現(xiàn)在出現(xiàn)了性能瓶頸，而且瓶頸在 IO 上，可以通過哪些方法來提升性能呢？針對這個問題，可以考慮以下三種方法：

• 設(shè)置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 參數(shù)，減少 binlog 的寫盤次數(shù)。這個方法是基于“額外的故意等待”來實現(xiàn)的，因此可能會增加語句的響應(yīng)時間，但沒有丟失數(shù)據(jù)的風(fēng)險。之所以說沒有丟失數(shù)據(jù)風(fēng)險，指的是無論fsync延遲多久，只要binlog沒有持久化，只是做回滾，不會出現(xiàn)丟數(shù)據(jù)。但是可能導(dǎo)致業(yè)務(wù)側(cè)超時。
• 將 sync_binlog 設(shè)置為大于 1 的值（比較常見是 100~1000）。這樣做的風(fēng)險是，主機掉電時會丟 binlog 日志。
• 將 innodb_flush_log_at_trx_commit 設(shè)置為 2。這樣做的風(fēng)險是，主機掉電的時候會丟數(shù)據(jù)。

但是并不建議把 innodb_flush_log_at_trx_commit 設(shè)置成 0。因為把這個參數(shù)設(shè)置成 0，表示 redo log 只保存在內(nèi)存中，這樣的話 MySQL 本身異常重啟也會丟數(shù)據(jù)，風(fēng)險太大。而 redo log 寫到文件系統(tǒng)的 page cache 的速度也是很快的，所以將這個參數(shù)設(shè)置成 2 跟設(shè)置成 0 其實性能差不多，但這樣做 MySQL 異常重啟時就不會丟數(shù)據(jù)了，相比之下風(fēng)險會更小。