一起來分析MySQL的update語句是怎麼執行的

本篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於一條update語句是怎樣執行的相關問題，執行update更新操作時，跟表有關的查詢緩存會失效，所以語句就會把表上所有快取結果都清空，下面就一起來看一下，希望對大家有幫助。

推薦學習：mysql教學

#前期準備

#先建立一張表，然後插入三條數據：

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資料庫主要分為兩個層級：服務層與儲存引擎層服務層：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教程

以上是一起來分析MySQL的update語句是怎麼執行的的詳細內容。更多資訊請關注PHP中文網其他相關文章！