前言

大家好，我是撿田螺的小男孩。最近行情越來越卷了，給大家整理了15道經典MySQL日志面試題，希望大家都能找到理想的offer

1. redo log是什么? 為什么需要redo log？
2. 什么是WAL技術, 好處是什么
3.  redo log的寫入方式
4. redo log的執行流程
5. redo log 為什么可以保證crash safe機制呢？
6. binlog的概念是什么, 起到什么作用, 可以保證crash-safe嗎?
7. binlog和redolog的不同點有哪些?
8. 執行器和innoDB在執行update語句時候的流程是什么樣的?
9. .如果數據庫誤操作, 如何執行數據恢復?
10. 說說binlog日志三種格式
11. 什么是MySQL兩階段提交, 為什么需要兩階段提交?
12. 如果不是兩階段提交, 先寫redo log和先寫bin log兩種情況各會遇到什么問題?
13. binlog刷盤機制
14. undo log 是什么？它有什么用
15. 說說redo log的記錄方式

1. redo log是什么? 為什么需要redo log？

redo log 是什么呢?

• redo log 是重做日志。
• 它記錄了數據頁上的改動。
• 它指事務中修改了的數據，將會備份存儲。
• 發生數據庫服務器宕機、或者臟頁未寫入磁盤，可以通過redo log恢復。
• 它是Innodb存儲引擎獨有的

為什么需要 redo log？

• redo log主要用于MySQL異常重啟后的一種數據恢復手段，確保了數據的一致性。
• 其實是為了配合MySQL的WAL機制。因為MySQL進行更新操作，為了能夠快速響應，所以采用了異步寫回磁盤的技術，寫入內存后就返回。但是這樣，會存在crash后內存數據丟失的隱患，而redo log具備crash safe的能力。

2. 什么是WAL技術, 好處是什么.

• WAL，中文全稱是Write-Ahead Logging，它的關鍵點就是日志先寫內存，再寫磁盤。MySQL執行更新操作后，在真正把數據寫入到磁盤前，先記錄日志。
• 好處是不用每一次操作都實時把數據寫盤，就算crash后也可以通過redo log恢復，所以能夠實現快速響應SQL語句。

3. redo log的寫入方式

redo log包括兩部分內容，分別是內存中的日志緩沖(redo log buffer)和磁盤上的日志文件(redo log file)。

mysql每執行一條DML語句，會先把記錄寫入redo log buffer，后續某個時間點再一次性將多個操作記錄寫到redo log file。這種先寫日志，再寫磁盤的技術，就是WAL。

在計算機操作系統中，用戶空間(user space)下的緩沖區數據，一般是無法直接寫入磁盤的，必須經過操作系統內核空間緩沖區(即OS Buffer)。

• 日志最開始會寫入位于存儲引擎Innodb的redo log buffer，這個是在用戶空間完成的。
• 然后再將日志保存到操作系統內核空間的緩沖區(OS buffer)中。
• 最后，通過系統調用fsync()，從OS buffer寫入到磁盤上的redo log file中，完成寫入操作。這個寫入磁盤的操作，就叫做刷盤。

圖片

我們可以發現，redo log buffer寫入到redo log file，是經過OS buffer中轉的。其實可以通過參數innodb_flush_log_at_trx_commit進行配置，參數值含義如下：

• 0：稱為延遲寫，事務提交時不會將redo log buffer中日志寫入到OS buffer，而是每秒寫入OS buffer并調用寫入到redo log file中。
• 1：稱為實時寫，實時刷”，事務每次提交都會將redo log buffer中的日志寫入OS buffer并保存到redo log file中。
• 2：稱為實時寫，延遲刷。每次事務提交寫入到OS buffer，然后是每秒將日志寫入到redo log file。

4. redo log的執行流程

我們來看下redo log的執行流程，假設執行的SQL如下：

update T set a =1 where id =666

圖片

Redo log的執行流程

1. MySQL客戶端將請求語句update T set a =1 where id =666，發往MySQL Server層。
2. MySQL Server 層接收到SQL請求后，對其進行分析、優化、執行等處理工作，將生成的SQL執行計劃發到InnoDb存儲引擎層執行。
3. InnoDb存儲引擎層將a修改為1的這個操作記錄到內存中。
4. 記錄到內存以后會修改redo log 的記錄，會在添加一行記錄，其內容是需要在哪個數據頁上做什么修改。
5. 此后，將事務的狀態設置為prepare ，說明已經準備好提交事務了。
6. 等到MySQL Server層處理完事務以后，會將事務的狀態設置為commit，也就是提交該事務。
7. 在收到事務提交的請求以后，redo log會把剛才寫入內存中的操作記錄寫入到磁盤中，從而完成整個日志的記錄過程。

5. redo log 為什么可以保證crash safe機制呢？

• 因為redo log每次更新操作完成后，就一定會寫入的，如果寫入失敗，說明此次操作失敗，事務也不可能提交。
• redo log內部結構是基于頁的，記錄了這個頁的字段值變化，只要crash后讀取redo log進行重放，就可以恢復數據。

6. binlog的概念是什么, 起到什么作用, 可以保證crash-safe嗎?

• bin log是歸檔日志，屬于MySQL Server層的日志?？梢詫崿F主從復制和數據恢復兩個作用。
• 當需要恢復數據時，可以取出某個時間范圍內的bin log進行重放恢復。
• 但是binlog不可以做crash safe，因為crash之前，bin log可能沒有寫入完全MySQL就掛了。所以需要配合redo log才可以進行crash safe。

