前言

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

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

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

redo log 是什么呢?

• redo log 是重做日志。
• 它記錄了數(shù)據(jù)頁上的改動(dòng)。
• 它指事務(wù)中修改了的數(shù)據(jù)，將會(huì)備份存儲(chǔ)。
• 發(fā)生數(shù)據(jù)庫服務(wù)器宕機(jī)、或者臟頁未寫入磁盤，可以通過redo log恢復(fù)。
• 它是Innodb存儲(chǔ)引擎獨(dú)有的

為什么需要 redo log？

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

2. 什么是WAL技術(shù), 好處是什么.

• WAL，中文全稱是Write-Ahead Logging，它的關(guān)鍵點(diǎn)就是日志先寫內(nèi)存，再寫磁盤。MySQL執(zhí)行更新操作后，在真正把數(shù)據(jù)寫入到磁盤前，先記錄日志。
• 好處是不用每一次操作都實(shí)時(shí)把數(shù)據(jù)寫盤，就算crash后也可以通過redo log恢復(fù)，所以能夠?qū)崿F(xiàn)快速響應(yīng)SQL語句。

3. redo log的寫入方式

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

mysql每執(zhí)行一條DML語句，會(huì)先把記錄寫入redo log buffer，后續(xù)某個(gè)時(shí)間點(diǎn)再一次性將多個(gè)操作記錄寫到redo log file。這種先寫日志，再寫磁盤的技術(shù)，就是WAL。

在計(jì)算機(jī)操作系統(tǒng)中，用戶空間(user space)下的緩沖區(qū)數(shù)據(jù)，一般是無法直接寫入磁盤的，必須經(jīng)過操作系統(tǒng)內(nèi)核空間緩沖區(qū)(即OS Buffer)。

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

圖片

我們可以發(fā)現(xiàn)，redo log buffer寫入到redo log file，是經(jīng)過OS buffer中轉(zhuǎn)的。其實(shí)可以通過參數(shù)innodb_flush_log_at_trx_commit進(jìn)行配置，參數(shù)值含義如下：

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

4. redo log的執(zhí)行流程

我們來看下redo log的執(zhí)行流程，假設(shè)執(zhí)行的SQL如下：

update T set a =1 where id =666

圖片

Redo log的執(zhí)行流程

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

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

• 因?yàn)閞edo log每次更新操作完成后，就一定會(huì)寫入的，如果寫入失敗，說明此次操作失敗，事務(wù)也不可能提交。
• redo log內(nèi)部結(jié)構(gòu)是基于頁的，記錄了這個(gè)頁的字段值變化，只要crash后讀取redo log進(jìn)行重放，就可以恢復(fù)數(shù)據(jù)。

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

• bin log是歸檔日志，屬于MySQL Server層的日志。可以實(shí)現(xiàn)主從復(fù)制和數(shù)據(jù)恢復(fù)兩個(gè)作用。
• 當(dāng)需要恢復(fù)數(shù)據(jù)時(shí)，可以取出某個(gè)時(shí)間范圍內(nèi)的bin log進(jìn)行重放恢復(fù)。
• 但是binlog不可以做crash safe，因?yàn)閏rash之前，bin log可能沒有寫入完全MySQL就掛了。所以需要配合redo log才可以進(jìn)行crash safe。

7. binlog和redolog的不同點(diǎn)有哪些?

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

• 執(zhí)行器在優(yōu)化器選擇了索引后，會(huì)調(diào)用InnoDB讀接口，讀取要更新的行到內(nèi)存中
• 執(zhí)行SQL操作后，更新到內(nèi)存，然后寫redo log，寫bin log，此時(shí)即為完成。
• 后續(xù)InnoDB會(huì)在合適的時(shí)候把此次操作的結(jié)果寫回到磁盤。

9. 如果數(shù)據(jù)庫誤操作, 如何執(zhí)行數(shù)據(jù)恢復(fù)?

數(shù)據(jù)庫在某個(gè)時(shí)候誤操作，就可以找到距離誤操作最近的時(shí)間節(jié)點(diǎn)的bin log，重放到臨時(shí)數(shù)據(jù)庫里，然后選擇誤刪的數(shù)據(jù)節(jié)點(diǎn)，恢復(fù)到線上數(shù)據(jù)庫。

10. binlog日志三種格式

binlog日志有三種格式

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

Statement格式

每一條會(huì)修改數(shù)據(jù)的sql都會(huì)記錄在binlog中

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

Row格式

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

• 優(yōu)點(diǎn)：binlog中可以不記錄執(zhí)行的sql語句的上下文相關(guān)的信息，僅需要記錄那一條記錄被修改成什么了。所以rowlevel的日志內(nèi)容會(huì)非常清楚的記錄下每一行數(shù)據(jù)修改的細(xì)節(jié)。不會(huì)出現(xiàn)某些特定情況下的存儲(chǔ)過程、或function、或trigger的調(diào)用和觸發(fā)無法被正確復(fù)制的問題。
• 缺點(diǎn):可能會(huì)產(chǎn)生大量的日志內(nèi)容。

Mixed格式

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

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

其實(shí)所謂的兩階段就是把一個(gè)事務(wù)分成兩個(gè)階段來提交。

兩階段提交

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

1. redo log在寫入后，進(jìn)入prepare狀態(tài)
2. 執(zhí)行器寫入bin log
3. 進(jìn)入commit狀態(tài)，事務(wù)可以提交。

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

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

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

• 先寫redo log，crash后bin log備份恢復(fù)時(shí)少了一次更新，與當(dāng)前數(shù)據(jù)不一致。
• 先寫bin log，crash后，由于redo log沒寫入，事務(wù)無效，所以后續(xù)bin log備份恢復(fù)時(shí)，數(shù)據(jù)不一致。

13. binlog刷盤機(jī)制

所有未提交的事務(wù)產(chǎn)生的binlog，都會(huì)被先記錄到binlog的緩存中。等該事務(wù)提交時(shí)，再將緩存中的數(shù)據(jù)寫入binlog日志文件中。緩存的大小由參數(shù)binlog_chache_size控制。

binlog什么時(shí)候刷新到磁盤呢？由參數(shù)sync_binlog控制

• 當(dāng)sync_binlog為0時(shí)，表示MySQL不控制binlog的刷新，而是由系統(tǒng)自行判斷何時(shí)寫入磁盤。選這種策略，一旦操作系統(tǒng)宕機(jī)，緩存中的binlog就會(huì)丟失。
• sync_binlog為N時(shí)，每N個(gè)事務(wù)，才會(huì)將binlog寫入磁盤。。
• 當(dāng)sync_binlog為1時(shí)，則表示每次commit，都將binlog 寫入磁盤。

來看一個(gè)比較完整的流程圖吧：

圖片

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

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

15. 說說Redo log的記錄方式

redo log的大小是固定。它采用循環(huán)寫的方式記錄，當(dāng)寫到結(jié)尾時(shí)，會(huì)回到開頭循環(huán)寫日志。如下圖（圖片來源網(wǎng)絡(luò)）：

redo log 循環(huán)寫入

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

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

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