前期準(zhǔn)備

上一篇文章說完MySQL的事務(wù)和鎖了，這次來詳細(xì)介紹一下在MySQL中一條更新語句的詳細(xì)執(zhí)行流程 (本文無特殊說明均是采用Innodb存儲(chǔ)引擎)。

??首先創(chuàng)建一張表，然后插入三條數(shù)據(jù)：

CREATE TABLE T(
    ID int(11) NOT NULL AUTO_INCREMENT,
    c int(11) NOT NULL,
    PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='測(cè)試表';

INSERT INTO T(c) VALUES (1), (2), (3);

讓后執(zhí)行更新操作：

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

在說更新操作前，大家先來看一下sql語句在MySQL中的執(zhí)行流程??~

SQL語句的執(zhí)行過程

MySQL數(shù)據(jù)庫主要分為兩個(gè)層級(jí)：服務(wù)層和存儲(chǔ)引擎層

服務(wù)層：[server層包括]{.blue}：連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器，包括大多數(shù)MySQL中的核心功能所有跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)，包括 存儲(chǔ)過程、觸發(fā)器、視圖等。

[存儲(chǔ)引擎層]{.purple}：存儲(chǔ)引擎層包括MySQL常見的存儲(chǔ)引擎，包括MyISAM、InnoDB和Memory等，最常用的是InnoDB，也是現(xiàn)在MySQL的默認(rèn)存儲(chǔ)引擎。

server層中的組件介紹

連接器?： 需要MySQL客戶端登錄，需要一個(gè) 連接器 來連接用戶和MySQL數(shù)據(jù)庫，“mysql -u 用戶名 -p 密碼” 進(jìn)行MySQL登錄，在完成 TCP握手 后，連接器會(huì)根據(jù)輸入的用戶名和密碼驗(yàn)證登錄身份。

查詢緩存?： MySQL在得到一個(gè)執(zhí)行請(qǐng)求后，會(huì)首先去 查詢緩存 中查找，是否執(zhí)行過這條SQL語句，之前執(zhí)行過得語句以及結(jié)果會(huì)以 key-value對(duì)的形式，放在內(nèi)存中。key是查詢語句，value是查詢的結(jié)果。如果通過key能夠查找到這條SQL語句，直接返回SQL的執(zhí)行結(jié)果。若不存在緩存中，就會(huì)繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后，執(zhí)行結(jié)果就會(huì)被放入查詢緩存中。優(yōu)點(diǎn)是效率高。但是查詢緩存不建議使用， 因?yàn)樵贛ySQL中對(duì)某張表進(jìn)行了更新操作，那么所有的查詢緩存就會(huì)失效，對(duì)于更新頻繁的數(shù)據(jù)庫來說，查詢緩存的命中率很低。需要注意：在MySQL8.0版本，查詢緩存功能就刪除了，不存在查詢緩存的功能了

分析器?: 分為詞法分析和語法分析

• 詞法分析： 首先，MySQL會(huì)根據(jù)SQL語句進(jìn)行解析，分析器會(huì)先做 詞法分析，你寫的SQL就是由多個(gè)字符串和空格組成的一條SQL語句，MySQL需要識(shí)別出里面的字符串是什么，代表什么。
• 語法分析： 然后進(jìn)行 語法分析， 根據(jù)詞法分析的結(jié)果，語法分析器會(huì)根據(jù)語法規(guī)則，判斷輸入的這個(gè)SQL語句是否滿足MySQL語法。如果SQL語句不正確，就提示：You have an error in your SQL suntax

• 優(yōu)化器?： 經(jīng)過分析器分析后，SQL就合法了，但在執(zhí)行之前，還需要進(jìn)行優(yōu)化器的處理，優(yōu)化器會(huì)判斷使用了哪種索引，使用哪種連接，優(yōu)化器的作用 就是確定效率最高的執(zhí)行方案。

• 執(zhí)行器?： 在執(zhí)行階段，MySQL首先會(huì)判斷有沒有執(zhí)行語句的權(quán)限，若無權(quán)限，返回沒有權(quán)限的錯(cuò)誤；若有權(quán)限，就打開表繼續(xù)執(zhí)行。打開表時(shí)，執(zhí)行器會(huì)根據(jù)標(biāo)的引擎定義，去使用該引擎提供的接口，對(duì)于有索引的表，執(zhí)行的邏輯類似。

