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

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

一起來分析MySQL的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的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。它寫的流程如下圖:
一起來分析MySQL的update語句是怎樣執行的

簡單總結一下: redo log日志是Innodb存儲引擎特有的機制,可以用來應對異?;謴?/strong>,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;

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

手動用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

? 版權聲明
THE END
喜歡就支持一下吧
點贊10 分享