SQL 在 MySQL 執行都發生了什么？

系統提示音：任務提示，基于邏輯架構，解析 MySQL 的 SQL 執行流程，組建團隊，逐步完成任務。完成 KPI 后解鎖晉升技術負責人，完不成則降級做真牛馬，單身沒女人……

重生后還要卷 KPI，算了，好說歹說，我只需要把互聯網世界的 MySQL 8.0 SQL 執行都涉及到哪些關鍵步驟列出來，再拆分出不同團隊開發即可完成任務……

在盛世當 SB 領導的牛馬，在這里做一回英明神武領導也挺不錯，大家肯定對我心服口服。

先從全局視角分析 SQL 語句的執行流程分為以下幾個步驟，如圖所示：

圖片

1. 客戶端連接器：客戶端連接到 MySQL 服務器，連接器調用 Server 層的安全管理負責驗證客戶端的身份和權限，如果通過驗證，就建立一個連接，MySQL 會創建一個專用的線程（以操作系統級別的線程實現）來為該客戶端服務，并從權限表中讀取該用戶的所有權限信息。
2. SQL 解析：SQL Interface(SQL 接口，用來接受用戶的 SQL 命令，使用 Parser 解析器對 SQL 語句進行語法分析；Parse 解析器會檢查 SQL 語句是否符合語法規則，并將其轉換為一棵語法樹。
3. Optimizer 優化：在語義分析之后，MySQL 會對 SQL 語句進行查詢優化。查詢優化器會分析查詢語句，并生成一個最優的執行計劃。執行計劃是指 MySQL 執行 SQL 語句的具體步驟，包括使用哪些索引、如何連接表等。
4. 執行引擎Actuator執行 SQL 語句：在完成解析和優化階段以后，MySQL 會生成對應的執行計劃，查詢執行引擎根據執行計劃給出的指令逐步執行得出結果。整個執行過程的大部分操作均是通過調用存儲引擎實現的接口來完成。

很明顯，服務層是 MySQL 中的核心組件，負責提供各種數據庫操作所需的基本功能，如 SQL 語法處理、事務管理、鎖管理等。

為了啟動項目，我決定以一條最基礎的 SELECT 查詢作為突破口，去組建一個 MySQL 團隊，干翻這蒼穹！

SELECT 查詢執行都發生了什么

總的來說查詢過程如下圖 2-1 所示：

圖片

首先程序的請求會通過 MySQL 的 connectors 與其進行交互，請求到 Server 層后，會暫時存放在連接池（connection pool)中并由處理器（Management Serveices & Utilities）管理。

當該請求從等待隊列進入到處理隊列，管理器會將該請求丟給 SQL 接口（SQL Interface）。

SQL 接口接收到請求后，它會將請求進行 hash 處理并與緩存中的結果進行對比，如果完全匹配則通過緩存直接返回處理結果（8.0 已經廢棄該步驟）；否則，需要完整的走一趟流程：

• 由 SQL 接口丟給后面的解析器（Parser），上面已經說到，解釋器會判斷 SQL 語句正確與否。
• 解釋器處理完，便來到后面的優化器（Optimizer），它會產生多種執行計劃,最終數據庫會選擇最優化的方案去執行,盡快返會結果。
• 確定最優執行計劃后，SQL 語句此時便可以交由存儲引擎（Engine）處理，并返回給 Server 層，由 Server 層執行 返回給客戶端。

1、 Connectors 客戶端/服務端通信協議

MySQL 客戶端/服務端通信協議 是 “半雙工” 的，在任一時刻，要么是服務器向客戶端發送數據，要么是客戶端向服務器發送數據，這兩個動作不能同時發生。

一旦一端開始發送消息，另一端要接收完整個消息才能響應它，所以無法也無須將一個消息切成小塊獨立發送，也沒有辦法進行流量控制。

因而在實際開發中，盡量保持查詢簡單且只返回必需的數據，減小通信間數據包的大小和數量是一個非常好的習慣，這也是查詢中盡量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一。

由連接器 Connectors 來完成與 MySQL Server 建立連接，連接器 Connectors 負責讓客戶端和 Server 端建立連接、并從 Server 端獲取權限、維持和管理連接。

mysql -hlocalhost -P3306 -uuser -ppasswd

