深入理解 MySQL 執行過程及執行順序

MySQL 在我們的開發中基本每天都要面對的，作爲開發中的數據的來源，MySQL 承擔者存儲數據和讀寫數據的職責。因爲學習和了解 MySQL 是至關重要的，那麼當我們在客戶端發起一個 SQL 到出現詳細的查詢數據，這其中究竟經歷了什麼樣的過程？MySQL 服務端是如何處理請求的，又是如何執行 SQL 語句的？本篇博客將來探討這些問題。

MySQL 執行過程

MySQL 整體的執行過程如下圖所示：

連接器

連接器的主要職責就是：

1、負責與客戶端的通信，是半雙工模式，這就意味着某一固定時刻只能由客戶端向服務器請求或者服務器向客戶端發送數據，而不能同時進行，其中 MySQL 在與客戶端連接 TC/IP 的。

2、驗證請求用戶的賬戶和密碼是否正確，如果賬戶和密碼錯誤，會報錯：Access denied for user 'root'@'localhost' (using password: YES)

3、如果用戶的賬戶和密碼驗證通過，會在 MySQL 自帶的權限表中查詢當前用戶的權限。

MySQL 中存在 4 個控制權限的表，分別爲 user 表，db 表，tables_priv 表，columns_priv 表：

• user 表：存放用戶賬戶信息以及全局級別（所有數據庫）權限，決定了來自哪些主機的哪些用戶可以訪問數據庫實例

• db 表：存放數據庫級別的權限，決定了來自哪些主機的哪些用戶可以訪問此數據庫

• tables_priv 表：存放表級別的權限，決定了來自哪些主機的哪些用戶可以訪問數據庫的這個表

• columns_priv 表：存放列級別的權限，決定了來自哪些主機的哪些用戶可以訪問數據庫表的這個字段

MySQL 權限表的驗證過程爲：

1、 先從 user 表中的 Host，User，Password 這 3 個字段中判斷連接的 IP、用戶名、密碼是否存在，存在則通過驗證。

2、通過身份認證後，進行權限分配，按照 user，db，tables_priv，columns_priv 的順序進行驗證。即先檢查全局權限表 user，如果 user 中對應的權限爲 Y，則此用戶對所有數據庫的權限都爲 Y，將不再檢查 db，tables_priv，columns_priv；如果爲 N，則到 db 表中檢查此用戶對應的具體數據庫，並得到 db 中爲 Y 的權限；如果 db 中爲 N，則檢查 tables_priv 中此數據庫對應的具體表，取得表中的權限 Y，以此類推。

3、如果在任何一個過程中權限驗證不通過，都會報錯。

緩存

MySQL 的緩存主要的作用是爲了提升查詢的效率，緩存以 key 和 value 的哈希表形式存儲，key 是具體的 SQL 語句，value 是結果的集合。如果無法命中緩存，就繼續走到分析器的這一步，如果命中緩存就直接返回給客戶端。不過需要注意的是在 MySQL 的 8.0 版本以後，緩存被官方刪除掉了。之所以刪除掉，是因爲查詢緩存的失效非常頻繁，如果在一個寫多讀少的環境中，緩存會頻繁的新增和失效。對於某些更新壓力大的數據庫來說，查詢緩存的命中率會非常低，MySQL 爲了維護緩存可能會出現一定的伸縮性的問題，目前在 5.6 的版本中已經默認關閉了，比較推薦的一種做法是將緩存放在客戶端，性能大概會提升 5 倍左右。

分析器

分析器的主要作用是將客戶端發過來的 SQL 語句進行分析，這將包括預處理與解析過程，在這個階段會解析 SQL 語句的語義，並進行關鍵詞和非關鍵詞進行提取、解析，並組成一個解析樹。具體的關鍵詞包括不限定於以下：select/update/delete/or/in/where/group by/having/count/limit 等。如果分析到語法錯誤，會直接給客戶端拋出異常：“ERROR:You have an error in your SQL syntax.”。

比如：select * from user where userId =1234;

在分析器中就通過語義規則器將 select from where 這些關鍵詞提取和匹配出來，MySQL 會自動判斷關鍵詞和非關鍵詞，將用戶的匹配字段和自定義語句識別出來。這個階段也會做一些校驗：比如校驗當前數據庫是否存在 user 表，同時假如 user 表中不存在 userId 這個字段同樣會報錯：“unknown column in field list.”。

優化器

能夠進入到優化器階段表示 SQL 是符合 MySQL 的標準語義規則的並且可以執行的，此階段主要是進行 SQL 語句的優化，會根據執行計劃進行最優的選擇，匹配合適的索引，選擇最佳的執行方案。比如一個典型的例子是這樣的：

表 T，對 A、B、C 列建立聯合索引，在進行查詢的時候，當 SQL 查詢到的結果是：select xx where B=x and A=x and C=x，很多人會以爲是用不到索引的，但其實會用到，雖然索引必須符合最左原則才能使用，但是本質上，優化器會自動將這條 SQL 優化爲：where A=x and B=x and C=X，這種優化會爲了底層能夠匹配到索引，同時在這個階段是自動按照執行計劃進行預處理，MySQL 會計算各個執行方法的最佳時間，最終確定一條執行的 SQL 交給最後的執行器。

執行器

在執行器的階段，此時會調用存儲引擎的 API，API 會調用存儲引擎，主要有一下存儲的引擎，不過常用的還是 myisam 和 innodb：

