MySQL 是怎么執行 SQL 語句的？

一顆小胡椒2022-07-27 12:07:06

假設你現在要用 Python 開發一個書籍管理系統，讓管理員能夠對 MySQL 數據庫中的書籍信息進行增刪改查，那么你會怎么做呢？其實很簡單，使用 web 框架編寫一個服務，提供好相應的 API，當請求到來時，根據請求類型和參數拼接 SQL 語句，然后交給 MySQL 執行即可。

整個需求再簡單不過了，但是 Python 程序在拼接好 SQL 語句之后，怎么交給 MySQL 執行呢？

Python 要想將 SQL 語句發給 MySQL 執行，那么首先要和 MySQL 建立連接，有了連接之后，才能將各種各樣的 SQL 語句交給 MySQL 執行。并且在 MySQL 執行完畢之后，還能拿到執行結果，如果執行出錯，也要能拿到 MySQL 拋出的錯誤。

而負責上述邏輯的，我們稱之為驅動，Python 里面的 MySQL 驅動最常用的就是 pymysql，這是一個同步驅動，異步驅動的話則是 asyncmy。當然不同的語言都有相應的 MySQL 驅動，有了驅動，便可以和 MySQL 數據庫建立連接，發送 SQL 語句給 MySQL 執行，然后獲取執行結果。

當拿到執行結果后，連接可以銷毀、也可以保留下來，我們一般會選擇后者。因為在生產環境中，肯定不止一個連接訪問數據庫，那樣同時能服務的用戶量太少。但連接的建立是比較耗時的，如果每來一個請求就創建一次連接、執行完之后又銷毀連接，那么效率會非常低下。

因此在生產中，我們的系統會維護一個連接池，池子里面有很多連接。當需要訪問數據庫時，就從池子里面取走一個，去和 MySQL 交互。當 MySQL 執行完畢、并拿到執行結果之后，再將連接放到池子里，方便下次使用。另外通過連接池，我們還可以限制同時訪問 MySQL 的連接數，以防止 MySQL 壓力過大。Python 里面的連接池可以通過 SQLAlchemy 實現，或者你也可以自己封裝一個。

驅動和 MySQL 之間建立的連接走的是 TCP，應用程序通過連接去訪問 MySQL，那么 MySQL 是不是也要創建連接來提供服務呢？答案是肯定的，每來一個客戶端連接，MySQL 作為服務端也要創建一個連接與之交互。因此 MySQL 內部必然也會維護一個連接池，負責處理來自客戶端的連接請求。

MySQL 的整體架構

MySQL 在面對一條 SQL 語句，需要做哪些工作呢？比如下面這條語句：

select * from student 
where age > 16;

這條語句的含義是查詢表 student 中 age 大于 16 的數據，那么 MySQL 在執行時內部都做了哪些事情呢？下面就來解析一下。

連接器

首先要連接到 MySQL 數據庫，這時候負責接待的就是 MySQL 的連接器，它內部維護了一個連接池，負責與客戶端建立連接并進行管理。此外，還要根據用戶名，判斷客戶端的權限。

# -h: ip
# -P: 端口
# -u: 用戶
# -p: 密碼，按下回車之后會自動提示輸入，當然也可以在控制臺輸入，但是不安全
mysql -h$ip -P$port -u$user -p
# 比如: mysql -uroot -p123456，回車之后直接進入

連接命令中的 mysql 是客戶端工具，和 Python 的 pymysql 驅動是等價的，都是用來跟服務端建立連接。在完成 TCP 三次握手后，連接器就要開始認證身份，這個時候用的就是我們輸入的用戶名和密碼。

如果用戶名或密碼不對，你會收到一個 "Access denied for user" 的錯誤，然后客戶端程序結束執行；
如果用戶名密碼認證通過，連接器會到權限表里面查找該用戶擁有的權限。之后這個連接里面的權限判斷邏輯，都將依賴于此時讀到的權限。這就意味著，一個用戶成功建立連接后，即使你用管理員賬號對這個用戶的權限做了修改，也不會影響已存在連接的權限。修改完成后，該用戶只有重新建立連接，才會使用新的權限設置。

連接完成后，如果沒有后續的動作，這個連接就處于空閑狀態，你可以在 show processlist 命令中看到它。

圖中顯示的就是 show processlist 的結果，其中 Command 列顯示為 Sleep 的這一行，表示現在系統里面有一個空閑連接。然后我們開啟了兩個終端，都使用 root 用戶建立連接，所以上面顯示有兩個 root 用戶。

