一.redo log

使用原因

在 MySQL 里有這個問題，如果每一次的更新操作都需要寫進磁盤，然后磁盤也要找到對應的那條記錄，然后再更新，整個程序 IO 成本、查找成本都很高

其實就是 MySQL 里經常說到的 WAL 技術，WAL 的全稱 是 Write-Ahead Logging，它的關鍵點就是先寫日志，再寫磁盤

當有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log里面，并更新記憶體，這個時候更新就算完成了，同時，InnoDB 引擎會在適當的時候，將這個操作記錄更新到磁盤里面，而這個更新往往是在系統比較空閑的時候做

原理

InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個檔案，每個文 件的大小是 1GB，那么這塊“粉板”總共就可以記錄 4GB 的操作，從頭開始寫，寫到末 尾就又回到開頭回圈寫，如上面這個圖所示，

write pos 是當前記錄的位置，一邊寫一邊后移，寫到第 3 號檔案末尾后就回到 0 號檔案 開頭，checkpoint 是當前要擦除的位置，也是往后推移并且回圈的，擦除記錄前要把記錄 更新到資料檔案，

write pos 和 checkpoint 之間的是“粉板”上還空著的部分，可以用來記錄新的操作，如 果 write pos 追上 checkpoint，表示“粉板”滿了，這時候不能再執行新的更新，得停下 來先擦掉一些記錄，把 checkpoint 推進一下，

有了 redo log，InnoDB 就可以保證即使資料庫發生例外重啟，之前提交的記錄都不會丟 失，這個能力稱為crash-safe，

二.binlog(歸檔日志)

與redo log的區別

1. redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 層實作的，所有引擎 都可以使用，

2. redo log 是物理日志，記錄的是“在某個資料頁上做了什么修改”,雖然沒有保存整頁資料，但是可以記錄頁面級別的變更，;binlog 是邏輯日 志，記錄的是這個陳述句的原始邏輯，比如“給 ID=2 這一行的 c 欄位加 1 ”，

3. redo log 是回圈寫的，空間固定會用完;binlog 是可以追加寫入的，“追加寫”是指 binlog 檔案寫到一定大小后會切換到下一個，并不會覆寫以前的日志，

三.兩段提交

更新程序

update T set c=c+1 where ID=2;

執行器先找引擎取 ID=2 這一行，ID 是主鍵，引擎直接用樹搜索找到這一行，如果 ID=2 這一行所在的資料頁本來就在記憶體中，就直接回傳給執行器;否則，需要先從磁盤讀入記憶體，然后再回傳，
執行器拿到引擎給的行資料，把這個值加上 1，比如原來是 N，現在就是 N+1，得到新的一行資料，再呼叫引擎介面寫入這行新資料，
引擎將這行新資料更新到記憶體中，同時將這個更新操作記錄到 redo log 里面，此時 redo log 處于 prepare 狀態(處于prepare狀態之后要寫入磁盤中,但是redo log的commit得標識為沒有commit)，然后告知執行器執行完成了，隨時可以提交事務，
執行器生成這個操作的 binlog，并把 binlog 寫入磁盤，
執行器呼叫引擎的提交事務介面，引擎把剛剛寫入的 redo log 改成提交(commit)狀態，更新完成，

淺色框表示是在 InnoDB 內部執行的，深色框表示是在執行器中執行的，

必要性

如果不使用“兩階段提交”，那么資料庫的狀態就有可能和用它的日志恢復出來的庫的狀態不一致，

兩段提交發生時機

我們單獨寫一個update陳述句的時候，就默認提交事務的，我們的兩階段，是發生在“提交階段”的
如果是有begin...commit的陳述句序列，在執行“commit”這個陳述句的時候發生的提交階段,兩階段也就在此時發生

