mysql學習之select查詢語句到底是怎么執行的？

本篇文章給大家帶來了關于mysql中select語句執行的相關知識，其中包括了連接器、分析器、優化器和執行器，希望對大家有幫助。

mysql 作為一個關系型數據庫，在國內使用應該是最廣泛的。也許你司使用 Oracle、Pg 等等，但是大多數互聯網公司，比如我司使用得最多的還是 Mysql，重要性不言而喻。

執行select * from table，數據庫底層到底發生了啥？從而我們得到數據呢？

假設現在我有張 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;

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

select * from user where id = 1;

01 mysql 架構概覽

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

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

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

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

1.1 連接器

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

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

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

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

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

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

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

1.1.1 查看連接狀態

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

1.1.2 控制連接

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

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

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

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

• mySQL 5.7 或以上版本，可以在每次執行一個占用內存大的操作后，執行mysql_reset_connection來重新連接資源，此時不需重連或重新做權限認證，但會把連接狀態恢復到剛創建完時。

1.2 查詢緩存

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

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

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

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

select SQL_CACHE * from user where id = 1;

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

1.3 分析器

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

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

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

1.4 優化器

過了分析器，就來到了優化器。MySQL 是個聰明的仔，再執行之前會自己優化下客戶端傳過來的語句，看看那種執行起來不那么占內存、快一點。比如下面的 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 表內連接查詢，再判斷 role 表里面 id 的值是否 = 666

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

兩種方案的執行結果是一樣的，但是效率不一樣、占用的資源也就不一樣。優化器就是在選擇執行的方案。它優化的是索引應該用哪個？多表聯查應該先查哪個表？怎么連接等等。

1.5 執行器

分析器知道了做啥、優化器知道了應該怎么做。接下來就交給執行器去執行了。

開始執行，判斷是否有相應的權限。比如該賬戶對 user 表沒權限就返回無權限的錯誤，如下所示：

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

PS：如果命中緩存沒走到執行器這里，那么在返回查詢結果時做權限驗證。

回到正題，如果有權限，繼續打開表執行。執行器會根據表定義的引擎去使用對應接口。比如我們上面的 sql 語句執行流程是這樣的：

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

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

至此，整個 SQL 的執行流程完畢，

推薦學習：mysql視頻教程