引擎以前的名字叫做：表處理器（其實這個名字我覺得更能表達它存在的意義）負責對具體的數據文件進行操作，對 SQL 的語義比如 select 或者 update 進行分析，執行具體的操作。在執行完以後會將具體的操作記錄到 binlog 中，需要注意的一點是：select 不會記錄到 binlog 中，只有 update/delete/insert 纔會記錄到 binlog 中。而 update 會採用兩階段提交的方式，記錄都 redolog 中。

執行的狀態

可以通過命令：show full processlist，展示所有的處理進程，主要包含了以下的狀態，表示服務器處理客戶端的狀態，狀態包含了從客戶端發起請求到後臺服務器處理的過程，包括加鎖的過程、統計存儲引擎的信息，排序數據、搜索中間表、發送數據等。囊括了所有的 MySQL 的所有狀態, 其中具體的含義如下圖：

SQL 的執行順序

事實上，SQL 並不是按照我們的書寫順序來從前往後、左往右依次執行的，它是按照固定的順序解析的，主要的作用就是從上一個階段的執行返回結果來提供給下一階段使用，SQL 在執行的過程中會有不同的臨時中間表，一般是按照如下順序：

例子：select distinct s.id from T t join S s on t.id=s.id where t. group by t.mobile having count(*)>2 order by s.create_time limit 5;

from

第一步就是選擇出 from 關鍵詞後面跟的表，這也是 SQL 執行的第一步：表示要從數據庫中執行哪張表。

實例說明：在這個例子中就是首先從數據庫中找到表 T。

join on

join 是表示要關聯的表，on 是連接的條件。通過 from 和 join on 選擇出需要執行的數據庫表 T 和 S，產生笛卡爾積，生成 T 和 S 合併的臨時中間表 Temp1。on：確定表的綁定關係，通過 on 產生臨時中間表 Temp2。

實例說明：找到表 S，生成臨時中間表 Temp1，然後找到表 T 的 id 和 S 的 id 相同的部分組成成表 Temp2，Temp2 裏面包含着 T 和 Sid 相等的所有數據。

where

where 表示篩選，根據 where 後面的條件進行過濾，按照指定的字段的值（如果有 and 連接符會進行聯合篩選）從臨時中間表 Temp2 中篩選需要的數據，注意如果在此階段找不到數據，會直接返回客戶端，不會往下進行。這個過程會生成一個臨時中間表 Temp3。注意：在 where 中不可以使用聚合函數，聚合函數主要是（min\max\count\sum 等函數）。

實例說明：在 temp2 臨時表集合中找到 T 表的 的數據，找到數據後會成臨時中間表 Temp3，T。emp3 裏包含 name 列爲 "Yrion" 的所有表數據。

group by

group by 是進行分組，對 where 條件過濾後的臨時表 Temp3 按照固定的字段進行分組，產生臨時中間表 Temp4，這個過程只是數據的順序發生改變，而數據總量不會變化，表中的數據以組的形式存在。

實例說明：在 Temp3 表數據中對 mobile 進行分組，查找出 mobile 一樣的數據，然後放到一起，產生 Temp4 臨時表。

Having

對臨時中間表 Temp4 進行聚合，這裏可以爲 count 等計數，然後產生中間表 Temp5，在此階段可以使用 select 中的別名。

實例說明：在 Temp4 臨時表中找出條數大於 2 的數據，如果小於 2 直接被捨棄掉，然後生成臨時中間表 Temp5。

select

對分組聚合完的表挑選出需要查詢的數據，如果爲 * 會解析爲所有數據，此時會產生中間表 Temp6。

實例說明：在此階段就是對 Temp5 臨時聚合表中 S 表中的 id 進行篩選產生 Temp6，此時 Temp6 就只包含有 s 表的 id 列數據，並且 ，通過 mobile 分組數量大於 2 的數據。

Distinct

Distinct 對所有的數據進行去重，此時如果有 min、max 函數會執行字段函數計算，然後產生臨時表 Temp7。

實例說明：此階段對 Temp5 中的數據進行去重，引擎 API 會調用去重函數進行數據的過濾，最終只保留 id 第一次出現的那條數據，然後產生臨時中間表 Temp7。

order by

會根據 Temp7 進行順序排列或者逆序排列，然後插入臨時中間表 Temp8，這個過程比較耗費資源。

實例說明：這段會將所有 Temp7 臨時表中的數據按照創建時間（create_time）進行排序，這個過程也不會有列或者行損失。

limit

limit 對中間表 Temp8 進行分頁，產生臨時中間表 Temp9，返回給客戶端。

實例說明：在 Temp7 中排好序的數據，然後取前五條插入到 Temp9 這個臨時表中，最終返回給客戶端。

PS：實際上這個過程也並不是絕對這樣的，中間 MySQL 會有部分的優化以達到最佳的優化效果，比如在 select 篩選出找到的數據集。

總結

本篇博客總結了 MySQL 的執行過程，以及 SQL 的執行順序，理解這些有助於我們對 SQL 語句進行優化，以及明白 MySQL 中的 SQL 語句從寫出來到最終執行的軌跡，有助於我們對 SQL 有比較深入和細緻的理解，提高我們的數據庫理解能力。同時，對於複雜 SQL 的執行過程、編寫都會有一定程度的意義。

作者：Yrion

來源：www.cnblogs.com/wyq178/p/11576065.html

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/SsvErZYdTi-BJ_jhczIu4A