本篇文章給大家?guī)砹岁P(guān)于mysql中select語句執(zhí)行的相關(guān)知識，其中包括了連接器、分析器、優(yōu)化器和執(zhí)行器，希望對大家有幫助。

mysql 作為一個關(guān)系型數(shù)據(jù)庫，在國內(nèi)使用應(yīng)該是最廣泛的。也許你司使用 Oracle、Pg 等等，但是大多數(shù)互聯(lián)網(wǎng)公司，比如我司使用得最多的還是 Mysql，重要性不言而喻。

執(zhí)行select * from table，數(shù)據(jù)庫底層到底發(fā)生了啥？從而我們得到數(shù)據(jù)呢？

假設(shè)現(xiàn)在我有張 user 表，只有兩列，一列 id 自增的，一列 name 是 varchar 類型。建表語句是這樣的：

CREATE TABLE IF NOT EXISTS `user`(    `id` INT UNSIGNED AUTO_INCREMENT,    `name` VARCHAR(100) NOT NULL,    PRIMARY KEY ( `id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8;

問題就是下面這個語句的執(zhí)行過程。

select * from user where id = 1;

01 mysql 架構(gòu)概覽

要想理解這個問題就必須要知道 mysql 的內(nèi)部架構(gòu)。為此，我畫了張 mysql 的架構(gòu)圖（你也可以理解為 sql 查詢語句的執(zhí)行過程），如下所示：

首先 msql 分為 server 層和存儲引擎層兩個部分。server 層包括四個功能模塊，分別是：連接器、查詢緩存、優(yōu)化器、執(zhí)行器。這一層負(fù)責(zé)了 mysql 的所有核心工作，比如：內(nèi)置函數(shù)、存儲過程、觸發(fā)器以及視圖等。

而存儲引擎層則是負(fù)責(zé)數(shù)據(jù)的存取。注意，存儲引擎在 mysql 是可選的，常見的還有：InnoDB、MyISAM 以及 Memory等，最常用的就是 InnoDB。現(xiàn)在默認(rèn)的存儲引擎也是它（從 mysql 5.5.5 版本開始），大家可以看到我上面的建表語句就是指定了 InnoDB 引擎。當(dāng)然，你不指定的話默認(rèn)也是它。

由于存儲引擎是可選的，所以 mysql 中，所有的存儲引擎其實是共用一個 server層的。回到正題，我們就以這張圖的流程來解決一下小胖的問題。

1.1 連接器

首先，數(shù)據(jù)庫要執(zhí)行 sql，肯定要先連接數(shù)據(jù)庫吧。這部分工作就是由連接器完成。它負(fù)責(zé)校驗賬戶密碼、獲取權(quán)限、管理連接數(shù)，最終與客戶端建立連接等工作。mysql 鏈接數(shù)據(jù)庫是這樣寫的：

mysql -h 127.0.0.1 -P 3306 -u root -p # 127.0.0.1 : ip 3306 : 端口 root : 用戶名

運行命令之后需要輸入密碼，當(dāng)然也可以跟在 -p 后面。不過不建議這么做，會有密碼泄露的風(fēng)險。

輸入命令后，連接器根據(jù)你的賬戶名密碼驗證身份。這是會出現(xiàn)兩種情況：

• 賬號或密碼不對，服務(wù)端會返回一個 "ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)" 的錯誤，退出連接。

• 驗證通過，連接器就會到權(quán)限表查出你的權(quán)限。之后你有啥權(quán)限都要通過這時讀到的權(quán)限進(jìn)行判斷。

注意，我說的是此時查到的權(quán)限。就算你用管理員賬號修改了當(dāng)前用戶的權(quán)限，此時已連接上的當(dāng)前用戶不受影響，必須要重啟 mysql 新的權(quán)限才會生效。

1.1.1 查看連接狀態(tài)

連接完成，如果后續(xù)沒有做任何事情，這個連接就處于空閑狀態(tài)。你可以用 show processlist; 命令查看 mysql 的連接信息，如下圖，我的數(shù)據(jù)庫連接都是 Sleep 狀態(tài)的，除了執(zhí)行 show processlist 操作的連接。

1.1.2 控制連接

如果客戶端太長時間沒有操作，此連接將會自動斷開。這個時間默認(rèn)是 8 小時，由參數(shù) wait_timeout 控制。如果斷開以后繼續(xù)操作就會收到 "Lost connection to MySQL server during query"的錯誤。這時就必須重連才能執(zhí)行請求。

數(shù)據(jù)庫里面有長短連接之分，長連接：連接成功后不斷有請求，就會一直使用同一連接。短連接：每次執(zhí)行完幾次請求就斷開連接，下次需要再建立。

由于建立連接是比較耗時的操作，所以建議使用長連接。但這會有個問題長連接一直連著就會導(dǎo)致內(nèi)存占用過大，被系統(tǒng)強行沙雕。從而導(dǎo)致 MySQL 異常重啟。如何解決呢？兩個方法：

• 定期斷開長連接。使用特定時間，或者程序判斷執(zhí)行一個占用內(nèi)存大的操作后，斷開連接。之后需要操作就重連。

• mySQL 5.7 或以上版本，可以在每次執(zhí)行一個占用內(nèi)存大的操作后，執(zhí)行mysql_reset_connection來重新連接資源，此時不需重連或重新做權(quán)限認(rèn)證，但會把連接狀態(tài)恢復(fù)到剛創(chuàng)建完時。

1.2 查詢緩存

連接建立以后可以執(zhí)行 select 語句了。這就會來到第二步：查詢緩存。

查詢緩存中存儲的數(shù)據(jù)是 key-value 的形式，key 是查詢語句，value 是查詢的結(jié)果。邏輯是這樣的：先看看查詢緩存有沒該語句對應(yīng)的 value？有則直接取出返回客戶端，無則繼續(xù)到數(shù)據(jù)庫執(zhí)行語句。查出結(jié)果后會放一份到緩存中，再返回客戶端。

你可能發(fā)現(xiàn)緩存真的香，但是并不建議使用查詢緩存，因為有弊端。查詢緩存的失效非常頻繁，只有某個表有更新。它馬上失效了，對于經(jīng)常更新的表來說，命中緩存的概率極低。它僅僅適用于那些不經(jīng)常更新的表。

而 MySQL 似乎也考慮到這點了。提供了 query_cache_type 參數(shù)，把它設(shè)置為 DEMAND 就不再適用韓村。而對于要使用緩存的語句則可用 SQL_CACHE 顯示指定，像這樣：

select SQL_CACHE * from user where id = 1;

PS：MySQL 8.0 及以上版本把查詢緩存刪掉了，之后再也沒有這塊功能了。

1.3 分析器

如果沒有命中緩存就進(jìn)入分析器，這里就是對 sql 進(jìn)行分析。分析器會做詞法分析。你輸入的 sql 是啥，由啥組成，MySQL 都需要知道它們代表什么。

首先根據(jù) "select" 識別出這是查詢語句。字符串"user"識別成"表名 user"、字符串"id"識別成"列名id"。

之后進(jìn)行語法分析，它會根據(jù)輸入的語句分析是不是符合 MySQL 的語法。具體表現(xiàn)就是 select、where、from 等關(guān)鍵字少了個字母，明顯不符合 MySQL 語法，這次就會報個語法錯誤的異常：它一般會提示錯誤行數(shù)，關(guān)注"use near"后面即可。

1.4 優(yōu)化器

過了分析器，就來到了優(yōu)化器。MySQL 是個聰明的仔，再執(zhí)行之前會自己優(yōu)化下客戶端傳過來的語句，看看那種執(zhí)行起來不那么占內(nèi)存、快一點。比如下面的 sql 語句：

select * from user u inner join role r on u.id = r.user_id where u.name = "狗哥" and r.id = 666

它可以先從 user 表拿出 name = "狗哥" 記錄的 ID 值再跟 role 表內(nèi)連接查詢，再判斷 role 表里面 id 的值是否 = 666

也可以反過來：先從 role 表拿出 id = 666 記錄的 ID 值再跟 user 表內(nèi)連接查詢，在判斷 user 表里面的 name 值是否 = "狗哥"。

兩種方案的執(zhí)行結(jié)果是一樣的，但是效率不一樣、占用的資源也就不一樣。優(yōu)化器就是在選擇執(zhí)行的方案。它優(yōu)化的是索引應(yīng)該用哪個？多表聯(lián)查應(yīng)該先查哪個表？怎么連接等等。

1.5 執(zhí)行器

分析器知道了做啥、優(yōu)化器知道了應(yīng)該怎么做。接下來就交給執(zhí)行器去執(zhí)行了。

開始執(zhí)行，判斷是否有相應(yīng)的權(quán)限。比如該賬戶對 user 表沒權(quán)限就返回?zé)o權(quán)限的錯誤，如下所示：

select * from user where id = 1; ERROR 1142 (42000): SELECT command denied to user 'nasus'@'localhost' for table 'user'

PS：如果命中緩存沒走到執(zhí)行器這里，那么在返回查詢結(jié)果時做權(quán)限驗證。

回到正題，如果有權(quán)限，繼續(xù)打開表執(zhí)行。執(zhí)行器會根據(jù)表定義的引擎去使用對應(yīng)接口。比如我們上面的 sql 語句執(zhí)行流程是這樣的：

• 走 id 索引、調(diào)用 InnoDB 引擎取"滿足條件的第一行"接口，再循環(huán)調(diào)用"滿足條件的下一行"接口（這些接口都是存儲引擎定義好的），直到表中不再有滿足條件的行。執(zhí)行器就將上述遍歷得到的行組成結(jié)果集返回給客戶端。

• 對于 id 不是索引的表，執(zhí)行器只能調(diào)用"取表記錄的第一行"接口，再判斷 id 是否 = 1。如果不是則跳過，是則存在結(jié)果集中；再調(diào)存儲引擎接口取"下一行"，重復(fù)判斷邏輯，直到表的最后一行。

至此，整個 SQL 的執(zhí)行流程完畢，

推薦學(xué)習(xí)：mysql視頻教程