SQL 執(zhí)行流程

其實一個 SQL 從輸入到返回數(shù)據(jù)，其過程大致為：建立連接、分析 SQL、優(yōu)化 SQL、執(zhí)行 SQL。

建立連接

當(dāng)我們發(fā)送 SQL 給 MySQL 之前，我們都會輸入賬號和密碼，從而與 MySQL 建立連接。這部分的工作，其實就是 MySQL 的連接器處理的。連接器負責(zé)跟客戶端建立連接、獲取權(quán)限、維持和管理連接。

當(dāng)我們用管理員賬號對賬號權(quán)限做修改后，不影響已經(jīng)存在的連接的權(quán)限，只有新建的連接才會使用新的權(quán)限設(shè)置。我們可以通過 show processlist 命令查看目前的連接情況，如下圖所示。

上圖中的 Command 列顯示 Sleep 有好幾個空閑的連接。如果客戶端太久沒動靜，連接器就會自動斷開，這個參數(shù)由 wait_timeout 控制，默認是 8 小時。

分析 SQL

在 MySQL 8.0 版本之前，MySQL 拿到一個查詢請求后，會先到查詢緩存中看看是否有查過。如果有，那么直接返回緩存的結(jié)果。但在 8.0 版本之后，查詢緩存功能直接被刪除了。主要是因為查詢緩存弊大于利。

因為只要對一個表進行更新，這個表上的查詢緩存就會被清空?？赡苣銊倓偘呀Y(jié)果緩存起來了，一個更新操作一來，這些緩存就全部失效了。所以查詢緩存適合那些更新不頻繁的表，用來提高查詢效率。

當(dāng)拿到 SQL 之后，MySQL 會對 SQL 進行詞法分析和語法分析。詞法分析會解析每個詞的含義，而語法分析則是解析語法是否準確，分析器先會做詞法分析，再做語法分析。

你輸入的是由多個字符串和空格組成的一條 SQL 語句，MySQL 需要識別出里面的字符串分別是什么，代表什么。例如：select 表示查詢，t 表示 t 這個表，字符串 ID 識別成列 ID。做完詞法分析之后，就會做語法分析。

根據(jù)詞法分析的結(jié)果，語法分析器會根據(jù)語法規(guī)則，判斷輸入的 SQL 語句是否滿足 MySQL 語法。如果不滿足語法，會有「You have an error in your SQL syntax」的錯誤提醒。

優(yōu)化 SQL

經(jīng)過分析器，MySQL 就知道你要做什么了。但在開始執(zhí)行之前，還要先經(jīng)過優(yōu)化器的處理。優(yōu)化器是在表里面有多個索引的時候，決定使用哪個索引?；蛘咴谝粋€語句有多表關(guān)聯(lián)(join)的時候，決定各個表的連接順序。

有時候兩種執(zhí)行方法的邏輯結(jié)果是一樣的，但是執(zhí)行的效率會有不同，而優(yōu)化器的作用就是決定選擇使用哪一個方案。優(yōu)化器階段完成后，這個語句的執(zhí)行方案就確定下來了，然后進入執(zhí)行器階段。

執(zhí)行 SQL

MySQL 通過分析器知道了你要做什么，通過優(yōu)化器知道了該怎么做，于是就進入了執(zhí)行器階段，開始執(zhí)行語句。開始執(zhí)行的時候，要先判斷一下你對這個表 T 有沒有執(zhí)行查詢的權(quán)限，如果沒有，就會返回沒有權(quán)限的錯誤。

如果有權(quán)限，就打開表繼續(xù)執(zhí)行。打開表的時候，執(zhí)行器就會根據(jù)表的引擎定義，去使用這個引擎提供的接口。例如對于 select * from T where ID=10; 這條語句，ID 字段沒有索引，那么執(zhí)行器的執(zhí)行流程是這樣的：

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

至此，這個語句就執(zhí)行完成了。

