一、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）執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果返回給客戶端

七、小結

總結下一條查詢語句的執行過程：

1.MySQL 客戶端與服務器間建立連接，客戶端發送一條查詢給服務器；

2.服務器先檢查查詢緩存，如果命中了緩存，則立刻返回存儲在緩存中的結果；否則進入下一階段；

3.服務器端進行 SQL 解析、預處理，生成合法的解析樹；

4.再由優化器生成對應的執行計劃；

5.MySQL 根據優化器生成的執行計劃，調用相應的存儲引擎的 API 來執行，并將執行結果返回給客戶端。

以上就是分析mysql中一條SQL查詢語句是如何執行的的詳細內容，更多關于mysql查詢語句是如何執行的的資料請關注腳本之家其它相關文章！