MySQL事務日志

大家都清楚，日志是 MySQL 數(shù)據(jù)庫的重要組成部分，記錄著數(shù)據(jù)庫運行期間各種狀態(tài)信息。MySQL 日志主要包括「錯誤日志」、「查詢日志」、「慢查詢日志」、「二進制日志(binlog)」和 事務日志(redo log、undo log)幾大類。

其中，「二進制日記」和「事務日記」尤為重要，一直被人重視、深入研究；可是事實很殘忍，重視或者說大多數(shù)人一般都是了解個表面，真正懂得人并不多。真想攻破這兩塊日記必須下血本，而且還不一定能攻破。但是不要緊，為了讓你們省下血本還能順利攻破這兩塊日記，我連續(xù)研究幾周 MySQL日記，最終肝出了這篇文章。

必要概念字典介紹

基礎不牢地動山搖，還是常規(guī)套路，先把必要知識普及/溫習一遍，當后續(xù)文章出現(xiàn)疑慮反過來看下這些概念字典，說不定能柳暗花明又一村呢？

寫了又寫，想了又想，糾結了好久，這部分知識確實有點多，最后還是決定將這些必要概念字典單獨分出一個文章，后續(xù)打算用截圖方式引入各個章節(jié)中，建議遇到不懂名詞查閱一下字典。

圖1：進階知識部分示意圖

認識二進制日記（Binlog）

Binlog 概念

Binlog 是邏輯日記，用于記錄數(shù)據(jù)庫執(zhí)行的寫入操作（查詢不記錄）信息，Server層記錄和引擎層無關，并且是以追加方式進行寫入，可以通過參數(shù)max_binlog_size設置每個 Binlog 文件的大小，文件大小達到設定值時會生成新的文件來保存日記。

Binlog 作用

在實際應用中，主要用在兩個場景：主從復制和數(shù)據(jù)恢復

主從復制場景：在 Master 主端開啟 Binlog，將 Binlog 發(fā)生到各個 Slave 從端，Slave 從端重放 Binlog 從而達到主從數(shù)據(jù)一致

數(shù)據(jù)恢復場景：通過使用 mysqlbinlog 工具來恢復數(shù)據(jù)

Binlog 記錄過程及刷盤時機

Binlog 大致記錄過程是先寫 Binlog Buffer，然后通過刷盤時機，控制刷入 OS Buffer，控制 fsync() 進行寫入 Binlog File 日記磁盤的過程。

對于 Binlog，MySQL 是通過參數(shù) sync_binlog 參數(shù)來控制刷盤時機，取值是 0、1 和 N 三種值。0 表示由系統(tǒng)自行判斷何時調用 sync() 寫入磁盤；1 表示每次事務 commit 都要調用 fsync() 寫入磁盤；N 表示每 N 個事務，才會調用 fsync() 寫入磁盤。

圖2：內存和磁盤日記結構圖

Binlog 記錄格式

MySQL 5.7.7 版本之前默認格式是STATEMENT，版本之后默認是ROW，可以通過參數(shù) binlog-format 指定。

認識事務日記（Undo log）

Undo log 概念

Undo log是邏輯日記、回滾日記。比如一條修改 +3 的邏輯語句，Undo log 會記錄對應一條 -3 的邏輯日記，一條插入語句則會記錄一條刪除語句，這樣發(fā)生錯誤時，根據(jù)執(zhí)行 Undo log 就可以回滾到事務之前的數(shù)據(jù)狀態(tài)。

Undo log 作用

回滾數(shù)據(jù)：當程序發(fā)生異常錯誤時等，根據(jù)執(zhí)行 Undo log 就可以回滾到事務之前的數(shù)據(jù)狀態(tài)，保證原子性，要么成功要么失敗。

MVCC 一致性視圖：通過 Undo log 找到對應的數(shù)據(jù)版本號，是保證 MVCC 視圖的一致性的必要條件。

Undo log 記錄過程及刷盤時機

