MySQL作為互聯(lián)網(wǎng)行業(yè)使用最多的關(guān)系型數(shù)據(jù)庫(kù)之一，與其免費(fèi)、開源的特性是密不可分的。然而，很多小伙伴工作了很多年，只知道使用MySQL進(jìn)行CRUD操作，這也導(dǎo)致很多小伙伴工作多年后，想跳槽進(jìn)入大廠，卻在面試的時(shí)候?qū)覍遗霰凇?/p>

問個(gè)簡(jiǎn)單的問題：select語(yǔ)句是如何在MySQL中執(zhí)行的？ 這也是很多面試官喜歡問的問題，如果你連這個(gè)簡(jiǎn)單的問題都不能回答的話，那就要好好規(guī)劃下自己的職業(yè)生涯了。

好了，今天我們就一起來(lái)聊聊select語(yǔ)句是如何在MySQL中執(zhí)行的。文章的主要內(nèi)容如下。

圖片

頻繁使用的select語(yǔ)句

為了更好地貫穿全文，這里先來(lái)列舉一個(gè)最簡(jiǎn)單的select查詢語(yǔ)句，例如：查詢user表中id為1001的用戶信息，使用下面的SQL語(yǔ)句進(jìn)行查詢。

select * from user where user_id = 1001;

當(dāng)我們?cè)贛ySQL的命令行中輸入上述SQL語(yǔ)句時(shí)，這條SQL語(yǔ)句到底在MySQL中是如何執(zhí)行的呢？接下來(lái)，我們就以這條SQL語(yǔ)句為例，說(shuō)說(shuō)select語(yǔ)句是如何在MySQL中執(zhí)行的。

MySQL邏輯架構(gòu)

在介紹select語(yǔ)句在MySQL中的執(zhí)行流程之前，我們先來(lái)看看MySQL的邏輯架構(gòu)，因?yàn)槿魏蜸QL語(yǔ)句的執(zhí)行都離不開MySQL邏輯架構(gòu)的支撐。也就是說(shuō)，SQL語(yǔ)句在MySQL中的執(zhí)行流程與MySQL的邏輯架構(gòu)是密不可分的。

圖片

在上圖中，我們簡(jiǎn)單的畫了下MySQL的邏輯架構(gòu)圖，并且給出了邏輯分層和每層中各部分的功能。從邏輯上，我們可以將MySQL粗略地分成三層：Server層、存儲(chǔ)引擎層和系統(tǒng)文件層，而Server層中又可以分成網(wǎng)絡(luò)連接層（連接器）和數(shù)據(jù)服務(wù)層（Server層）。

Server層中包含了連接器、查詢緩存、分析器、優(yōu)化器和執(zhí)行器等MySQL的核心組成部分，另外，在Server層中還包含了所有的內(nèi)置函數(shù)（比如：日期時(shí)間函數(shù)、加解密函數(shù)、聚合函數(shù)、數(shù)學(xué)函數(shù)等），存儲(chǔ)引擎、觸發(fā)器、視圖等等。

存儲(chǔ)引擎層主要負(fù)責(zé)和系統(tǒng)文件層進(jìn)行交互，存儲(chǔ)引擎層本身是插件式的架構(gòu)設(shè)計(jì)，支持InnoDB、MyISAM、Archive、Memory等存儲(chǔ)引擎。在MySQL 5.5.5及以后的版本中，MySQL的默認(rèn)存儲(chǔ)引擎是InnoDB。

系統(tǒng)文件層主要負(fù)責(zé)存儲(chǔ)實(shí)際的數(shù)據(jù)，將數(shù)據(jù)以文件的形式存儲(chǔ)到服務(wù)器的磁盤上。

接下來(lái)，我們就來(lái)說(shuō)說(shuō)一條select語(yǔ)句在MySQL的邏輯架構(gòu)的每一部分到底是如何執(zhí)行的。

連接器是如何授權(quán)的？

首先，我們先來(lái)看看在服務(wù)器命令行輸入連接MySQL的命令時(shí)，MySQL的連接器是如何進(jìn)行驗(yàn)證的。比如，我們?cè)诜?wù)器的命令行輸入了如下命令。

mysql -ubinghe -p

執(zhí)行“回車”后，輸入binghe賬戶的密碼，與MySQL進(jìn)行連接。此時(shí)，連接的過(guò)程需要完成經(jīng)典的TCP握手操作（有關(guān)TCP的握手相關(guān)的知識(shí)，小伙伴們可以參考《【面經(jīng)】面試官：講講七層網(wǎng)絡(luò)模型與TCP三次握手與四次斷開？》）。之后，連接器就開始認(rèn)證連接的身份是否合法，最直接的就是驗(yàn)證用戶名和密碼是否正確。

