阿裡三面當場懵逼：MySQL執行更新語句時做了什麼?

1 事前

• 建立一個示例表

• 

• 插倆條資料

• 

• 更新一條資料

• 

2 SQL語句基本執行流程

同樣适用于更新語句。

1. 執行語句前，先通過連接配接器連接配接DB
2. 表上有更新時，此表有關查詢緩存就會失效，是以該語句就會把表中的所有緩存置空
3. 分析器通過詞法、文法解析，哦原來這是一條更新語句
4. 優化器決定使用id索引
5. 執行器負責具體執行，找到這一行，更新之

與查詢流程不同的是更新過程涉及兩個日志子產品：

• redo log（重做日志）
• binlog（歸檔日志）

3 redo log

賒賬或還賬時，一般有兩種做法：

• 直接拿出小本本，把這次賒的賬加上或扣掉
• 先在借條記下這次的賬，等晚上下班了，再把小本本翻出來核算

在日常 996 強度下，我們肯定選後者，因為前者太麻煩：

首先，得從一堆記錄找到這個人的賒賬記錄

找到了，再拿出電腦計算

最後，再将結果寫回小本本

是以還是先在借條記一下友善。若我沒有借條，每次記賬都拿出小本本，效率豈不是低死啦？

MySQL也面對這種問題：若每次更新操作都直接寫進磁盤，然後磁盤也要找到對應記錄，然後再更新，整個過程I/O成本、搜尋成本都很高。

何解？就采用我的借條思路。

借條和小本本的協作過程，就是MySQL的WAL（Write-Ahead Logging）。關鍵就在于：

先寫日志（先寫借條）

再寫磁盤（不忙時，寫小本本）

當一條記錄需要更新，InnoDB先把記錄寫到redo log（借條），并更新記憶體，更新就算完成了。

InnoDB在适當時，将操作記錄更新到磁盤，而這個更新往往是在系統比較空閑的時候做，這就像打烊以後掌櫃做的事。

若今天欠錢的不多，我可以忙完了再整理。若某天欠錢的多了，借條寫滿了，怎麼辦？

放下手中活兒，把借條一部分欠款記錄更新到賬本，然後把這些記錄從借條擦掉，為記新賬騰出空間。

類似的InnoDB的redo log固定大小，比如可配置為一組4個檔案，每個檔案的大小是1GB，那麼這塊“借條”總共就可以記錄4GB的操作。

從頭開始寫，寫到末尾就又回到開頭循環寫

write pos是目前記錄位置，一邊寫一邊後移，寫到第3号檔案末尾後回到0号檔案開頭

checkpoint是目前要擦除位置，往後推移并且循環，擦除記錄前要把記錄更新到資料檔案

write pos和checkpoint之間是“借條”上還空着的部分，用來記錄新操作。若write pos追上checkpoint，“借條”滿，不能再執行新的更新，得停下來先擦掉一些記錄，checkpoint推進。

redo log可以保證即使資料庫發生異常重新開機，不會丢失之前送出的記錄，這叫crash-safe。

隻要欠款記錄記在借條或寫在小本本上，之後即使我忘記了，比如突然停業，恢複生意後依然可以通過賬本和粉闆上的資料明确賒賬賬目。

4 binlog

• MySQL Server層，負責MySQL功能層面
• 引擎層，負責存儲相關
• 借條redo log是InnoDB引擎特有的日志，而Server層也有自己的日志，稱為binlog（歸檔日志）。

為什麼需要兩份日志？

最開始MySQL并無InnoDB，自帶的是MyISAM，沒有crash-safe能力，binlog隻能用于歸檔。

InnoDB是另一個公司以插件形式引入MySQL，隻依靠binlog沒有crash-safe，是以InnoDB使用另外一套日志系統redo log實作crash-safe能力。

日志對比

• redo log是InnoDB引擎特有；binlog是MySQL的Server層實作的，所有引擎都可以使用
• redo log是實體日志，記錄的是“在某個資料頁上做了什麼修改”；binlog是邏輯日志，記錄的是這個語句的原始邏輯，比如“給ID=2這一行的c字段加1 ”

redo log是循環寫的，空間固定會用完；binlog是可以追加寫入的。“追加寫”是指binlog檔案寫到一定大小後會切換到下一個，并不會覆寫以前的日志。

