MySQL存儲(chǔ)引擎完成更新語句執(zhí)行的方法

假設(shè)我們有一條SQL語句是這樣的：

updatet_usersetname='月伴飛魚'whereid=1;

那么我們先想一下這條SQL語句是如何執(zhí)行的？

首先肯定是我們的系統(tǒng)通過一個(gè)數(shù)據(jù)庫連接發(fā)送到了MySQL上，然后肯定會(huì)經(jīng)過SQL接口、解析器、優(yōu)化器、執(zhí)行器幾個(gè)環(huán)節(jié)，解析SQL語句，生成執(zhí)行計(jì)劃，接著去由執(zhí)行器負(fù)責(zé)這個(gè)計(jì)劃的執(zhí)行，調(diào)用InnoDB存儲(chǔ)引擎的接口去執(zhí)行。

大致會(huì)走下圖的這個(gè)流程

我們就來探索一下這個(gè)存儲(chǔ)引擎里的架構(gòu)設(shè)計(jì)，以及如何基于存儲(chǔ)引擎完成一條更新語句的執(zhí)行

緩沖池

InnoDB存儲(chǔ)引擎中有一個(gè)非常重要的放在內(nèi)存里的組件，就是緩沖池（Buffer Pool），這里面會(huì)緩存很多的數(shù)據(jù)， 以便于以后在查詢的時(shí)候，萬一你要是內(nèi)存緩沖池里有數(shù)據(jù)，就可以不用去查磁盤了

所以當(dāng)我們的InnoDB存儲(chǔ) 引擎要執(zhí)行更新語句的時(shí)候 ，比如對“id=1”這一行數(shù)據(jù)，他其實(shí)會(huì)先將“id=1”這一行數(shù)據(jù)看看是否在緩沖池里，如果不在的 話，那么會(huì)直接從磁盤里加載到緩沖池里來，而且接著還會(huì)對這行記錄加獨(dú)占鎖。

因?yàn)槲覀兿胍幌?，在我們更新“id=1”這一行數(shù)據(jù)的時(shí)候，肯定是不允許別人同時(shí)更新的，所以必須要對這行記錄加 獨(dú)占鎖

undo日志文件

如何讓你更新的數(shù)據(jù)可以回滾？

接著下一步，假設(shè)“id=1”這行數(shù)據(jù)的name原來是“周星星”，現(xiàn)在我們要更新為“月伴飛魚”，那么此時(shí)我們得先 把要更新的原來的值“周星星”和“id=1”這些信息，寫入到undo日志文件中去。

數(shù)據(jù)庫中，如果我們執(zhí)行一個(gè)更新語句，要是他是在一個(gè)事務(wù)里的話，那么事 務(wù)提交之前我們都是可以對數(shù)據(jù)進(jìn)行回滾的，也就是把你更新為“月伴飛魚”的值回滾到之前的“周星星”去。

所以為了考慮到未來可能要回滾數(shù)據(jù)的需要，這里會(huì)把你更新前的值寫入undo日志文件，我們看下圖。

更新buffer pool中的緩存數(shù)據(jù)

這里所謂的更新內(nèi)存緩沖池里的數(shù)據(jù)，意思就是把內(nèi)存里的“id=1”這行數(shù)據(jù)的name字段修改為“月伴飛魚”

當(dāng)我們把要更新的那行記錄從磁盤文件加載到緩沖池，同時(shí)對他加鎖之后，而且還把更新前的舊值寫入undo日志文件 之后，我們就可以正式開始更新這行記錄了，更新的時(shí)候，先是會(huì)更新緩沖池中的記錄，此時(shí)這個(gè)數(shù)據(jù)就是臟數(shù)據(jù) 了。

那么為什么說此時(shí)這行數(shù)據(jù)就是臟數(shù)據(jù)了呢？

因?yàn)檫@個(gè)時(shí)候磁盤上“id=1”這行數(shù)據(jù)的name字段還是“周星星”，但是內(nèi)存里這行數(shù)據(jù)已經(jīng)被修改了，所以 就會(huì)叫他是臟數(shù)據(jù)。

redo log

接著我們來思考一個(gè)問題，按照上圖的說明，現(xiàn)在已經(jīng)把內(nèi)存里的數(shù)據(jù)進(jìn)行了修改，但是磁盤上的數(shù)據(jù)還沒修改

那么此時(shí)萬一MySQL所在的機(jī)器宕機(jī)了，必然會(huì)導(dǎo)致內(nèi)存里修改過的數(shù)據(jù)丟失，這可怎么辦呢？這個(gè)時(shí)候，就必須要把對內(nèi)存所做的修改寫入到一個(gè)Redo Log Buffer里去，這也是內(nèi)存里的一個(gè)緩沖區(qū)，是用來存 放redo日志的

所謂的redo日志，就是記錄下來你對數(shù)據(jù)做了什么修改，比如對“id=1這行記錄修改了name字段的值為“月伴飛魚”，這 就是一個(gè)日志。我們先看下圖

這個(gè)redo日志其實(shí)是用來在MySQL突然宕機(jī)的時(shí)候，用來恢復(fù)你更新過的數(shù)據(jù)的

提交事務(wù)的時(shí)候?qū)edo日志寫入磁盤中

接著我們想要提交一個(gè)事務(wù)了，此時(shí)就會(huì)根據(jù)一定的策略把redo日志從redo log buffer里刷入到磁盤文件里去。

