MySQL實(shí)戰(zhàn)-2 日志系統(tǒng):一條更新語(yǔ)句是如何執(zhí)行的脯宿?

前一節(jié)已經(jīng)介紹了執(zhí)行過(guò)程中涉及的處理模塊。一條查詢語(yǔ)句的執(zhí)行過(guò)程是一般經(jīng)過(guò)連接器泉粉、分析器连霉、優(yōu)化器、執(zhí)行器等功能模塊嗡靡,最后到達(dá)存儲(chǔ)引擎跺撼。
那么,一條更新語(yǔ)句的執(zhí)行流程又是怎么樣的呢叽躯?
也許你可能經(jīng)常聽(tīng)到DBA同事說(shuō)财边,MySQL可以恢復(fù)到半個(gè)月內(nèi)任意一秒的狀態(tài),驚嘆的同時(shí)点骑,你是不是也會(huì)好奇酣难,這是怎么樣做到的呢?
我們還是從一個(gè)表的一條更新語(yǔ)句說(shuō)起黑滴,下面是這個(gè)表的創(chuàng)建語(yǔ)句憨募,這個(gè)表有一個(gè)主鍵ID和一個(gè)整型字段C:

mysql> create table T(ID int primary key, c int);

如果要將這ID=2這一行的值增加1,SQL語(yǔ)句就會(huì)這么寫:

mysql> update T set c=c+1 where ID=2;

前面我有跟你介紹過(guò)SQL語(yǔ)句基本的執(zhí)行鏈路袁辈。

MySQL 邏輯架構(gòu)圖

你想要執(zhí)行語(yǔ)句菜谣,就需要先連接數(shù)據(jù)庫(kù),這是連接器的工作。
前面我們說(shuō)過(guò)尾膊,在一個(gè)表更新的時(shí)候媳危,跟這個(gè)表有關(guān)的查詢緩存會(huì)失效,所以這條語(yǔ)句就會(huì)把表T上所有緩存結(jié)果都清空冈敛。這也就是我們一般不建議使用查詢緩存的原因待笑。
接下來(lái),分析器會(huì)通過(guò)詞法和語(yǔ)法解析知道這是一條更新語(yǔ)句抓谴。優(yōu)化器決定要使用ID這個(gè)索引暮蹂。然后,執(zhí)行器負(fù)責(zé)具體執(zhí)行癌压,找到這一行仰泻,然后更新。
與查詢流程不一樣的是滩届,更新流程還涉及兩個(gè)重要的日志模塊集侯,它們正是我們今天要討論的豬腳:redo log(重做日志)和binlog(歸檔日志)。如果接觸MySQL帜消,那這兩個(gè)詞肯定是繞不過(guò)的浅悉。
重要的日志模塊:redo log
MySQL如果沒(méi)一次更新都需要寫進(jìn)磁盤,然后磁盤也要找到對(duì)應(yīng)的那條記錄券犁,然后在更新术健,整個(gè)過(guò)程IO成本、查找成本很高粘衬。為了解決這個(gè)成本高的問(wèn)題荞估,MySQL就采用了WAL技術(shù)(Write-ahead logging),它的關(guān)鍵點(diǎn)就是先寫日志稚新,再寫磁盤勘伺,這樣就可以防止忙碌的時(shí)候,造成IO輸出過(guò)高褂删。
具體來(lái)說(shuō)飞醉,當(dāng)有一條記錄需要更新的時(shí)候,InnoDB引擎就會(huì)把記錄寫到redo log 里面屯阀,并更新到內(nèi)存缅帘,這個(gè)時(shí)候更新就完成了。同時(shí)难衰,InnoDB引擎會(huì)在適當(dāng)?shù)臅r(shí)候钦无,將這個(gè)操作記錄更新到磁盤里面,而這個(gè)更新往往是在系統(tǒng)比較空閑的時(shí)候做盖袭。
如果系統(tǒng)在空閑的時(shí)候失暂,可以等到寫redo log日志后彼宠,在寫到內(nèi)存磁盤。如果系統(tǒng)比較繁忙的時(shí)候弟塞,這時(shí)redo log 日志也寫滿的時(shí)候凭峡,這時(shí)只能暫停redo log 日志的寫入,將redo log 中的部分記錄更新到內(nèi)存磁盤中决记。然后清除已更新到內(nèi)存磁盤中的記錄想罕,騰出空間為新記錄。
與此類似,InnoDB的redo log 是固定大小的霉涨,比如可以配置為一組4個(gè)文件夾。每個(gè)文件的大小為1GB惭适,那么這個(gè)redo log 日志文件的總共可以記錄4GB的文件笙瑟。
從頭開(kāi)始寫,寫到末尾就又回到開(kāi)頭循環(huán)寫癞志,如下圖所示往枷。
redo log 寫日志流程

