原文：MySQL系統(tǒng)學(xué)習(xí)（02）：日志模塊，一條SQL更新語句是如何執(zhí)行的
前言
在上一篇學(xué)習(xí)筆記MySQL系統(tǒng)學(xué)習(xí)（01）：基礎(chǔ)架構(gòu)，一條SQL查詢語句是如何執(zhí)行的我們系統(tǒng)的了解了一個查詢的SQL語句的執(zhí)行流程，并介紹了查詢過程中涉及到的處理模塊，一般查詢語句的執(zhí)行過程會包含：連接器、分析器、優(yōu)化器、執(zhí)行器等功能模塊，最后到達(dá)存儲引擎。
那么，一條更新語句的執(zhí)行流程又是怎么的？跟查詢語句有什么區(qū)別？
之前有看到過MySQL可以恢復(fù)到半個月內(nèi)任一一秒的狀態(tài)。如果你沒有聽過這句話，那你有了解過自己公司的MySQL數(shù)據(jù)同步方案嗎？我以前的公司有兩個機(jī)房，一般我們的真是操作只會發(fā)生在其中一個機(jī)房（具體發(fā)生在哪個機(jī)房，這個跟業(yè)務(wù)層的請求分發(fā)有關(guān)），雙機(jī)房的數(shù)據(jù)同步有跨機(jī)房同步方案，仔細(xì)了解下到是容易理解，但是同機(jī)房內(nèi)MySQL集群間數(shù)據(jù)是怎么做同步的？一直比較好奇。
我們還是從一張表的一條更新語句說起，下面有一個非常簡單的創(chuàng)建語句，這個表有一個主鍵ID，和整形字段c:
mysql> create table T(
`id` int(11) unsigned primary key,
`c` int
);
如果要將 ID=2 這一行的值加 1，SQL 語句就會這么寫：
mysql> update T set c = c+1 where id = 2;
上篇文章中有介紹過SQL語句的執(zhí)行鏈路，這里再把這張圖拿過來，我們可以先簡單的看看這張圖回顧下。首先，可以準(zhǔn)確的說查詢語句的那一套執(zhí)行流程，更新語句也同樣會執(zhí)行一遍。
執(zhí)行語句前第一步仍然是需要先建立數(shù)據(jù)庫連接，這是連接器的工作。
上篇文章中提到過，一個表如果有更新操作的話，這張表的所有緩存都會失效，所以這條語句會將這張表上的所有緩存結(jié)果都清空。這也是一般不建議使用查詢緩存的原因。
接下來，分析器會通過“詞法分析”和“語法分析”解析知道這還是一條更新語句。
優(yōu)化器決定要使用ID這個索引。
執(zhí)行器負(fù)責(zé)具體執(zhí)行，找到這一行，然后更新。
大體來看與查詢流程幾乎一樣。但是不一樣的是，更新流程還涉及到兩個重要的日志模塊。這也正式我今天學(xué)習(xí)的主要內(nèi)容，redo log（重做日志）和 binlog（歸檔日志）。如果接觸MySQL，那這兩個詞肯定是繞不開的。不過話說回來，redo log和binlog在設(shè)計上有很多有意思的地方，如果靜下心去了解一下，這些可能也能應(yīng)用到我們自己的程序中來。
重要日志模塊：redo log
小時候家里開過“小賣部”，等同與今天的便利店。家里會有一個賬本，村里人來買東西，有的人會賒賬（在農(nóng)村商店賒賬很常見，因?yàn)槎际亲筻徲疑幔蠹叶己苁煜ぃ?，專門用來記賬用的。一開始賒賬的人不多，就靠人腦記憶（聽起來有點(diǎn)高端哈，就是記憶），但是當(dāng)賒賬的人多了，光靠記憶是記不住的，或則會記混亂，而且有的人是在我爸、我媽手上賒的賬，有的人是在我手上賒的，信息做不到同步，容易丟、容易混。后面賒賬的人變多了，這才有了賬本這個東西。
如果有人賒賬或者還賬的話，我一般的做法有兩種：
（1）一種做法是把賬本拿出來，記上去或者劃掉
（2）另一種做法是現(xiàn)在腦子里記著，等晚上了關(guān)門以后在把賬本拿出來核算。
在生意紅火的時候，我一般會選擇后者（有人說我缺心眼！例子覺得有點(diǎn)不合適，體諒下，就當(dāng)我是選擇后者吧）。你想想下，那么多人買東西，我每個都直接翻賬本的話，首先我要找到這個人賒賬總額那條記錄。密密麻麻十幾頁，找到還得在盤算，那其他排隊的客人豈不是早都不耐煩了嗎。 效率太低，會影響生意。
同樣，在MySQL里也有這個問題，如果每一次的更新操作的日志都需要寫進(jìn)磁盤，然后磁盤也要找到對應(yīng)的那條記錄，然后在更新，整個過程的IO成本、查找成本都是相當(dāng)?shù)母摺榱私鉀Q這個問題，MySQL的設(shè)計者就利用我對待小賣部記賬的第二種方式來解決這個問題。
而記憶力和賬本配合的整個過程，其實(shí)就是MySQL里面經(jīng)常說到的WAL技術(shù)，WAL全稱是Write-Ahead Logging，它的關(guān)鍵點(diǎn)就是先寫日志，在寫磁盤（類似于我先記到腦子里，等到不忙的時候在一塊更新到賬本上）。
具體來說，當(dāng)有一條記錄需要更新的時候，InnoDB引擎會先把記錄寫到redo log里面，并更新內(nèi)存，這個時候更新就算完成了。同時，InnoDB引擎會在適當(dāng)?shù)臅r候，將這個更新操作記錄更新到磁盤里面，而這個更新往往是在系統(tǒng)比較空閑的時候做，就像晚上停業(yè)后我在將記憶里的賬目更新到賬本上一樣。
如果今天賒賬不多，我可以等晚上停業(yè)后在更新賬本。但是如果今天賒賬非常多的話，我可能一個上午腦子里面就亂了，記不住了怎么辦？這個時候我會聽下手中的活，先把更早的賒賬記錄同步到賬目，只需要記住最近幾筆賒賬記錄，晚點(diǎn)在更新。
于此類似，InnoDB的redo log的大小是固定的，比如可以配置為一組是4個文件，每個文件大小是1GB，那么這個redo log最多可以記錄4GB的更新記錄。從開頭開始記錄，一直記到末尾，記錄滿了，然后有循環(huán)重復(fù)繼續(xù)記錄（記錄慢了還怎么記？忘記上面我們提到的記錄不下的時候會放下手中的活，把最早的一部分賬先同步到磁盤了嗎）。
有了redo log，InnoDB就能保證及時數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會丟失，這個能力成為crash-safe
重要日志模塊binlog
上面MYSQL基礎(chǔ)架構(gòu)圖里面提到了MySQL從整體來看，其實(shí)就只有兩塊：Server層，它主要負(fù)責(zé)MySQL功能層面的事情；存儲引擎層，負(fù)責(zé)存儲相關(guān)的具體事宜。上面我們提到redo log是存儲引擎特有的日志，而server層也有自己的日志，稱為binlog（歸檔日志）
是不是比較好奇會為什么會有兩份日志，有這個必要嗎？
因?yàn)樽铋_始MySQL里面并沒有InnoDB引擎。MySQL自帶的引擎是MyISAM，但是MyISAM沒有crash-safe的能力，binlog日志只能用于歸檔。其實(shí)InnoDB是由另外一個公司已插件的形式引入MySQL的，也是因?yàn)殚_始的MySQL沒有crash-safe的能力，所以InnoDB才引入了redo log來實(shí)現(xiàn)這一能力。
這兩個日志主要有3個不同點(diǎn)：
1.redo log是InnoDB引擎特有的；binlog是MySQL的Server層實(shí)現(xiàn)的，所有引擎都可以使用。
2.redo log是物理日志，記錄的是“在某個數(shù)據(jù)頁上做了什么修改”；binlog是邏輯日志，記錄的是這個語句的原始邏輯，比如“給ID=2這一行的c字段加1”
3.redo log是循環(huán)寫的，空間固定會用完；binlog可以追加寫入（這里的追加寫入指當(dāng)binlog的日志文件寫到一定程度，會自動切的下一個文件，不會直接覆蓋）。
有了對兩個log文件的概念性理解，我們再來看執(zhí)行器和InnoDB引擎在執(zhí)行這個簡單的update語句時的內(nèi)流程。
1.執(zhí)行器先找引擎取ID=2這一行記錄。ID是主鍵，引擎直接通過數(shù)搜索找到這一行。如果ID=2這一行數(shù)據(jù)本身就在內(nèi)存中，就直接返回給執(zhí)行器；否則需要先從磁盤讀入內(nèi)存，在返回。
2.執(zhí)行器拿到引擎給的行數(shù)據(jù)，把c這個值加1，得到新的一行數(shù)據(jù)，然后在調(diào)用引擎接口，寫入這行新數(shù)據(jù)。
3.引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時將這個更新操作記錄到redo log里面，此時redo log處于準(zhǔn)備就緒（prepare）狀態(tài)。然后告知執(zhí)行器執(zhí)行完成，隨時可以提交事務(wù)。
4.執(zhí)行器生產(chǎn)這個操作的binlog，并吧binlog寫入磁盤。
5.執(zhí)行器調(diào)用引擎的事務(wù)提交接口，引擎把剛剛的redo log改成提交（commit）狀態(tài)，更新完成。
這里我又畫了一個這個update語句的執(zhí)行流程圖，圖中淺色框表示在InnoDB中執(zhí)行的，深色框標(biāo)識在執(zhí)行器執(zhí)行的。
看上面流程圖，可能你會注意到最后3不有點(diǎn)繞，將redo log的寫入拆分成了倆個步驟：prepare和commit，這就是“兩階段提交”
兩階段提交
為什么要有“兩階段提交”呢？這是為了讓兩份日志之間的邏輯一致。要說明這個問題，我們要從文章開頭的那個問題說起：怎樣讓數(shù)據(jù)庫回到半月內(nèi)的任一一秒的狀態(tài)？或者說同機(jī)房數(shù)據(jù)如何同步？
前面我提到了，binlog會記錄所有的邏輯操作，并且是采用“追加寫”的形式。如果你的DBA承諾半個月內(nèi)的可以恢復(fù)，那么備份系統(tǒng)中一定會保留半個月內(nèi)的所有binlog，同時系統(tǒng)會定期做整庫備份。這里的定期取決于你的業(yè)務(wù)系統(tǒng)的重要性/或者說數(shù)據(jù)的重要性，可以一天一備份，也可以一周一備份。
當(dāng)需要恢復(fù)到指定的某一秒時，比如某天下午兩點(diǎn)發(fā)現(xiàn)上午10點(diǎn)有一次誤刪表的操作，需要找回數(shù)據(jù)你可以這么做（一般這個恢復(fù)的操作都是由DBA操作的，業(yè)務(wù)開發(fā)人員根本沒有這個權(quán)限，但是可以了解下）
1.首先，找到最近一次的全量備份，如果你運(yùn)氣好，可能就是昨天晚上的一個備份，從這個備份恢復(fù)到臨時庫。
2.然后，從備份的時間點(diǎn)開始，將備份的binlog依次取出來，重放到上午誤刪表的那個時刻。
這樣你的臨時庫就跟誤刪之前的線上庫一樣了，然后你可以把表數(shù)據(jù)從臨時表中取出來，按需恢復(fù)到線上庫去。
好了，說完了數(shù)據(jù)恢復(fù)過程，我們在回頭來看為什么要有“兩階段提交”。這個可以反證法來解釋一下：
由于redo log和bin log是兩個獨(dú)立的邏輯，如果不用“兩階段提交”，那么就要先寫完redo log再寫binlog，或者采用反過來的順序。我們看看這兩種會有什么問題？
1.先寫redo log后寫binlog。假設(shè)在redolog寫完，binlog還沒寫的時候MYSQL進(jìn)程異常重啟。我們前面說過，redo log寫完后，系統(tǒng)即使崩潰，仍然能夠把數(shù)據(jù)恢復(fù)，所以恢復(fù)后的這一行c值為1。但是由于binlog還沒有寫完就崩潰了（crash），這時候binlog里面還沒有這條語句。因此之后備份日志的時候，存起來的binlog里面就沒有這條語句。然后我們會發(fā)現(xiàn)，如果需要用這個binlog來恢復(fù)臨時庫的話，由于這條語句的binlog丟失，這個臨時庫就會少了這一次更新，恢復(fù)出來的這一行c值就是0，與原庫的值不同。
2.先寫binlog后寫redo log。如果在binlog寫完之后crash，由于redo log還沒寫，崩潰恢復(fù)以后這個事務(wù)無效，所以這一行c值肯定是0.但是binlog里面已經(jīng)記錄“c值由0變成1”的這條記錄。所以之后用binlog恢復(fù)出來的數(shù)據(jù)就多了一個事務(wù)出來，原值應(yīng)該是0的。
可以看到，如果不使用“兩階段提交”，那么數(shù)據(jù)庫的狀態(tài)就有可能跟用它日志恢復(fù)出來的數(shù)據(jù)不一致，這樣是有問題的。
可能會覺得這個概率是不是很低，平時也沒有什么動不動恢復(fù)臨時庫的場景？
其實(shí)不是的，比如我一開是提到的第二個疑惑，同機(jī)房數(shù)據(jù)是怎么同步的，其實(shí)也是要依賴binlog的。包括MySQL集群在做擴(kuò)容哦時候，需要數(shù)據(jù)的同步也是一樣的道理。
簡單說，redo log和binlog都可以用來表示事務(wù)的提交狀態(tài)，而“兩階段提交”就是為了讓這兩個狀態(tài)保持邏輯上的一致。
總結(jié)
這篇學(xué)習(xí)筆記中首先我簡單的介紹了一條更新SQL的執(zhí)行流程，與查詢SQL類似，通過不一樣的地方引出記錄MySQL操作的兩個非常重要的日志，即物理日志redo log和邏輯日志binlog。
redo log用于保證crash-safe能力。innodb_log_at_trx_commit這個參數(shù)的值設(shè)置為1的時候，表示每次事務(wù)的redo log都持久化到磁盤。這個參數(shù)我建議設(shè)置為1，因?yàn)檫@樣可以MySQL異常重啟后數(shù)據(jù)不丟失。
sync_binlog這個參數(shù)設(shè)置為1的時候，表示每次事務(wù)的binlog都持久化到磁盤，同理，這個我也建議設(shè)置為1.
最后介紹了為什么要有“兩階段提交”，他的作用是為了保證redo log和bin log的事務(wù)提交狀態(tài)在邏輯上保持一致。
我的更多興趣分享：[個人網(wǎng)站RelaxHeart網(wǎng) - TEC博客](http://www.relaxheart.cn/to/master/blog