但如果客戶端太長時間沒動靜，連接器就會自動將它斷開，這個時間是由參數 wait_timeout 控制的，默認值是 8 小時。如果在連接被斷開之后，客戶端再次發送請求的話，就會收到一個錯誤提醒：Lost connection to MySQL server during query。這時候你就需要重新連接，然后再執行請求了。

另外數據庫里面有長連接和短連接，長連接是指連接成功后，如果客戶端持續有請求，則一直使用同一個連接；短連接則是指每次執行完很少的幾次查詢后就斷開連接，下次查詢再重新建立一個。

這兩種策略都有利有弊，首先連接的建立本身比較耗時，因此為了保證效率，應該減少連接建立的動作，也就是使用長連接。但如果全部使用長連接，MySQL 占用的內存就會漲的特別快，這是因為 MySQL 在執行過程中臨時使用的內存是管理在連接對象里面的，而這些資源會在連接斷開的時候才釋放。

所以長連接累積的過多，可能導致內存占用太大，被系統強行殺掉（OOM），結果看起來就像是 MySQL 異常重啟了。而解決這個問題有兩種辦法：

定期斷開長連接，使用一段時間、或者執行過一個占用內存的大查詢后，就斷開連接，之后要查詢的時候再重新連；
如果是 MySQL 5.7 以及之后的版本，可以在執行完內存占用較大的查詢后，通過 mysql_reset_connection 來重新初始化連接資源。這個過程不需要重連和重新做權限驗證，但是會將連接恢復到剛剛創建完時的狀態；

查詢緩存

連接建立成功并讀到客戶端發來的 SQL 語句之后，會先去查詢緩存，看看之前是不是執行過這條語句。因為執行過的語句及其結果會以 key-value 的形式，被直接緩存在內存中。key 是查詢語句，value 是查詢結果。如果你的查詢在這個緩存中已存在，那么會直接將對應的 value 返回給客戶端。

如果語句不在緩存中，就會繼續后面的執行階段。執行完成后，執行結果會被放入緩存中。如果查詢命中緩存，MySQL 不需要執行后面的復雜操作，可以直接返回結果，這個效率會很高

但是 MySQL 的緩存有一個問題，如果某張表更新了，那么該表的所有緩存都會被清空，所以它只適合更新頻率非常低的表。因此 MySQL 8.0 版本直接將查詢緩存的整塊功能刪掉了，從 8.0 開始徹底沒有這個功能了，因此緩存這一塊就無需太關注了

分析器

如果沒有命中緩存，或者是 MySQL 8.0 以及之后的版本，那么 SQL 語句會交給分析器。因為 SQL 語句本質上就是一堆文本，它要先進行解析，而解析的工作就交給分析器負責。

分析器內部包含詞法分析器、語法分析器、預處理器。

1）首先詞法分析器會對 SQL 語句進行分詞，將整個文本切分成一個個的 token。

2）然后是語法分析器，會基于內部定義好的語法規則，在詞法分析的基礎上進行語法分析，也就是對 token 進行語法分析，然后生成語法解析樹。這一步會進行語法檢測，也就是判斷客戶端發送的 SQL 語句是否符合語法規則。

如果語句不對，就會收到 You have an error in your SQL syntax 的錯誤提醒，比如下面這個語句的 select 少打了開頭的字母 s。

mysql> elect * from student where age > 16;
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your 
MySQL server version for the right syntax 
to use near 'elect * from student where age > 16' at line 1

一般語法錯誤會提示第一個出現錯誤的位置，所以需要關注的是緊接 "use near" 之后的內容。

另外關于詞法分析和語法分析，基本上任何一門語言都會有兩步。

3）如果語法正確，那么再交給預處理器，預處理器會進一步檢測解析樹的合法性。比如檢測要查詢的表、字段是否存在，別名是否有歧義等等，如果檢測通過則生成新的語法解析樹，然后交給接下來要說的優化器。

優化器

經過了分析器，MySQL 就知道你要做什么了。不過在開始執行之前，還要先經過優化器的處理。

優化器的作用是對你的 SQL 語句進行優化，比如在表里面有多個索引的時候，決定使用哪個索引。或者語句中有多表關聯（join）的時候，決定各個表的連接順序。比如執行兩個表的 join：

select * from t1 join t2 
using(id) where t1.c=10 and t2.d=20;

這條語句可以有兩種解釋：

先從表 t1 里面取出 c = 10 的記錄，根據 id 值關聯到表 t2，再篩選出 t2 里面 d = 20 的記錄；
先從表 t2 里面取出 d = 20 的記錄，根據 id 值關聯到表 t1，再篩選出 t1 里面 c = 10 的記錄；

