EXPLAIN 執行計劃

有了慢查詢語句後，就要對語句進行分析。一條查詢語句在經過 MySQL 查詢優化器的各種基於成本和規則的優化會後生成一個所謂的執行計劃，這個執行計劃展示了接下來具體執行查詢的方式，比如多表連線的順序是什麼，對於每個表採用什麼存取方法來具體執行查詢等等。EXPLAIN 語句來能夠檢視某個查詢語句的具體執行計劃，要搞懂 EPLATN 的各個輸出項都有什麼作用，從而可以有針對性的提升查詢語句的效能。

通過使用 EXPLAIN 關鍵字可以模擬優化器執行 SQL 查詢語句，從而知道 MySQL 是如何處理 SQL 語句的。分析查詢語句或是表結構的效能瓶頸。

EXPLAIN 可以得到以下結果：

表的讀取順序；
資料讀取操作的操作型別；
哪些索引可以使用；
哪些索引被實際使用；
表之間的參照；
每張表有多少行被優化器查詢；

EXPLAIN 基本語法

執行計劃的語法非常簡單：在 SQL 查詢的前面加上 EXPLAIN 關鍵字。比如：EXPLAIN select * from tableName;

除了以 SELECT 開頭的查詢語句，其餘的 DELETE、INSERT、UPOATE 語句前邊都可以加上 EXPLAIN，用來檢視這些語句的執行計劃，只不過大多數情況下都會對 SELECT 語句更感興趣。

EXPLAIN 詳解

執行 sql explain SELECT * FROM order_test; 輸出結果如圖：

每列的含義

id

查詢語句一般都以 SELECT 關鍵字開頭，比較簡單的查詢語句裡只有一個 SELECT 關鍵字，稍微複雜一點的連線查詢中也只有一個 SELECT 關鍵字，比如:

explain SELECT * FROM test1 t1 INNER JOIN test2 t2 ON t1.id=t2.id ;

但是下邊兩種情況下在一條查詢語句中會出現多個 SELECT 關鍵字：

查詢中包含子查詢的情況：explain SELECT * FROM test1 WHERE id IN(SELECT id FROM test2);
查詢中包含 UNION / UNION ALL 語句的情況：EXPLAIN SELECT * FROM test1 UNION ALL SELECT * FROM test2;

查詢語句中每出現一個 SELECT 關鍵字，MySQL 就會為它分配一個唯一的 id 值。這個 id 值就是 EXPLAIN 語句的第一列的值，並且 id 的順序是按 SELECT 出現的順序增長的，id 列越大執行優先順序越高，id 相同則從上往下執行，id 為 NULL 最後執行。

單 SELECT 關鍵字

比如下邊這個查詢中只有一個 SELECT 關鍵字，所以 EXPLAIN 的結果中也就只有一條 id 列為 1 的記錄。

連線查詢 對於連線查詢來說，一個 SELECT 關鍵字後邊的 FROM 子句中可以跟隨多個表，所以在連線查詢的執行計劃中，每個表都會對應一條記錄，但是這些記錄的 id 值都是相同的。

可以看到，上述連線查詢中參與連線的 test1 和 test2 表分別對應一條記錄，但是這兩條記錄對應的 id 值都是 1，在連線查詢的執行計劃中，每個表都會對應一條記錄，這些記錄的 id 列的值是相同的。

包含子查詢

對於包含子查詢的查詢語句來說，就可能涉及多個 SELECT 關鍵字，所以在包含子查詢的查詢語句的執行計劃中，每個 SELECT 關鍵字都會對應一個唯一的 id 值。

但是這裡需要特別注意，查詢優化器可能對涉及子查詢的查詢語句進行重寫，從而轉換為連線查詢。所以如果我們想知道查詢優化器對某個包含子查詢的語句是否進行了重寫，直接檢視執行計劃。

可以看到，雖然查詢語句是一個子查詢，但是執行計劃中 test1 和 test2 表對應的記錄的 id 值全部是 1，這就表明了查詢優化器將子查詢轉換為了連線查詢。

包含 UNION \ UNION ALL 子句