write pos 是當(dāng)前記錄的位置,一邊寫一邊后移凄杯,寫到第3號(hào)文件末尾后就回到0號(hào)文件開(kāi)頭错洁。checkpoint 是當(dāng)前要擦除的位置,也是往后推移并且循環(huán)的戒突,擦除記錄前要把記錄更新到數(shù)據(jù)文件屯碴。
write pos 和 checkpoint 之間的是redo log 日志文件中空著的部分,還可以用來(lái)記錄新的操作膊存。如果write pos 追上 checkpoint ,表示 redo log 已經(jīng)滿了导而,這時(shí)不能再執(zhí)行新的更新,得停下來(lái)先擦除一些記錄隔崎,把checkpoint推進(jìn)一下今艺。
有了redo log , InnoDB就可以保證即使數(shù)據(jù)庫(kù)發(fā)生異常重啟爵卒,之前提交的記錄都不會(huì)丟失虚缎,這個(gè)能力稱為crash-safe
要理解crash-safe這個(gè)概念钓株,例如賒賬將記錄記錄在了粉板上或者寫在了賬本上实牡,之后即便是掌柜忘記了,比如突然停業(yè)幾天轴合,恢復(fù)生意后依然可以通過(guò)賬本和粉板上的數(shù)據(jù)明確賒賬賬目铲掐。
重要的日志模塊:binlog
MySQL整體看來(lái),其實(shí)就有兩塊值桩,一塊是Server層摆霉,它主要做的是MySQL功能層面的事情;還有一層就是引擎層,負(fù)責(zé)存儲(chǔ)相關(guān)的具體事宜携栋。redo log 日志就是InnoDB引擎特有的日志搭盾,而Server層也有自己的日志,稱為binlog(歸檔日志) 婉支。

