腳本之家服務器常用軟件

快捷導航

MySQL中一條查詢SQL語句的完整執(zhí)行流程

更新時間：2024年05月08日 08:51:56 作者：隨機的未知

通常我們在使用MySQL時,我們看到的只是輸入一條語句,返回一個結果,卻不知道這條語句在MySQL內部的執(zhí)行過程,這篇文章主要給大家介紹了關于MySQL中一條查詢SQL語句的完整執(zhí)行流程,需要的朋友可以參考下

表結構和數據如下：

我們分析的sql語句如下：

select tb_id,tb_name,tb_address from tb_user where tb_id = 66;

大體來說，MySQL可以分為Server層和存儲引擎層兩部分:

Server層

包括:連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等
涵蓋MySQL的大多數核心服務功能
所有的內置函數(如日期、時間、數學和加密函數等），所有跨存儲引擎的功能都在這一層實現
- 比如:存儲過程、觸發(fā)器、視圖等
存儲引擎層:
- 負責數據的存儲和提取
- 可插拔式存儲引擎:InnoDB、MyISAM、Memory等
  - 最常用存儲引擎是InhoDB
  - 從MySQL 5.5版本開始，默認存儲引擎是lnnoDB

第一步:連接到數據庫

首先會連接到這個數據庫上，這時候接待我們的就是連接器。

mysql -uroot -p

連接完成后，如果沒有后續(xù)的動作，這個連接就處于空閑狀態(tài)?？蛻舳巳绻L時間沒動靜，連接器就會自動將它斷開。這個時間是由參數wait_timeout控制的，默認值是8小時。

show processlist;

其中的 Command 列顯示為“Sleep”的這一行，就表示現在系統里面有一個空閑連接。

第二步:查緩存

MySQL拿到一個查詢請求后，會先到查詢緩存看看，之前是不是執(zhí)行過這條語句。之前執(zhí)行過的語句及其結果可能會以key-value對的形式，被直接緩存在內存中。key是查詢的語句hash之后的值，value是查詢的結果。

如果查詢語句在緩存中，會被直接返回給客戶端。
如果語句不在查詢緩存中，就會繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后，執(zhí)行結果會被存入查詢緩存中。

如果查詢命中緩存，MySQL不需要執(zhí)行后面的復雜操作就可以直接返回結果，效率會很高!但是不建議使用MySQL的內置緩存功能。

查詢緩存

查詢緩存默認是關閉的狀態(tài)。

# 查看是否開啟緩存
show variables like 'query_cache_type';
# 查看緩存的命中次數：
show status like 'qcache_hits';

開啟緩存

在/etc/my.cnf文件中修改“query_cache_type”參數

值為`0或OFF`會禁止使用緩存。
值為`1或ON`將啟用緩存，但以`SELECT SQL_NO_CACHE`開頭的語句除外。
值為`2或DEMAND`時，只緩存以`SELECT SQL_CACHE`開頭的語句。

清空查詢緩存

可以使用下面三個SQL來清理查詢緩存：

# 清理查詢緩存內存碎片。
FLUSH QUERY CACHE; 
# 從查詢緩存中移出所有查詢。
RESET QUERY CACHE;
# 關閉所有打開的表，同時該操作將會清空查詢緩存中的內容。
FLUSH TABLES;

不建議使用MySQL的查詢緩存

因為查詢緩存往往弊大于利

成本高:查詢緩存的失效非常頻繁，只要有對一個表的更新，這個表上所有的查詢緩存都會被清空。因此很可能你費勁地把結果存起來，還沒使用呢，就被一個更新全清空了。
命中率不高:對于更新壓力大的數據庫來說，查詢緩存的命中率會非常低。除非你的業(yè)務就是有一張靜態(tài)表，很長時間才會更新一次。比如，一個系統配置表，那這張表上的查詢才適合使用查詢緩存。
功能并不如專業(yè)的緩存工具更好:redis、memcache、ehcache…
MySQL提供了按需使用的方式，我們可以將參數query_cache_type設置成DEMAND，這樣對于默認的SQL語句都不使用查詢緩存。而對于你確定要使用查詢緩存的語句，可以用SQL_CACHE顯式指定，像下面這個語句一樣:
```
select sql_cache * from tb_user where tb_id = 16;
```

MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了。

第三步:分析SQL語句

如果查詢緩存沒有命中，接下來就需要進入正式的查詢階段了。客戶端程序發(fā)送過來的請求，實際上只是一個字符串而已，所以MySQL服務器程序首先需要對這個字符串做分析，判斷請求的語法是否正確，然后從字符串中將要查詢的表、列和各種查詢條件都提取出來，本質上是對一個SQL語句編譯的過程，涉及詞法解析、語法分析、預處理器等。

詞法分析:詞法分析就是把一個完整的SQL語句分割成一個個的字符串；
語法分析:語法分析器根據詞法分析的結果做語法檢查，判斷你輸入的SQL語句是否滿足MySQL語法；
預處理器:預處理器則會進一步去檢查解析樹是否合法，比如表名是否存在，語句中表的列是否存在等等，在這一步MySQL會檢驗用戶是否有表的操作權限。

詞法分析

比如我們前文所提到的sql語句，分割前為：

select tb_id,tb_name,tb_address from tb_user where tb_id = 66;

分割后為

select,
tb_id,
tb_name,
tb_address,
from,
tb_user,
where,
tb_id,
=,
66
;

MySQL同時需要識別出這個SQL語句的字符串分別是什么，代表什么。

把select關鍵字識別出來，是查詢語句；
把tb_user識別出來是表名tb_user；
…

語法分析

如果語法正確就會根據MySQL語法規(guī)則與SQL語句生成一個數據結構——解析樹；

如果我們把from寫成form；

會報出如下錯誤：

我們前面的SQL語句，生成解析樹如下：

select tb_id,tb_name,tb_address from tb_user where tb_id = 66;

預處理器

預處理器會進一步去檢查解析樹是否合法，比如表名是否存在，語句中表的列是否存在等等，在這一步MySQL會檢驗用戶是否有表的操作權限。

預處理之后會得到一個新的解析樹，然后調用對應執(zhí)行模塊。

第四步:優(yōu)化SQL語句

優(yōu)化器顧名思義就是對查詢進行優(yōu)化。作用是根據解析樹生成不同的執(zhí)行計劃，然后選擇最優(yōu)的執(zhí)行計劃。

MySQL里面使用的是基于成本模型的優(yōu)化器，哪種執(zhí)行計劃Explain執(zhí)行時成本最小就用哪種。而且它是io_cost和cpu_cost的開銷總和，它通常也是我們評價一個查詢的執(zhí)行效率的一個常用指標。

show status like 'Last_query_cost';

查看上次查詢成本開銷，默認值是0

優(yōu)化器可以做的優(yōu)化有:

當有多個索引可用的時候，決定使用哪個索引；
在一個語句有多表關聯（join）的時候，決定各個表的連接順序，以哪個表為基準表。

優(yōu)化器最多是輔助，作用很有限，我們的SQL語句不能依賴于MySQL的優(yōu)化器去調優(yōu)！

第五步:執(zhí)行SQL語句

判斷執(zhí)行權限

開始執(zhí)行的時候，要先判斷一下對這表tb_user有沒有執(zhí)行查詢的權限，如果沒有權限，就會返回無權限的錯誤。

比如：我們新建一個用戶hello_user，只有庫sjdwz_test表tab_test的查詢權限，沒有表tb_user的查詢權限。

CREATE USER `hello_user`@`localhost` IDENTIFIED BY '7654321@Hello';
GRANT Select ON TABLE `sjdwz_test`.`tab_test` TO `hello_user`@`localhost`;

使用這個用戶hello_user連接mysql，

mysql -uhello_user -p

執(zhí)行下面的查詢語句，就會返回沒有權限的錯誤

select tb_id,tb_name,tb_address from tb_user where tb_id = 66;

調用存儲引擎接口查詢

如果有權限，就使用指定的存儲引擎打開表開始查詢。執(zhí)行器會根據表的引擎定義，去使用這個引擎提供的查詢接口提取數據。

tb_id是主鍵執(zhí)行流程:
- 調用InnoDB引擎接口，從主鍵索引中檢索c_id=14的記錄。
- 主鍵索引等值查詢只會查詢出一條記錄，直接將該記錄返回客戶端。
- 至此，這個語句就執(zhí)行完成了。
tb_id不是主鍵執(zhí)行流程:全表掃描
- 調用InnoDB引擎接口取這個表的第一行，判斷tb_id 值是不是66，如果不是則跳過，如果是則將這行緩存在結果集中;
- 調用引擎接口取”下一行"，重復相同的判斷邏輯，直到取到這個表的最后一行。
- 執(zhí)行器將上述遍歷過程中所有滿足條件的行組成的結果集返回給客戶端。
- 至此，這個語句就執(zhí)行完成了。