一、 一條簡單SQL在MySQL執行過程

一張簡單的圖說明下，MySQL架構有哪些組件和組建間關係，接下來給大家用SQL語句分析

例如如下SQL語句

SELECT department_id FROM employee WHERE name = 'Lucy' AND age > 18 
GROUP BY department_id

其中name為索引，我們按照時間順序來分析一下

1. 客戶端：如MySQL命令行工具、Navicat、DBeaver或其他應用程式發送SQL查詢到MySQL伺服器。

2. 連接器：負責與客戶端建立連接、管理連接和維護連接。當客戶端連接到MySQL伺服器時，連接器驗證客戶端的用戶名和密碼，然後分配一個線程來處理客戶端的請求。

3. 查詢緩存：查詢緩存用於緩存先前執行過的查詢及其結果。當收到新的查詢請求時，MySQL首先檢查查詢緩存中是否已有相同的查詢及其結果。如果查詢緩存中有匹配的查詢結果，MySQL將直接返回緩存的結果，而無需再次執行查詢。但是，如果查詢緩存中沒有匹配的查詢結果，MySQL將繼續執行查詢。

4. 分析器：

◦ 解析查詢語句，檢查語法。

◦ 驗證表名和列名的正確性。

◦ 生成查詢樹。

5. 優化器：分析查詢樹，考慮各種執行計劃，估算不同執行計劃的成本，選擇最佳的執行計劃。在這個例子中，優化器可能會選擇使用name索引進行查詢，因為name是索引列。

6. 執行器：根據優化器選擇的執行計劃，向存儲引擎發送請求，獲取滿足條件的數據行。

7. 存儲引擎（如InnoDB）：

◦ 負責實際執行索引掃描，如在employee表的name索引上進行等值查詢，因查詢全部列，涉及到回表訪問磁碟。

◦ 在訪問磁碟之前，先檢查InnoDB的緩衝池（Buffer Pool）中是否已有所需的數據頁。如果緩衝池中有符合條件的數據頁，直接使用緩存的數據。如果緩衝池中沒有所需的數據頁，從磁碟載入數據頁到緩衝池中。

8. 執行器：

◦ 對於每個找到的記錄，再次判斷記錄是否滿足索引條件name。這是因為基於索引條件載入到記憶體中是數據頁，數據頁中也有可能包含不滿足索引條件的記錄，所以還要再判斷一次name條件，滿足name條件則繼續判斷age > 18過濾條件。

◦ 根據department_id對滿足條件的記錄進行分組。

◦ 執行器將處理後的結果集返回給客戶端。

在整個查詢執行過程中，這些組件共同協作以高效地執行查詢。客戶端負責發送查詢，連接器管理客戶端連接，查詢緩存嘗試重用先前查詢結果，解析器負責解析查詢，優化器選擇最佳執行計劃，執行器執行優化器選擇的計劃，存儲引擎（如InnoDB）負責管理數據存儲和訪問。這些組件的協同作用使得MySQL能夠高效地執行查詢並返回結果集。

根據索引列過濾條件載入索引的數據頁到記憶體這個操作是存儲引擎做的。載入到記憶體中之後，執行器會進行索引列和非索引列的過濾條件判斷。

二、 查詢SQL關鍵字執行順序

執行順序，如下：

1、對存儲引擎的操作

（1）FROM：用於查詢SQL的數據表。執行器會根據優化器選擇的執行計劃從存儲引擎中獲取相關表的數據。

（2）ON： 與JOIN一起使用，用於指定連接條件。執行器會根據ON給定的條件條件從存儲引擎獲取匹配條件的記錄。如果連接條件涉及到索引列，存儲引擎會使用索引進行優化。

（3）JOIN：指定表之間連接方式（如INNER JOIN,LEFT JOIN等）。執行器會根據優化器選擇的執行計劃，從存儲引擎中獲取連接表數據。然後執行器根據JOIN連接類型和ON連接條件，對數據連接處理。

（4）WHERE：執行器對從存儲引擎返回的數據進行過濾，只保留滿足WHERE子句條件的記錄。過濾條件如有索引，存儲引擎層會通過索引過濾後返回。

2、對返回結果集的操作

（5）GROUP BY：執行器對滿足WHERE條件的記錄按照GROUP BY指定的列分組。

（6）HAVING：執行器在執行分組後，根據HAVING條件對分組後的記錄再次過濾。

（7）SELECT：執行器根據優化器選擇的執行計劃和指定列獲取查詢結果。

（8）DISTINCT：執行器對查詢結果進行去重，只返回不重覆的記錄。

（9）ORDER BY：執行器對查詢結果按照ORDER BY子句中指定的列進行排序。

（10）LIMIT：執行器根據LIMIT子句中指定的限制條件對查詢結果進行截斷，只返回部分記錄

三、表關聯查詢SQL在MySQL中的執行過程

SELECT s.id, s.name, s.age, es.subject, es.score 
FROM employee s JOIN employee_score es ON s.id = es.employee_id 
WHERE s.age >18 AND es.subject_id =3 AND es.score >80;

這個例子中，subject_id和score是聯合索引，age是索引。 我們按照時間順序來分析一下

1. 連接器：當客戶端連接到MySQL伺服器時，連接器負責建立和管理連接。它驗證客戶端提供的用戶名和密碼，確定客戶端具有相應的許可權，然後建立連接。

2. 查詢緩存：MySQL伺服器在處理查詢之前，會先檢查查詢緩存。如果查詢緩存中已經存在該結果集，伺服器將直接返回緩存中的結果。