了解完SQL語句的執(zhí)行流程我們接下來詳細(xì)分析一下上面update T set c=c+1 where ID=2;是如何執(zhí)行的。

Update語句分析

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

??在執(zhí)行update更新操作的時(shí)候，跟這個(gè)表有關(guān)的查詢緩存會(huì)失效，所以這條語句就會(huì)把表 T 上所有緩存結(jié)果都清空。接下來，分析器會(huì)經(jīng)過語法分析和詞法分析，知道了這是一條更新語句后，優(yōu)化器決定要使用哪一個(gè)索引，然后執(zhí)行器負(fù)責(zé)具體的執(zhí)行，先找到這一行，然后做更新。

按照我們平常的思路，就是 找出這條記錄，把它的值改好，保存就OK了 。但我們追究一下細(xì)節(jié)，由于涉及到修改數(shù)據(jù)，所以涉及到日志了。更新操作涉及到兩個(gè)重要的日志模塊。redo log(重做日志)，bin log(歸檔日志)。MySQL中的這兩個(gè)日志也是必學(xué)的。

redo log(重做日志)

• 在 MySQL 里，如果每一次的更新操作都需要寫進(jìn)磁盤，然后磁盤也要找到對(duì)應(yīng)的那條記錄，然后再更新，整個(gè)過程 IO 成本、查找成本都很高。 MySQL里使用WAL(預(yù)寫式日志)技術(shù)，WAL 的全稱是 Write-Ahead Logging，它的關(guān)鍵點(diǎn)就是 先寫日志，再寫磁盤。
• 具體來說，當(dāng)有一條記錄需要更新的時(shí)候，InnoDB 引擎就會(huì)先把記錄寫到 redo log里面，并更新內(nèi)存，這個(gè)時(shí)候更新就算完成了。同時(shí)，InnoDB 引擎會(huì)在適當(dāng)?shù)臅r(shí)候，將這個(gè)操作記錄更新到磁盤里面，而這個(gè)更新往往是在系統(tǒng)比較空閑的時(shí)候做。
• InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個(gè)文件，每個(gè)文件的大小是 1GB，那么總共就可以記錄 4GB 的操作。從頭開始寫，寫到末尾就又回到開頭循環(huán)寫。

聽完上面對(duì)redo log日志的介紹后，小伙伴們可能會(huì)問：redo log日志存儲(chǔ)在哪？， 數(shù)據(jù)庫信息保存在磁盤上，redo log日志也保存在磁盤上，為什么要先寫到redo log中再寫到數(shù)據(jù)庫中呢？，redo log日志如果存滿數(shù)據(jù)了怎么辦？等等。接下來就解答一下這些疑問。

redo log存儲(chǔ)在哪里？

??InnoDB引擎先把記錄寫到redo log 中，redo log 在哪，它也是在磁盤上，這也是一個(gè)寫磁盤的過程， 但是與更新過程不一樣的是，更新過程是在磁盤上隨機(jī)IO，費(fèi)時(shí)。 而寫redo log 是在磁盤上順序IO。效率要高。

redo log 空間是固定，那它會(huì)不會(huì)用完呢？

??首先不用擔(dān)心 redo log 會(huì)用完空間，因?yàn)樗茄h(huán)利用的。例如 redo log 日志配置為一組4個(gè)文件，每個(gè)文件分別為1G。它寫的流程如下圖：

簡單總結(jié)一下: redo log日志是Innodb存儲(chǔ)引擎特有的機(jī)制，可以用來應(yīng)對(duì)異?；謴?fù)，Crash-safe，redo可以保證mysql異常重啟時(shí)，將未提交的事務(wù)回滾，已提交的事務(wù)安全落庫。

crash-safe: 有了 redo log，InnoDB 就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為crash-safe。

binlog(歸檔日志)

??redo log是innoDB 引擎特有的日志。而binlog是mysql server層的日志。

其實(shí)bin log日志出現(xiàn)的時(shí)間比redo log早，因?yàn)樽铋_始MySQL是沒有InnoDB存儲(chǔ)引擎的，5.5之前是MyISAM。但是 MyISAM 沒有 crash-safe 的能力，binlog 日志只能用于歸檔。而 InnoDB 是另一個(gè)公司以插件形式引入 MySQL 的，既然只依靠 binlog 是沒有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系統(tǒng)——也就是 redo log 來實(shí)現(xiàn) crash-safe 能力。

