SQL 執(zhí)行流程

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

建立連接

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

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

上圖中的 Command 列顯示 Sleep 有好幾個(gè)空閑的連接。如果客戶(hù)端太久沒(méi)動(dòng)靜，連接器就會(huì)自動(dòng)斷開(kāi)，這個(gè)參數(shù)由 wait_timeout 控制，默認(rèn)是 8 小時(shí)。

分析 SQL

在 MySQL 8.0 版本之前，MySQL 拿到一個(gè)查詢(xún)請(qǐng)求后，會(huì)先到查詢(xún)緩存中看看是否有查過(guò)。如果有，那么直接返回緩存的結(jié)果。但在 8.0 版本之后，查詢(xún)緩存功能直接被刪除了。主要是因?yàn)椴樵?xún)緩存弊大于利。

因?yàn)橹灰獙?duì)一個(gè)表進(jìn)行更新，這個(gè)表上的查詢(xún)緩存就會(huì)被清空。可能你剛剛把結(jié)果緩存起來(lái)了，一個(gè)更新操作一來(lái)，這些緩存就全部失效了。所以查詢(xún)緩存適合那些更新不頻繁的表，用來(lái)提高查詢(xún)效率。

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

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

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

優(yōu)化 SQL

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

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

執(zhí)行 SQL

MySQL 通過(guò)分析器知道了你要做什么，通過(guò)優(yōu)化器知道了該怎么做，于是就進(jìn)入了執(zhí)行器階段，開(kāi)始執(zhí)行語(yǔ)句。開(kāi)始執(zhí)行的時(shí)候，要先判斷一下你對(duì)這個(gè)表 T 有沒(méi)有執(zhí)行查詢(xún)的權(quán)限，如果沒(méi)有，就會(huì)返回沒(méi)有權(quán)限的錯(cuò)誤。

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

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

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

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

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

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

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

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

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

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

InnoDB 存儲(chǔ)引擎

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

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

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

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

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

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

總結(jié)

最后我們總結(jié)一下一條 SQL 語(yǔ)句從查詢(xún)到返回?cái)?shù)據(jù)的4個(gè)階段，分別是：

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