MySQL中一條update語(yǔ)句是怎么執(zhí)行的

本篇內(nèi)容主要講解“MySQL中一條update語(yǔ)句是怎么執(zhí)行的”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL中一條update語(yǔ)句是怎么執(zhí)行的”吧!

前期準(zhǔn)備

??首先創(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;

在說(shuō)更新操作前，大家先來(lái)看一下sql語(yǔ)句在MySQL中的執(zhí)行流程~

SQL語(yǔ)句的執(zhí)行過(guò)程

如圖所示：MySQL數(shù)據(jù)庫(kù)主要分為兩個(gè)層級(jí)：服務(wù)層和存儲(chǔ)引擎層服務(wù)層：server層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器，包括大多數(shù)MySQL中的核心功能所有跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)，包括 存儲(chǔ)過(guò)程、觸發(fā)器、視圖等。 存儲(chǔ)引擎層：存儲(chǔ)引擎層包括MySQL常見(jiàn)的存儲(chǔ)引擎，包括MyISAM、InnoDB和Memory等，最常用的是InnoDB，也是現(xiàn)在MySQL的默認(rèn)存儲(chǔ)引擎。

server層中的組件介紹???

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

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

3. 分析器: 分為詞法分析和語(yǔ)法分析

1. 詞法分析： 首先，MySQL會(huì)根據(jù)SQL語(yǔ)句進(jìn)行解析，分析器會(huì)先做 詞法分析，你寫(xiě)的SQL就是由多個(gè)字符串和空格組成的一條SQL語(yǔ)句，MySQL需要識(shí)別出里面的字符串是什么，代表什么。

2. 語(yǔ)法分析： 然后進(jìn)行 語(yǔ)法分析， 根據(jù)詞法分析的結(jié)果，語(yǔ)法分析器會(huì)根據(jù)語(yǔ)法規(guī)則，判斷輸入的這個(gè)SQL語(yǔ)句是否滿足MySQL語(yǔ)法。如果SQL語(yǔ)句不正確，就提示：You have an error in your SQL suntax

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

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

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

Update語(yǔ)句分析

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

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

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

redo log(重做日志)

• 在 MySQL 里，如果每一次的更新操作都需要寫(xiě)進(jìn)磁盤，然后磁盤也要找到對(duì)應(yīng)的那條記錄，然后再更新，整個(gè)過(guò)程 IO 成本、查找成本都很高。
  MySQL里使用WAL(預(yù)寫(xiě)式日志)技術(shù)，WAL 的全稱是 Write-Ahead Logging，它的關(guān)鍵點(diǎn)就是 先寫(xiě)日志，再寫(xiě)磁盤。

• 具體來(lái)說(shuō)，當(dāng)有一條記錄需要更新的時(shí)候，InnoDB 引擎就會(huì)先把記錄寫(xiě)到 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 的操作。從頭開(kāi)始寫(xiě)，寫(xiě)到末尾就又回到開(kāi)頭循環(huán)寫(xiě)。

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

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

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

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

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

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

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

binlog(歸檔日志)

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

其實(shí)bin log日志出現(xiàn)的時(shí)間比redo log早，因?yàn)樽铋_(kāi)始MySQL是沒(méi)有InnoDB存儲(chǔ)引擎的，5.5之前是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 能力。

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

• redo log是為了保證innoDB引擎的crash-safe能力，也就是說(shuō)在mysql異常宕機(jī)重啟的時(shí)候，之前提交的事務(wù)可以保證不丟失；(因?yàn)槌晒μ峤坏氖聞?wù)肯定是寫(xiě)入了redo log,可以從redo log恢復(fù))

• bin log是歸檔日志，將每個(gè)更新操作都追加到日志中。這樣當(dāng)需要將日志恢復(fù)到某個(gè)時(shí)間點(diǎn)的時(shí)候，就可以根據(jù)全量備份+bin log重放實(shí)現(xiàn)。 如果沒(méi)有開(kāi)啟binlog,那么數(shù)據(jù)只能恢復(fù)到全量備份的時(shí)間點(diǎn)，而不能恢復(fù)到任意時(shí)間點(diǎn)。如果連全量備份也沒(méi)做，mysql宕機(jī)，磁盤也壞了，那就很尷尬了。。

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

• redo log 是 InnoDB 引擎特有的；bin log 是 MySQL 的 Server 層實(shí)現(xiàn)的，所有引擎都可以使用。

• redo log 是物理日志，記錄的是“在某個(gè)數(shù)據(jù)頁(yè)上做了什么修改”；bin log 是邏輯日志，記錄的是這個(gè)語(yǔ)句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ”。

• redo log 是循環(huán)寫(xiě)的，空間固定會(huì)用完；binlog 是可以追加寫(xiě)入的?！白芳訉?xiě)”是指 binlog 文件寫(xiě)到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。

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

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

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

事實(shí)上，redo log在內(nèi)存中有一個(gè)redo log buffer,binlog 也有一個(gè)binlog cache.所以在手動(dòng)開(kāi)啟的事務(wù)中，你執(zhí)行sql語(yǔ)句，其實(shí)是寫(xiě)到redo log buffer和binlog cache中去的(肯定不可能是直接寫(xiě)磁盤日志，一個(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過(guò)程中，mysql突然宕機(jī)，會(huì)發(fā)生什么情況?

1. 如果redolog寫(xiě)入了，處于prepare狀態(tài)，binlog還沒(méi)寫(xiě)入，那么宕機(jī)重啟后，redolog中的這個(gè)事務(wù)就直接回滾了。

2. 如果redolog寫(xiě)入了，binlog也寫(xiě)入了，但redolog還沒(méi)有更新為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寫(xiě)入了，那么就會(huì)被從庫(kù)或者用這個(gè)binlog恢復(fù)出來(lái)的庫(kù)使用，為了數(shù)據(jù)一致性就采用了這個(gè)策略)

redo log和binlog是通過(guò)xid這個(gè)字段關(guān)聯(lián)起來(lái)的

到此，相信大家對(duì)“MySQL中一條update語(yǔ)句是怎么執(zhí)行的”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！