刷盤過程及時機類似于 Binlog 和 Redo，可以參考 Redo log 刷盤時機章節(jié)給出的圖片，已經體現(xiàn)出來了。

Undo log 總結

Undo log 日記內容不是很多，重點是回滾和多版本控制 MVCC那塊。此外，我記得印象筆記深刻的是長事務會導致日記過多，這個日記就是 Undo log。因為長事務存在，導致需要保存很多視圖快照，其實這里就是涉及到Undo log 何時刪除和生成的問題，當時糾結好久，其實很簡單。生成是事務開始后寫 Redo log 之前生成，當沒有事務需要用到 Undo log 時就會被刪除。舉個例子，如果事務 A 一直存活，那么事務 A 之后產生的事務 B、C...等等就算提交了，也不會被刪除，因為事務 A 需要用到 B、C... 事務去找 A 的版本。所以避免長事務可以減少Undo log 日記量，當然還可以提高性能。

認識事務日記 （Redo log）

Redo log 概念

Redo log 是重做日記，屬于InnoDB引擎的日記。是物理日記，日記記錄的內容的是數(shù)據(jù)頁的更改，這個頁 “做了什么改動”。如：add xx記錄 to Page1，向數(shù)據(jù)頁Page1增加一個記錄。

Redo log 作用

前滾操作：具備crash-safe能力，提供斷電重啟時解決事務丟失數(shù)據(jù)問題。

提高性能：先寫Redo log記錄更新。當?shù)鹊接锌臻e線程、內存不足、Redo log滿了時刷臟。寫 Redo log 是順序寫入，刷臟是隨機寫，節(jié)省的是隨機寫磁盤的 IO 消耗（轉成順序寫），所以性能得到提升。此技術稱為WAL技術：Write-Ahead Logging，它的關鍵點就是先寫日記磁盤，再寫數(shù)據(jù)磁盤。

Redo log 兩階段提交

更新內存后引擎層寫 Redo log 將狀態(tài)改成 prepare 為預提交第一階段，Server 層寫 Binlog，將狀態(tài)改成 commit為提交第二階段。兩階段提交可以確保 Binlog 和 Redo log 數(shù)據(jù)一致性。

Redo log 容災恢復過程

MySQL的處理過程如下

判斷 redo log 是否完整，如果判斷是完整（commit）的，直接用 Redo log 恢復

如果 redo log 只是預提交 prepare 但不是 commit 狀態(tài)，這個時候就會去判斷 binlog 是否完整，如果完整就提交 Redo log，用 Redo log 恢復，不完整就回滾事務，丟棄數(shù)據(jù)。

只有在 redo log 狀態(tài)為 prepare 時，才會去檢查 binlog 是否存在，否則只校驗 redo log 是否是 commit 就可以啦。怎么檢查 binlog：一個完整事務 binlog 結尾有固定的格式。

Redo log 刷盤時機

Undo log 的刷盤時機和 Redo log 差不多，但是對于 Undo log 我沒找到對應的刷盤參數(shù)設計，所以不在提。Redo log 每次先寫入 Redo Log Buffer 中，然后通過刷盤時機控制刷入 OS Buffer 時間和刷入日記磁盤的時間。

圖3：內存和磁盤日記結構圖

在 Undo Log 中，MySQL 是通過參數(shù)innodb_flush_log_at_trx_commit來控制刷盤時機，取值是 0、1 和 2三種值。0 表示事務提交后，每秒寫入 OS Buffer 并調用 fsync() 寫入日記磁盤中；1 表示每次事務提交會寫入OS Buffer 并調用 fsync() 將日記寫入日記磁盤中。2 表示事務每次提交寫入到 OS Buffer，每秒調用 fsync() 寫入日記磁盤?？梢妳?shù)為 1 是最安全的，同時也是默認值。

圖4：Redo log刷盤時機參數(shù)對應操作圖

Redo log 存儲方式

圖5：Redo log File 環(huán)形存儲結構圖