對於包含 UNION 子句的查詢語句來說，每個 SELECT 關鍵字對應一個 id 值也是沒錯的，不過還是有點兒特別。

這個語句的執行計劃的第三條記錄是因為 UNION 子句會把多個查詢的結果集合並起來並對結果集中的記錄進行去重，MySQL 使用的是內部的臨時表。UNION 子句是為了把 id 為 1 的查詢和 id 為 2 的查詢的結果集合並起來並去重，所以在內部建立了一個名為 <union1，2> 的臨時表，就是執行計劃第三條記錄的 table 列的名稱，id 為 NULL 表明這個臨時表是為了合併兩個查詢的結果集而建立的。

跟 UNION 對比起來 UNION ALL 就不需要為最終的結果集進行去重，它只是單純的把多個查詢的結果集中的記錄合併成一個並返回給使用者，所以也就不需要使用臨時表。所以在包含 UNION ALL 子句的查詢的執行計劃中，就沒有那個 id 為 NULL 的記錄。

select_type

一條大的查詢語句裡邊可以包含若干個 SELECT 關鍵字，每個 SELECT 關鍵字代表著一個小的查詢語句，而每個 SELECT 關鍵字的 FROM 子句中都可以包含若干張表，每一張表都對應著執行計劃輸出中的一條記錄，對於在同一個 SELECT 關鍵字中的表來說，它們的 id 值是相同的。

MySQL 為每一個 SELECT 關鍵字代表的小查詢都定義了一個稱之為：select_type 的屬性，意思是我們只要知道了某個小查詢的 select_type 屬性，就知道了這個小查詢在整個大查詢中扮演了一個什麼角色，select_type 取值如下：

SIMPLE：簡單的 SELECT 查詢，不使用 union 及子查詢；
PRIMARY：最外層的 SELECT 查詢；
UNION：UNION 中的第二個或隨後的 SELECT 查詢，不依賴於外部查詢的結果集；
UNION RESULT：UNION 結果集；
SUBQUERY：子查詢中的第一個 SELECT 查詢,不依賴於外部查詢的結果集；
DERIVED：用於 FROM 子句裡有子查詢的情況，MySQL 會遞迴執行這些子查詢，把結果放在臨時表裡；

SIMPLE

簡單的 select 查詢，查詢中不包含子查詢或者 UNION。

連線查詢也算是 SIMPLE 型別。

PRIMARY

對於包含 UNION、UNION ALL 或者子查詢的大查詢來說，它是由幾個小查詢組成的，其中最左邊的那個查詢的 select_type 值就是 PRIMARY。

從結果中可以看到，最左邊的小查詢 SELECT * FROMN test1 對應的是執行計劃中的第一條記錄，它的 select_type 值就是 PRIMARY。

UNION

對於包含 UNION 或者 UNION ALL 的大查詢來說，它是由幾個小查詢組成的，其中除了最左邊的那個小查詢以外，其餘的查詢的 select_type 值就是 UNION。

UNION RESULT

MySQL 選擇使用臨時表來完成 UNION 查詢的去重工作，針對該臨時表的查詢的 select_type 就是 UNION RESULT，如上圖。

SUBQUERY

包含在 SELECT 中的子查詢，不在 FROM 子句中。

DERIVED

包含在 FROM 子句中的子查詢。MySQL 會將結果存放在一個臨時表中，也稱為派生表。

從執行計劃中可以看出， id 為 2 的記錄就代表子查詢的執行方式，它的 select_type 是 DERIVED ，說明該子查詢是以派生表的方式執行的。id 為 1 的記錄代表外層查詢，注意看它的 table 列顯示的是 <derived2>，表示該查詢是針對將派生表之後的表進行查詢的。

table

不論我們的查詢語句有多複雜，裡邊包含了多少個表，到最後也是需要對每個表進行單表存取的，MySQL 規定 EXPLAIN 語句輸出的每條記錄都對應著某個單表的存取方法，該條記錄的 table 列代表著該表的表名。

只涉及對 test1 表的單表查詢，所以 EXPLAIN 輸出中只有一條記錄，其中的 table 列的值是 test1；

