前言

日常開(kāi)發(fā)中，我們經(jīng)常需要寫(xiě)查詢SQL。但是，大家知道一條查詢SQL在mysql內(nèi)部是如何執(zhí)行的嘛？比如這條簡(jiǎn)單的SQL:

select*fromtest_db.user_info_tabwhereuser_id=123;

我們知道在mySQL客戶端，輸入一條查詢SQL,然后看到返回查詢的結(jié)果。這條查詢語(yǔ)句在 MySQL 內(nèi)部到底是如何執(zhí)行的呢？本文跟大家探討一下哈，我們先來(lái)看下MySQL基本架構(gòu)~

MySQL 基本架構(gòu)

總體來(lái)說(shuō)，MySQL大體分為兩部分，分別是Server 層和存儲(chǔ)引擎層。

Server 層

它包括連接器、查詢緩存、分析器、優(yōu)化器、執(zhí)行器等。比如存儲(chǔ)過(guò)程，觸發(fā)器，視圖都是在這一層實(shí)現(xiàn)的。

連接器（Connection Manager）：負(fù)責(zé)處理客戶端與服務(wù)器之間的連接。它接受來(lái)自客戶端的請(qǐng)求，并進(jìn)行身份驗(yàn)證和權(quán)限檢查，建立和管理連接。

查詢緩存（Query Cache）：在舊版 MySQL 中有，但在較新的版本中已不推薦使用。它能夠緩存查詢和對(duì)應(yīng)的結(jié)果，以提高查詢性能。然而，在高并發(fā)和大型數(shù)據(jù)庫(kù)中，它反而可能成為性能瓶頸，因?yàn)樗谀承┣闆r下會(huì)引起鎖和不必要的開(kāi)銷。

分析器（Parser）：負(fù)責(zé)分析 SQL 查詢語(yǔ)句，驗(yàn)證其語(yǔ)法和語(yǔ)義，確保查詢的正確性。它將 SQL 語(yǔ)句轉(zhuǎn)換成內(nèi)部數(shù)據(jù)結(jié)構(gòu)供優(yōu)化器和執(zhí)行器使用。

優(yōu)化器（Optimizer）：接收來(lái)自分析器的查詢請(qǐng)求，并決定如何最有效地執(zhí)行查詢。優(yōu)化器的目標(biāo)是找到最佳的執(zhí)行路徑，選擇合適的索引、連接順序和訪問(wèn)方法，以提高查詢性能。

執(zhí)行器（Executor）：負(fù)責(zé)執(zhí)行優(yōu)化器生成的執(zhí)行計(jì)劃，獲取存儲(chǔ)引擎返回的數(shù)據(jù)，并處理客戶端請(qǐng)求。它與存儲(chǔ)引擎交互，執(zhí)行查詢并返回結(jié)果給用戶。

存儲(chǔ)引擎層: 它負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取。Mysql支持InnoDB、MyISAM、Memory 等多個(gè)存儲(chǔ)引擎。我們?nèi)粘ｉ_(kāi)發(fā)中，一般用的存儲(chǔ)引擎就是InnoDB。從 MySQL 5.5 版本開(kāi)始，InnoDB 就成為了默認(rèn)的存儲(chǔ)引擎。

介紹完MySQL基本架構(gòu)，帶大家看一下，每個(gè)組件，一條查詢SQL主要做什么事~~

連接器

我們要執(zhí)行查詢SQL，一般在MySQL客戶端, 需要輸入連接命令，連接到MySQL服務(wù)端。在MySQL服務(wù)端，就是連接器負(fù)責(zé)跟你的客戶端建立連接、獲取權(quán)限、維持和管理連接。

連接命令如下：

mysql-h(ip地址)-P(端口)-u(用戶名)-p

輸入完連接命令之后，我們接著輸入正確的密碼，經(jīng)過(guò)經(jīng)典的TCP握手之后，就可以成功連接到MySQL服務(wù)器啦，如下：