上圖是日記磁盤的 Redo log 環(huán)形設計圖（從頭寫，寫到結束又從頭開始寫~循環(huán)）。write pos 和 check point 是兩個指針，write pos指針指向當前日記文件寫入的位置，check point 指針指向當前要擦除的開始位置。圖中綠色部分是可以寫入 Redo log 地方，每次寫入，write pos 指針會順時針推進，當然基本不會與 check point 指針重合，因為 MySQL 有這種機制去實現(xiàn)，每次觸發(fā)檢查點checkpoint，check point 會指針向前推進，這個過程就是需要進行刷日記和數(shù)據(jù)磁盤，記錄相應的 LSN，引出難點 LSN。

Redo Log 檢查點

啥時候會觸發(fā)檢查點 checkpoint

圖6：檢查點觸發(fā)時機

「Checkpoint 發(fā)生的時間、條件及臟頁的選擇等都非常復雜。而 Checkpoint 所做的事情無外乎是將緩沖池中的臟頁刷回到磁盤，不同之處在于每次刷新多少頁到磁盤，每次從哪里取臟頁，以及什么時間觸發(fā) Checkpoint。這些本文不會去研究」。

Redo Log LSN

LSN 這個概念，比較復雜。LSN 稱為日志的邏輯序列號(log sequence number)，在 innodb 存儲引擎中，lsn占用 8 個字節(jié)。LSN 的值會隨著日志的寫入而逐漸增大。「可以簡單理解SLN就是記錄從開始到現(xiàn)在已經產生了多少字節(jié)的Redo log值」。

存儲方式兩個指針又是通過 LSN 計算得到指向位置，因為 LSN 記錄的是文件的大小字節(jié)，當超過文件大小時，需要用取模計算出這兩個指針位置，取模使得寫入就會從頭開始寫，這樣使得兩個指針在一個文件中，一直落在循環(huán)位置，你追我趕的過程。這就是 Redo log 環(huán)形邏輯思想設計實現(xiàn)。

上面提到LSN比較復雜，是因為它有很多個值，輸入命令 show engine innodb status; ，可以看到四個的 lsn 記錄

圖7：LSN值列表

為了方便識別，我都為它們重新命名，如下所示。名詞記不住，后面無法繼續(xù)深入

內存日記：redo log buffer lsn；磁盤日記：redo log file lsn；

一般關系為：redo log buffer lsn >= redo log file lsn，如果刷盤時機為1，則redo log buffer lsn = redo log file lsn。

內存數(shù)據(jù)頁：data buffer lsn；數(shù)據(jù)磁盤數(shù)據(jù)頁：data disk lsn；

一般關系為data buffer lsn > data disk lsn，如果已經刷入數(shù)據(jù)磁盤，則data buffer lsn = data disk lsn。

檢查點：chckpoint lsn；

后面提到檢查點刷盤，數(shù)據(jù)刷盤和日記刷盤（如果有日記刷盤：則說明我假設的日記刷盤的時機設置值不為1，為1 是同步的，即始終 redo log buffer lsn = redo log file lsn，不會由檢查點觸發(fā)刷日記磁盤）。

都說 Redo log 是環(huán)形記錄，那么怎么記錄的？下面結合 LSN 給出記錄過程虛構圖，可以對比 Redo log 存儲方式圖

?

相關知識：日記磁盤 + redo log file lsn + checkpoint lsn + 雙指針（write pos、check point）

?

1-8 按時間順序發(fā)生。1 點是假設最初的狀態(tài)；2、3 點寫日記磁盤；4 點是觸發(fā)了檢查點 checkpoint，進行刷盤，「checkpoint lsn=1開始」，刷盤結束并更新「checkpoint lsn=512」。在 5 點、6 點已經刷過了一循環(huán)內存、二循環(huán)內存，「從頭開始寫入 log，兩個指針指向回到了頭部」。第 7 點也是一個觸發(fā) checkpoint 的過程。9 點是假設沒有更新，最后達到平衡的結果，即內存中數(shù)據(jù)頁和日記都完成了刷盤。