如果用戶名或者密碼錯(cuò)誤，MySQL會(huì)提示 Access denied for user。如果用戶名和密碼正確，則連接器會(huì)到MySQL的權(quán)限表中查詢當(dāng)前連接擁有的權(quán)限。查詢到權(quán)限之后，只要這個(gè)連接沒有斷開，則這個(gè)連接涉及到的權(quán)限操作都會(huì)依賴此時(shí)查詢到的權(quán)限。

換句話說(shuō)，一個(gè)用戶登錄MySQL并成功連接MySQL后，哪怕是管理員對(duì)當(dāng)前用戶的權(quán)限進(jìn)行了修改操作，此時(shí)只要這個(gè)用戶沒有斷開MySQL的連接，就不會(huì)受到管理修改權(quán)限的影響。管理員修改權(quán)限后，只有對(duì)新建的連接起作用。

如果客戶端連接MySQL后，長(zhǎng)時(shí)間沒有執(zhí)行任何操作，則連接器會(huì)自動(dòng)斷開與這個(gè)客戶端的連接。具體多長(zhǎng)時(shí)間斷開是由MySQL的參數(shù)wait_timeout控制的，這個(gè)值默認(rèn)是8小時(shí)。我們可以根據(jù)實(shí)際業(yè)務(wù)需要，自行調(diào)整這個(gè)參數(shù)的值，以使MySQL能夠滿足我們的實(shí)際業(yè)務(wù)場(chǎng)景。

由于客戶端與MySQL的連接是比較復(fù)雜的，這個(gè)過(guò)程也是比較耗時(shí)的，它會(huì)涉及TCP的握手操作，還會(huì)查詢當(dāng)前連接的權(quán)限信息等。往往在實(shí)際的工作過(guò)程中，我們會(huì)使用數(shù)據(jù)庫(kù)連接池的方式，將數(shù)據(jù)庫(kù)的連接緩存起來(lái)，這就意味著我們是使用長(zhǎng)連接與MySQL進(jìn)行交互的。

但是使用長(zhǎng)連接連接MySQL也會(huì)有一個(gè)問題：那就是有時(shí)候會(huì)發(fā)現(xiàn)MySQL占用的內(nèi)存漲得特別快，這是因?yàn)?strong>MySQL在執(zhí)行的過(guò)程中，使用的臨時(shí)內(nèi)存是在連接對(duì)象里面進(jìn)行管理的。這些占用的資源只有在連接斷開的時(shí)候，才會(huì)被釋放。如果連接長(zhǎng)時(shí)間不釋放，就會(huì)出現(xiàn)大量的臨時(shí)內(nèi)存占用內(nèi)存空間。如果時(shí)間久了，可能會(huì)導(dǎo)致占用過(guò)多的內(nèi)存，從而被操作系統(tǒng)“消滅”了，給人的感覺就是MySQL意外重啟了。

我們可以使用如下的方案來(lái)解決這個(gè)問題：

• 定期或者執(zhí)行過(guò)一個(gè)比較占內(nèi)存的查詢操作后，斷開連接，以后再重新建立和MySQL的連接。
• 如果使用MySQL 5.7或更新的MySQL版本，可以通過(guò)執(zhí)行mysql_reset_connection重新初始化MySQL的資源。重新初始化的過(guò)程不會(huì)重新連接MySQL，也不會(huì)重新做權(quán)限的驗(yàn)證操作。

查詢緩存的作用是什么？

登錄MySQL后，客戶端就會(huì)與MySQL建立連接，此時(shí)執(zhí)行select語(yǔ)句時(shí)，首先會(huì)到查詢緩存中查詢是否執(zhí)行過(guò)當(dāng)前select語(yǔ)句。如果之前執(zhí)行過(guò)相應(yīng)的select語(yǔ)句，則執(zhí)行過(guò)的select語(yǔ)句和查詢結(jié)果會(huì)以key-value的形式存放在查詢緩存中，其中，key是查詢語(yǔ)句，value是查詢的結(jié)果數(shù)據(jù)。

如果在查詢緩存中沒有找到相應(yīng)的數(shù)據(jù)，則會(huì)繼續(xù)執(zhí)行后續(xù)的查詢階段。執(zhí)行完成后，會(huì)將結(jié)果緩存到查詢緩存中。后續(xù)的查詢?nèi)绻芯彺?，則直接返回查詢緩存中的數(shù)據(jù)，性能還是挺高的。

但是，大多數(shù)時(shí)候我不太建議小伙伴們開啟查詢緩存，為啥？原因很簡(jiǎn)單：查詢緩存失效的頻率是非常頻繁的，只要對(duì)一個(gè)表進(jìn)行更新操作，則這張表上所有的查詢緩存都會(huì)被清空。 而且在MySQL 8.0中，直接刪除了查詢緩存的功能（冰河在看MySQL源碼時(shí)，也證明了這一點(diǎn)）。

分析器對(duì)select語(yǔ)句做了什么？