連線查詢的執行計劃中有兩條記錄，這兩條記錄的 table 列分別是 test1和 test2。

partitions

和分割區表有關，一般情況下查詢語句的執行計劃的 partitions 列的值都是NULL。

type

執行計劃的一條記錄就代表著 MySQL 對某個表的執行查詢時的存取方法或存取型別，其中的 type 列就表明了這個存取方法或存取型別是較為重要的一個指標，結果值從最好到最壞依次是：system > const > eq_ref > ref > range > index > ALL。

一般來說，得保證查詢至少達到 range 級別，最好能達到 ref。

system

當表中只有一條記錄並且該表使用的儲存引擎的統計資料是精確的，比如：MyISAM、Memory，那麼對該表的存取方法就是 system。

如果改成使用 InnoDB 儲存引擎，type 的值就是 all。

const

根據主鍵或者唯一二級索引列與常數進行等值匹配時，對單表的存取方法就是 const。因為只匹配一行資料，所以很快。

B+ Tree 葉子節點中的記錄是按照索引列排序的，對於的聚簇索引來說，它對應的B+樹葉子節點中的記錄就是按照id列排序的。B+樹矮胖，所以這樣根據主鍵值定位一條記錄的速度很快。類似的，我們根據唯一二級索引列來定位一條記錄的速度也很快的，比如下邊這個查詢：

eq_ref

在連線查詢時，如果被驅動表是通過主鍵或者唯一二級索引列等值匹配的方式進行存取的，如果該主鍵或者唯一二級索引是聯合索引的話，所有的索引列都必須進行等值比較，則對該被驅動表的存取方法就是：eq_ref。

從執行計劃的結果中可以看出，MySQL 打算將 test2 作為驅動表，test1 作為被驅動表，重點關注 test1 的存取方法是 eq_ref，表明在存取 test1 表的時候可以通過主鍵的等值匹配來進行存取。

驅動表與被驅動表：A 表和 B 表 join 連線查詢，如果通過 A 表的結果集作為迴圈基礎資料，然後一條一條地通過該結果集中的資料作為過濾條件到 B 表中查詢資料，然後合併結果。那麼我們稱 A 表為驅動表，B 表為被驅動表。

ref

當通過普通的二級索引列與常數進行等值匹配時來查詢某個表，那麼對該表的存取方法就可能是 ref。

本質上也是一種索引存取，它返回所有匹配某個單獨值的行，它可能會找到多個符合條件的行，所以他屬於查詢和掃描的混合體。

對於這個查詢可以選擇全表掃描來逐一對比搜尋條件是否滿足要求，也可以先使用二級索引找到對應記錄的 id 值，然後再回表到聚簇索引中查詢完整的使用者記錄。

由於普通二級索引並不限制索引列值的唯一性，所以可能找到多條對應的記錄，也就是說使用二級索引來執行查詢的代價取決於等值匹配到的二級索引記錄條數。

如果匹配的記錄較少，則回表的代價還是比較低的，所以 MySQL 可能選擇使用索引而不是全表掃描的方式來執行查詢。這種搜尋條件為二級索引列與常數等值比較，採用二級索引來執行查詢的存取方法稱為：ref。

對於普通的二級索引來說，通過索引列進行等值比較後可能匹配到多條連續的記錄，而不是像主鍵或者唯一二級索引那樣最多隻能匹配 1 條記錄，所以這種 ref 存取方法比 const 要差些，但是在二級索引等值比較時匹配的記錄數較少時的效率還是很高的，如果匹配的二級索引記錄太多那麼回表的成本就太大了。

對於某個包含多個索引列的二級索引來說，只要是最左邊的連續索引列是與常數的等值比較就可能採用 ref 的存取方法。

range

如果使用索引獲取某些範圍區間的記錄，那麼就可能使用到 range 存取方法，一般就是在 where 語句中出現了 between、<、>、in 等的查詢。

這種範圍掃描索引比全表掃描要好，因為它只需要開始於索引的某一點，而結束語另一點，不用掃描全部索引。

