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

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

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

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

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

大致會走下圖的這個流程

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

緩沖池

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

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

因為我們想一下，在我們更新“id=1”這一行數(shù)據(jù)的時候，肯定是不允許別人同時更新的，所以必須要對這行記錄加 獨占鎖

undo日志文件

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

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

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

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

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

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

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

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

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

redo log

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

責任編輯人：CC