一條 SQL 語句在 MySQL 中是如何執行的？

JavaGuide發表於2024-09-22
MySql

本篇文章會分析下一個 SQL 語句在 MySQL 中的執行流程，包括 SQL 的查詢在 MySQL 內部會怎麼流轉，SQL 語句的更新是怎麼完成的。

在分析之前我會先帶著你看看 MySQL 的基礎架構，知道了 MySQL 由那些元件組成以及這些元件的作用是什麼，可以幫助我們理解和解決這些問題。

一 MySQL 基礎架構分析

1.1 MySQL 基本架構概覽

下圖是 MySQL 的一個簡要架構圖，從下圖你可以很清晰的看到使用者的 SQL 語句在 MySQL 內部是如何執行的。

先簡單介紹一下下圖涉及的一些元件的基本作用幫助大家理解這幅圖，在 1.2 節中會詳細介紹到這些元件的作用。

  • 聯結器： 身份認證和許可權相關(登入 MySQL 的時候)。
  • 查詢快取： 執行查詢語句的時候，會先查詢快取（MySQL 8.0 版本後移除，因為這個功能不太實用）。
  • 分析器： 沒有命中快取的話，SQL 語句就會經過分析器，分析器說白了就是要先看你的 SQL 語句要幹嘛，再檢查你的 SQL 語句語法是否正確。
  • 最佳化器： 按照 MySQL 認為最優的方案去執行。
  • 執行器： 執行語句，然後從儲存引擎返回資料。 -

簡單來說 MySQL 主要分為 Server 層和儲存引擎層：

  • Server 層：主要包括聯結器、查詢快取、分析器、最佳化器、執行器等，所有跨儲存引擎的功能都在這一層實現，比如儲存過程、觸發器、檢視，函式等，還有一個通用的日誌模組 binlog 日誌模組。
  • 儲存引擎：主要負責資料的儲存和讀取，採用可以替換的外掛式架構，支援 InnoDB、MyISAM、Memory 等多個儲存引擎，其中 InnoDB 引擎有自有的日誌模組 redolog 模組。現在最常用的儲存引擎是 InnoDB，它從 MySQL 5.5 版本開始就被當做預設儲存引擎了。

1.2 Server 層基本元件介紹

1) 聯結器

聯結器主要和身份認證和許可權相關的功能相關，就好比一個級別很高的門衛一樣。

主要負責使用者登入資料庫，進行使用者的身份認證，包括校驗賬戶密碼，許可權等操作，如果使用者賬戶密碼已透過，聯結器會到許可權表中查詢該使用者的所有許可權，之後在這個連線裡的許可權邏輯判斷都是會依賴此時讀取到的許可權資料，也就是說，後續只要這個連線不斷開，即使管理員修改了該使用者的許可權，該使用者也是不受影響的。

2) 查詢快取(MySQL 8.0 版本後移除)

查詢快取主要用來快取我們所執行的 SELECT 語句以及該語句的結果集。

連線建立後，執行查詢語句的時候，會先查詢快取，MySQL 會先校驗這個 SQL 是否執行過，以 Key-Value 的形式快取在記憶體中，Key 是查詢語句，Value 是結果集。如果快取 key 被命中，就會直接返回給客戶端，如果沒有命中，就會執行後續的操作，完成後也會把結果快取起來，方便下一次呼叫。當然在真正執行快取查詢的時候還是會校驗使用者的許可權，是否有該表的查詢條件。

MySQL 查詢不建議使用快取，因為查詢快取失效在實際業務場景中可能會非常頻繁，假如你對一個表更新的話，這個表上的所有的查詢快取都會被清空。對於不經常更新的資料來說，使用快取還是可以的。

所以，一般在大多數情況下我們都是不推薦去使用查詢快取的。

MySQL 8.0 版本後刪除了快取的功能，官方也是認為該功能在實際的應用場景比較少，所以乾脆直接刪掉了。

3) 分析器

