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

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

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

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

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

圖片

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

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

為了啟動(dòng)項(xiàng)目，我決定以一條最基礎(chǔ)的 SELECT 查詢作為突破口，去組建一個(gè) MySQL 團(tuán)隊(duì)，干翻這蒼穹！

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

總的來(lái)說(shuō)查詢過(guò)程如下圖 2-1 所示：

圖片

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

當(dāng)該請(qǐng)求從等待隊(duì)列進(jìn)入到處理隊(duì)列，管理器會(huì)將該請(qǐng)求丟給 SQL 接口（SQL Interface）。

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

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

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

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

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

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

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

mysql -hlocalhost -P3306 -uuser -ppasswd

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

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

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

反之短連接是指每次執(zhí)行完很少的幾次查詢就斷開(kāi)連接，下次查詢?cè)僦匦陆⒁粋€(gè)。

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

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

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

3、Parser 解析器解析 SQL

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

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

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

查詢中包含任何用戶自定義函數(shù)、存儲(chǔ)函數(shù)、用戶變量、臨時(shí)表、MySQL 庫(kù)中的系統(tǒng)表，其查詢結(jié)果都不會(huì)被緩存。比如函數(shù) NOW() 或者 CURRENT_DATE() 會(huì)因?yàn)椴煌牟樵儠r(shí)間，返回不同的查詢結(jié)果，將這樣的查詢結(jié)果緩存起來(lái)沒(méi)有任何的意義。

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

正因?yàn)槿绱?，在任何的?xiě)操作時(shí)，MySQL 必須將對(duì)應(yīng)表的所有緩存都設(shè)置為失效。

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

Parser 解析器會(huì)對(duì) SQL 語(yǔ)句進(jìn)行分析，檢查其是否符合語(yǔ)法規(guī)則。如果 SQL 語(yǔ)句不符合語(yǔ)法規(guī)則，MySQL 將會(huì)返回一個(gè)錯(cuò)誤消息。詳細(xì)的來(lái)說(shuō)又可分為以下幾步：

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

舉個(gè)例子。

SELECT name, age FROM student WHERE id = 1;

語(yǔ)法分析將 SQL 語(yǔ)句分割成以下詞法單元：

圖片

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

select_statement: SELECT select_expression_list FROM table_reference_list [WHERE where_condition]

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

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

4、Optimizer 優(yōu)化器

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

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

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

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

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

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

王妮瑪：為何要對(duì) SQL 語(yǔ)句重寫(xiě)？多此一舉

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

子查詢往往需要運(yùn)行重復(fù)的查詢，子查詢生成的臨時(shí)表上也沒(méi)有索引，因此效率會(huì)更低。

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

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

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

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

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

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

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

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

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

沒(méi)有索引

對(duì)于沒(méi)有有索引的表使用全表掃描。

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

索引掃描

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

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

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

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

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

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

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

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

這是新的挑戰(zhàn)，也是新的成長(zhǎng)。我明白，這場(chǎng)冒險(xiǎn)才剛剛開(kāi)始。

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