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视频教程

以上是mysql學習之select查詢語句到底是怎麼執行的？的詳細內容。更多資訊請關注PHP中文網其他相關文章！