在MySQL數據庫中，優化查詢性能是每個開發人員和數據庫管理員都需要面對的重要挑戰之一。其中，EXPLAIN關鍵字是一個強大的工具，可以幫助我們深入了解MySQL是如何執行查詢的，以及如何優化查詢性能。

本文將深入探討MySQL中的EXPLAIN關鍵字，探究其背后的工作原理和輸出信息含義。通過本文的闡述，您將了解如何解讀EXPLAIN的輸出結果，優化查詢執行計劃，提升數據庫性能，以及避免常見的查詢性能陷阱。

無論您是初學者還是有經驗的數據庫專家，本文都將為您提供有價值的見解和實用的技巧，助您在MySQL數據庫中更好地利用EXPLAIN關鍵字，優化查詢性能，提升數據庫應用的效率和穩定性。

詳解explain對應關鍵字

通過explain關鍵字可以獲取我們給定查詢SQL經由成本和規則優化后的執行計劃，通過這個計劃我們可以得到查詢語句實際的工作步驟，這里我們就針對這關鍵字得出的執行計劃的每一列都進行介紹。

為了更直觀的演示explain各個關鍵字段的信息，我們這里不妨通過兩張表針對每一種訪問方式進行講解，對應數據表的DDL如下所示，可以看到筆者創建了一張s1表，其中：

• id作為主鍵。
• key1作為普通索引。
• key2是唯一索引。
• key_part1+key_part2+key_part3構成唯一索引。

s2與s1結構一致，這里就不多做介紹，對應DDL語句如下所示：

CREATE TABLE s1
(
    id           INT NOT NULL AUTO_INCREMENT,
    key1         VARCHAR(100),
    key2         INT,
    key3         VARCHAR(100),
    key_part1    VARCHAR(100),
    key_part2    VARCHAR(100),
    key_part3    VARCHAR(100),
    common_field VARCHAR(100),
    PRIMARY KEY (id),
    KEY          idx_key1 (key1),
    UNIQUE KEY idx_key2 (key2),
    KEY          idx_key3 (key3),
    KEY          idx_key_part(key_part1,key_part2,key_part3)
) Engine = InnoDB CHARSET = utf8;

id字段

針對每一個select語句都會為其分配一個id字段，該id就代表的每一個select語句的執行計劃信息。

我們先說個簡單的例子，針對下面這句單表執行的語句，它就只有一行數據，所以就只有一個id為1的執行計劃：

explain select * FROM  s1 WHERE s1.common_field =1;

我們再來一個union合并查詢：

explain select * FROM  s1 union select * from s2;

從執行計劃可以看到s1的id為1，s2的id為2，分別進行了一個select查詢：

需要注意的是連接查詢的驅動表和被驅動表的id都是一樣的，出現在前面的是驅動表，而后面的就是被驅動表：

explain select * from s1 inner join s2 on s1.id =s2.id ;

從執行計劃就可以看出，s1就是驅動表，s2就是被驅動表：

我們再來一個特殊的SQL，這句原本是子查詢，正常情況下應該是s1表的id為1，s2表的id為2：

explain select * FROM s1  where key1 in (select key3 from s2 );

但是SQL優化器經過分析發現這句可以被優化為連接查詢，即下面這句SQL：

explain select * from s1 inner join s2 on s1.key1 =s2.key3 ;

所以執行計劃就顯示id是一樣的，且s1作為驅動表，s2作為被驅動表：

table字段

table字段含義比較簡單，它表示當前查詢計劃所針對的數據表，例如下面這個簡單查詢語句：

explain select * FROM  s1 WHERE s1.common_field =1;

它所查詢的就是針對s1表：

而下面這句涉及連接查詢，所以從執行計劃中也能看出不同執行計劃所針對的表：

explain select * from s1 inner join s2;

可以看到驅動表s1進行全表掃描，而被驅動表s2是通過hash join進行連接查詢：

select_type

select_type決定了你的SQL涉及的查詢類型，常見的有:

(1) SIMPLE：簡單查詢，如下所示，可以看到簡單的SQL語句就屬于這種查詢類型

explain select * from s1

對應的執行計劃如下所示：

(2) PRIMARY：涉及關聯或者子查詢的語句對應左邊的語句就是PRIMARY，如下SQL所示，可以看到對應的u表的查詢就可以作為PRIMARY語句：

explain select * FROM  s1 union select * from s2;

我們查看執行計劃的截圖，可以看到涉及這種嵌套查詢的SQL左邊的SQL就是PRIMARY:

(3) UNION：從執行計劃的截圖就可以看出union關鍵字后面的SQL就屬于union

explain select * FROM  s1 union select * from s2;

