原來select陳述句在MySQL中是這樣執行的！看完又漲見識了！這回我要碾壓面試官！

大家好，我是冰河~~

MySQL作為互聯網行業使用最多的關系型資料庫之一，與其免費、開源的特性是密不可分的，然而，很多小伙伴作業了很多年，只知道使用MySQL進行CRUD操作，這也導致很多小伙伴作業多年后，想跳槽進入大廠，卻在面試的時候屢屢碰壁，

問個簡單的問題：select陳述句是如何在MySQL中執行的？ 這也是很多面試官喜歡問的問題，如果你連這個簡單的問題都不能回答的話，那就要好好規劃下自己的職業生涯了，

好了，今天我們就一起來聊聊select陳述句是如何在MySQL中執行的，文章的主要內容如下，

頻繁使用的select陳述句

為了更好地貫穿全文，這里先來列舉一個最簡單的select查詢陳述句，例如：查詢user表中id為1001的用戶資訊，使用下面的SQL陳述句進行查詢，

select * from user where user_id = 1001;

當我們在MySQL的命令列中輸入上述SQL陳述句時，這條SQL陳述句到底在MySQL中是如何執行的呢？接下來，我們就以這條SQL陳述句為例，說說select陳述句是如何在MySQL中執行的，

MySQL邏輯架構

在介紹select陳述句在MySQL中的執行流程之前，我們先來看看MySQL的邏輯架構，因為任何SQL陳述句的執行都離不開MySQL邏輯架構的支撐，也就是說，SQL陳述句在MySQL中的執行流程與MySQL的邏輯架構是密不可分的，

在上圖中，我們簡單的畫了下MySQL的邏輯架構圖，并且給出了邏輯分層和每層中各部分的功能，從邏輯上，我們可以將MySQL粗略地分成三層：Server層、存盤引擎層和系統檔案層，而Server層中又可以分成網路連接層（連接器）和資料服務層（Server層），

Server層中包含了連接器、查詢快取、分析器、優化器和執行器等MySQL的核心組成部分，另外，在Server層中還包含了所有的內置函式（比如：日期時間函式、加解密函式、聚合函式、數學函式等），存盤引擎、觸發器、視圖等等，

存盤引擎層主要負責和系統檔案層進行互動，存盤引擎層本身是插件式的架構設計，支持InnoDB、MyISAM、Archive、Memory等存盤引擎，在MySQL 5.5.5及以后的版本中，MySQL的默認存盤引擎是InnoDB，

系統檔案層主要負責存盤實際的資料，將資料以檔案的形式存盤到服務器的磁盤上，

接下來，我們就來說說一條select陳述句在MySQL的邏輯架構的每一部分到底是如何執行的，

連接器是如何授權的？

首先，我們先來看看在服務器命令列輸入連接MySQL的命令時，MySQL的連接器是如何進行驗證的，比如，我們在服務器的命令列輸入了如下命令，

mysql -ubinghe -p

執行“回車”后，輸入binghe賬戶的密碼，與MySQL進行連接，此時，連接的程序需要完成經典的TCP握手操作（有關TCP的握手相關的知識，小伙伴們可以參考《【面經】面試官：講講七層網路模型與TCP三次握手與四次斷開？》），之后，連接器就開始認證連接的身份是否合法，最直接的就是驗證用戶名和密碼是否正確，

如果用戶名或者密碼錯誤，MySQL會提示 Access denied for user，如果用戶名和密碼正確，則連接器會到MySQL的權限表中查詢當前連接擁有的權限，查詢到權限之后，只要這個連接沒有斷開，則這個連接涉及到的權限操作都會依賴此時查詢到的權限，

換句話說，一個用戶登錄MySQL并成功連接MySQL后，哪怕是管理員對當前用戶的權限進行了修改操作，此時只要這個用戶沒有斷開MySQL的連接，就不會受到管理修改權限的影響，管理員修改權限后，只有對新建的連接起作用，

如果客戶端連接MySQL后，長時間沒有執行任何操作，則連接器會自動斷開與這個客戶端的連接，具體多長時間斷開是由MySQL的引數wait_timeout控制的，這個值默認是8小時，我們可以根據實際業務需要，自行調整這個引數的值，以使MySQL能夠滿足我們的實際業務場景，

由于客戶端與MySQL的連接是比較復雜的，這個程序也是比較耗時的，它會涉及TCP的握手操作，還會查詢當前連接的權限資訊等，往往在實際的作業程序中，我們會使用資料庫連接池的方式，將資料庫的連接快取起來，這就意味著我們是使用長連接與MySQL進行互動的，