MySQL 沒有命中快取，那麼就會進入分析器，分析器主要是用來分析 SQL 語句是來幹嘛的，分析器也會分為幾步：

第一步，詞法分析，一條 SQL 語句有多個字串組成，首先要提取關鍵字，比如 select，提出查詢的表，提出欄位名，提出查詢條件等等。做完這些操作後，就會進入第二步。

第二步，語法分析，主要就是判斷你輸入的 SQL 是否正確，是否符合 MySQL 的語法。

完成這 2 步之後，MySQL 就準備開始執行了，但是如何執行，怎麼執行是最好的結果呢？這個時候就需要最佳化器上場了。

4) 最佳化器

最佳化器的作用就是它認為的最優的執行方案去執行（有時候可能也不是最優，這篇文章涉及對這部分知識的深入講解），比如多個索引的時候該如何選擇索引，多表查詢的時候如何選擇關聯順序等。

可以說，經過了最佳化器之後可以說這個語句具體該如何執行就已經定下來。

5) 執行器

當選擇了執行方案後，MySQL 就準備開始執行了，首先執行前會校驗該使用者有沒有許可權，如果沒有許可權，就會返回錯誤資訊，如果有許可權，就會去呼叫引擎的介面，返回介面執行的結果。

二 語句分析

2.1 查詢語句

說了以上這麼多，那麼究竟一條 SQL 語句是如何執行的呢？其實我們的 SQL 可以分為兩種，一種是查詢，一種是更新（增加，修改，刪除）。我們先分析下查詢語句，語句如下：

select * from tb_student  A where A.age='18' and A.name=' 張三 ';

結合上面的說明，我們分析下這個語句的執行流程：

  • 先檢查該語句是否有許可權，如果沒有許可權，直接返回錯誤資訊，如果有許可權，在 MySQL8.0 版本以前，會先查詢快取，以這條 SQL 語句為 key 在記憶體中查詢是否有結果，如果有直接快取，如果沒有，執行下一步。
  • 透過分析器進行詞法分析，提取 SQL 語句的關鍵元素，比如提取上面這個語句是查詢 select，提取需要查詢的表名為 tb_student，需要查詢所有的列，查詢條件是這個表的 id='1'。然後判斷這個 SQL 語句是否有語法錯誤，比如關鍵詞是否正確等等，如果檢查沒問題就執行下一步。
  • 接下來就是最佳化器進行確定執行方案，上面的 SQL 語句，可以有兩種執行方案：a.先查詢學生表中姓名為“張三”的學生，然後判斷是否年齡是 18。b.先找出學生中年齡 18 歲的學生，然後再查詢姓名為“張三”的學生。那麼最佳化器根據自己的最佳化演算法進行選擇執行效率最好的一個方案（最佳化器認為，有時候不一定最好）。那麼確認了執行計劃後就準備開始執行了。
  • 進行許可權校驗，如果沒有許可權就會返回錯誤資訊，如果有許可權就會呼叫資料庫引擎介面，返回引擎的執行結果。

2.2 更新語句

以上就是一條查詢 SQL 的執行流程，那麼接下來我們看看一條更新語句如何執行的呢？SQL 語句如下：

update tb_student A set A.age='19' where A.name=' 張三 ';

我們來給張三修改下年齡，在實際資料庫肯定不會設定年齡這個欄位的，不然要被技術負責人打的。其實這條語句也基本上會沿著上一個查詢的流程走，只不過執行更新的時候肯定要記錄日誌啦，這就會引入日誌模組了，MySQL 自帶的日誌模組是 binlog（歸檔日誌） ，所有的儲存引擎都可以使用，我們常用的 InnoDB 引擎還自帶了一個日誌模組 redo log（重做日誌），我們就以 InnoDB 模式下來探討這個語句的執行流程。流程如下：

  • 先查詢到張三這一條資料，不會走查詢快取，因為更新語句會導致與該表相關的查詢快取失效。
  • 然後拿到查詢的語句，把 age 改為 19，然後呼叫引擎 API 介面，寫入這一行資料，InnoDB 引擎把資料儲存在記憶體中，同時記錄 redo log，此時 redo log 進入 prepare 狀態，然後告訴執行器，執行完成了，隨時可以提交。
  • 執行器收到通知後記錄 binlog，然後呼叫引擎介面，提交 redo log 為提交狀態。
  • 更新完成。