對應執行計劃如下圖所示：

(4) UNION RESULT：包含union的處理結果集，在union和union all語句中，基于其它查詢結果進行合并(可能有去重的過程)，需要通過一個臨時表才能完成的操作就是UNION RESULT也就是我們上述那句SQL的第三步。

(5) DEPENDENT SUBQUERY：如下SQL所示，在SQL優化器明確指明子查詢無法轉為半連接查詢的情況下，第一個select的子查詢對應的select type就是DEPENDENT SUBQUERY：

explain select * from s1 where s1.common_field in (SELECT id from s2 WHERE s1.common_field=s2.common_field) or key3='a';

對應的執行計劃如下所示，可以看到s2的執行類型就是DEPENDENT SUBQUERY：

(6) DEPENDENT UNION：如下SQL所示，在涉及union的子查詢中有無數個小查詢，除去union的左邊哪個小查詢，其余的都是DEPENDENT UNION

explain select * from s1 WHERE key1 IN (SELECT key1 from s1 union SELECT key1 from s2)

這一點，從執行計劃中就可以看出，子查詢內部的s2查詢的類型就是DEPENDENT UNION

(7) DERIVED：在FROM列表中包含的子查詢被標記為DERIVED(衍生);MySQL會遞歸執行這些子查詢, 把結果放在臨時表里:

explain SELECT * from (select COUNT(*)  from student as a) b

對應的我們可以在執行計劃中印證這一點：

type字段(重點)

type決定了進行SQL查詢的時的訪問方法，該字段對于SQL執行性能分析有著至關重要的參考價值：

(1) system：表中只有一行或者空表，即存儲引擎中統計的數據是正確的。

(2) const：基于聚簇索引或者非空的唯一二級索進行定位數據，時間復雜度為O(1)，這種高速的常量級查詢我們就可以稱為const：

explain select * FROM  s1 WHERE id=1;

對應執行計劃如下：

(3) eq_ref：該查詢意味著進行關聯查詢時，被驅動表內部走了聚簇索引或者非空的二級索引查詢：

explain SELECT * FROM s1 inner join  s2 on s1.id=s2.id;

(4) ref：通過那些非唯一的二級索引進行精準定位，這種在二級索引區間構成一個掃描區間進行定位，然后再通過回表獲取所有數據的執行就是ref:

explain select * from s1 WHERE  key1='a';

對應的執行計劃截圖如下圖所示：

(5) fulltext：全文匹配，大字符索引匹配。

(6) ref_or_null：基于普通二級索引查詢且查詢時還需要查詢可能為空的情況：

explain select * from s1 WHERE  key1='a' or key1 is NULL ;

(7) unique_subquery：即子查詢被優化為exist，且子查詢返回的是聚簇索引：

explain select * from s1 where s1.common_field in (SELECT id from s2 WHERE s1.common_field=s2.common_field) or key3='a';

(8) index_subquery：和上述查詢類似，只不過子查詢內部返回的是普通二級索引：

(9) range：范圍查詢。

(10) index_merge：索引合并，即進行SQL查詢時對應的條件都是索引類型，SQL優化器進行查詢時讓兩個索引分別到自己的二級索引樹拿到有序的id集合然后取交集得到聚簇索引值進行回表：

對飲的SQL如下，可以看到我們查詢條件都走了索引，查詢結果是基于多個索引的掃描區間共同構成的聚簇索引，然后取并集進行回表：

EXPLAIN select * FROM s1 WHERE KEY1='a' or key3='b'

這一點我們可以通過查詢執行計劃印證： +

(11) index：如下SQL所示，查詢時基于聯合索引，但不符合最左匹配原則，所以需要進行全索引掃描匹配key_part2，但查詢時無需回表，這種基于二級索引全掃描但無需回表的訪問方法就是index：

explain select s1.key_part1,s1.key_part2,s1.key_part3 from s1 WHERE  key_part2='a';

對應執行計劃如下圖所示：

(12) ALL：全表掃描。

extra

這個字段也很重要，它表示當前SQL語句的一些額外的信息：

• Using filesort:即代表SQL查詢時用到了文件掃描，使用了外部的索引進行排序，并沒有用到我們自己定義的索引，性能較差。
• using index:這種方式性能就不錯了，使用了索引并且不需要回表就得到了我們需要的數據，即用到了索引覆蓋。
• Using temporary:MySQL查詢排序時使用了臨時表性能較于filesort更差。
• using where：即代表查詢時僅僅用到了普通的where條件，并沒有用到任何索引，查詢需要在server層進行判斷。
• Using join buffer:在進行連接查詢時，被驅動表的數據定位并沒有走索引，于是將驅動表的數據放入緩沖區進行關聯匹配。
• impossible where:說明where條件基本得不到需要的結果，篩選數據時一直處于false的狀態。

