MySQL中update語句的執行過程是咋樣的

2024-07-24 12:33:14

字體：大中小

來源：轉載

供稿：網友

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

　　前期準備

　　首先創建一張表，然后插入三條數據：

　　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);

　　讓后執行更新操作：

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

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

　　SQL語句的執行過程

　　MySQL中update語句的執行過程是怎么樣的

　　如圖所示：MySQL數據庫主要分為兩個層級：服務層和存儲引擎層服務層：server層包括連接器、查詢緩存、分析器、優化器、執行器，包括大多數MySQL中的核心功能所有跨存儲引擎的功能也在這一層實現，包括存儲過程、觸發器、視圖等。存儲引擎層：存儲引擎層包括MySQL常見的存儲引擎，包括MyISAM、InnoDB和Memory等，最常用的是InnoDB，也是現在MySQL的默認存儲引擎。

　　server層中的組件介紹

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

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

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

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

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

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

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

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

　　Update語句分析

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

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

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

　　redo log(重做日志)

　　在 MySQL 里，如果每一次的更新操作都需要寫進磁盤，然后磁盤也要找到對應的那條記錄，然后再更新，整個過程 IO 成本、查找成本都很高。

　　MySQL里使用WAL(預寫式日志)技術，WAL 的全稱是 Write-Ahead Logging，它的關鍵點就是先寫日志，再寫磁盤。

　　具體來說，當有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log里面，并更新內存，這個時候更新就算完成了。同時，InnoDB 引擎會在適當的時候，將這個操作記錄更新到磁盤里面，而這個更新往往是在系統比較空閑的時候做。

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

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

　　redo log存儲在哪里？

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

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

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

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

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

　　binlog(歸檔日志)

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

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

　　redo log和bin log的總結

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

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

　　redo log和bin log的區別:

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

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

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

　　InnoDB引擎部分在執行這個簡單的update語句的時候的內部流程

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

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

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

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

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

　　如果redolog寫入了，binlog也寫入了，但redolog還沒有更新為commit狀態，那么宕機重啟以后，mysql會去檢查對應事務在binlog中是否完整。如果是，就提交事務；如果不是，就回滾事務。 (redolog處于prepare狀態，binlog完整啟動時就提交事務，為啥要這么設計? 主要是因為binlog寫入了，那么就會被從庫或者用這個binlog恢復出來的庫使用，為了數據一致性就采用了這個策略)

　　redo log和binlog是通過xid這個字段關聯起來的。

　　以上是“MySQL中update語句的執行過程是怎么樣的”這篇文章的所有內容，感謝各位的閱讀！

（編輯：武林網）

上一篇：該如何查詢mysql的最大連接數

下一篇：MySQL索引結構是什么樣的