MySQL中update語句的執(zhí)行過程是怎么樣的

這篇文章主要為大家展示了“MySQL中update語句的執(zhí)行過程是怎么樣的”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“MySQL中update語句的執(zhí)行過程是怎么樣的”這篇文章吧。

前期準備

首先創(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='測試表';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ù)庫主要分為兩個層級：服務(wù)層和存儲引擎層服務(wù)層：server層包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器，包括大多數(shù)MySQL中的核心功能所有跨存儲引擎的功能也在這一層實現(xiàn)，包括 存儲過程、觸發(fā)器、視圖等。 存儲引擎層：存儲引擎層包括MySQL常見的存儲引擎，包括MyISAM、InnoDB和Memory等，最常用的是InnoDB，也是現(xiàn)在MySQL的默認存儲引擎。

server層中的組件介紹

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

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

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

  
  

• 詞法分析： 首先，MySQL會根據(jù)SQL語句進行解析，分析器會先做 詞法分析，你寫的SQL就是由多個字符串和空格組成的一條SQL語句，MySQL需要識別出里面的字符串是什么，代表什么。

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

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

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

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

Update語句分析

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

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

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

redo log(重做日志)

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

• 具體來說，當(dāng)有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log里面，并更新內(nèi)存，這個時候更新就算完成了。同時，InnoDB 引擎會在適當(dāng)?shù)臅r候，將這個操作記錄更新到磁盤里面，而這個更新往往是在系統(tǒng)比較空閑的時候做。

• InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4 個文件，每個文件的大小是 1GB，那么總共就可以記錄 4GB 的操作。從頭開始寫，寫到末尾就又回到開頭循環(huán)寫。

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

redo log存儲在哪里？

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

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

首先不用擔(dān)心 redo log 會用完空間，因為它是循環(huán)利用的。例如 redo log 日志配置為一組4個文件，每個文件分別為1G。它寫的流程如下圖：

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

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

binlog(歸檔日志)

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

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

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

• redo log是為了保證innoDB引擎的crash-safe能力，也就是說在mysql異常宕機重啟的時候，之前提交的事務(wù)可以保證不丟失；(因為成功提交的事務(wù)肯定是寫入了redo log,可以從redo log恢復(fù))

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

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

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

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

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

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

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

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

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

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

• 如果redolog寫入了，處于prepare狀態(tài)，binlog還沒寫入，那么宕機重啟后，redolog中的這個事務(wù)就直接回滾了。

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

以上是“MySQL中update語句的執(zhí)行過程是怎么樣的”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道！