連接密碼驗證通過，連接器會到權限表里面查出你擁有的權限，之后這個連接里面的權限判斷邏輯，都將依賴于此時讀到的權限，一個用戶成功建立連接后，即使管理員對這個用戶的權限做了修改，也不會影響已經存在連接的權限，修改完后，只有再新建的連接才會使用新的權限設置。

2、連接管理和線程池管理

建立連接的過程通常是比較復雜的，所以使用長連接，如果客戶端持續有請求，則一直使用同一個連接。

反之短連接是指每次執行完很少的幾次查詢就斷開連接，下次查詢再重新建立一個。

MySQL 采用池化技術，節省了 TCP 鏈接創建和銷毀的成本。

默認情況下，每個客戶端連接都會在服務器進程中擁有一個線程，所以還有個線程池，每一個 TCP 連接從線程池中獲取一個線程，省去了創建和銷毀線程的開銷。

嘿嘿嘿，我需要招聘幾個擅長 TCP 網絡編程和多線程技術的高手，這個團隊就叫 「Connectors 王霸隊」。

3、Parser 解析器解析 SQL

在 MySQL 8.0 之前，MySQL 會先檢查查詢語句是否命中緩存，如果命中緩存則直接返回緩存中的數據。

MySQL 8.0 中已移除了查詢緩存功能，使用者需要自行實現相關功能，如使用 Redis、Memcached 等中間緩存系統。

為啥移除查詢緩存功能呢？

查詢中包含任何用戶自定義函數、存儲函數、用戶變量、臨時表、MySQL 庫中的系統表，其查詢結果都不會被緩存。比如函數 NOW() 或者 CURRENT_DATE() 會因為不同的查詢時間，返回不同的查詢結果，將這樣的查詢結果緩存起來沒有任何的意義。

MySQL 查詢緩存系統會跟蹤查詢中涉及的每個表，如果這些表 (數據或結構) 發生變化，那么和這張表相關的所有緩存數據都將失效。

正因為如此，在任何的寫操作時，MySQL 必須將對應表的所有緩存都設置為失效。

如果查詢緩存非常大或者碎片很多，這個操作就可能帶來很大的系統消耗，甚至導致系統僵死一會兒。

Parser 解析器會對 SQL 語句進行分析，檢查其是否符合語法規則。如果 SQL 語句不符合語法規則，MySQL 將會返回一個錯誤消息。詳細的來說又可分為以下幾步：

• 詞法分析：主要負責從 SQL 語句中提取關鍵字，比如：查詢的表，字段名，查詢條件等等。詞法分析器會將 SQL 語句分割成一個個的詞法單元（token），并為每個 token 賦予一個類型（type）和值（value）。
• 語法規則：主要判斷 SQL 語句是否合乎 MySQL 的語法。
• 語義分析：主要負責檢查 SQL 語句的語義是否正確，比如：表名和字段名是否存在，數據類型是否匹配，函數是否合法等。語義分析器會根據數據字典（data dictionary）和目錄（catalog）來驗證 SQL 語句的有效性。

舉個例子。

SELECT name, age FROM student WHERE id = 1;

語法分析將 SQL 語句分割成以下詞法單元：

圖片

根據 MySQL 的語法規則，檢查詞法單元是否符合以下格式。

select_statement: SELECT select_expression_list FROM table_reference_list [WHERE where_condition]

接著進行語義分析，比如檢查表 student 是否存在、字段 name, age, id 是否屬于表 student。

將 SQL 翻譯成語法樹，我需要招聘幾個精通操作系統和編譯原理的大神為了建功立業！這個團隊就叫「編譯大寶劍」。

4、Optimizer 優化器

一條查詢 SQL 可以有很多種執行方式，最后都返回相同的結果**,優化器的作用就是找到這其中最好的執行計劃**。

需要設計一個評估執行成本的優化器，預測一個查詢使用某種執行計劃時的成本，并選擇其中成本最小的一個。

SQL 語句在 Optimizer 優化階段會經歷以下步驟。

• SQL 語句重寫：對 SQL 語句進行一些語法和邏輯上的變換，例如，將子查詢轉換為連接，將 or 條件轉換為 union，將 in 條件轉換為 exists 等。
• 查詢分解：MySQL 會將一條復雜的 SQL 語句分解為多個簡單的子查詢，每個子查詢可以單獨優化和執行。
• 預處理：MySQL 會對 SQL 語句進行一些基本的檢查和處理，例如檢查語法錯誤，解析參數。
• 優化器：MySQL 會根據統計信息和成本模型，為 SQL 語句選擇一個最佳的執行計劃。執行計劃包括了連接順序，訪問方法，索引選擇，排序策略等。