在兩階段提交的不同時刻，MySQL 例外重啟會出現什么現象，
- 如果在圖中時刻 A 的地方，也就是寫入 redo log 處于 prepare 階段之后、寫 binlog 之前，發生了崩潰(crash)，由于此時 binlog 還沒寫，redo log 也還沒提交，所以崩潰恢復的時候，這個事務會回滾，這時候，binlog 還沒寫，所以也不會傳到備庫，
- 在時刻 B，也就是 binlog 寫完，redo log 還沒 commit 前發生 crash，那崩潰恢復的時候 MySQL 會怎么處理 ?
```
1. 如果 redo log 里面的事務是完整的，也就是已經有了 commit 標識，則直接提交;
2. 如果 redo log 里面的事務只有完整的 prepare，則判斷對應的事務 binlog 是否存在并完整:
		a. 如果是，則提交事務; 
		b. 否則，回滾事務，
		
協助理解:
當時刻B發生crash，重啟后這部分redo log都丟失了，那么何談判斷redo log是否有完整的prepare還是commit標志呢？
答: 寫binlog之前，就已經都寫了盤并且fsync同步到磁盤了
```
- 時刻 B 發生 crash 對應的就是 2(a) 的情況，崩潰恢復程序中事務會被提交，

四.binlog寫入機制

其實，binlog 的寫入邏輯比較簡單:事務執行程序中，先把日志寫到 binlog cache，事務提交的時候，再把 binlog cache 寫到 binlog 檔案中，

一個事務的 binlog 是不能被拆開的，因此不論這個事務多大，也要確保一次性寫入，這就涉及到了 binlog cache 的保存問題，

系統給 binlog cache 分配了一片記憶體，每個執行緒一個，引數 binlog_cache_size 用于控制單個執行緒內 binlog cache 所占記憶體的大小，如果超過了這個引數規定的大小，就要暫存到磁盤，

事務提交的時候，執行器把 binlog cache 里的完整事務寫入到 binlog 中，并清空 binlog cache，狀態如圖 1 所示，

可以看到，每個執行緒有自己 binlog cache，但是共用同一份 binlog 檔案，

圖中的 write，指的就是指把日志寫入到檔案系統的 page cache，并沒有把資料持久化到磁盤，所以速度比較快，此時write步驟,寫入作業系統維護的記憶體中,此記憶體是磁盤中檔案系統申請的記憶體;

圖中的 fsync，才是將資料持久化到磁盤的操作，一般情況下，我們認為 fsync 才占磁盤的 IOPS，

write 和 fsync 的時機，是由引數 sync_binlog 控制的:

sync_binlog=0 的時候，表示每次提交事務都只 write，不 fsync;
sync_binlog=1 的時候，表示每次提交事務都會執行 fsync;
sync_binlog=N(N>1) 的時候，表示每次提交事務都 write，但累積 N 個事務后才fsync，

因此，在出現 IO 瓶頸的場景里，將 sync_binlog 設定成一個比較大的值，可以提升性能，在實際的業務場景中，考慮到丟失日志量的可控性，一般不建議將這個引數設成 0，比較常見的是將其設定為 100~1000 中的某個數值，

但是，將 sync_binlog 設定為 N，對應的風險是:如果主機發生例外重啟，會丟失最近 N 個事務的 binlog 日志，

五.redo log寫入機制

redo log buffer 里面的內容，是不是每次生成后都要直接持久化到磁盤呢?

答案是，不需要，如果事務執行期間 MySQL 發生例外重啟，那這部分日志就丟了，由于事務并沒有提交，所以這時日志丟了也不會有損失，

那么，另外一個問題是，事務還沒提交的時候，redo log buffer 中的部分日志有沒有可能被持久化到磁盤呢?

答案是，確實會有, 下面解釋，

1.寫入狀態

這個問題，要從 redo log 可能存在的三種狀態說起，這三種狀態，對應的就是圖 2 中的三個顏色塊，

這三種狀態分別是:

存在redo log buffer中,物理上是存在mysql行程記憶體中,就是圖中紅色部分
寫到磁盤(write),但是沒有持久化(fsync),物理上是在檔案系統的page cache里面,也就是圖中得黃色部分
持久化到磁盤,對應的是hard disk,也就是圖中得綠色部分

日志寫到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是持久化到磁盤的速度就慢多了，

2.寫入策略

為了控制 redo log 的寫入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 參數，它有三種可能取值:

設定為 0 的時候，表示每次事務提交時都只是把 redo log 留在 redo log buffer 中 ;
設定為 1 的時候，表示每次事務提交時都將 redo log 直接持久化到磁盤;
設定為 2 的時候，表示每次事務提交時都只是把 redo log 寫到 page cache，

InnoDB 有一個后臺執行緒，每隔 1 秒，就會把 redo log buffer 中的日志，呼叫 write 寫到檔案系統的 page cache，然后呼叫 fsync 持久化到磁盤，

注意，事務執行中間程序的 redo log 也是直接寫在 redo log buffer 中的，這些 redo log 也會被后臺執行緒一起持久化到磁盤，也就是說，一個沒有提交的事務的 redo log，也是可能已經持久化到磁盤的，

實際上，除了后臺執行緒每秒一次的輪詢操作外，還有兩種場景會讓一個沒有提交的事務的 redo log 寫入到磁盤中，

一種是，redo log buffer 占用的空間即將達到 innodb_log_buffer_size 一半的時候，后臺執行緒會主動寫盤，注意，由于這個事務并沒有提交，所以這個寫盤動作只是 write，而沒有呼叫 fsync，也就是只留在了檔案系統的 page cache，

另一種是，并行的事務提交的時候，順帶將這個事務的 redo log buffer 持久化到磁盤，假設一個事務 A 執行到一半，已經寫了一些 redo log 到 buffer 中，這時候有另外一個執行緒的事務 B 提交，如果 innodb_flush_log_at_trx_commit 設定的是 1，那么按照這個引數的邏輯，事務 B 要把 redo log buffer 里的日志全部持久化到磁盤，這時候，就會帶上事務 A 在 redo log buffer 里的日志一起持久化到磁盤，

3.兩階段提交詳解

這里需要說明的是，我們介紹兩階段提交的時候說過，時序上 redo log 先 prepare，再寫 binlog，最后再把 redo log commit，

如果把 innodb_flush_log_at_trx_commit 設定成 1，那么 redo log 在 prepare 階段就要持久化一次，因為有一個崩潰恢復邏輯是要依賴于 prepare 的 redo log，再加上 binlog 來恢復的，

每秒一次后臺輪詢刷盤，再加上崩潰恢復這個邏輯，InnoDB 就認為 redo log 在 commit 的時候就不需要 fsync 了，只會 write 到檔案系統的 page cache 中就夠了，

通常我們說 MySQL 的“雙 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都設定成 1，也就是說，一個事務完整提交前，需要等待兩次刷盤，一次是 redo log(prepare 階段)，一次是 binlog，

在“雙 1”配置下,兩階段提交的詳細流程

redo log的prepare write階段,寫入到page cache 里(redo log prepare)

然后binlog進入write階段,寫入page cache里(binlog)

然后redo log 的prepare階段,進行持久化fsync操作,持久化到磁盤(redo log prepare)

binlog進行持久化階段fsync階段,持久化到磁盤(binlog)

commit階段,先將binlog添加commit標識,再將redo log添加commit標識,redo log進行write,寫入page cache(commit)

六.組提交

1.redo log組提交

這里，我需要先和你介紹日志邏輯序列號(log sequence number，LSN)的概念，LSN 是單調遞增的，用來對應 redo log 的一個個寫入點，每次寫入長度為 length 的 redo log， LSN 的值就會加上 length，

LSN 也會寫到 InnoDB 的資料頁中，來確保資料頁不會被多次執行重復的 redo log，關于 LSN 和 redo log、checkpoint 的關系，我會在后面的文章中詳細展開，

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

從圖中可以看到，

trx1 是第一個到達的，會被選為這組的 leader;

等 trx1 要開始寫盤的時候，這個組里面已經有了三個事務，這時候 LSN 也變成了160;

trx1 去寫盤的時候，帶的就是 LSN=160，因此等 trx1 回傳時，所有 LSN 小于等于160 的 redo log，都已經被持久化到磁盤;

這時候 trx2 和 trx3 就可以直接回傳了，