C:MySQLMySQLServer8.0in>mysql-h127.0.0.1-P3306-uroot-p
Enterpassword:******
WelcometotheMySQLmonitor.Commandsendwith;org.
YourMySQLconnectionidis50
Serverversion:8.0.31MySQLCommunityServer-GPL

Copyright(c)2000,2022,Oracleand/oritsaffiliates.

OracleisaregisteredtrademarkofOracleCorporationand/orits
affiliates.Othernamesmaybetrademarksoftheirrespective
owners.

Type'help;'or'h'forhelp.Type'c'toclearthecurrentinputstatement.

mysql>

如果輸入密碼錯(cuò)誤，則會(huì)收到一個(gè) Access denied的錯(cuò)誤信息，如下：

C:ProgramFilesMySQLMySQLServer8.0in>mysql-h127.0.0.1-P3306-uroot-p
Enterpassword:*****
ERROR1045(28000):Accessdeniedforuser'root'@'localhost'(usingpassword:YES)

連接成功之后，大家就可以直接輸入查詢SQL，就可以看到結(jié)果啦。

mysql>select*fromtest_db.user_info_tabwhereuser_id=123;
+---------+----------------+------+------+--------+---------+--------------------------+
|id|user_name|age|city|status|user_id|password|
+---------+----------------+------+------+--------+---------+--------------------------+
|1570091|撿田螺的小男孩|28|深圳|活躍|123|523da7ne+yndc5nb1zWWlA==|
+---------+----------------+------+------+--------+---------+--------------------------+
1rowinset(0.01sec)

大家注意一下哈，如果連接成功后，沒(méi)有后續(xù)的輸入查詢SQL等其他操作。這時(shí)候，這個(gè)連接是空閑的哈，可以用show processlist查看。

查詢緩存

在老版本的MySQL中，連接成功后，我們執(zhí)行查詢SQL，會(huì)先執(zhí)行查詢緩存。

也就是說(shuō)MySQL接受到一個(gè)查詢SQL請(qǐng)求時(shí)，會(huì)先去查詢緩存看看，如果緩存有這條SQL的查詢結(jié)果，會(huì)直接返回。如果查詢緩存沒(méi)有，就繼續(xù)往下執(zhí)行，執(zhí)行完之后，把結(jié)果寫(xiě)入緩存。其中，這個(gè)查詢緩存是key-value的結(jié)果，你可以把它理解為一個(gè)map吧，其中key就是這個(gè)查詢SQL，value則是這個(gè)查詢的結(jié)果。

同時(shí)，如果你查詢的表進(jìn)行更新的時(shí)候，會(huì)清空緩存的。一個(gè)表更新比較頻繁的話，使用查詢緩存命中率會(huì)很低，你剛查完放到緩存，更新SQL又清空了，就很不劃算。有些時(shí)候，一些靜態(tài)配置表，很少更新的，才建議使用查詢緩存。其他更新頻繁的表，則不建議使用查詢緩存，你可以通過(guò)這個(gè)參數(shù)query_cache_type 設(shè)值是否走查詢緩存。

其實(shí)，MySQL 比較新的版本，如8.0 已經(jīng)廢棄了查詢緩存，并且相應(yīng)的參數(shù) query_cache_type 也不再存在。因?yàn)樵诟卟l(fā)和大型數(shù)據(jù)庫(kù)環(huán)境下，查詢緩存可能導(dǎo)致性能問(wèn)題，并且在實(shí)際測(cè)試中發(fā)現(xiàn)，禁用查詢緩存可能會(huì)提高整體性能和可伸縮性。

分析器

如果查詢SQL沒(méi)有命中查詢緩存的話，繼續(xù)往下執(zhí)行，就到分析器上場(chǎng)了。它負(fù)責(zé)分析 SQL 查詢語(yǔ)句，驗(yàn)證其語(yǔ)法和語(yǔ)義，確保查詢的正確性。

你扔個(gè)SQL給MySQL服務(wù)器，它肯定需要先解析，才知道這個(gè)SQL是做什么的，對(duì)吧。它會(huì)派出分析器，先做詞法分析。你提交過(guò)來(lái)的查詢SQL是由很多個(gè)字符創(chuàng)和空格組成的，MySQL會(huì)先解析出這些字符串表示什么意思。