3. 解析器：解析並檢查SQL語法正確性。解析器會將查詢語句分解成多個組成部分，例如表、列、條件等。在這個示例中，解析器會識別出涉及的表（employee和employee_score）以及需要的列（id、name、age、subject、score）。

4. 優化器：根據解析器提供的信息生成執行計劃。優化器會分析多種可能的執行策略，並選擇成本最低的策略。在這個示例中，優化器會選擇age索引和subject_id與score的聯合索引。對於連接操作，優化器還要決定連接策略，例如是否使用Nested-Loop Join或Hash Join等一些連接策略。優化器還會根據表的大小、索引、查詢條件和統計信息來決定哪張表作為驅動表，以及選擇最佳的連接策略。例如，如果兩個表的大小差異很大，**Nested-Loop Join**可能是一個好的選擇，而對於大小相似的兩個表，**Hash Join**或**Sort-Merge Join**可能更加高效。

5. 執行器：根據優化器生成的執行計劃執行查詢，向存儲引擎發送請求，獲取滿足條件的數據行。

6. 存儲引擎（如InnoDB）：管理數據存儲和檢索。存儲引擎首先接收來自執行器的請求，該請求可能是基於優化器的執行計劃。

◦ 存儲引擎首先接收來自執行器的請求。請求可能包括獲取滿足查詢條件的數據行，以及使用哪種掃描方法（如全表掃描或索引掃描）。

◦ 假設執行器已經決定使用索引掃描。在這個示例中，存儲引擎可能會先對employee表進行索引掃描（使用age索引），然後對employee_score表進行索引掃描（使用subject_id和score的聯合索引）。

◦ 存儲引擎會根據請求查詢相應的索引。在employee索引中會找到滿足age > 18條件的記錄。在employee_score索引中找到滿足subject_id = 3 AND score > 80條件的記錄。

◦ 一旦找到了滿足條件的記錄，存儲引擎需要將這些記錄所在的數據頁從磁碟載入到記憶體中。存儲引擎首先檢查緩衝池（InnoDB Buffer Pool），看這些數據頁是否已經存在於記憶體中。如果已經存在，則無需再次從磁碟載入。如果不存在，存儲引擎會將這些數據頁從磁碟載入到緩衝池中。

◦ 載入到緩衝池中的記錄可以被多個查詢共用，這有助於提高查詢效率。

7. 執行器：處理連接、排序、聚合、過濾等操作。

◦ 在記憶體中執行連接操作，將employee表和employee_score表的數據行連接起來。

◦ 對連接後的結果集進行過濾，只保留滿足查詢條件（age > 18、subject_id = 3、score > 80）的數據行。

◦ 將過濾後的數據行作為查詢結果返回給客戶端。

前面說過，根據存儲引擎根據索引條件載入到記憶體的數據頁有多數據，可能有不滿足索引條件的數據，如果執行器不再次進行索引條件判斷， 則無法判斷哪些記錄滿足索引條件的，雖然在存儲引擎判斷過了，但是在執行器還是會有索引條件 age > 18、subject_id = 3、score > 80 的判斷。

我們再以全局視野來分析一下

1. 確定驅動表: 首先，MySQL優化器會選擇一個表作為"驅動表"。通常，返回記錄數較少的表會被選為驅動表。假設employee_score表中滿足subject_id = 3 AND score > 80條件的記錄數量較少，那麼這張表可能被選為驅動表。這是優化器的工作，它預估哪個表作為驅動表更為高效，制定執行計劃。雖然驅動表的選擇很大程度上是基於預估的返回記錄數，但實際選擇還會受其他因素影響，例如表之間的連接類型、可用的索引等。
2. 使用驅動表的索引進行篩選: 優化器會首先對驅動表進行篩選。如果employee_score是驅動表，優化器會使用subject_id和score的聯合索引來篩選出subject_id = 3 AND score > 80的記錄。這是執行器按照優化器的計劃向存儲引擎發出請求，獲取需要的數據。存儲引擎負責訪問索引，並根據索引定位到實際的數據頁，從而獲取數據行。
3. 連接操作: 執行器會基於上一步從驅動表中篩選出的記錄對另一個表（即employee表）進行連接。這時，執行器會使用employee表上的索引（如id索引）來高效地找到匹配的記錄。
4. 進一步的篩選: 在連接的過程中，執行器會考慮employee表的其他篩選條件，如age > 18，通常連接後才過濾篩選，這也是執行器的工作，執行器在連接過程中或之後，根據優化器制定的計划進一步篩選結果集。但是這裡employee表的age索引其葉子節點包含age和主鍵id信息，在進行連接時，可以直接按照age範圍掃描該索引，利用其葉子節點中的id信息進行高效的JOIN操作，因此在連接時就完成篩選，這個過程由MySQL優化器自動完成。從上面可以看到，當存在可以被利用的索引時，MySQL可以在連接過程中執行這些過濾操作。
5. 返回結果: 這是執行器最後的步驟，返回最終的查詢結果。

四、總結

本文采用一張簡單的架構圖說明瞭MySQL查詢中使用的組件和組件間關係。

解析了一條sql語句從客戶端請求mysql伺服器到返回給客戶端的整個生命周期流程。

列舉了單表sql、關聯表sql 兩種不同SQL在整個生命周期中的執行順序和及內部組件邏輯關係。

通過如上案例的解析可以讓開發者們掌握到單表sql、關聯表sql的底層sql知識，為理解慢sql的產生和優化鑒定基礎。

作者：京東物流 高峰

來源：京東雲開發者社區 自猿其說Tech 轉載請註明來源