從輸入 SQL 到返回?cái)?shù)據(jù),到底發(fā)生了什么?
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í)行流程是這樣的:
- 調(diào)用 InnoDB 引擎接口取這個(gè)表的第一行,判斷 ID 值是不是 10,如果不是則跳過(guò),如果是則將這行存在結(jié)果集中。
- 調(diào)用引擎接口取「下一行」,重復(fù)相同的判斷邏輯,直到取到這個(gè)表的最后一行。
- 執(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ù)端。