MySQL中redo log、undo log和binlog三者的區別是什么

今天就跟大家聊聊有關MySQL中redo log、undo log和binlog三者的區別是什么，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結了以下內容，希望大家根據這篇文章可以有所收獲。

事務的實現

redo log保證事務的持久性，undo log用來幫助事務回滾及MVCC的功能。

InnoDB存儲引擎體系結構

redo log

Write Ahead Log策略

事務提交時，先寫重做日志再修改頁；當由于發生宕機而導致數據丟失時，就可以通過重做日志來完成數據的恢復。

• InnoDB首先將重做日志信息先放到重做日志緩存

• 按一定頻率刷新到重做日志文件

重做日志文件： 在默認情況，InnoDB存儲引擎的數據目錄下會有兩個名為ib_logfile1和ib_logfile2的文件。每個InnoDB存儲引擎至少有1個重做日志文件組(group)，每個文件組下至少有2個重做日志文件。

下面圖一，很好說明重做日志組以循環寫入方式運行，InnoDB存儲引擎先寫ib_logfile1，當達到文件最后時，會切換至重做日志文件ib_logfile2.

而圖2，增加一個OS Buffer，有助于理解fsync過程。

關于log group，稱為重做日志組，是一個邏輯上的概念。InnoDB存儲引擎實際只有一個log group。

log group中第一個redo log file，其前2KB部分保存4個512字節大小塊：

重做日志緩沖刷新到磁盤

下面三種情況刷新：

• Master Thread每一秒將重做日志緩沖刷新到重做日志文件

• 每個事務提交時會將重做日志緩沖刷新到重做日志文件

• 當重做日志緩沖池剩余空間小于1/2時，重做日志刷新到重做日志文件

補充上述三種情況第二種，觸發寫磁盤過程由參數innodb_flush_log_at_trx_commit控制，表示提交(commit)操作時，處理重做日志的方式。

參數innodb_flush_log_at_trx_commit有效值有0、1、2

• 0表示當提交事務時，并不將事務的重做日志寫入磁盤上日志文件，而是等待主線程每秒刷新。

• 1表示在執行commit時將重做日志緩沖同步寫到磁盤，即伴有fsync的調用

• 2表示將重做日志異步寫到磁盤，即寫到文件系統的緩存中。不保證commit時肯定會寫入重做日志文件。

0，當數據庫發生宕機時，部分日志未刷新到磁盤，因此會丟失最后一段時間的事務。
2，當操作系統宕機時，重啟數據庫后會丟失未從文件系統緩存刷新到重做日志文件那部分事務。

下圖有助于理解

重做日志塊

在InnoDB存儲引擎中，重做日志都是以512字節進行存儲的。意味著重做日志緩存、重做日志文件都是以塊(block)的方式進行保存的，每塊512字節。

重做日志頭12字節，重做日志尾8字節，故每個重做日志塊實際可以存儲492字節。

重做日志格式

redo log是基于頁的格式來記錄的。默認情況下，innodb的頁大小是16KB(由 innodb_page_size變量控制)，一個頁內可以存放非常多的log block(每個512字節)，而log block中記錄的又是數據頁的變化。

log body的格式分為4部分：

• redo_log_type：占用1個字節，表示redo log的日志類型。

• space：表示表空間的ID，采用壓縮的方式后，占用的空間可能小于4字節。

• page_no：表示頁的偏移量，同樣是壓縮過的。

• redo_log_body表示每個重做日志的數據部分，恢復時會調用相應的函數進行解析。例如insert語句和delete語句寫入redo log的內容是不一樣的。

如下圖，分別是insert和delete大致的記錄方式。

redo日志恢復

下面LSN(Log Sequence Number)代表checkpoint，當數據庫在LSN為10000時發生宕機，恢復操作僅恢復LSN10000-LSN13000范圍內日志

undo log

undo log的作用

undo是邏輯日志，只是將數據庫邏輯地恢復到原來的樣子；所有修改都被邏輯地取消了，但是數據結構和頁本身在回滾之后可能不大相同。