分析器主要是對(duì)select語(yǔ)句進(jìn)行 詞法分析和語(yǔ)法分析 操作。

如果select語(yǔ)句沒有命中緩存，則首先會(huì)由分析器對(duì)其進(jìn)行“詞法分析”操作，此時(shí)，MySQL會(huì)識(shí)別select語(yǔ)句中的每個(gè)字符串代表什么含義。

例如，MySQL會(huì)通過(guò)"select"關(guān)鍵字識(shí)別出這是一個(gè)查詢語(yǔ)句，也會(huì)把"user"識(shí)別為"數(shù)據(jù)表名user"，把"id"識(shí)別成"字段名id"。接下來(lái)，就要進(jìn)行“語(yǔ)法分析了”，根據(jù)語(yǔ)法規(guī)則，判斷select語(yǔ)句是否滿足MySQL的語(yǔ)法。如果判斷出輸入的SQL語(yǔ)句不滿足語(yǔ)法規(guī)則，則MySQL會(huì)提示相應(yīng)的錯(cuò)誤信息。

優(yōu)化器是如何優(yōu)化select語(yǔ)句的？

對(duì)select語(yǔ)句進(jìn)行了詞法分析和語(yǔ)法分析后，還要經(jīng)過(guò)優(yōu)化器的優(yōu)化處理才能執(zhí)行。比如，我們的select語(yǔ)句中如果使用了多個(gè)索引，則優(yōu)化器會(huì)決定使用哪個(gè)索引來(lái)查詢數(shù)據(jù)；再比如，在select語(yǔ)句中，有多表關(guān)聯(lián)的操作，優(yōu)化器會(huì)決定各表的連接順序，數(shù)據(jù)表的連接順序不同，對(duì)于執(zhí)行的效率會(huì)大不相同，優(yōu)化器往往會(huì)選擇使用查詢效率高的連接順序。

如果select語(yǔ)句經(jīng)過(guò)優(yōu)化器的優(yōu)化之后，就會(huì)進(jìn)入執(zhí)行階段了。

執(zhí)行器如何執(zhí)行select語(yǔ)句？

進(jìn)入執(zhí)行階段的select語(yǔ)句，首先，執(zhí)行器會(huì)對(duì)當(dāng)前連接進(jìn)行權(quán)限檢查，最直接的方式就是檢查當(dāng)前連接是否對(duì)數(shù)據(jù)表user具有查詢權(quán)限。如果當(dāng)前連接對(duì)數(shù)據(jù)表user沒有查詢權(quán)限，就會(huì)返回沒有權(quán)限的錯(cuò)誤。例如，會(huì)返回如下錯(cuò)誤。

ERROR 1142 (42000): SELECT command denied to user 'binghe'@'localhost' for table 'user'

如果當(dāng)前連接具有對(duì)數(shù)據(jù)表user的查詢權(quán)限，則會(huì)繼續(xù)執(zhí)行。首先會(huì)進(jìn)行打開數(shù)據(jù)表的操作，此時(shí)優(yōu)化器會(huì)根據(jù)創(chuàng)建表時(shí)使用的存儲(chǔ)引擎，使用相應(yīng)存儲(chǔ)引擎的接口執(zhí)行查詢操作。這里，我們舉一個(gè)例子：

假設(shè)，我們?cè)趇d字段上沒有建立索引，執(zhí)行器執(zhí)行的流程大致如下所示。

（1）通過(guò)存儲(chǔ)引擎讀取數(shù)據(jù)表user的第一行數(shù)據(jù)，判斷當(dāng)前行的id值是否等于1001，如果不等于1001，則繼續(xù)讀取下一行數(shù)據(jù)；如果等于1001，則將當(dāng)前行放入結(jié)果集中。

（2）繼續(xù)通過(guò)存儲(chǔ)引擎讀取下一行數(shù)據(jù)，執(zhí)行與（1）相同的邏輯判斷，直到處理完user表中的所有數(shù)據(jù)。

（3）處理完所有的數(shù)據(jù)后，執(zhí)行器就會(huì)將結(jié)果集中的數(shù)據(jù)返回給客戶端。

如果在id字段上有索引的話，執(zhí)行的整體邏輯與id字段上沒有索引大體一致。

如果開啟了慢查詢的話，執(zhí)行select語(yǔ)句時(shí)，會(huì)在慢查詢?nèi)罩局休敵鲆粋€(gè)rows_examined字段，這個(gè)字段表示select語(yǔ)句在執(zhí)行的過(guò)程中掃描了數(shù)據(jù)表中的多少行數(shù)據(jù)。不過(guò)在有些場(chǎng)景下，執(zhí)行器調(diào)用一次，存儲(chǔ)引擎內(nèi)部會(huì)會(huì)掃描多行，這就導(dǎo)致存儲(chǔ)引擎掃描的行數(shù)與rows_examined字段標(biāo)識(shí)的行數(shù)并不完全相同。