redo log和bin log的總結(jié)??:

• redo log是為了保證innoDB引擎的crash-safe能力，也就是說在mysql異常宕機(jī)重啟的時(shí)候，之前提交的事務(wù)可以保證不丟失；(因?yàn)槌晒μ峤坏氖聞?wù)肯定是寫入了redo log,可以從redo log恢復(fù))
• bin log是歸檔日志，將每個(gè)更新操作都追加到日志中。這樣當(dāng)需要將日志恢復(fù)到某個(gè)時(shí)間點(diǎn)的時(shí)候，就可以根據(jù)全量備份+bin log重放實(shí)現(xiàn)。 如果沒有開啟binlog,那么數(shù)據(jù)只能恢復(fù)到全量備份的時(shí)間點(diǎn)，而不能恢復(fù)到任意時(shí)間點(diǎn)。如果連全量備份也沒做，mysql宕機(jī)，磁盤也壞了，那就很尷尬了。。

redo log和bin log的區(qū)別??:

• redo log 是 InnoDB 引擎特有的；bin log 是 MySQL 的 Server 層實(shí)現(xiàn)的，所有引擎都可以使用。
• redo log 是物理日志，記錄的是“在某個(gè)數(shù)據(jù)頁上做了什么修改”；bin log 是邏輯日志，記錄的是這個(gè)語句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”。
• redo log 是循環(huán)寫的，空間固定會(huì)用完；binlog 是可以追加寫入的。“追加寫”是指 binlog 文件寫到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。

InnoDB引擎部分在執(zhí)行這個(gè)簡單的update語句的時(shí)候的內(nèi)部流程

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

??手動(dòng)用begin開啟事務(wù)，然后執(zhí)行update語句，再然后執(zhí)行commit語句，那上面的update更新流程之前 哪些是update語句執(zhí)行之后做的，哪些是commit語句執(zhí)行之后做的?

事實(shí)上??，redo log在內(nèi)存中有一個(gè)redo log buffer,binlog 也有一個(gè)binlog cache.所以在手動(dòng)開啟的事務(wù)中，你執(zhí)行sql語句，其實(shí)是寫到redo log buffer和binlog cache中去的(肯定不可能是直接寫磁盤日志，一個(gè)是性能差一個(gè)是回滾的時(shí)候不可能去回滾磁盤日志吧)，然后當(dāng)你執(zhí)行commit的時(shí)候，首先要將redo log的提交狀態(tài)游prepare改為commit狀態(tài)，然后就要把binlog cache刷新到binlog日志(可能也只是flush到操作系統(tǒng)的page cache，這個(gè)就看你的mysql配置)，redo log buffer刷新到redo log 日志(刷新時(shí)機(jī)也是可以配置的)。 如果你回滾的話，就只用把binlog cache和redo log buffer中的數(shù)據(jù)清除就行了。

??在update過程中，mysql突然宕機(jī)，會(huì)發(fā)生什么情況?

• 如果redolog寫入了，處于prepare狀態(tài)，binlog還沒寫入，那么宕機(jī)重啟后，redolog中的這個(gè)事務(wù)就直接回滾了。
• 如果redolog寫入了，binlog也寫入了，但redolog還沒有更新為commit狀態(tài)，那么宕機(jī)重啟以后，mysql會(huì)去檢查對(duì)應(yīng)事務(wù)在binlog中是否完整。如果是，就提交事務(wù)；如果不是，就回滾事務(wù)。 (redolog處于prepare狀態(tài)，binlog完整啟動(dòng)時(shí)就提交事務(wù)，為啥要這么設(shè)計(jì)? 主要是因?yàn)閎inlog寫入了，那么就會(huì)被從庫或者用這個(gè)binlog恢復(fù)出來的庫使用，為了數(shù)據(jù)一致性就采用了這個(gè)策略) redo log和binlog是通過xid這個(gè)字段關(guān)聯(lián)起來的。

以上就是詳解一條update語句是怎樣執(zhí)行原理解析的詳細(xì)內(nèi)容，更多關(guān)于update語句執(zhí)行原理的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！