undo log有兩個作用：提供回滾和多個行版本控制(MVCC)。

InnoDB存儲引擎回滾時，對于每個INSERT，會完成一個DELETE；對于每個DELETE，會執行一個INSERT；對于每個UPDATE，會執行一個相反的UPDATE，將修改前的行放回去。

MVCC： 當用戶讀取一行記錄時，若該記錄已經被其他事務占用，當前事務可以通過undo讀取之前的行版本信息，以此實現非鎖定讀取。

undo log的存儲方式

innodb存儲引擎對undo的管理采用段的方式。rollback segment稱為回滾段，每個回滾段中有1024個undo log segment。

在以前老版本，只支持1個rollback segment，這樣就只能記錄1024個undo log segment。后來MySQL5.5可以支持128個rollback segment，即支持128*1024個undo操作，還可以通過變量 innodb_undo_logs (5.6版本以前該變量是 innodb_rollback_segments )自定義多少個rollback segment，默認值為128。

undo log默認存放在共享表空間中。

事務提交undo log處理過程

當事務提交時，InnoDB存儲引擎會做以下兩件事：

• 將undo log放入一個列表中，以供之后的purge使用，是否可以最終刪除undo log及所在頁由purge線程來判斷

• 判斷undo log 所在的頁是否可以重用，若可以，分配給下個事務使用

當事務提交時，首先將undo log放入鏈表中，然后判斷undo頁的使用空間是否小于3/4，若是，則表示該undo頁可以被重用，之后新的undo log記錄在當前undo log的后面

undo log分為：

• insert undo log

• update undo log

因為事務隔離性，insert undo log對其他事務不可見，所以該undo log可以在事務提交后直接刪除，不需要進行purge操作。

update undo log記錄的是對delete和update操作產生的undo log。該undo log可能需要提供MVCC機制，因此不能提交時就進行刪除

update分為兩種情況：

• date的列如果不是主鍵列，在undo log中直接反向記錄是如何update的。即update是直接進行的。

• update主鍵的操作可以分為兩步：

• 首先將原主鍵記錄標記為已刪除，因此需要產生一個類型為TRX_UNDO_DEL_MARK_REC的undo log

• 之后插入一條新的記錄，產生一個類型為TRX_UNDO_INSERT_MARK_REC的undo log

InnoDB purge時，會先從history列表找undo log，然后再從undo page中找undo log；可以避免大量隨機讀取操作，從而提高purge效率。

MVCC(多版本并發控制)

MVCC其實就是在每一行記錄后面增加兩個隱藏列，記錄創建版本號和刪除版本號，而每一個事務在啟動的時候，都有一個唯一的遞增的版本號。

MVCC只在REPEATABLE READ 和READ COMMITTED兩個隔離級別下工作。讀未提交不存在版本問題，序列化則對所有讀取行加鎖。

示例：

插入操作：記錄的創建版本號就是事務版本號

如插入一條記錄，事務id假設是1，則創建版本號也是1

更新操作：先標記舊版本號為已刪除，版本號就是當前版本號，再插入一條新的記錄

如事務2把name字段更新
update table set name = 'new test' where id = 1;

原來的記錄被標記刪除，刪除版本號為2，并插入新記錄，創建版本號為2

刪除操作：把事務版本作為刪除版本號

如事務3把記錄刪除
delete from table where id = 1;

查詢操作

需滿足以下兩個條件的記錄才能被事務查詢出來：

• InnoDB只查找版本早于當前事務版本的數據行

• 行的刪除版本要么未定義，要么大于當前版本號，這可以確保事務讀取到的行，在事務未開始之前未被刪除

MVCC好處：減少鎖的爭用，提升性能

binlog

二進制文件概念及作用

二進制文件(binary log)記錄了對MySQL數據庫執行更改的所有操作(不包含SELECT、SHOW等，因為對數據沒有修改)

二進制文件主要幾種作用：