需要注意的是，我可以讓優化器使用緩存來提高查詢速度。

1. 表緩存：用于存儲表的元數據，如表的結構定義。當查詢需要表信息時，優先從表緩存中獲取，避免磁盤操作。
2. 線程緩存：用于復用服務器的連接線程。當一個連接關閉后，它的線程會被放回線程緩存池中，供新的連接使用。線程池意味著減少了創建和銷毀線程的開銷。
3. 緩沖池：主要用于 InnoDB 存儲引擎，緩沖池管理緩存的數據頁，包括數據和索引。當需要訪問這些頁時，可以直接從緩沖池讀取，提高訪問速度。

王妮瑪：為何要對 SQL 語句重寫？多此一舉

非也，連接查詢通常比子查詢更快，因為 MySQL 優化器可以生成更佳的執行計劃，可以預先裝載數據，更高效地處理查詢。

子查詢往往需要運行重復的查詢，子查詢生成的臨時表上也沒有索引，因此效率會更低。

連接查詢可以利用索引加速。

王妮瑪：你如何評估執行成本？

• 表依賴關系：MySQL 優化器會分析 SQL 語句中涉及到的表之間是否有依賴關系。
• 索引：分析 SQL 語句中參與條件過濾或排序的列是否有可用索引，并根據索引類型和覆蓋度來選擇合適的索引。
• 預估行數：根據數據字典和目錄中存儲的統計信息來預估每個表或每個索引范圍內的行數。這些行數會影響成本模型中的 I/O 代價和 CPU 代價。
• 預估成本：優化器會根據預估行數和成本常數（cost constant）來預估每個執行計劃的成本。

就這樣，得到一個執行計劃。

這里需要一個成本評估模型，使優化器能夠精準預測最優執行路徑。所以我需要招聘一些成本優化算法大牛，就叫做「成本估算榨干隊」

5、調用存儲引擎執行 SQL 語句

Server 層在完成解析和優化階段以后，MySQL 會生成對應的執行計劃，執行器會根據查詢語句，調用存儲引擎接口從磁盤讀取數據，并將其存儲在內存中。

引擎層負責存儲數據和執行 SQL 語句。然后，執行器會對數據進行排序、分組、聚合等操作，最終生成查詢結果。

比如執行 select * from student where id = 1;。

沒有索引

對于沒有有索引的表使用全表掃描。

1. 調用 InnoDB 引擎接口取這個表的第一行，判斷 id 值是不是 1，如果不是則跳過，如果是則將這行存在結果集中；
2. 調用引擎接口取下一行，重復相同的判斷邏輯，直到取到這個表的最后一行。
3. 執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。

索引掃描

假設 student 表主鍵是 id，執行計劃是先掃描 student 表的索引 idx_score（id），然后回表獲取 student 數據。

執行引擎是核心，需要對文件處理、磁盤和索引有著高技術的能力，他們需要精通操作系統、文件系統和數據結構與算法。這個團隊就叫「存儲引擎風火輪」。

6、過濾數據和處理

引擎層從磁盤文件獲取到數據后返回給 Server 層，MySQL 會根據執行計劃中的過濾條件（where，group by，having，order by，limit 等），對讀取到的數據進行過濾和處理。

過濾條件可以減少返回給客戶端的數據量，提高查詢效率。接著把過濾后的數據返回給客戶端，并釋放相關的資源，客戶端可以接收到結果集，并進行后續的操作。

最后一個階段就是將結果返回給客戶端。即使查詢不到數據，MySQL 仍然會返回這個查詢的相關信息，比如該查詢影響到的行數以及執行時間等。

系統的提示音再次響起。

恭喜完成階段性任務，晉升為異世界技術負責人。下一個任務，接續分析修改語句在 MySQl 都發生了什么，并設計 MySQL 的事務管理模塊。

這是新的挑戰，也是新的成長。我明白，這場冒險才剛剛開始。

異世界的事務管理，又將掀起怎樣的風暴？蕭劍臣是否能找到回歸的路？更多精彩，敬請期待下一集！