但是使用長連接連接MySQL也會有一個問題：那就是有時候會發現MySQL占用的記憶體漲得特別快，這是因為MySQL在執行的程序中，使用的臨時記憶體是在連接物件里面進行管理的，這些占用的資源只有在連接斷開的時候，才會被釋放，如果連接長時間不釋放，就會出現大量的臨時記憶體占用記憶體空間，如果時間久了，可能會導致占用過多的記憶體，從而被作業系統“消滅”了，給人的感覺就是MySQL意外重啟了，

我們可以使用如下的方案來解決這個問題：

• 定期或者執行過一個比較占記憶體的查詢操作后，斷開連接，以后再重新建立和MySQL的連接，
• 如果使用MySQL 5.7或更新的MySQL版本，可以通過執行mysql_reset_connection重新初始化MySQL的資源，重新初始化的程序不會重新連接MySQL，也不會重新做權限的驗證操作，

查詢快取的作用是什么？

登錄MySQL后，客戶端就會與MySQL建立連接，此時執行select陳述句時，首先會到查詢快取中查詢是否執行過當前select陳述句，如果之前執行過相應的select陳述句，則執行過的select陳述句和查詢結果會以key-value的形式存放在查詢快取中，其中，key是查詢陳述句，value是查詢的結果資料，

如果在查詢快取中沒有找到相應的資料，則會繼續執行后續的查詢階段，執行完成后，會將結果快取到查詢快取中，后續的查詢如果命中快取，則直接回傳查詢快取中的資料，性能還是挺高的，

但是，大多數時候我不太建議小伙伴們開啟查詢快取，為啥？原因很簡單：查詢快取失效的頻率是非常頻繁的，只要對一個表進行更新操作，則這張表上所有的查詢快取都會被清空， 而且在MySQL 8.0中，直接洗掉了查詢快取的功能（冰河在看MySQL原始碼時，也證明了這一點），

分析器對select陳述句做了什么？

分析器主要是對select陳述句進行 詞法分析和語法分析 操作，

如果select陳述句沒有命中快取，則首先會由分析器對其進行“詞法分析”操作，此時，MySQL會識別select陳述句中的每個字串代表什么含義，

例如，MySQL會通過"select"關鍵字識別出這是一個查詢陳述句，也會把"user"識別為"資料表名user"，把"id"識別成"欄位名id"，接下來，就要進行“語法分析了”，根據語法規則，判斷select陳述句是否滿足MySQL的語法，如果判斷出輸入的SQL陳述句不滿足語法規則，則MySQL會提示相應的錯誤資訊，

優化器是如何優化select陳述句的？

對select陳述句進行了詞法分析和語法分析后，還要經過優化器的優化處理才能執行，比如，我們的select陳述句中如果使用了多個索引，則優化器會決定使用哪個索引來查詢資料；再比如，在select陳述句中，有多表關聯的操作，優化器會決定各表的連接順序，資料表的連接順序不同，對于執行的效率會大不相同，優化器往往會選擇使用查詢效率高的連接順序，

如果select陳述句經過優化器的優化之后，就會進入執行階段了，

執行器如何執行select陳述句？

進入執行階段的select陳述句，首先，執行器會對當前連接進行權限檢查，最直接的方式就是檢查當前連接是否對資料表user具有查詢權限，如果當前連接對資料表user沒有查詢權限，就會回傳沒有權限的錯誤，例如，會回傳如下錯誤，

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

如果當前連接具有對資料表user的查詢權限，則會繼續執行，首先會進行打開資料表的操作，此時優化器會根據創建表時使用的存盤引擎，使用相應存盤引擎的介面執行查詢操作，這里，我們舉一個例子：

假設，我們在id欄位上沒有建立索引，執行器執行的流程大致如下所示，

（1）通過存盤引擎讀取資料表user的第一行資料，判斷當前行的id值是否等于1001，如果不等于1001，則繼續讀取下一行資料；如果等于1001，則將當前行放入結果集中，

（2）繼續通過存盤引擎讀取下一行資料，執行與（1）相同的邏輯判斷，直到處理完user表中的所有資料，

（3）處理完所有的資料后，執行器就會將結果集中的資料回傳給客戶端，

如果在id欄位上有索引的話，執行的整體邏輯與id欄位上沒有索引大體一致，

如果開啟了慢查詢的話，執行select陳述句時，會在慢查詢日志中輸出一個rows_examined欄位，這個欄位表示select陳述句在執行的程序中掃描了資料表中的多少行資料，不過在有些場景下，執行器呼叫一次，存盤引擎內部會會掃描多行，這就導致存盤引擎掃描的行數與rows_examined欄位標識的行數并不完全相同，

好了，今天就到這兒吧，我是冰河，大家有啥問題可以在下方留言，也可以加我微信：sun_shine_lyz，我拉你進群，一起交流技術，一起進階，一起牛逼~~