表結(jié)構(gòu)和數(shù)據(jù)如下：

我們分析的sql語(yǔ)句如下：

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

大體來(lái)說(shuō)，MySQL可以分為Server層和存儲(chǔ)引擎層兩部分:

Server層

• 包括:連接器、查詢(xún)緩存、分析器、優(yōu)化器、執(zhí)行器等

• 涵蓋MySQL的大多數(shù)核心服務(wù)功能

• 所有的內(nèi)置函數(shù)(如日期、時(shí)間、數(shù)學(xué)和加密函數(shù)等），所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn)

  • 比如:存儲(chǔ)過(guò)程、觸發(fā)器、視圖等

• 存儲(chǔ)引擎層:

  • 負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取
  • 可插拔式存儲(chǔ)引擎:InnoDB、MyISAM、Memory等
    • 最常用存儲(chǔ)引擎是InhoDB
    • 從MySQL 5.5版本開(kāi)始，默認(rèn)存儲(chǔ)引擎是lnnoDB

第一步:連接到數(shù)據(jù)庫(kù)

首先會(huì)連接到這個(gè)數(shù)據(jù)庫(kù)上，這時(shí)候接待我們的就是連接器。

mysql -uroot -p

連接完成后，如果沒(méi)有后續(xù)的動(dòng)作，這個(gè)連接就處于空閑狀態(tài)。客戶(hù)端如果太長(zhǎng)時(shí)間沒(méi)動(dòng)靜，連接器就會(huì)自動(dòng)將它斷開(kāi)。這個(gè)時(shí)間是由參數(shù)wait_timeout控制的，默認(rèn)值是8小時(shí)。

show processlist;

其中的 Command 列顯示為“Sleep”的這一行，就表示現(xiàn)在系統(tǒng)里面有一個(gè)空閑連接。

第二步:查緩存

MySQL拿到一個(gè)查詢(xún)請(qǐng)求后，會(huì)先到查詢(xún)緩存看看，之前是不是執(zhí)行過(guò)這條語(yǔ)句。之前執(zhí)行過(guò)的語(yǔ)句及其結(jié)果可能會(huì)以key-value對(duì)的形式，被直接緩存在內(nèi)存中。key是查詢(xún)的語(yǔ)句hash之后的值，value是查詢(xún)的結(jié)果。

• 如果查詢(xún)語(yǔ)句在緩存中，會(huì)被直接返回給客戶(hù)端。

• 如果語(yǔ)句不在查詢(xún)緩存中，就會(huì)繼續(xù)后面的執(zhí)行階段。執(zhí)行完成后，執(zhí)行結(jié)果會(huì)被存入查詢(xún)緩存中。

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

查詢(xún)緩存

查詢(xún)緩存默認(rèn)是關(guān)閉的狀態(tài)。

# 查看是否開(kāi)啟緩存
show variables like 'query_cache_type';
# 查看緩存的命中次數(shù)：
show status like 'qcache_hits';

開(kāi)啟緩存

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

值為`0或OFF`會(huì)禁止使用緩存。
值為`1或ON`將啟用緩存，但以`SELECT SQL_NO_CACHE`開(kāi)頭的語(yǔ)句除外。
值為`2或DEMAND`時(shí)，只緩存以`SELECT SQL_CACHE`開(kāi)頭的語(yǔ)句。

清空查詢(xún)緩存

可以使用下面三個(gè)SQL來(lái)清理查詢(xún)緩存：

# 清理查詢(xún)緩存內(nèi)存碎片。
FLUSH QUERY CACHE; 
# 從查詢(xún)緩存中移出所有查詢(xún)。
RESET QUERY CACHE;
# 關(guān)閉所有打開(kāi)的表，同時(shí)該操作將會(huì)清空查詢(xún)緩存中的內(nèi)容。
FLUSH TABLES; 

不建議使用MySQL的查詢(xún)緩存

因?yàn)椴樵?xún)緩存往往弊大于利

• 成本高:查詢(xún)緩存的失效非常頻繁，只要有對(duì)一個(gè)表的更新，這個(gè)表上所有的查詢(xún)緩存都會(huì)被清空。因此很可能你費(fèi)勁地把結(jié)果存起來(lái)，還沒(méi)使用呢，就被一個(gè)更新全清空了。

• 命中率不高:對(duì)于更新壓力大的數(shù)據(jù)庫(kù)來(lái)說(shuō)，查詢(xún)緩存的命中率會(huì)非常低。除非你的業(yè)務(wù)就是有一張靜態(tài)表，很長(zhǎng)時(shí)間才會(huì)更新一次。比如，一個(gè)系統(tǒng)配置表，那這張表上的查詢(xún)才適合使用查詢(xún)緩存。

• 功能并不如專(zhuān)業(yè)的緩存工具更好:redis、memcache、ehcache…
  MySQL提供了按需使用的方式，我們可以將參數(shù)query_cache_type設(shè)置成DEMAND，這樣對(duì)于默認(rèn)的SQL語(yǔ)句都不使用查詢(xún)緩存。而對(duì)于你確定要使用查詢(xún)緩存的語(yǔ)句，可以用SQL_CACHE顯式指定，像下面這個(gè)語(yǔ)句一樣:

  select sql_cache * from tb_user where tb_id = 16;
  

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

