SQL 在 MySQL 執(zhí)行都發(fā)生了什么？

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

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

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

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

圖片

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

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

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

SELECT 查詢執(zhí)行都發(fā)生了什么

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

圖片

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

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

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

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

1、 Connectors 客戶端/服務(wù)端通信協(xié)議

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

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

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

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

mysql -hlocalhost -P3306 -uuser -ppasswd

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

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

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

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

MySQL 采用池化技術(shù)，節(jié)省了 TCP 鏈接創(chuàng)建和銷毀的成本。

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

嘿嘿嘿，我需要招聘幾個擅長 TCP 網(wǎng)絡(luò)編程和多線程技術(shù)的高手，這個團隊就叫 「Connectors 王霸隊」。

3、Parser 解析器解析 SQL

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

MySQL 8.0 中已移除了查詢緩存功能，使用者需要自行實現(xiàn)相關(guān)功能，如使用 Redis、Memcached 等中間緩存系統(tǒng)。

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

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

MySQL 查詢緩存系統(tǒng)會跟蹤查詢中涉及的每個表，如果這些表 (數(shù)據(jù)或結(jié)構(gòu)) 發(fā)生變化，那么和這張表相關(guān)的所有緩存數(shù)據(jù)都將失效。

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

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

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

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

舉個例子。

SELECT name, age FROM student WHERE id = 1;

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

圖片

根據(jù) MySQL 的語法規(guī)則，檢查詞法單元是否符合以下格式。

select_statement: SELECT select_expression_list FROM table_reference_list [WHERE where_condition]

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

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

4、Optimizer 優(yōu)化器

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

需要設(shè)計一個評估執(zhí)行成本的優(yōu)化器，預(yù)測一個查詢使用某種執(zhí)行計劃時的成本，并選擇其中成本最小的一個。

SQL 語句在 Optimizer 優(yōu)化階段會經(jīng)歷以下步驟。

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

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

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

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

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

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

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

王妮瑪：你如何評估執(zhí)行成本？

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

就這樣，得到一個執(zhí)行計劃。

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

5、調(diào)用存儲引擎執(zhí)行 SQL 語句

Server 層在完成解析和優(yōu)化階段以后，MySQL 會生成對應(yīng)的執(zhí)行計劃，執(zhí)行器會根據(jù)查詢語句，調(diào)用存儲引擎接口從磁盤讀取數(shù)據(jù)，并將其存儲在內(nèi)存中。

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

比如執(zhí)行 select * from student where id = 1;。

沒有索引

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

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

索引掃描

假設(shè) student 表主鍵是 id，執(zhí)行計劃是先掃描 student 表的索引 idx_score（id），然后回表獲取 student 數(shù)據(jù)。

執(zhí)行引擎是核心，需要對文件處理、磁盤和索引有著高技術(shù)的能力，他們需要精通操作系統(tǒng)、文件系統(tǒng)和數(shù)據(jù)結(jié)構(gòu)與算法。這個團隊就叫「存儲引擎風火輪」。

6、過濾數(shù)據(jù)和處理

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

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

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

系統(tǒng)的提示音再次響起。

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

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

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