我想你肯定會(huì)問(wèn)鸯隅,為什么會(huì)有兩份日志呢?
因?yàn)樽铋_(kāi)始 MySQL 里并沒(méi)有 InnoDB 引擎向挖。MySQL 自帶的引擎是 MyISAM蝌以,但是 MyISAM 沒(méi)有 crash-safe 的能力,binlog 日志只能用于歸檔何之。而 InnoDB 是另一個(gè)公司以插件形式引入 MySQL 的跟畅,既然只依靠 binlog 是沒(méi)有 crash-safe 能力的,所以 InnoDB 使用另外一套日志系統(tǒng)——也就是 redo log 來(lái)實(shí)現(xiàn) crash-safe 能力溶推。
這兩種日志有以下三點(diǎn)不同徊件。

  • 1、 redo log 是 InnoDB 引擎特有的蒜危;binlog 是 MySQL 的 Server 層實(shí)現(xiàn)的虱痕,所有引擎都可以使用。
  • 2辐赞、redo log 是物理日志部翘,記錄的是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改”;binlog 是邏輯日志响委,記錄的是這個(gè)語(yǔ)句的原始邏輯略就,比如“給 ID=2 這一行的 c 字段加 1 ”乡范。
  • 3到忽、redo log 是循環(huán)寫的拘央,空間固定會(huì)被用完戈擒;binlog 是可以追加寫入的雷绢≈课粒“追加寫”是指 binlog 文件寫到一定大小后會(huì)切換到下一個(gè)印机,并不會(huì)覆蓋以前的日志藏否。
    有了對(duì)這兩個(gè)日志的概念性理解蛔翅,我們?cè)賮?lái)看執(zhí)行器和 InnoDB 引擎在執(zhí)行這個(gè)簡(jiǎn)單的 update 語(yǔ)句時(shí)的內(nèi)部流程敲茄。
  • 1、執(zhí)行器先找引擎取 ID=2 這一行山析。ID 是主鍵堰燎,引擎直接用樹搜索找到這一行。如果 ID=2 這一行所在的數(shù)據(jù)頁(yè)本來(lái)就在內(nèi)存中笋轨,就直接返回給執(zhí)行器秆剪;否則赊淑,需要先從磁盤讀入內(nèi)存,然后再返回仅讽。
  • 2陶缺、 執(zhí)行器拿到引擎給的行數(shù)據(jù),把這個(gè)值加上 1洁灵,比如原來(lái)是 N饱岸,現(xiàn)在就是 N+1,得到新的一行數(shù)據(jù)徽千,再調(diào)用引擎接口寫入這行新數(shù)據(jù)苫费。
  • 3、引擎將這行新數(shù)據(jù)更新到內(nèi)存中双抽,同時(shí)將這個(gè)更新操作記錄到 redo log 里面百框,此時(shí) redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了荠诬,隨時(shí)可以提交事務(wù)。
  • 4位仁、執(zhí)行器生成這個(gè)操作的 binlog柑贞,并把 binlog 寫入磁盤。
  • 5聂抢、執(zhí)行器調(diào)用引擎的提交事務(wù)接口钧嘶,引擎把剛剛寫入的 redo log 改成提(commit)狀態(tài),更新完成琳疏。
    這里我給出這個(gè) update 語(yǔ)句的執(zhí)行流程圖有决,圖中淺色框表示是在 InnoDB 內(nèi)部執(zhí)行的,深色框表示是在執(zhí)行器中執(zhí)行的空盼。
    Update 語(yǔ)句執(zhí)行流程

    兩階段提交
    為什么必須有“兩階段提交”呢书幕?這是為了讓兩份日志之間的邏輯一致。要說(shuō)明這個(gè)問(wèn)題揽趾,我們得從文章開(kāi)頭的那個(gè)問(wèn)題說(shuō)起:怎樣讓數(shù)據(jù)庫(kù)恢復(fù)到半個(gè)月內(nèi)任意一秒的狀態(tài)台汇?
    前面我們說(shuō)過(guò)了,binlog 會(huì)記錄所有的邏輯操作篱瞎,并且是采用“追加寫”的形式苟呐。如果你的 DBA 承諾說(shuō)半個(gè)月內(nèi)可以恢復(fù),那么備份系統(tǒng)中一定會(huì)保存最近半個(gè)月的所有binlog俐筋,同時(shí)系統(tǒng)會(huì)定期做整庫(kù)備份牵素。這里的“定期”取決于系統(tǒng)的重要性,可以是一天一備澄者,也可以是一周一備笆呆。 當(dāng)需要恢復(fù)到指定的某一秒時(shí)请琳,比如某天下午兩點(diǎn)發(fā)現(xiàn)中午十二點(diǎn)有一次誤刪表,需要找回?cái)?shù)據(jù)腰奋,那你可以這么做:
  • 首先单起,找到最近的一次全量備份,如果你運(yùn)氣好劣坊,可能就是昨天晚上的一個(gè)備份嘀倒,從這個(gè)備份恢復(fù)到臨時(shí)庫(kù);
  • 然后局冰,從備份的時(shí)間點(diǎn)開(kāi)始测蘑,將備份的 binlog 依次取出來(lái),重放到中午誤刪表之前的那個(gè)時(shí)刻康二。 這樣你的臨時(shí)庫(kù)就跟誤刪之前的線上庫(kù)一樣了碳胳,然后你可以把表數(shù)據(jù)從臨時(shí)庫(kù)取出來(lái),按需要恢復(fù)到線上庫(kù)去沫勿。
    好了挨约,說(shuō)完了數(shù)據(jù)恢復(fù)過(guò)程,我們回來(lái)說(shuō)說(shuō)产雹,為什么日志需要“兩階段提交”诫惭。這里不妨用反證法來(lái)進(jìn)行解釋。
    由于 redo log 和 binlog 是兩個(gè)獨(dú)立的邏輯蔓挖,如果不用兩階段提交夕土,要么就是先寫完 redo log 再寫 binlog,或者采用反過(guò)來(lái)的順序瘟判。我們看看這兩種方式會(huì)有什么問(wèn)題怨绣。
    仍然用前面的 update 語(yǔ)句來(lái)做例子。假設(shè)當(dāng)前 ID=2 的行拷获,字段 c 的值是 0篮撑,再假設(shè)執(zhí)行 update 語(yǔ)句過(guò)程中在寫完第一個(gè)日志后,第二個(gè)日志還沒(méi)有寫完期間發(fā)生了 crash匆瓜,會(huì)出現(xiàn)什么情況呢咽扇?
  • 1、先寫 redo log 后寫 binlog陕壹。假設(shè)在 redo log 寫完质欲,binlog 還沒(méi)有寫完的時(shí)候,MySQL 進(jìn)程異常重啟糠馆。由于我們前面說(shuō)過(guò)的嘶伟,redo log 寫完之后,系統(tǒng)即使崩潰又碌,仍然能夠把數(shù)據(jù)恢復(fù)回來(lái)九昧,所以恢復(fù)后這一行 c 的值是 1绊袋。 但是由于 binlog 沒(méi)寫完就 crash 了,這時(shí)候 binlog 里面就沒(méi)有記錄這個(gè)語(yǔ)句铸鹰。因此癌别,之后備份日志的時(shí)候,存起來(lái)的 binlog 里面就沒(méi)有這條語(yǔ)句蹋笼。 然后你會(huì)發(fā)現(xiàn)展姐,如果需要用這個(gè) binlog 來(lái)恢復(fù)臨時(shí)庫(kù)的話,由于這個(gè)語(yǔ)句的 binlog 丟失剖毯,這個(gè)臨時(shí)庫(kù)就會(huì)少了這一次更新圾笨,恢復(fù)出來(lái)的這一行 c 的值就是 0,與原庫(kù)的值不同逊谋。
  • 2擂达、先寫 binlog 后寫 redo log。如果在 binlog 寫完之后 crash胶滋,由于 redo log 還沒(méi)寫板鬓,崩潰恢復(fù)以后這個(gè)事務(wù)無(wú)效,所以這一行 c 的值是 0究恤。但是 binlog 里面已經(jīng)記錄了“把 c 從 0 改成 1”這個(gè)日志俭令。所以,在之后用 binlog 來(lái)恢復(fù)的時(shí)候就多了一個(gè)事務(wù)出來(lái)丁溅,恢復(fù)出來(lái)的這一行 c 的值就是 1唤蔗,與原庫(kù)的值不同探遵。
    可以看到窟赏,如果不使用“兩階段提交”,那么數(shù)據(jù)庫(kù)的狀態(tài)就有可能和用它的日志恢復(fù)出來(lái)的庫(kù)的狀態(tài)不一致箱季。 你可能會(huì)說(shuō)涯穷,這個(gè)概率是不是很低,平時(shí)也沒(méi)有什么動(dòng)不動(dòng)就需要恢復(fù)臨時(shí)庫(kù)的場(chǎng)景呀藏雏?
    其實(shí)不是的拷况,不只是誤操作后需要用這個(gè)過(guò)程來(lái)恢復(fù)數(shù)據(jù)。當(dāng)你需要擴(kuò)容的時(shí)候掘殴,也就是需要再多搭建一些備庫(kù)來(lái)增加系統(tǒng)的讀能力的時(shí)候赚瘦,現(xiàn)在常見(jiàn)的做法也是用全量備份加上應(yīng)用 binlog 來(lái)實(shí)現(xiàn)的,這個(gè)“不一致”就會(huì)導(dǎo)致你的線上出現(xiàn)主從數(shù)據(jù)庫(kù)不一致的情況奏寨。
    簡(jiǎn)單說(shuō)起意,redo log 和 binlog 都可以用于表示事務(wù)的提交狀態(tài),而兩階段提交就是讓這兩個(gè)狀態(tài)保持邏輯上的一致病瞳。
    小結(jié)
    MySQL 里面最重要的兩個(gè)日志揽咕,即物理日志 redo log 和邏輯日志 binlog悲酷。redo log 用于保證 crash-safe 能力innodb_flush_log_at_trx_commit 這個(gè)參數(shù)設(shè)置成 1 的時(shí)候亲善,表示每次事務(wù)的 redo log 都直接持久化到磁盤设易。這個(gè)參數(shù)我建議你設(shè)置成 1,這樣可以保證 MySQL 異常重啟之后數(shù)據(jù)不丟失蛹头。
    sync_binlog 這個(gè)參數(shù)設(shè)置成 1 的時(shí)候顿肺,表示每次事務(wù)的 binlog 都持久化到磁盤。這個(gè)參數(shù)我也建議你設(shè)置成 1掘而,這樣可以保證 MySQL 異常重啟之后 binlog 不丟失挟冠。
    我還跟你介紹了與 MySQL 日志系統(tǒng)密切相關(guān)的“兩階段提交”。兩階段提交是跨系統(tǒng)維持?jǐn)?shù)據(jù)邏輯一致性時(shí)常用的一個(gè)方案袍睡,即使你不做數(shù)據(jù)庫(kù)內(nèi)核開(kāi)發(fā)知染,日常開(kāi)發(fā)中也有可能會(huì)用到。
    問(wèn)題
    前面我說(shuō)到定期全量備份的周期“取決于系統(tǒng)重要性斑胜,有的是一天一備控淡,有的是一周一備”。那么在什么場(chǎng)景下止潘,一天一備會(huì)比一周一備更有優(yōu)勢(shì)呢掺炭?或者說(shuō),它影響了這個(gè)數(shù)據(jù)庫(kù)系統(tǒng)的哪個(gè)指標(biāo)凭戴?
    答案