此時(shí)這個(gè)策略是通過innodb_flush_log_at_trx_commit來配置的，他有幾個(gè)選項(xiàng)。當(dāng)這個(gè)參數(shù)的值為0的時(shí)候，那么你提交事務(wù)的時(shí)候，不會(huì)把redo log buffer里的數(shù)據(jù)刷入磁盤文件的，此時(shí)可能你都 提交事務(wù)了，結(jié)果mysql宕機(jī)了，然后此時(shí)內(nèi)存里的數(shù)據(jù)全部丟失。相當(dāng)于你提交事務(wù)成功了，但是由于MySQL突然宕機(jī)，導(dǎo)致內(nèi)存中的數(shù)據(jù)和redo日志都丟失了

當(dāng)這個(gè)參數(shù)的值為1的時(shí)候，你提交事務(wù)的時(shí)候，就必須把redo log從內(nèi)存刷入到磁盤文件里去，只要事務(wù)提交成功，那么redo log就 必然在磁盤里了

那么只要提交事務(wù)成功之后，redo日志一定在磁盤文件里，此時(shí)你肯定會(huì)有一條redo日志說了，“我此時(shí)對哪個(gè)數(shù)據(jù)做了一個(gè)什么修 改，比如name字段修改為月伴飛魚了”。

然后哪怕此時(shí)buffer pool中更新過的數(shù)據(jù)還沒刷新到磁盤里去，此時(shí)內(nèi)存里的數(shù)據(jù)是已經(jīng)更新過的“name=月伴飛魚”，然后磁盤上的數(shù) 據(jù)還是沒更新過的“name=周星星”。

此時(shí)如果說提交事務(wù)后處于上圖的狀態(tài)，然后mysql系統(tǒng)突然崩潰了，此時(shí)會(huì)如何？會(huì)丟失數(shù)據(jù)嗎？

肯定不會(huì)啊，因?yàn)殡m然內(nèi)存里的修改成name=月伴飛魚的數(shù)據(jù)會(huì)丟失，但是redo日志里已經(jīng)說了，對某某數(shù)據(jù)做了修改 name=月伴飛魚。

所以此時(shí)mysql重啟之后，他可以根據(jù)redo日志去恢復(fù)之前做過的修改

最后來看看，如果innodb_flush_log_at_trx_commit參數(shù)的值是2呢？

他的意思就是，提交事務(wù)的時(shí)候，把redo日志寫入磁盤文件對應(yīng)的os cache緩存里去，而不是直接進(jìn)入磁盤文件，可 能1秒后才會(huì)把os cache里的數(shù)據(jù)寫入到磁盤文件里去。

這種模式下，你提交事務(wù)之后，redo log可能僅僅停留在os cache內(nèi)存緩存里，沒實(shí)際進(jìn)入磁盤文件，萬一此時(shí)你要 是機(jī)器宕機(jī)了，那么os cache里的redo log就會(huì)丟失，同樣會(huì)讓你感覺提交事務(wù)了，結(jié)果數(shù)據(jù)丟了

三種redo日志刷盤策略到底選擇哪一種？

innodb_flush_log_at_trx_commit=0提交事務(wù)的時(shí)候，不會(huì)將內(nèi)存中的redo log刷入磁盤

優(yōu)點(diǎn)，純內(nèi)存操作速度快，缺點(diǎn)，redo日志沒有落地磁盤，如果提交事務(wù)的一瞬間，MySQL宕機(jī)，那么如果是修改數(shù)據(jù)，內(nèi)存數(shù)據(jù)沒了，磁盤也沒來的及更新，就丟失了本次修改操作。

innodb_flush_log_at_trx_commit=1，提交事務(wù)之前一定會(huì)將redo log 刷入磁盤

優(yōu)點(diǎn)，事務(wù)提交之前，事務(wù)操作log一定刷入磁盤，事務(wù)成功，磁盤一定有redo日志，如果事務(wù)提交成功，內(nèi)存修改，磁盤還沒有更新，完全可以讀取redo日志恢復(fù)數(shù)據(jù)。缺點(diǎn)，寫磁盤確實(shí)會(huì)消耗很多性能，如果是高并發(fā)，大量寫入，一定會(huì)影響寫入性能，吞吐量和處理時(shí)間都會(huì)影響到。

innodb_flush_log_at_trx_commit=2，將redo日志刷入OS cache，間隔可能一秒寫入磁盤。方案鑒于一和二方案之間。

優(yōu)點(diǎn)，利用OS cache去緩存部分日志，可以提高吞吐量，間隔時(shí)間，異步刷入磁盤。缺點(diǎn)，提交事務(wù)之后，可能redo日志還在cache中。此時(shí)，日志存在丟失的風(fēng)險(xiǎn)。

三種方案，第一種方案適用于，允許不重要的數(shù)據(jù)，但是大批量插入的場景，可能丟失，比如一些大批量的任務(wù)執(zhí)行日志上報(bào)的數(shù)據(jù)。

方案二適用于數(shù)據(jù)不可丟失的插入更新，比如訂單，用戶等核心數(shù)據(jù)。

方案三，適用于高并發(fā)插入，允許一定數(shù)據(jù)丟失，但是大部分可靠的場景，比如用戶行為日志，APP異常上報(bào)等。

一般建議redo日志刷盤策略設(shè)置為1，保證事務(wù)提交之后，數(shù)據(jù)絕對不能丟失，MySQL中這個(gè)參數(shù)默認(rèn)值為1

責(zé)任編輯人：CC