所以，一次組提交里面，組員越多，節約磁盤 IOPS 的效果越好，但如果只有單執行緒壓測，那就只能老老實實地一個事務對應一次持久化操作了，

在并發更新場景下，第一個事務寫完 redo log buffer 以后，接下來這個 fsync 越晚調用，組員可能越多，節約 IOPS 的效果就越好，

2.binlog組提交

為了讓一次 fsync 帶的組員更多，MySQL 有一個很有趣的優化:拖時間，在介紹兩階段提交的時候，我曾經給你畫了一個圖，現在我把它截過來，

圖中，我把“寫 binlog”當成一個動作，但實際上，寫 binlog 是分成兩步的:

先把 binlog 從 binlog cache 中寫到磁盤上的 binlog 檔案,此時write步驟,寫入作業系統維護的記憶體中,此記憶體是磁盤中檔案系統申請的記憶體;

呼叫 fsync 持久化，

MySQL 為了讓組提交的效果更好，把 redo log 做 fsync 的時間拖到了步驟 1 之后，也就是說，上面的圖變成了這樣:

這么一來，binlog 也可以組提交了，在執行圖 5 中第 4 步把 binlog fsync 到磁盤時，如果有多個事務的 binlog 已經寫完了，也是一起持久化的，這樣也可以減少 IOPS 的消耗，

不過通常情況下第 3 步執行得會很快，所以 binlog 的 write 和 fsync 間的間隔時間短，導致能集合到一起持久化的 binlog 比較少，因此 binlog 的組提交的效果通常不如 redo log 的效果那么好，

如果你想提升 binlog 組提交的效果，可以通過設定 binlog_group_commit_sync_delay 和binlog_group_commit_sync_no_delay_count 來實作，

binlog_group_commit_sync_delay 引數，表示延遲多少微秒后才呼叫 fsync;

binlog_group_commit_sync_no_delay_count 引數，表示累積多少次以后才呼叫fsync，

這兩個條件是或的關系，也就是說只要有一個滿足條件就會呼叫 fsync，

所以，當 binlog_group_commit_sync_delay 設定為 0 的時候， binlog_group_commit_sync_no_delay_count 也無效了，

之前有同學在評論區問到，WAL 機制是減少磁盤寫，可是每次提交事務都要寫 redo log 和 binlog，這磁盤讀寫次數也沒變少呀?

現在你就能理解了，WAL 機制主要得益于兩個方面:

redo log 和 binlog 都是順序寫，磁盤的順序寫比隨機寫速度要快;

組提交機制，可以大幅度降低磁盤的 IOPS 消耗，

七.mysql提升IO性能

可以考慮以下三種方法:

設定 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 引數，減少 binlog 的寫盤次數，這個方法是基于“額外的故意等待”來實作的，因此可能會增加陳述句的回應時間，但沒有丟失資料的風險，

將 sync_binlog 設定為大于 1 的值(比較常見是 100~1000)，這樣做的風險是，主機掉電時會丟 binlog日志，

將 innodb_flush_log_at_trx_commit 設定為 2，這樣做的風險是，主機掉電的時候會丟資料，

我不建議你把 innodb_flush_log_at_trx_commit 設定成 0，因為把這個引數設定成 0，表示 redo log 只保存在沒有生氣啦申請的記憶體中，這樣的話 MySQL 本身例外重啟也會丟資料，風險太大，而 redo log 寫到檔案系統的 page cache 的速度也是很快的，所以將這個引數設定成 2 跟設定成 0 其實性能差不多，但這樣做 MySQL 例外重啟時就不會丟資料了(因為資料已經寫入了磁盤中檔案系統申請的記憶體中是作業系統維護的記憶體,而不是在mysql申請的記憶體中)，相比之下風隙訓更小，

站在巨人的肩膀上摘蘋果:

https://time.geekbang.org/column/intro/100020801

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/74693.html

標籤：MySQL

上一篇：MySQL 多表查詢

下一篇：mysql中的事務隔離級別及可重復讀讀提交詳細分析(mvcc多版本控制/undo log)

mysql兩個重要的日志redolog和binlog