對于有索引的表，執(zhí)行的邏輯也差不多。第一次調(diào)用的是「取滿足條件的第一行」這個接口，之后循環(huán)取「滿足條件的下一行」這個接口，這些接口都是引擎中已經(jīng)定義好的。

你會在數(shù)據(jù)庫的慢查詢?nèi)罩局锌吹揭粋€ rows_examined 的字段，表示這個語句在執(zhí)行器執(zhí)行過程中掃描了多少行。這個值就是在執(zhí)行器每次調(diào)用引擎獲取數(shù)據(jù)行的時候累加的。在有些場景下，執(zhí)行器調(diào)用一次，在引擎內(nèi)部則掃描了多行，因此引擎掃描行數(shù)跟 rows_examined 并不是完全相同的。

MySQL 技術(shù)架構(gòu)

其實上面的過程，就是按著 MySQL 的技術(shù)架構(gòu)來的，其技術(shù)架構(gòu)如下圖所示。

大體來說，MySQL 技術(shù)架構(gòu)可以分為 Server 層和存儲引擎層兩部分。

Server 層負責(zé)建立連接、分析 SQL 等功能。 所有跨存儲引擎的功能都在這一層實現(xiàn)，例如存儲過程、觸發(fā)器、視圖等。

存儲引擎層負責(zé)數(shù)據(jù)的存儲和提取。 其架構(gòu)模式是插件式的，支持 InnoDB、MyISAM、Memory 等多個存儲引擎?，F(xiàn)在最常用的是 InnoDB 存儲引擎，從 MySQL 5.5.5 開始成為了默認的存儲引擎。

InnoDB 存儲引擎

目前使用最廣泛的是 InnoDB 存儲引擎，其體系架構(gòu)分為三大塊，分別是：后臺線程、內(nèi)存池、文件，其體系架構(gòu)如下圖所示。

InnoDB 存儲引擎體系架構(gòu)

在上圖中，后臺線程負責(zé)刷新內(nèi)存池的數(shù)據(jù)，內(nèi)存池負責(zé)緩存磁盤的數(shù)據(jù)，文件則是具體的數(shù)據(jù)存儲。

后臺線程的主要工作是負責(zé)刷新內(nèi)存池的數(shù)據(jù)，保證緩沖池中的內(nèi)存緩存的是最近的數(shù)據(jù)。InnoDB 存儲引擎是多線程的模型，因此其后臺有多個不同的后臺線程，負責(zé)處理不同的任務(wù)。目前有 4 種不同類型的處理線程，分別是：Master Tread、IO Thread、Purge Thread、Page Cleaner Thread。

內(nèi)存池是 InnoDB 所管理內(nèi)存的統(tǒng)稱，主要用于緩存磁盤數(shù)據(jù)，從而加快數(shù)據(jù)的讀取。根據(jù)其用途不同，內(nèi)存池還可以分為：緩沖池、重做日志緩沖、額外內(nèi)存池三大塊。

文件則是最終存取數(shù)據(jù)庫數(shù)據(jù)的地方，其存儲了包括索引文件、數(shù)據(jù)文件等相關(guān)的數(shù)據(jù)文件。

總結(jié)

最后我們總結(jié)一下一條 SQL 語句從查詢到返回數(shù)據(jù)的4個階段，分別是：

• 建立連接?？蛻舳藭紫扰c MySQL 建立 TCP 連接，在連接器中會進行連接管理、權(quán)限驗證等操作。
• 分析 SQL。分析器進行詞法、語法分析，詞法分析知道要查詢什么內(nèi)容，語法分析判斷語法是否有問題。
• 優(yōu)化 SQL。優(yōu)化器根據(jù) SQL 情況，判斷使用哪種執(zhí)行方式更好，例如使用哪個索引，哪種表連接方式。
• 執(zhí)行 SQL。根據(jù)優(yōu)化器的優(yōu)化結(jié)果，生成執(zhí)行計劃，執(zhí)行器調(diào)用存儲引擎的 API 來執(zhí)行查詢，最終將數(shù)據(jù)返回給客戶端。