select*fromtest_db.user_info_tabwhereuser_id=123;

它先把關(guān)鍵字select解析出來(lái)，然后把user_info_tab解析成表，user_id解析成列名。做完詞法分析之后，開(kāi)始做語(yǔ)法分析。語(yǔ)法分析主要就是判斷，你的SQL是否滿足MYSQL的語(yǔ)法。

如果你的SQL寫(xiě)錯(cuò)了，語(yǔ)法分析就會(huì)報(bào)錯(cuò)誤提示： ERROR 1064 (42000): You have an error in your SQL syntax;

mysql>select*romtest_db.user_info_tabwhereuser_id=123;
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourMySQLserverversionfortherightsyntaxtousenear'romtest_db.user_info_tabwhereuser_id=123'atline1

平時(shí)大家看到這個(gè)錯(cuò)誤的時(shí)候，只需要，關(guān)注關(guān)鍵詞 syntax to use near 就可以快速知道哪里寫(xiě)錯(cuò)啦。比如這個(gè)例子，就是我的from寫(xiě)錯(cuò)了，少了f。

優(yōu)化器

經(jīng)過(guò)分析器之后，MySQL已經(jīng)知道需要做什么了。但是在經(jīng)過(guò)執(zhí)行器之前，還會(huì)先經(jīng)過(guò)優(yōu)化器。優(yōu)化器做的事情就是，怎么去做才是最好的。對(duì)于一條查詢SQL來(lái)說(shuō)就是:怎么去查是最佳效率的。

比如這個(gè)查詢SQL:

select*fromtest_db.user_info_tabwhereuser_id=123anduser_name='田螺';

其中，在user_info_tab表中，user_id為索引字段，user_name也是索引字段。

這條SQL執(zhí)行的時(shí)候，可能使用索引user_id，也可能使用使用user_name。選擇不同的索引，執(zhí)行效率是不一樣的。具體怎么選擇，就是優(yōu)化器所做的事情。

大家是否還記得explain。我們使用它加在我們查詢的SQL，就可以幫助了解優(yōu)化器在執(zhí)行查詢時(shí),選擇的執(zhí)行計(jì)劃和相應(yīng)的優(yōu)化策略。

經(jīng)過(guò)優(yōu)化器之后，就來(lái)到了執(zhí)行器階段。也就是真正執(zhí)行查詢SQL了。

執(zhí)行器

select*fromtest_db.user_info_tabwhereuser_id=123;

在要開(kāi)始執(zhí)行時(shí)候，會(huì)判斷一下，該用戶是否對(duì)這個(gè)SQL有查詢的權(quán)限，如果沒(méi)有，則會(huì)報(bào)權(quán)限錯(cuò)誤。如果有權(quán)限的時(shí)候，打開(kāi)表直接執(zhí)行。執(zhí)行的過(guò)程，其實(shí)類似于執(zhí)行調(diào)用引擎提供的接口。

我們現(xiàn)在假設(shè)user_id不是索引字段，我們使用的是InnoDb存儲(chǔ)引擎，這個(gè)查詢SQL執(zhí)行過(guò)程就是這樣：

調(diào)用InnoDb存儲(chǔ)引擎提供的接口，獲取user_info_tab表的第一行。

判斷user_id是不是為123，如果不是，跳過(guò)這一行。如果是，把這一行放到結(jié)果集。

調(diào)用InnoDb存儲(chǔ)引擎提供的接口，獲取user_info_tab表的下一行。

判斷user_id是不是為123，如果不是，跳過(guò)這一行。如果是，把這一行放到結(jié)果集。

重復(fù)3、4步驟，一直掃描完user_info_tab表的所有行。最后把結(jié)果集返回客戶端。

聊到這里，其實(shí)一條查詢SQL的執(zhí)行過(guò)程，已經(jīng)講完啦，是不是很簡(jiǎn)單呀~~

審核編輯：黃飛