1 prepare階段 2 寫binlog 3 commit
當(dāng)在2之前崩潰時(shí)
重啟恢復(fù):后發(fā)現(xiàn)沒(méi)有commit涧狮,回滾。備份恢復(fù):沒(méi)有binlog 么夫。 >>>一致
當(dāng)在3之前崩潰
重啟恢復(fù):雖沒(méi)有commit者冤,但滿足prepare和binlog完整,所以重啟后會(huì)自動(dòng)commit档痪。備份:有binlog. >>一致

binlog是可以關(guān)的涉枫,你如果有權(quán)限,可以set sql_log_bin=0關(guān)掉本線程的binlog日志腐螟。 所以只依賴binlog來(lái)恢復(fù)就靠不住愿汰。

崩潰恢復(fù)過(guò)程的話,可以接受“redolog prepare 并且binlog完整” 的情況
Crash-safe是崩潰恢復(fù)乐纸,就是原地滿血復(fù)活衬廷;binlog恢復(fù)是制造一個(gè)影分身(副本)

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市汽绢,隨后出現(xiàn)的幾起案子吗跋,更是在濱河造成了極大的恐慌,老刑警劉巖庶喜,帶你破解...
    沈念sama閱讀 211,817評(píng)論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件小腊,死亡現(xiàn)場(chǎng)離奇詭異救鲤,居然都是意外死亡,警方通過(guò)查閱死者的電腦和手機(jī)秩冈,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,329評(píng)論 3 385
  • 文/潘曉璐 我一進(jìn)店門本缠,熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái),“玉大人入问,你說(shuō)我怎么就攤上這事丹锹。” “怎么了芬失?”我有些...
    開(kāi)封第一講書人閱讀 157,354評(píng)論 0 348
  • 文/不壞的土叔 我叫張陵楣黍,是天一觀的道長(zhǎng)。 經(jīng)常有香客問(wèn)我棱烂,道長(zhǎng)租漂,這世上最難降的妖魔是什么? 我笑而不...
    開(kāi)封第一講書人閱讀 56,498評(píng)論 1 284
  • 正文 為了忘掉前任颊糜,我火速辦了婚禮哩治,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘衬鱼。我一直安慰自己业筏,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,600評(píng)論 6 386
  • 文/花漫 我一把揭開(kāi)白布鸟赫。 她就那樣靜靜地躺著蒜胖,像睡著了一般。 火紅的嫁衣襯著肌膚如雪抛蚤。 梳的紋絲不亂的頭發(fā)上台谢,一...
    開(kāi)封第一講書人閱讀 49,829評(píng)論 1 290
  • 那天,我揣著相機(jī)與錄音霉颠,去河邊找鬼对碌。 笑死荆虱,一個(gè)胖子當(dāng)著我的面吹牛蒿偎,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播怀读,決...
    沈念sama閱讀 38,979評(píng)論 3 408
  • 文/蒼蘭香墨 我猛地睜開(kāi)眼诉位,長(zhǎng)吁一口氣:“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼!你這毒婦竟也來(lái)了菜枷?” 一聲冷哼從身側(cè)響起苍糠,我...
    開(kāi)封第一講書人閱讀 37,722評(píng)論 0 266
  • 序言:老撾萬(wàn)榮一對(duì)情侶失蹤,失蹤者是張志新(化名)和其女友劉穎啤誊,沒(méi)想到半個(gè)月后岳瞭,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體拥娄,經(jīng)...
    沈念sama閱讀 44,189評(píng)論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,519評(píng)論 2 327
  • 正文 我和宋清朗相戀三年瞳筏,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了稚瘾。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 38,654評(píng)論 1 340
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡姚炕,死狀恐怖摊欠,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情柱宦,我是刑警寧澤些椒,帶...
    沈念sama閱讀 34,329評(píng)論 4 330
  • 正文 年R本政府宣布,位于F島的核電站掸刊,受9級(jí)特大地震影響免糕,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜忧侧,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,940評(píng)論 3 313
  • 文/蒙蒙 一说墨、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧苍柏,春花似錦尼斧、人聲如沸。這莊子的主人今日做“春日...
    開(kāi)封第一講書人閱讀 30,762評(píng)論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至熄捍,卻和暖如春烛恤,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背余耽。 一陣腳步聲響...
    開(kāi)封第一講書人閱讀 31,993評(píng)論 1 266
  • 我被黑心中介騙來(lái)泰國(guó)打工缚柏, 沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人碟贾。 一個(gè)月前我還...
    沈念sama閱讀 46,382評(píng)論 2 360
  • 正文 我出身青樓币喧,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親袱耽。 傳聞我的和親對(duì)象是個(gè)殘疾皇子杀餐,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,543評(píng)論 2 349

推薦閱讀更多精彩內(nèi)容