這種利用索引（聚簇索引、二級索引）進行範圍匹配的存取方法稱之為：range。

index

可以使用索引覆蓋，但需要掃描全部的索引記錄時，該表的存取方法就是：index。

ALL

最熟悉的全表掃描，將遍歷全表以找到匹配的行。

possible_keys 和 key

在 EXPLAIN 語句輸出的執行計劃中，possible_keys 列表示在某個查詢語句中，對某個表執行單表查詢時可能用到的索引有哪些。key 列表示實際用到的索引有哪些，如果為 NULL，則沒有使用索引。

上述執行計劃的 possible_keys 列的值表示該查詢可能使用到 u_idx_day_status、idx_insert_time 兩個索引，然後 key 列的值是 u_idx_day_status，表示經過查詢優化器計算使用不同索引的成本後，最後決定使用 u_idx_day_status 來執行查詢比較划算。

key_len

key_len 列，表示當優化器決定使用某個索引執行查詢時，該索引記錄的最大長度，計算方式如下：

對於使用固定長度型別的索引列來說，它實際佔用的儲存空間的最大長度就是該固定值，對於指定字元集的變長型別的索引列來說，如：某個索引列的型別是VARCHAR(100)，使用的字元集是 utf8，那麼該列實際佔用的最大儲存空間就是100x3=300個位元組；
如果該索引列可以儲存 NULL 值，則 key_len 比不可以儲存 NULL 值時多 1 個位元組；
對於變長欄位來說，都會有 2 個位元組的空間來儲存該變長列的實際長度；

由於 id 列的型別是 bigint，並且不可以儲存 NULL 值，所以在使用該列的索引時 key_len 大小就是 8。

對於可變長度的索引列來說。

由於 order_no 列的型別是 VARCHAR(50)，所以該列實際最多佔用的儲存空間就是 50*3 位元組，又因為該列是可變長度列，所以 key_len 需要加2，所以最後ken_len 的值就是 152。

執行計劃的生成是在 MySQL server 層中的功能，並不是針對具體某個儲存引擎的功能，MySQL 在執行計劃中輸出 key_len 列主要是為了區分某個使用聯合索引的查詢具體用了幾個索引列，而不是為了準確的說明針對某個具體儲存引擎儲存變長欄位的實際長度佔用的空間到底是佔用幾個位元組。

key_len 表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好。

key_len 顯示的值為索引欄位的最大可能長度，並非實際使用長度，即 key_len 是根據表定義計算而得，不是通過表內檢索出的。

注：char 和 varchar 跟字元編碼也有密切的聯絡，比如 latin1 佔用 1 個位元組，gbk 佔用 2 個位元組，utf8 佔用 3 個位元組。

ref

當使用索引列等值查詢時，與索引列進行等值匹配的物件資訊；

可以看到 ref 列的值是 const，表明在使用 idx_order_no 索引執行查詢時，與 order_no 列作等值匹配的物件是一個常數。

複雜的情況。

可以看到對被驅動表 t1 的存取方法是 eg_ref，而對應的 ref 列的值是 test.t2.id，這說明在對被驅動表進行存取時會用到 PRIMARY 索引，也就是聚簇索引與一個列進行等值匹配的條件，與 t2 表的 id 作等值匹配的物件就是 test.t2.id列，格式：資料庫名稱.表名.欄位。

rows

如果查詢優化器決定使用全表掃描的方式對某個表執行查詢時，執行計劃的 rows 列就代表預計需要掃描的行數，如果使用索引來執行查詢時，執行計劃的 rows 列就代表預計掃描的索引記錄行數。

filtered

某個表經過搜尋條件過濾後剩餘記錄條數的百分比；

從執行計劃的 key 列中可以看出來，該查詢使用 PRIMARY 索引來執行查詢，從 rows 列可以看出滿足 id>5890 的記錄有 5177 條。執行計劃的 filtered 列就代表查詢優化器預測在這 5177 條記錄中，有多少條記錄滿足其餘的搜尋條件，也就是 order_note='a' 這個條件的百分比。此處 filtered 列的值是 10.0，說明查詢優化器預測在 5177 條記錄中有 10.00% 的記錄滿足 order_note='a' 這個條件。

