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 可以獲取配套教程，目前尚在更新中。