一文讀懂一條 SQL 查詢語句是如何執行的
2001 年 MySQL 發布 3.23 版本，自此便開始獲得廣泛應用，隨著不斷地升級迭代，至今 MySQL 已經走過了 20 個年頭。
為了充分發揮 MySQL 的性能並順利地使用，就必須正確理解其設計思想，因此，了解 MySQL 的邏輯架構是必要的。本文將通過一條 SQL 查詢語句的具體執行過程來詳細介紹 MySQL 架構中的各個組件。
MySQL 邏輯架構概覽
MySQL 最重要、最與眾不同的特性就是它的可插拔存儲引擎架構（pluggable storage engine architecture），這種架構的設計將查詢處理及其他系統任務和數據的存儲/提取分離開來。來看官網的解釋：
The MySQL pluggable storage engine architecture enables a database professional to select a specialized storage engine for a particular application need while being completely shielded from the need to manage any specific application coding requirements.
大致意思就是，MySQL 可插拔存儲引擎架構使開發者能夠為特定應用程式需求選擇專門的存儲引擎，同時完全無需管理任何特定應用程式編碼要求。也就是說，儘管不同存儲引擎具有不同的功能，但應用程式不受這些差異的影響。
如果應用程式更改帶來了需要更改底層存儲引擎的需求，或者需要添加一個或多個存儲引擎來支援新需求，則無需進行重大的編碼或流程更改即可使工作正常進行。 MySQL 伺服器架構通過提供適用於跨存儲引擎的一致且易於使用的 API，使應用程式免受存儲引擎底層複雜性的影響。
MySQL 的邏輯架構圖如下，參考《高性能 MySQL – 第 3 版》：
我們可以大致把 MySQL 的邏輯架構分成 Server 層和存儲引擎層：
1）大多數 MySQL 的核心服務功能都在 Server 層，包括連接，查詢解析、分析、優化、快取以及所有的內置函數（例如，日期、時間、數學和加密函數），所有跨存儲引擎的功能都在這一層實現：存儲過程、觸發器、視圖等。
值得一提的是，Server 最上面的服務也就是連接器，擁有管理 MySQL 連接、許可權驗證的功能。顯然這並非 MySQL 所獨有，大多數基於網路的客戶端/伺服器的工具或者服務都有類似的架構。
2）第二層就是存儲引擎（支援 InnoDB、MyISAM、Memory 等多個存儲引擎）。存儲引擎負責 MySQL 中數據的存儲和提取，響應上層伺服器的請求。每個存儲引擎自然是有它的優勢和劣勢，不同的存儲引擎之間無法相互通訊，所以我們需要根據不同的場景來選擇合適的存儲引擎。
伺服器通過 API 與存儲引擎進行通訊。這些介面屏蔽了不同存儲引擎之間的差異，使得這些差異對上層的查詢過程透明。存儲引擎 API 包含幾十個底層函數，用於執行諸如 「開始一個事務」 或者 「根據主鍵提取一行記錄」 等操作。
需要注意的是，在 MySQL 5.1 及之前的版本，MyISAM 是默認的存儲引擎，而在 MySQL 5.5.5 後，InnoDB 成為了默認的存儲引擎。
連接器（Connector）
MySQL 5.7 的官方文檔中，是這樣描述連接器的：
MySQL Connectors provide connectivity to the MySQL server for client programs.
MySQL 連接器為客戶端程式提供到 MySQL 伺服器的連接。 說得更細節一點的話，連接器其實會做兩個事情，一個是管理 MySQL 連接，一個是許可權驗證。我們依次來解釋下。
首先，要連接到 MySQL 伺服器，我們通常需要提供 MySQL 用戶名和密碼，並且如果伺服器運行在我們登錄的機器以外的機器上，還需要指定一個主機名比如 host。 所以連接命令一般是這樣的：
shell> mysql -h host -u user -p
Enter password: ********
當然了，如果在運行 MySQL 的同一台機器上登錄，就可以省略主機名，只需使用以下內容：
shell> mysql -u user -p
上面這個命令各位應該都很熟悉。
OK，通過上述命令完成經典的 TCP 三次握手建立連接後，連接器就會根據你輸入的用戶名和密碼來認證你的身份：
1）如果用戶名或密碼不對，你就會收到一個 “Access denied for user” 的錯誤，然後客戶端程式結束執行。
2）如果用戶名密碼認證通過，你會看到下面這一串內容：
mysql> 就是在提示你 MySQL 已準備好了，你可以開始輸入 SQL 語句了！
當然，連接器做的事情不僅僅是比對一下用戶名和密碼，它還會驗證該用戶是否具有執行某個特定查詢的許可權（例如，是否允許該用戶對 world 資料庫的 Country 表執行 SELECT 語句）。之後，這個連接裡面的所有許可權判斷邏輯，都將依賴於此時讀到的許可權。
這意味著，當一個用戶成功建立連接後，即使你在另一個終端用管理員帳號對這個用戶的許可權做了修改，對當前已經存在連接的許可權不會造成任何影響。
也就是說，當修改了用戶許可權後，只有再新建的連接才會使用新的許可權設置。
當一個連接建立起來後，如果你沒有後續的動作，那麼這個連接就處於空閑狀態（Sleep）。
事實上，對於一個 MySQL 連接來說（或者說一個執行緒），任何時刻都有一個狀態，該狀態表示了 MySQL 當前正在做什麼。有很多種方式能查看當前的狀態，最簡單的是使用 SHOW FULL PROCESSLIST 命令（該命令返回結果中的 Command 列就表示當前的狀態）。
在一個查詢的生命周期中，狀態會變化很多次。這裡就不詳細列出來了，上圖中的 Sleep 狀態就是說當前連接正在等待客戶端發送新的請求，Query 狀態表示當前連接正在執行查詢或者正在將結果發送給客戶端。
在 MyQL 的默認設置中，如果一個連接處在 Sleep 狀態 8 小時（就是超過 8 小時沒有使用），伺服器將斷開這條連接，後續在該連接上進行的所有操作都將失敗。這個時間是由參數 wait_timeout 控制的：
查詢快取（Query Cache）
OK，連接建立完成後，我們就可以輸入 select 語句進行查詢了。執行邏輯就來到了第二步：查詢快取。
官方文檔是這樣解釋 Query Cache 的：
The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.
就是說查詢快取存儲了 SELECT 語句的文本以及響應給客戶端的相應結果。這樣，如果伺服器稍後接收到相同的 SELECT 語句，伺服器會先從查詢快取中檢索結果，而不是再次解析和執行該語句。查詢快取在 session 之間共享，因此可以發送一個客戶端生成的結果集以響應另一個客戶端發出的相同查詢。
如果當前的查詢恰好命中了查詢快取，那麼在返回查詢結果之前 MySQL 會檢查一次用戶許可權。這仍然是無須解析查詢SQL語句的，因為在查詢快取中已經存放了當前查詢需要訪問的表資訊。
那麼既然涉及到快取，就必然繞不開快取一致性問題了。值得慶幸的是，不需要我們進行額外操作，查詢快取並不會返回陳舊數據！
The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.
當表被修改時，查詢快取中的任何相關條目都會被 flushed，注意，這裡的 flushed 翻譯為清空而不是刷新。
看起來好像還不錯？不用我們手動操作，失效快取就能夠被自動清空。
然而，很不幸的是，正是由於這個特性，從 MySQL 5.7.20 開始，官方不再推薦使用查詢快取，並在 MySQL 8.0 中直接刪除了查詢快取！
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.
其實不難理解，舉個例子，對於一個流量很大的論壇項目來說，查詢帖子表的需求每時每刻都存在，帖子也幾乎每時每刻都在增加，那隻要這個表一更新，這個表上所有的查詢快取都會被清空，這對於 MySQL 資料庫的壓力之大，可想而知了吧。費個勁把查詢結果存起來，還沒來得及使用呢，就被一個更新全清空了。
對於 MySQL 8.0 之前的版本來說，你可以將參數 query_cache_type 設置成 DEMAND，這樣所有的 SQL 語句都不會再使用查詢快取。而對於你確定要使用查詢快取的語句，可以用 SQL_CACHE 顯式指定，像下面這個語句一樣：
mysql> select SQL_CACHE * from t1 where id = 1;
解析器（Parser）
如果沒有命中或者沒有開啟查詢快取，MySQL 伺服器接下來要做的就是將一條 SQL 語句轉換成一個執行計劃，再依照這個執行計劃和存儲引擎進行交互。這包括多個子階段：解析 SQL、預處理、優化 SQL 執行計劃。這個過程中任何錯誤（例如語法錯誤）都可能終止查詢。
其中解析 SQL 和預處理就是解析器做的事情，優化 SQL 執行計劃就是優化器做的事情。這裡我們先說解析器。
這裡《高性能 MySQL – 第 3 版》書中分得更細緻點，解析器用來解析 SQL，預處理器則用來預處理，我暫且把它們都歸為解析器吧
所謂解析 SQL 就是說，MySQL 通過關鍵字對 SQL 語句進行解析，並生成一棵對應的 「解析樹」，用於根據語法規則來驗證語句是否正確。例如，它將驗證是否使用錯誤的關鍵字，或者使用關鍵字的順序是否正確等，再或者它還會驗證引號是否能前後正確匹配。
而預處理則會進一步檢查解析樹是否合法，例如，檢查數據表和數據列是否存在，檢查表名和欄位名是否正確等。
優化器（Optimizer）
現在，解析樹是合法的了，MySQL 已經知道你要做什麼了。不過，一條查詢可以有很多種執行計劃，最後都返回相同的結果，那到底該選擇哪種執行計劃呢？
舉個簡單的例子：
mysql> select * from t1 where id = 10 and name = "good";
對於上面這個語句，可以先查找 name = good 再查找 id = 10，也可以先查找 id = 10 再查找 name = good，這兩種不同的執行計劃可能耗費的時間成本是不一樣的。
那麼優化器的作用就是找到這其中最好的執行計劃。需要注意的是，這裡的執行計劃是一個數據結構，而不是和很多其他的關係型資料庫那樣會生成對應的位元組碼。
另外，優化器並不關心表使用的是什麼存儲引擎，但存儲引擎對於優化查詢是有影響的。優化器會請求存儲引擎提供容量或某個具體操作的開銷資訊，以及表數據的統計資訊等。
當優化器階段完成後，這個語句的執行計劃就確定下來了，就可以進入執行器階段了。
執行器
和命中查詢快取一樣，在開始執行 SQL 語句之前，執行器會先判斷一下當前用戶對這個表有沒有執行查詢的許可權，如果沒有，就會返回沒有許可權的錯誤。
許可權認證完成後，MySQL 就會根據執行計劃給出的指令逐步執行。在根據執行計劃逐步執行的過程中，有大量的操作需要通過調用存儲引擎實現的介面來完成，這些介面也就是我們稱為 「handler API」 的介面。
查詢中的每一個表由一個 handler 的實例表示。實際上，MySQL 在優化階段就為每個表創建了一個 handler 實例，優化器根據這些實例的介面可以獲取表的相關資訊，包括表的所有列名、索引統計資訊，等等。
舉個例子：
mysql> select * from t1 where id = 10;
假設我們使用默認的 InnoDB 引擎，則執行器的執行流程大概是這樣的（注意，如果 id 不是索引則會進行全表掃描，一行一行的查找，如果是索引則會在索引組織表中查詢，比較負責。這裡以非索引舉例）：
1）調用 InnoDB 引擎介面獲取這個表的第一行記錄，判斷 id 值是不是 10，如果是則將這行記錄存在一個集合中；如果不是則進入下一行的判斷，直到取到這個表的最後一行
2）執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果返回給客戶端
小結
文末放一張《高性能 MySQL – 第 3 版》中的圖片，總結下一條查詢語句的執行過程：
-
MySQL 客戶端與伺服器間建立連接，客戶端發送一條查詢給伺服器；
-
伺服器先檢查查詢快取，如果命中了快取，則立刻返回存儲在快取中的結果；否則進入下一階段；
-
伺服器端進行 SQL 解析、預處理，生成合法的解析樹；
-
再由優化器生成對應的執行計劃；
-
MySQL 根據優化器生成的執行計劃，調用相應的存儲引擎的 API 來執行，並將執行結果返回給客戶端。
🎉 關注公眾號 | 飛天小牛肉，即時獲取更新
- 部落客東南大學碩士在讀，攜程 Java 後台開發暑期實習生，利用課餘時間運營一個公眾號『 飛天小牛肉 』，2020/12/29 日開通，專註分享電腦基礎（數據結構 + 演算法 + 電腦網路 + 資料庫 + 作業系統 + Linux）、Java 技術棧等相關原創技術好文。本公眾號的目的就是讓大家可以快速掌握重點知識，有的放矢。關注公眾號第一時間獲取文章更新，成長的路上我們一起進步
- 並推薦個人維護的開源教程類項目： CS-Wiki（Gitee 推薦項目，現已累計 1.7k+ star）, 致力打造完善的後端知識體系，在技術的路上少走彎路，歡迎各位小夥伴前來交流學習 ~ 😊
- 如果各位小夥伴春招秋招沒有拿得出手的項目的話，可以參考我寫的一個項目「開源社區系統 Echo」Gitee 官方推薦項目，目前已累計 800+ star，基於 SpringBoot + MyBatis + MySQL + Redis + Kafka + Elasticsearch + Spring Security + … 並提供詳細的開發文檔和配套教程。公眾號後台回復 Echo 可以獲取配套教程，目前尚在更新中。