這兩種執行方案的邏輯結果是一樣的，但是執行的效率會有不同，而優化器的作用就是決定選擇哪一種方案。而優化器階段完成后，這個語句的執行方案就確定下來了，然后進入執行器階段。

執行器

MySQL 通過連接器拿到了 SQL 語句，通過分析器知道了你要做什么，通過優化器得出了最佳方案，也就是執行計劃，然后就進入了執行器階段，開始執行語句。

首先MySQL 可以分為 Server 層和存儲引擎層兩部分。

1）Server 層包括連接器、查詢緩存、分析器、優化器、執行器等，涵蓋 MySQL 的大多數核心服務功能，比如內置函數，存儲過程、觸發器、視圖等所有跨存儲引擎的功能，都在這一層實現。

2）而存儲引擎層負責數據的存儲和提取，其架構模式是插件式的，支持 InnoDB, MyISAM, Memory 等多個存儲引擎。現在最常用的存儲引擎是 InnoDB，它從 MySQL 5.5.5 版本開始成為了默認存儲引擎。

在執行 create table 建表的時候，如果不指定引擎類型，默認使用的就是 InnoDB。不過我們也可以通過指定存儲引擎的類型來選擇別的引擎，比如在 create table 語句中通過 engine=memory，來指定使用內存引擎創建表。不同存儲引擎的表數據的存儲方式不同，支持的功能也不同。

所以從圖中不難看出，不同的存儲引擎共用一個 Server 層，也就是從連接器到執行器的部分。

那么問題來了，為什么要有存儲引擎這一層呢？很簡單，我們的數據既可以放在內存，也可以放在磁盤，如果 SQL 執行的時候，要去哪里找這些數據呢？是從內存里面找，還是從磁盤里面找？如果是從磁盤里面找，要從哪個磁盤文件開始找呢？

所以這個時候就需要存儲引擎了，存儲引擎其實就是執行 SQL 語句的，它會按照一定的步驟去查詢內存數據，更新磁盤數據，查詢磁盤數據等等，執行諸如此類的一系列操作。并且針對不同的操作，存儲引擎都提供了相應的接口，而調用這些接口的就是 Server 層的執行器。

因此執行器就是不停地調用存儲引擎的各種接口去完成優化器生成的執行計劃。

但是調用之前會先判斷用戶是否具有相應的權限，如果沒有，就會返回沒有權限的錯誤：ERROR 1142 (42000): SELECT command denied to user 'xxx'@'localhost' for table 't'。另外這個權限，就是在建立連接時，由連接器讀到的權限。

可能有人好奇了，為什么權限驗證非要留在執行器階段去做。其實這是必須的，因為 SQL 語句要操作的表不止字面上的那些，比如有個觸發器，那么就必須在執行階段才能確定。

如果有權限，就打開表繼續執行。打開表的時候，執行器會根據表的引擎定義，去使用存儲引擎提供的接口。定義的時候使用哪種引擎，查詢的時候也使用哪種。

select * from student 
where age > 16;

比如我們這個例子中的表 student，存儲引擎顯然是 InnoDB，如果 age 字段沒有索引，那么執行器的執行流程是這樣的：

調用 InnoDB 引擎接口獲取這個表的第一行，判斷 age 值是否大于 16，如果為假則跳過，為真則將這行數據存在結果集中。另外我們這里是 select *，如果是 select name, age，那么只會選擇 name 和 age 兩個字段的值；
調用引擎接口獲取下一行，重復相同的判斷邏輯，直到取到這個表的最后一行；
執行器將上述遍歷過程中所有滿足條件的行組成的結果集返回給客戶端；

至此，這個語句就執行完成了，邏輯還是很好理解的。就是不斷地調用存儲引擎接口，每調用一次，獲取一行數據，如果滿足 where 條件，則該行保留，否則跳過。

而對于使用了索引的查詢，執行邏輯也差不多，只是稍有不同。該查詢第一次調用的是 "取滿足條件的第一行" 這個接口，之后循環調用 "取滿足條件的下一行" 這個接口，這些接口都是引擎中已經定義好的。

所以對于沒使用索引的查詢，每調用一次接口，只掃描一行數據；對于使用了索引的查詢，每調用一次接口，會掃描多行數據。

小結

現在我們對 MySQL 應該有了一個宏觀的認識，說白了數據庫本身也是用編程語言寫出來的一個軟件而已。在啟動之后，也是一個進程，執行它內部的各種代碼。但是基于 MySQL，我們能更方便地管理文件。