圖8：Redo Log File存儲過程

整個流程：

在某些情況下，觸發(fā) checkpoint，觸發(fā)數(shù)據(jù)頁和日志頁刷盤，此時將內存中的臟數(shù)據(jù)---數(shù)據(jù)臟頁和日志臟數(shù)據(jù)"分別刷到數(shù)據(jù)磁盤和日記磁盤中，而且兩者刷盤速度不一樣。checkpoint 會保護機制，當數(shù)據(jù)刷盤速度超過日志刷盤時，將會暫時停止數(shù)據(jù)刷盤，等待日志刷盤進度超過數(shù)據(jù)刷盤。

刷盤時，對于數(shù)據(jù)磁盤，全部都是在內存中，此時每次刷一個數(shù)據(jù)頁到內存更新數(shù)據(jù)頁也更新了「data disk lsn」為「data buffer lsn」（在更新內存數(shù)據(jù)頁時，會更新data buffer lsn）「。」

對于日記磁盤，除了要記錄 checkpoint lsn 的值為檢查點 checkpoint的值（必須在結束時直接記錄一個值，速度很快），這里是針對日記刷盤時機不是1（1是同步緩存刷日記刷盤）時，并且日記還沒刷到日記磁盤需要觸發(fā)將緩存中日記提前刷到日記磁盤中，此時會將redo buffer log刷到redo log file中也更新了redo log file lsn為redo log buffer lsn。

模擬檢查點觸發(fā)前后，整個流程變化，一個數(shù)據(jù)頁和日記，「數(shù)據(jù)變化及l(fā)sn從179-180的變化圖（刷盤時機不為1）」

Redo log 容災恢復過程與 LSN

Redo log 容災恢復過程和 LSN 的知識，再次細化 Redo log 恢復過程

重啟 innodb 時，Redo log 完不完整，采用 Redo log 相關知識。用 Redo log 恢復，啟動數(shù)據(jù)庫時，InnoDB 會掃描數(shù)據(jù)磁盤的數(shù)據(jù)頁 data disk lsn 和日志磁盤中的 checkpoint lsn。兩者相等則從 checkpoint lsn 點開始恢復，恢復過程是利用 redo log 到 buffer pool，直到 checkpoint lsn 等于 redo log file lsn，則恢復完成。

如果 checkpoint lsn 小于 data disk lsn，說明在檢查點觸發(fā)后還沒結束刷盤時數(shù)據(jù)庫宕機了。因為 checkpoint lsn 最新值是在數(shù)據(jù)刷盤結束后才記錄的，檢查點之后有一部分數(shù)據(jù)已經刷入數(shù)據(jù)磁盤，這個時候數(shù)據(jù)磁盤已經寫入部分的部分恢復將不會重做，直接跳到沒有恢復的 lsn 值開始恢復。

了解 ChangeBuffer

為啥提到 ChangeBuffer

為啥本文我會提到 ChangeBuffer 呢，其實很多時候會將 ChangeBuffer 和 Redo log 搞混，兩者都是巧用內存，減少磁盤 IO，為了不弄混我覺得有必要專門對這個進行一個講解。

ChangeBuffer 概念及作用

下面是我對 ChangeBuffer 的簡單介紹

也就是說對于更新的操作，如果用到了 ChangeBuffer，更新的數(shù)據(jù)所在的數(shù)據(jù)頁如果不在內存中，將不用去數(shù)據(jù)磁盤將數(shù)據(jù)頁讀到內存，而是將這一次操作記錄在 ChangeBuffer 中，「ChangeBuffer 主要節(jié)省的則是隨機讀磁盤的 IO 消耗」，下次讀取查詢等讀取數(shù)據(jù)頁時用上 ChangeBuffer 中的記錄即可。其實也是一種巧用內存的思想。

ChangeBuffer 與 Redo log 區(qū)別