第三步:分析SQL語(yǔ)句

如果查詢(xún)緩存沒(méi)有命中，接下來(lái)就需要進(jìn)入正式的查詢(xún)階段了?？蛻?hù)端程序發(fā)送過(guò)來(lái)的請(qǐng)求，實(shí)際上只是一個(gè)字符串而已，所以MySQL服務(wù)器程序首先需要對(duì)這個(gè)字符串做分析，判斷請(qǐng)求的語(yǔ)法是否正確，然后從字符串中將要查詢(xún)的表、列和各種查詢(xún)條件都提取出來(lái)，本質(zhì)上是對(duì)一個(gè)SQL語(yǔ)句編譯的過(guò)程，涉及詞法解析、語(yǔ)法分析、預(yù)處理器等。

• 詞法分析:詞法分析就是把一個(gè)完整的SQL語(yǔ)句分割成一個(gè)個(gè)的字符串；
• 語(yǔ)法分析:語(yǔ)法分析器根據(jù)詞法分析的結(jié)果做語(yǔ)法檢查，判斷你輸入的SQL語(yǔ)句是否滿(mǎn)足MySQL語(yǔ)法；
• 預(yù)處理器:預(yù)處理器則會(huì)進(jìn)一步去檢查解析樹(shù)是否合法，比如表名是否存在，語(yǔ)句中表的列是否存在等等，在這一步MySQL會(huì)檢驗(yàn)用戶(hù)是否有表的操作權(quán)限。

詞法分析

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

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同時(shí)需要識(shí)別出這個(gè)SQL語(yǔ)句的字符串分別是什么，代表什么。

• 把select關(guān)鍵字識(shí)別出來(lái)，是查詢(xún)語(yǔ)句；
• 把tb_user識(shí)別出來(lái)是表名tb_user；
• …

語(yǔ)法分析

如果語(yǔ)法正確就會(huì)根據(jù)MySQL語(yǔ)法規(guī)則與SQL語(yǔ)句生成一個(gè)數(shù)據(jù)結(jié)構(gòu)——解析樹(shù)；

如果我們把from寫(xiě)成form；

會(huì)報(bào)出如下錯(cuò)誤：

我們前面的SQL語(yǔ)句，生成解析樹(shù)如下：

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

預(yù)處理器

預(yù)處理器會(huì)進(jìn)一步去檢查解析樹(shù)是否合法，比如表名是否存在，語(yǔ)句中表的列是否存在等等，在這一步MySQL會(huì)檢驗(yàn)用戶(hù)是否有表的操作權(quán)限。

預(yù)處理之后會(huì)得到一個(gè)新的解析樹(shù)，然后調(diào)用對(duì)應(yīng)執(zhí)行模塊。

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

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

MySQL里面使用的是基于成本模型的優(yōu)化器，哪種執(zhí)行計(jì)劃Explain執(zhí)行時(shí)成本最小就用哪種。而且它是io_cost和cpu_cost的開(kāi)銷(xiāo)總和，它通常也是我們?cè)u(píng)價(jià)一個(gè)查詢(xún)的執(zhí)行效率的一個(gè)常用指標(biāo)。

show status like 'Last_query_cost';

查看上次查詢(xún)成本開(kāi)銷(xiāo)，默認(rèn)值是0

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

• 當(dāng)有多個(gè)索引可用的時(shí)候，決定使用哪個(gè)索引；
• 在一個(gè)語(yǔ)句有多表關(guān)聯(lián)（join）的時(shí)候，決定各個(gè)表的連接順序，以哪個(gè)表為基準(zhǔn)表。

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

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

判斷執(zhí)行權(quán)限

開(kāi)始執(zhí)行的時(shí)候，要先判斷一下對(duì)這表tb_user有沒(méi)有執(zhí)行查詢(xún)的權(quán)限，如果沒(méi)有權(quán)限，就會(huì)返回?zé)o權(quán)限的錯(cuò)誤。

比如：我們新建一個(gè)用戶(hù)hello_user，只有庫(kù)sjdwz_test表tab_test的查詢(xún)權(quán)限，沒(méi)有表tb_user的查詢(xún)權(quán)限。

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

使用這個(gè)用戶(hù)hello_user連接mysql，

mysql -uhello_user -p

執(zhí)行下面的查詢(xún)語(yǔ)句，就會(huì)返回沒(méi)有權(quán)限的錯(cuò)誤

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

調(diào)用存儲(chǔ)引擎接口查詢(xún)

如果有權(quán)限，就使用指定的存儲(chǔ)引擎打開(kāi)表開(kāi)始查詢(xún)。執(zhí)行器會(huì)根據(jù)表的引擎定義，去使用這個(gè)引擎提供的查詢(xún)接口提取數(shù)據(jù)。

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

總結(jié) 

到此這篇關(guān)于MySQL中一條查詢(xún)SQL語(yǔ)句的完整執(zhí)行流程的文章就介紹到這了,更多相關(guān)MySQL一條查詢(xún)SQL語(yǔ)句內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！