看執行器和InnoDB引擎在執行這個簡單的update語句時的内部流程。

執行器先找引擎取id=2這行。id是主鍵，引擎直接用b+樹搜尋。這一行所在資料頁本就在記憶體，則直接傳回給執行器；否則先從磁盤讀入記憶體，再傳回

執行器拿到引擎給的行資料，把這個值加1，得到新的一行資料，再調用引擎接口寫入這行新資料

引擎将這行新資料更新到記憶體，同時将更新操作記錄到redo log，此時redo log處prepare态。然後告知執行器執行完成，随時可以送出事務

執行器生成這個操作的binlog，并把binlog寫入磁盤

執行器調用引擎的送出事務接口，引擎把剛剛寫入的redo log改成送出（commit）狀态，更新完成

1. 淺色框表示是在InnoDB内部執行

   深色框表示是在執行器執行

最後三步看上去有點“繞”，将redo log的寫入拆成了兩個步驟：prepare和commit，這就是"兩階段送出"。

兩階段送出

為什麼必須有“兩階段送出”？

為了讓兩份日志之間的邏輯一緻。

思考怎樣讓資料庫恢複到半個月内任意一秒的狀态？

binlog會記錄所有的邏輯操作，并且采用“追加寫”。如果DBA承諾說半個月内可以恢複，那麼備份系統中一定會儲存最近半個月的所有binlog，同時系統會定期做整庫備份。

“定期”取決于系統的重要性，可以是一天一備，也可以是一周一備。

資料恢複過程

當需要恢複到指定秒時，比如某天下午兩點發現中午十二點有次誤删表，需要找回資料：

1. 找到最近的一次全量備份。若運氣好，可能就是昨天晚上備份的，從這個備份恢複到臨時庫

1. 從備份時間點開始，将備份的binlog依次取出來，重放到中午誤删表之前的那個時刻

這樣你的臨時庫就跟誤删之前的線上庫一樣了，然後你可以把表資料從臨時庫取出來，按需要恢複到線上庫去。

為什麼需要“兩階段送出”

由于redo log和binlog是兩個獨立的邏輯，如果不用兩階段送出，要麼就是先寫完redo log再寫binlog，或者反序。

假設目前ID=2的行，字段c的值是0，再假設執行update語句過程中，在寫完第一個日志後，第二個日志還沒有寫完期間發生crash？

先寫redo log後寫binlog

假設在redo log寫完，binlog還沒寫完，MySQL異常重新開機。redo log寫完後，系統即使崩潰，仍能把資料恢複，是以恢複後這一行c的值是1

但由于binlog沒寫完就crash，binlog裡沒有記錄這語句。是以，之後備份日志時，存的binlog裡沒有這條語句。

然後你會發現，如果需要用這binlog來恢複臨時庫，由于這語句的binlog丢失，臨時庫就會少這次更新，恢複出來的這一行c的值就是0，與原庫的值不同

先寫binlog後寫redo log

若在binlog寫完之後crash，由于redo log還沒寫，崩潰恢複以後這個事務無效，是以這一行c的值是0。但是binlog裡面已經記錄了“把c從0改成1”這個日志。是以，在之後用binlog來恢複的時候就多了一個事務出來，恢複出來的這一行c的值就是1，與原庫的值不同。

是以不使用“兩階段送出”，則DB狀态就有可能和用它的日志恢複出來的庫的狀态不一緻。

機率是不是很低，平時也沒有什麼動不動就需要恢複臨時庫的場景呀？不是的，不隻是誤操作後需要用這個過程來恢複資料。需要擴容時，需要再多搭建一些備庫來增加系統的讀能力的時候，現在常見的做法也是用全量備份加上應用binlog來實作的，這個“不一緻”就會導緻你的線上出現主從資料庫不一緻的情況。

redo log和binlog都可用于表示事務的送出狀态，而兩階段送出就是讓這兩個狀态保持邏輯上的一緻。

innodb_flush_log_at_trx_commit設成1，表示每次事務的redo log都直接持久化到磁盤。建議設成1，保證MySQL異常重新開機之後資料不丢失

sync_binlog這個參數設定成1的時候，表示每次事務的binlog都持久化到磁盤。建議設成1，保證MySQL異常重新開機之後binlog不丢失

參考

https://segmentfault.com/a/1190000023827696