技術文章 » 女朋友說：你要搞懂了MySQL三大紀錄檔，我就讓你嘿嘿嘿！

女朋友說：你要搞懂了MySQL三大紀錄檔，我就讓你嘿嘿嘿！

2022-07-01 06:01:39

1. 背景

MySQL實現事務、崩潰恢復、叢集的主從複製，底層都離不開紀錄檔，所以紀錄檔是MySQL的精華所在。只有瞭解MySQL紀錄檔，才算是徹底搞懂MySQL。

今天一燈就帶你深入淺出的學習MySQL的三大紀錄檔系統，Redo Log（重做紀錄檔）、Undo Log（恢復紀錄檔）、Bin Log（備份紀錄檔）。

2. Redo Log（重做紀錄檔）

2.1 Redo Log的內容與作用

Redo Log 記錄的是物理紀錄檔，也就是磁碟資料頁的修改。

作用： 用來保證服務崩潰後，仍能把事務中變更的資料持久化到磁碟上。

MySQL事務中永續性就是使用Redo Log實現的。

2.2 什麼時候寫入Redo Log？

  1. 從磁碟載入資料到記憶體
  2. 在記憶體中修改資料
  3. 把新資料寫到Redo Log Buffer中
  4. 把Redo Log Buffer中資料持久化到Redo Log檔案中
  5. 把Redo Log檔案中資料持久化到資料庫磁碟中

你可能會問，為什麼需要寫Redo Log Buffer和Redo Log FIle？直接持久化到磁碟不好嗎？

直接寫磁碟會有產生嚴重的效能問題：

  1. InnoDB在磁碟中儲存的基本單元是頁，可能本次修改只變更一頁中幾個位元組，但是需要重新整理整頁的資料，就很浪費資源。

  2. 一個事務可能修改了多頁中的資料，頁之間又是不連續的，就會產生隨機IO，效能更差。

這種方案叫做WAL（Write-Ahead Logging），預寫紀錄檔，就是先寫紀錄檔，再寫磁碟。

2.3 Redo Log刷盤規則

寫入Redo Log Buffer之後，並不會立即持久化到Redo Log FIle，需要等待作業系統呼叫fsync()操作，才會刷到磁碟上。

具體什麼時候可以把Redo Log Buffer刷到Redo Log FIle中，可以通過innodb_flush_log_at_trx_commit引數設定決定。

引數值 含義
0（延遲寫） 提交事務後，不會立即刷到OS Buffer中，而是等一秒後重新整理到OS Buffer並呼叫fsync()寫入Redo Log FIle，可能會丟失一秒鐘的資料。
1（實時寫 每次提交事務，都會重新整理到OS Buffer並呼叫fsync()寫到Redo Log FIle，效能較差
2（延遲重新整理） 每次提交事務只重新整理到OS Buffer，一秒後再呼叫fsync()寫入Redo Log FIle。

InnoDB 的Redo Log File是固定大小的。可以設定為每組4個檔案，每個檔案的大小是 1GB，那麼Redo Log File可以記錄4GB的操作。

採用迴圈寫入覆蓋的方式，write pos記錄開始寫的位置，向後移動。checkpoint記錄將要擦除的位置，也是向後移動。write pos到checkpoint之間的位置，是可寫區域，checkpoint到write pos之間的位置是已寫區域。

3. Undo Log（回滾紀錄檔）

3.1 Undo Log的內容與作用

Undo Log記錄的是邏輯紀錄檔，也就是SQL語句。

比如：當我們執行一條insert語句時，Undo Log就記錄一條相反的delete語句。

作用：

  1. 回滾事務時，恢復到修改前的資料。

  2. 實現 MVCC（多版本並行控制，Multi-Version Concurrency Control） 。

MySQL事務中原子性就是使用Undo Log實現的。

3.2 Undo Log如何回滾到上一個版本

實現方式通過兩個隱藏列trx_id（最近一次提交事務的ID）和roll_pointer（上個版本的地址），建立一個版本鏈。並在事務中讀取的時候生成一個ReadView（讀檢視），在Read Committed隔離級別下，每次讀取都會生成一個讀檢視，而在Repeatable Read隔離級別下，只會在第一次讀取時生成一個讀檢視。

4. Bin Log（備份紀錄檔）

4.1 Bin Log的內容與作用

Bin Log記錄的是邏輯紀錄檔，即原始的SQL語句，是MySQL自帶的。

作用： 資料備份和主從同步。

Bin Log共有三種紀錄檔格式，可以binlog_format設定引數指定。

引數值 含義
Statement 記錄原始SQL語句，會導致更新時間與原庫不一致。
比如 update_time=now()
Row 記錄每行資料的變化，保證了資料與原庫一致，缺點是資料量較大。
Mixed Statement和Row的混合模式，預設採用Statement模式，涉及日期、函數相關的時候採用Row模式，既減少了資料量，又保證了資料一致性。

4.2 什麼時候寫入Bin Log？

Bin Log採用追加寫入的模式，並不會覆蓋原有紀錄檔，所以可以用來恢復到之前某個時刻的資料。

Bin Log也是採用WAL模式，先寫紀錄檔，再寫磁碟。

至於什麼時候重新整理到磁碟，可以sync_binlog設定引數指定。

引數值 含義
0（延遲寫） 每次提交事務都不會刷盤，由系統自己決定什麼時候刷盤，可能會丟失資料。
1（實時寫） 每次提交事務，都會刷盤，效能較差。
N（延遲寫） 提交N個事務後，才會刷盤。

加入寫Bin Log之後的事務流程：

這就是二階段提交的概念，先寫處於prepare狀態的Redo Log，事務提交後，再寫處於commit狀態的Redo Log。

知識點總結：

有了MySQL紀錄檔的基礎，下篇就可以一塊學習MySQL叢集和主從同步了。

文章持續更新，可以微信搜一搜「 一燈架構 」第一時間閱讀更多技術乾貨。