• 恢復：某些數據的恢復需要二進制日志

• 復制： 通過復制和執行二進制日志使一臺遠程的MySQL(slave)與另一臺MySQL數據庫(master)進行實時同步

• 審計： 用戶可以通過二進制日志中信息來進行審計，判斷是否有對數據庫進行注入的攻擊

二進制文件三個格式

MySQL 5.1開始引入binlog_format參數，該參數可設值有STATEMENT、ROW和MIX

• STATEMENT： 二進制文件記錄的是日志的邏輯SQL語句

• ROW：記錄表的行更改情況。如果設置了ROW模式，可以將InnoDB事務隔離級別設為READ_COMMITTED，以獲得更好的并發性

• MIX：MySQL默認采用STATEMENT格式進行二進制文件的記錄，但在一些情況下會使用ROW，可能的情況有：

• 表的存儲引擎為NDB，這時對表DML操作都以ROW格式進行

• 使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不確定函數

• 使用了INSERT DELAY語句

• 使用了用戶定義函數

• 使用了臨時表

redo log和二進制文件區別

(二進制文件用來進行POINT-IN-TIME(PIT))的恢復及主從復制環境的建立。

• 二進制文件會記錄所有與MySQL數據庫有關的日志記錄，包括InnoDB、MyISAM等其他存儲引擎的日志。而InnoDB存儲引擎的重做日志只記錄有關該存儲引擎本身的事務日志。

• 記錄的內容不同，無論用戶將二進制日志文件記錄的格式設為STATEMENT、ROW或MIXED，其記錄的都是關于一個事務的具體操作內容，即該日志是邏輯日志。而InnoDB存儲引擎的重做日志文件記錄的是關于每個頁的更改的物理情況。

• 此外，寫入的時間頁不同，二進制日志文件僅再事務提交前進行提交，即只寫磁盤一次，不論這時該事務多大。而在事務進行的過程中，卻不斷有重做日志條目(reod entry)被寫入到重做日志文件中。

group commit

若事務為非只讀事務，則每次事務提交時需要進行一次fsync操作，以此保證重做日志都已經寫入磁盤。但磁盤fsync性能有限，為提高磁盤fsync效率，當前數據庫都提供group commit功能，即一次可以刷新確保多個事務日志被寫入文件。

對InnoDB group commit，進行兩階段操作：

• 修改內存中事務對應的信息，并且將日志寫入重做日志緩沖

• 調用fsync將確保日志都從重做日志緩沖寫入磁盤

InnoDB1.2前，開啟二進制文件，group commit功能失效問題：

開啟二進制文件后，其步驟如下：
1）當事務提交時，InnoDB存儲引擎進行prepare操作
2）MySQL數據庫上層寫入二進制文件
3）InnoDB將日志寫入重做日志文件

a）修改內存中事務對應的信息，并將日志寫入重做日志緩沖b）調用fsync將確保日志都從重做日志緩沖寫入磁盤

其中在保證MySQL數據庫上層二進制文件的寫入順序，和InnoDB事務提交順序一致，MySQL內部使用了prepare_commit_mutex鎖，從而步驟3）中a）步不可以在其他事務執行步驟b）時進行，從而導致roup commit功能失效。

解決方案便是BLGC(Binary Log Group Commit)

MySQL 5.6 BLGC實現方式分為三個階段：

• Flush階段：將每個事務的二進制文件寫入內存

• Sync階段：將內存中的二進制刷新到磁盤，若隊列有多個事務，那么僅一次fsync操作就完成了二進制日志的寫入，這就是BLGC

• Commit階段：leader根據順序調用存儲引擎層事務提交，由于innodb本就支持group commit，所以解決了因為鎖 prepare_commit_mutex 而導致的group commit失效問題。

看完上述內容，你們對MySQL中redo log、undo log和binlog三者的區別是什么有進一步的了解嗎？如果還想了解更多知識或者相關內容，請關注億速云行業資訊頻道，感謝大家的支持。