7. binlog和redolog的不同點有哪些?

8. 執行器和innoDB在執行update語句時候的流程是什么樣的?

• 執行器在優化器選擇了索引后，會調用InnoDB讀接口，讀取要更新的行到內存中
• 執行SQL操作后，更新到內存，然后寫redo log，寫bin log，此時即為完成。
• 后續InnoDB會在合適的時候把此次操作的結果寫回到磁盤。

9. 如果數據庫誤操作, 如何執行數據恢復?

數據庫在某個時候誤操作，就可以找到距離誤操作最近的時間節點的bin log，重放到臨時數據庫里，然后選擇誤刪的數據節點，恢復到線上數據庫。

10. binlog日志三種格式

binlog日志有三種格式

• Statement：基于SQL語句的復制((statement-based replication,SBR))
• Row:基于行的復制。(row-based replication,RBR)
• Mixed:混合模式復制。(mixed-based replication,MBR)

Statement格式

每一條會修改數據的sql都會記錄在binlog中

• 優點：不需要記錄每一行的變化，減少了binlog日志量，節約了IO，提高性能。
• 缺點：由于記錄的只是執行語句，為了這些語句能在備庫上正確運行，還必須記錄每條語句在執行的時候的一些相關信息，以保證所有語句能在備庫得到和在主庫端執行時候相同的結果。

Row格式

不記錄sql語句上下文相關信息，僅保存哪條記錄被修改。

• 優點：binlog中可以不記錄執行的sql語句的上下文相關的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內容會非常清楚的記錄下每一行數據修改的細節。不會出現某些特定情況下的存儲過程、或function、或trigger的調用和觸發無法被正確復制的問題。
• 缺點:可能會產生大量的日志內容。

Mixed格式

實際上就是Statement與Row的結合。一般的語句修改使用statment格式保存binlog，如一些函數，statement無法完成主從復制的操作，則采用row格式保存binlog,MySQL會根據執行的每一條具體的sql語句來區分對待記錄的日志形式

11. 什么是MySQL兩階段提交, 為什么需要兩階段提交?

其實所謂的兩階段就是把一個事務分成兩個階段來提交。

兩階段提交

兩階段提交主要有三步曲：

1. redo log在寫入后，進入prepare狀態
2. 執行器寫入bin log
3. 進入commit狀態，事務可以提交。

為什么需要兩階段提交呢?

• 如果不用兩階段提交的話，可能會出現這樣情況：bin log寫入之前，機器crash導致需要重啟。重啟后redo log繼續重放crash之前的操作，而當bin log后續需要作為備份恢復時，會出現數據不一致的情況。
• 如果是bin log commit之前crash，那么重啟后，發現redo log是prepare狀態且bin log完整（bin log寫入成功后，redo log會有bin log的標記），就會自動commit，讓存儲引擎提交事務。
• 兩階段提交就是為了保證redo log和binlog數據的安全一致性。只有在這兩個日志文件邏輯上高度一致了。你才能放心的使用redo log幫你將數據庫中的狀態恢復成crash之前的狀態，使用binlog實現數據備份、恢復、以及主從復制。

12. 如果不是兩階段提交, 先寫redo log和先寫bin log兩種情況各會遇到什么問題?

• 先寫redo log，crash后bin log備份恢復時少了一次更新，與當前數據不一致。
• 先寫bin log，crash后，由于redo log沒寫入，事務無效，所以后續bin log備份恢復時，數據不一致。

13. binlog刷盤機制

所有未提交的事務產生的binlog，都會被先記錄到binlog的緩存中。等該事務提交時，再將緩存中的數據寫入binlog日志文件中。緩存的大小由參數binlog_chache_size控制。

binlog什么時候刷新到磁盤呢？由參數sync_binlog控制

• 當sync_binlog為0時，表示MySQL不控制binlog的刷新，而是由系統自行判斷何時寫入磁盤。選這種策略，一旦操作系統宕機，緩存中的binlog就會丟失。
• sync_binlog為N時，每N個事務，才會將binlog寫入磁盤。。
• 當sync_binlog為1時，則表示每次commit，都將binlog 寫入磁盤。

來看一個比較完整的流程圖吧：

圖片

14.undo log 是什么？它有什么用

• undo log 叫做回滾日志，用于記錄數據被修改前的信息。
• 它跟redo log重做日志所記錄的相反，重做日志記錄數據被修改后的信息。undo log主要記錄的是數據的邏輯變化，為了在發生錯誤時回滾之前的操作，需要將之前的操作都記錄下來，這樣發生錯誤時才可以回滾。

15. 說說Redo log的記錄方式

redo log的大小是固定。它采用循環寫的方式記錄，當寫到結尾時，會回到開頭循環寫日志。如下圖（圖片來源網絡）：

redo log 循環寫入

redo log buffer(內存中)是由首尾相連的四個文件組成的，它們分別是：ib_logfile_1、ib_logfile_2、ib_logfile_3、ib_logfile_4。

• write pos表示當前寫入記錄位置(寫入磁盤的數據頁的邏輯序列位置)
• check point表示刷盤(寫入磁盤)后對應的位置。
• write pos到check point之間的部分用來記錄新日志，也就是留給新記錄的空間。
• check point到write pos之間是待刷盤的記錄，如果不刷盤會被新記錄覆蓋。

有了 redo log，當數據庫發生宕機重啟后，可通過 redo log將未落盤的數據（check point之后的數據）恢復，保證已經提交的事務記錄不會丟失，這種能力稱為crash-safe。