Extra

Extra 列是用來說明一些額外資訊的，可以通過這些額外資訊來更準確的理解 MySQL 到底將如何執行給定的查詢語句。MySQL 提供的額外資訊很多，常見的重要值如下：

Using index：當我們的查詢列表以及搜尋條件中只包含屬於某個索引的列，也就是在可以使用索引覆蓋的情況下，在 Extra 列將會提示該額外資訊。

這個查詢中只需要用到 u_idx_day_status 而不需要回表操作。

Using where：當我們使用全表掃描來執行對某個表的查詢，並且該語句的 WHERE 子句中有針對該表的搜尋條件時，在 Extra 列中會提示。

Using where 只是表示 MySQL 使用 where 子句中的條件對記錄進行了過濾。

Using index condition：有些搜尋條件中雖然出現了索引列，但卻不能使用到索引。

其中的 order_no >'z' 可以使用到索引，但是 order_no LIKE '%a' 卻無法使用到索引，在以前版本的 MySQL 中，是按照下邊步驟來執行這個查詢的：

先根據 order_no>'z' 這個條件，從二級索引 idx_order_no 中獲取到對應的二級索引記錄；
根據上一步驟得到的二級索引記錄中的主鍵值進行回表，找到完整的使用者記錄再檢測該記錄是否符合 order_no LIKE '%a' 這個條件，將符合條件的記錄加入到最後的結果集；

雖然 order_no LIKE '%a' 不能組成範圍區間參與 range 存取方法的執行，但這個條件畢竟只涉及到了 order_no 列，MySQL 把上邊的步驟改進了一下。

索引條件下推

先根據 order_no>'z' 這個條件，定位到二級索引 idx_order_no 中對應的二級索引記錄；
對於指定的二級索引記錄，先不著急回表，而是先檢測一下該記錄是否滿足 order_no LIKE '%a' 這個條件，如果這個條件不滿足，則該二級索引記錄壓根兒就沒必要回表；
對於滿足 order_no LIKE '%a' 這個條件的二級索引記錄執行回表操作，回表操作其實是一個隨機 IO 比較耗時；

所以上述修改可以省去很多回表操作的成本，這個改進稱之為索引條件下推。

如果在查詢語句的執行過程中將要使用索引條件下推這個特性，在 Extra 列中將會顯示 Using index condition。

Using temporary：在許多查詢的執行過程中，MySQL 可能會藉助臨時表來完成一些功能，比如去重、排序之類的，比如在執行許多包含 DISTINCT、GROUPBY、UNION 等子句的查詢過程中，如果不能有效利用索引來完成查詢，MySQL 很有可能尋求通過建立內部的臨時表來執行查詢。如果查詢中使用到了內部的臨時表，在執行計劃的 Extra 列將會顯示 Using temporary。

上邊的 GROUP BY 的執行計劃的 Extra 列不僅僅包含 Using temporary 提示，還包含 Using filesort 提示，可是查詢語句中明明沒有寫 ORDER BY 子句，這是因為 MySQL 會在包含 GROUP BY 子句的查詢中預設新增上 ORDER BY 子句。

如果不想為包含 GROUP BY 子句的查詢進行排序，需要顯式的寫上 ORDER BY NULL。

Using filesort：有一些情況下對結果集中的記錄進行排序是可以使用到索引的。

這個查詢語句可以利用 idx_order_no 索引直接取出 order_no 列的 10 條記錄，然後再進行回表操作。但是很多情況下排序操作無法使用到索引，只能在記憶體中或者磁碟中進行排序，MySQL 把這種在記憶體中或者磁碟上進行排序的方式統稱為檔案排序。如果某個查詢需要使用檔案排序的方式執行查詢，就會在執行計劃的Extra 列中顯示 Using filesort。

Select tables optimized away：使用某些聚合函數，比如：max、min 來存取存在索引的某個欄位是。

作者：京東物流張士欣

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

MYSQL EXPLAIN 執行計劃