possible_keys

表示當前查詢可能用到的索引。如下這個執行計劃，它就以為著可能用到了主鍵

key(用到的索引名稱)

表示用到的索引名稱，如下所示下面這條sql可能就用到了這兩個索引。

key_len

key_len表示使用索引時，對應使用到的索引的長度，在MySQL的EXPLAIN語句中，key_len列表示使用索引的鍵部分的字節數。它是一個估計值，根據查詢中使用的索引類型和數據類型來計算。通常，key_len越小，性能就越好，因為它意味著需要讀取更少的數據塊。 例如，如果你有一個使用VARCHAR(100)數據類型的列作為索引，并且查詢中只使用了前10個字符作為搜索條件，則key_len將是10。如果你使用的是INT(10)數據類型的列作為索引，則key_len將是4，因為INT類型占用4個字節。 在優化查詢時，理解key_len可以幫助你確定哪些索引可以更有效地支持查詢，以及如何進一步優化索引設計。

例如下面這一句，實際上索引長度就是303，原因很簡單：

• key1為varchar(100)且用的是utf8，所以長度為300字節。
• 允許空再加一個字節。
• varchar需要2字節維護長度進行再加2字節。 最終得到303字節：

explain select * from s1 WHERE key1>'a' and key1<'b';

ref

表示進行索引匹配時，與之比對的數據類型，例如下面這句key1比對的是一個函數計算值，所以ref是func：

explain select * FROM s1 inner join s2 on s2.key1 =UPPER(s1.key1);

例如這句與索引匹配的是常數，所以得到的是const：

explain select * from s1 WHERE  key1='a';

當然進行關聯查詢時被驅動表得到的就是驅動表的id，如下返回的就是s1.id:

explain SELECT * FROM s1 inner join  s2 on s1.id=s2.id;

rows

rows意味著我們查詢時大體需要掃描多少行，對于單表查詢沒什么，但是對于多表查詢，從這個數據我們可以得知關聯查詢哪個作為驅動表：

explain SELECT * FROM customer c inner join customer_balances cb on c.id =cb.c_id ;

因為cb的rows為1，可知這張表變為被驅動表走索引定位：

id|select_type|table|partitions|type|possible_keys             |key                       |key_len|ref    |rows   |filtered|Extra|
--+-----------+-----+----------+----+--------------------------+--------------------------+-------+-------+-------+--------+-----+
 1|SIMPLE     |c    |          |ALL |PRIMARY                   |                          |       |       |4270364|   100.0|     |
 1|SIMPLE     |cb   |          |ref |customer_balances_c_id_IDX|customer_balances_c_id_IDX|8      |db.c.id|      1|   100.0|     |

filter(讀取和過濾占比)

表示選取的行和讀取的行占比，例如下面這句SQL：

explain select * from  s1 WHERE key1 > '1' and s1.common_field ='1';

從筆者執行計劃來看，可能會掃描49902，只有大約10%的符合要求：

該查詢在單表查詢中沒有太大意義，但是在連接查詢中就比較有參考價值了，例如下面這句SQL：

explain
 select
 *
from
 s1
inner join s2 on
 s1.key1 = s2.key1
WHERE
 s1.common_field = 'a'

從執行計劃可以看出s1作為驅動表大約掃描99805列數據，有10%符合要求，而被驅動表s2過濾值為1和100%比例，這意味著針對被驅動表的查詢次數可能是99805*0.1大約9980次。

小結

通過本文的探索，我們深入了解了MySQL中的EXPLAIN關鍵字的重要性和作用。EXPLAIN不僅可以幫助我們分析查詢執行計劃，還可以為我們提供優化查詢性能的關鍵線索。

通過解讀EXPLAIN的輸出結果，我們學會了如何識別潛在的性能瓶頸，并優化查詢以提高數據庫的效率和響應速度。了解索引的使用、表連接順序以及訪問類型等信息，能夠幫助我們更好地優化查詢并避免常見的查詢性能問題。

在實際應用中，不斷深入學習和理解EXPLAIN的輸出結果，結合實際場景進行優化實踐，將為我們的數據庫應用帶來明顯的性能改善和優勢。通過不斷優化查詢性能，我們可以提升數據庫系統的整體效率，提供更好的用戶體驗和服務質量。

在今后的數據庫開發和維護工作中，讓我們繼續積極運用EXPLAIN關鍵字，不斷優化查詢執行計劃，提升數據庫性能，為應用程序的穩定性和可靠性打下堅實的基礎。