MySql執行流程與生命週期詳解

2022-09-14 22:12:36

我們經常會在一起討論怎樣優化 SQL、怎樣優化資料庫，那麼請問你知道執行一條 SQL 的過程中都經過了哪些動作嗎？如果不知道的話，又怎麼去優化 SQL、優化資料庫呢？為了更好地優化 MySQL 資料庫，接下來我們就來討論一下執行一條 SQL 都經過哪些步驟。

為了方便理解，在介紹 SQL 執行流程之前，我們先通過一張圖，來詳細闡述一下 SQL 執行流程。

由上圖，可以得出：在 MySQL 中，我們大致可以分為三個部分，分別是MySQL 使用者端、MySQL 伺服器端和儲存層。其中，儲存層主要是硬體層面，不在今天討論的範圍內，所以這裡我們主要討論前兩者。

一、MySQL 使用者端

我們應該都聽說過 MySQL 資料庫支援很多程式語言的 API 介面，其實這句話底層的含義是很多程式語言是內建 MySQL 使用者端。除此之外，MySQL 還有一個常用的使用者端就是 MySQL 資料庫自帶的一個 mysql 命令，這個命令使用如下：

$ mysql -u$username -p$password -h$host -P$port
備註：
- username 是使用者名稱
- password 是密碼
- host 是 MySQL 伺服器端地址
- port 是 MySQL 伺服器端埠
複製程式碼

一條 SQL 執行的第一步是由 MySQL 使用者端傳送到 MySQL 伺服器端。在這個步驟中直接影響資料速度的是網路，所以，資料庫伺服器端和使用者端之間最好要有良好的網路環境。不過，網路不屬於本次討論的重點，這裡不做過多的討論。我們重點討論一下 MySQL 中的各個模組對於資料庫速度的影響。

嚴格來說，MySQL 連線池屬於 MySQL 伺服器端的模組，因其跟 MySQL 使用者端關係密切，我將其拿到 MySQL 使用者端中來介紹。

MySQL 使用者端成功連線 MySQL 伺服器端之後，MySQL 伺服器端的連線池會對使用者端的連線進行許可權驗證，當許可權驗證通過之後，MySQL 伺服器端會將使用者端的連結記錄在伺服器端的連線池中，之後的各種操作將不再進行許可權認證。

這裡你肯定會有疑問：為什麼 MySQL 要增加一個連線池模組呢？直接連線 MySQL 伺服器端不是更簡單且高效嗎？下面我們就來分析一下。

每一次運算元據庫，都需要有一個 MySQL 使用者端和伺服器端之間的連結，建立一個連結就會有一定的時間消耗；當在高並行的情況下，每一次存取資料都會建立一個連結，這樣就會持續建立很多重複的資料庫連結，很沒有必要而且大量的建立連結可能會導致資料庫記憶體溢位等問題。

為了解決這個問題，MySQL 資料庫就提供了一個連結池模組，主要用來儲存和安全校驗連線過來的使用者端，當一個使用者端連線成功之後，並不會立即釋放該連線，而是會將該連結儲存在連線池中，這樣下一次同一個使用者端連線過來就不需要再重複建立連線和安全驗證了，可以在很大程度上降低資料庫的資源消耗並且減少連線資料的延時。

二、MySQL 伺服器端

在 MySQL 伺服器端中，SQL 執行過程中是需要經過很多模組的，其中比較重要的模組是快取、SQL 解析器、查詢優化器以及 SQL 執行器等模組。下面我來詳細介紹一下。

1. 查詢快取

在 MySQL 資料庫中，如果開啟了快取查詢，每一次的查詢都會在快取器中以 KV 形式快取一份。一條 SQL 在執行過程中，如果命中了快取，就會跳過 SQL 解析器、查詢優化器以及 SQL 執行器，並且立即返回資料，這樣做的目的主要是提高資料庫的效能。

其實 MySQL 資料庫是將快取以雜湊的形式儲存在記憶體中的一個參照表中，並且把本次查詢的 SQL、資料庫名稱以及協定的 hash 值作為 key，這樣做的主要目的是下一次同一個查詢過來之後可以直接命中查詢。

不過快取並不是永恆不變的，也會失效，我總結了以下幾個快取失效的情況：

該條快取對應的資料、資料表發生變化時，快取就會自動失效；
查詢過程中有變化的資料時，是不會建立快取的，例如 now()。

所以，在使用 MySQL 資料庫查詢的時候，要儘量避免更改資料和使用有變化的資料。

那麼，既然快取能夠提高 MySQL 資料的效能，應該怎麼設定開啟快取呢？首先，我們可以使用SHOW VARIABLES LIKE '%query_cache%';來查詢快取的設定項：

-- 查詢快取設定項
mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.01 sec)

其中，query_cache_type有三個選項，分別是：

OFF（0）：關閉快取。
ON（1）：開啟快取。
DEMAND（2）：按需開啟快取，加上 SQL_CACHE 關鍵字才會快取。

所以，要開啟查詢快取可以在 MySQL 資料庫的組態檔 my.cnf 中新增query_cache_type = 1即可。如果需要使用 DEMAND 的話，就需要設定成query_cache_type = 2。如果需要快取的話，就需要增加SQL_CACHE關鍵字，具體操作如下：

mysql> select SQL_CACHE * from info;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 小仙女       |
|  2 | 小帥哥       |
|  3 | 鐵錘妹妹     |
+----+--------------+
3 rows in set, 1 warning (0.00 sec)

2. SQL 解析器

當沒有命中快取時，這個時候 MySQL 資料庫就得去查詢資料了。在查詢之前必須解析使用者端傳送過來的以一系列字串和空格組成的 SQL，此時就必須用到 MySQL 資料庫中的另一個模組：SQL 解析器。

SQL 解析器的主要功能是解析使用者端傳送過來的 SQL，就比如匹配到 SQL 中是以 select 開頭的，那就可以認定其為查詢語句；以 insert 開頭的 SQL，就可以認定其為插入語句。但如果在判斷的過程中，出現了匹配不到的這種情況，就會報出ERROR 1064 (42000): You have an error in your SQL syntax;的錯誤。具體如下：

mysql> elect * from info;
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 info' at line 1

一般情況下，具體的錯誤資訊都是在最後邊的單引號內，具體問題具體對待。

3. 查詢優化器

查詢優化器，顧名思義就是優化 SQL 的執行效率，也就是說查詢優化器的具體功能是為了找到 SQL 的最佳執行方案。

在 MySQL 資料庫中的查詢優化器優化 SQL 具體有兩個方面，分別是邏輯層面和物理層面。

物理層面主要是跟硬體有關，很難通過邏輯去優化，所以這裡我們從邏輯層面說明一下。

邏輯層面的優化主要有命中索引優化、順序優化、排序優化等。例如連表查詢，具體如下：

mysql> select * from province inner join city on city.fid = province.id where province.id = 1;
+----+-----------+----+--------------+------+
| id | name      | id | name         | fid  |
+----+-----------+----+--------------+------+
|  1 | 上海市    |  1 | 徐彙區         |    1 |
|  1 | 上海市    |  2 | 浦東新區       |    1 |
+----+-----------+----+--------------+------+
2 rows in set (0.00 sec)

上面的 SQL 連線 province 和 city 兩個資料表，在記憶體中，有如下兩種情況。