Redo log 主要節(jié)省的是隨機寫磁盤的 IO 消耗（轉成順序寫），而 ChangeBuffer 主要節(jié)省的則是隨機讀磁盤的 IO 消耗

這句話怎么理解，看下面：

Redo log 與 ChangeBuffer (含磁盤持久化) 這 2 個機制，不同之處在于優(yōu)化了整個變更流程的不同階段。

先不考慮 Redo log、ChangeBuffer 機制，簡化抽象一個更新 (insert、update、delete) 流程：

從磁盤讀取待變更的行所在的數(shù)據(jù)頁，讀入內存頁中

對內存頁中的行，執(zhí)行變更操作

將變更后的數(shù)據(jù)頁，寫入至數(shù)據(jù)磁盤中

其中，流程中的步驟 1 涉及隨機讀磁盤 IO；步驟 3 涉及隨機寫磁盤 IO；剛好對應 ChangeBuffer 和 Redo log。

對那句話的理解答案：

ChangeBuffer 機制，優(yōu)化了步驟 1——避免了隨機讀磁盤 IO ，將不在內存中的數(shù)據(jù)頁的操作寫入ChangeBuffer 中，而不是將數(shù)據(jù)頁從磁盤讀入內存頁中

Redo log 機制， 優(yōu)化了步驟 3——避免了隨機寫磁盤 IO，將隨機寫磁盤，優(yōu)化為了順序寫磁盤(寫 Redo log，確保 crash-safe)

有沒有用到 ChangeBuffer 對于 Redo log 的區(qū)別

Redo log 機制，為了保證 crash-safe，一直都會用到。有無用到 ChangeBuffer 機制，對于 redo log 這步的區(qū)別在于—— 用到了 ChangeBuffer 機制時，在 Redo log 中記錄的本次變更，是記錄 new change buffer item 相關的信息，而不是直接的記錄物理頁的變更。在我們 mysql innodb 中， ChangeBuffer 機制不是一直會被應用到，僅當待操作的數(shù)據(jù)頁當前不在內存中，需要先讀磁盤加載數(shù)據(jù)頁時，ChangeBuffer 才有用武之地。

ChangeBuffer的 merge 過程

除了訪問這個數(shù)據(jù)頁會觸發(fā) merge 外，系統(tǒng)有后臺線程會定期 merge。在數(shù)據(jù)庫正常關閉（shutdown）的過程中，也會執(zhí)行 merge 操作。

merge 過程做三步

從磁盤讀入數(shù)據(jù)頁到內存（老版本的數(shù)據(jù)頁）；

從 change buffer 里找出這個數(shù)據(jù)頁的 change buffer 記錄 (可能有多個），依次應用，得到新版數(shù)據(jù)頁；

寫 redo log。這個 redo log 包含了數(shù)據(jù)的變更和 change buffer 的變更。

日記大連貫U-R-B，一舉攻破拿下

前面分別講的是 Binlog、Undo log 和 Redo log，下面將他們都串聯(lián)起來，在一些流程體現(xiàn)全部日記。

同樣，以一些最經典的更新語句例子展開說明。

制造演示數(shù)據(jù)

測試語句：插入語句+查詢語句，a字段是普通索引

1、insert into ta(a,b) values(2,5),(7, 5) 2、select * from t where a in (2, 7)

假設原來的數(shù)據(jù)如下圖，數(shù)據(jù)頁 page1 在內存中，page2 不在。插入的數(shù)據(jù) (2，5) 落在 page1，數(shù)據(jù) (7,5) 落在page2 中。

假設沒有日記和 ChangeBuffer 示范

先不考慮所有日記及 ChangeBuffer 機制，簡化抽象一個更新 insert 流程

從磁盤讀取待變更的行所在的數(shù)據(jù)頁，讀入內存頁中

對內存頁中的行，執(zhí)行變更操作

將變更后的數(shù)據(jù)頁，寫入至數(shù)據(jù)磁盤中

考慮所有日記和 ChangeBuffer 示范--現(xiàn)有 Innodb 流程

過程是 兩階段提交-----日記刷盤------數(shù)據(jù)刷盤（涉及 Redo log lsn 和 ChangeBuffer 的內容）

兩階段提交過程

數(shù)據(jù) (2,5) 所在頁 page 1 在內存中直接更新內存；數(shù)據(jù) (7,5) 所在頁 page 2 不在內存中，記錄 change buffer（具有唯一性的索引或者沒有使用 change buffer 的操作是將磁盤中的數(shù)據(jù)頁讀入內存中并做更新）。

寫 undo 日記?！赶葘懢彺妫竺娓鶕?jù)刷盤參數(shù)決定何時刷入磁盤，后面的 redo/Binlog 都一樣」。日記刷盤 在每一個日記中基本已經提到，它和設置的參數(shù)有關，下文不會再展開介紹。