這裡肯定有同學會問，為什麼要用兩個日誌模組，用一個日誌模組不行嗎?

這是因為最開始 MySQL 並沒有 InnoDB 引擎（InnoDB 引擎是其他公司以外掛形式插入 MySQL 的），MySQL 自帶的引擎是 MyISAM，但是我們知道 redo log 是 InnoDB 引擎特有的，其他儲存引擎都沒有，這就導致會沒有 crash-safe 的能力(crash-safe 的能力即使資料庫發生異常重啟，之前提交的記錄都不會丟失)，binlog 日誌只能用來歸檔。

並不是說只用一個日誌模組不可以，只是 InnoDB 引擎就是透過 redo log 來支援事務的。那麼，又會有同學問，我用兩個日誌模組，但是不要這麼複雜行不行，為什麼 redo log 要引入 prepare 預提交狀態？這裡我們用反證法來說明下為什麼要這麼做？

  • 先寫 redo log 直接提交，然後寫 binlog，假設寫完 redo log 後，機器掛了，binlog 日誌沒有被寫入，那麼機器重啟後，這臺機器會透過 redo log 恢復資料，但是這個時候 binlog 並沒有記錄該資料，後續進行機器備份的時候，就會丟失這一條資料，同時主從同步也會丟失這一條資料。
  • 先寫 binlog，然後寫 redo log，假設寫完了 binlog，機器異常重啟了，由於沒有 redo log，本機是無法恢復這一條記錄的，但是 binlog 又有記錄，那麼和上面同樣的道理，就會產生資料不一致的情況。

如果採用 redo log 兩階段提交的方式就不一樣了，寫完 binlog 後，然後再提交 redo log 就會防止出現上述的問題，從而保證了資料的一致性。那麼問題來了，有沒有一個極端的情況呢？假設 redo log 處於預提交狀態，binlog 也已經寫完了，這個時候發生了異常重啟會怎麼樣呢？
這個就要依賴於 MySQL 的處理機制了，MySQL 的處理過程如下：

  • 判斷 redo log 是否完整，如果判斷是完整的，就立即提交。
  • 如果 redo log 只是預提交但不是 commit 狀態，這個時候就會去判斷 binlog 是否完整，如果完整就提交 redo log, 不完整就回滾事務。

這樣就解決了資料一致性的問題。

三 總結

  • MySQL 主要分為 Server 層和引擎層，Server 層主要包括聯結器、查詢快取、分析器、最佳化器、執行器，同時還有一個日誌模組（binlog），這個日誌模組所有執行引擎都可以共用，redolog 只有 InnoDB 有。
  • 引擎層是外掛式的，目前主要包括，MyISAM,InnoDB,Memory 等。
  • 查詢語句的執行流程如下：許可權校驗（如果命中快取）--->查詢快取--->分析器--->最佳化器--->許可權校驗--->執行器--->引擎
  • 更新語句執行流程如下：分析器---->許可權校驗---->執行器--->引擎---redo log(prepare 狀態)--->binlog--->redo log(commit 狀態)

四 參考

  • 《MySQL 實戰 45 講》
  • MySQL 5.6 參考手冊:https://dev.MySQL.com/doc/refman/5.6/en/

文章推薦

  • MySQL 常見面試題總結
  • MySQL 索引詳解
  • MySQL 三大日誌(binlog、redo log 和 undo log)詳解
  • MySQL 事務隔離級別詳解
  • InnoDB 儲存引擎對 MVCC 的實現
  • SQL 語句在 MySQL 中的執行過程

相關文章