寫 redo 日記（先記在內存中的更新，然后記不在內存中的 change buffer 的改變）

日記狀態(tài)改成 prepare 階段。

寫 Binlog日記。

提交事務，日記狀態(tài)改成 commit 階段。

merge 過程

緊接著上文，圖片可上下參考，假設現(xiàn)在執(zhí)行查詢語句 select * from t where a in (2, 7) ，此次查詢索引 a=7 所在的數(shù)據(jù)頁不在內存中，并且上一步更新已經在 change buffer 中有記錄，將會觸發(fā) merge 過程

將 page 2 讀入內存

依次應用 change buffer 中的記錄，得到最新版數(shù)據(jù)頁

寫入 redo，之前記錄的 changebuffer 改動，現(xiàn)在改成數(shù)據(jù)頁的改動

至于 changebuffer 被應用后是刪除還是標記，還有 redo 中原有的記錄 changebuffer 的改動怎么調整是刪除還是修改成數(shù)據(jù)頁的改動這里下面的圖是按照自己的想法描述出來，如有誤望留言指正。

數(shù)據(jù)刷盤過程

數(shù)據(jù)刷盤 flush 的有四種情況

InnoDB 的 redo log 寫滿了。這時候系統(tǒng)會停止所有更新操作，把 checkpoint 往前推進，redo log 留出空間可以繼續(xù)寫

系統(tǒng)內存不足。當需要新的內存頁，而內存不夠用的時候，就要淘汰一些數(shù)據(jù)頁，空出內存給別的數(shù)據(jù)頁使用。如果淘汰的是臟頁，就要先將臟頁寫到磁盤

MySQL 認為系統(tǒng)空閑的時候

MySQL 正常關閉的情況

數(shù)據(jù)刷盤也代表著 Redo log 檢查點 checkpoint 觸發(fā)，較為復雜。

假設數(shù)據(jù)刷盤 flush 的四種情況發(fā)生了一種，那么聯(lián)系上文的過程將如下

將臟頁從內存中刷回到數(shù)據(jù)磁盤

刷完后更新檢查點 checkpoint 的值

流程中間某個環(huán)節(jié)數(shù)據(jù)庫宕機后，恢復具體過程，這些留在心里了，沒往上去寫，讀者可以自行思考，不難。

結尾

整個文章講了 Binlog、Undo log 和 Redo log，隨帶一提 ChangeBuffer，對此，講到這里，基本上要把我要將的已經講完，內容挺多，有耐心可以慢慢啃，不懂歡迎留言！

思考環(huán)節(jié)，下面留下兩個問題，歡迎大家留言解答

1、為啥 Binlog 沒有 crash-safe 功能？

2、保證 crash-safe 為啥要用兩個日記，不能用一個日記嗎（Redo log 或 Binglog）?

責任編輯：xj

原文標題：面試官的靈魂一擊：你懂 MySQL 事務日志嗎？

文章出處：【微信公眾號：數(shù)據(jù)分析與開發(fā)】歡迎添加關注！文章轉載請注明出處。