在MySQL數(shù)據(jù)庫中，優(yōu)化查詢性能是每個開發(fā)人員和數(shù)據(jù)庫管理員都需要面對的重要挑戰(zhàn)之一。其中，EXPLAIN關(guān)鍵字是一個強大的工具，可以幫助我們深入了解MySQL是如何執(zhí)行查詢的，以及如何優(yōu)化查詢性能。

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

無論您是初學(xué)者還是有經(jīng)驗的數(shù)據(jù)庫專家，本文都將為您提供有價值的見解和實用的技巧，助您在MySQL數(shù)據(jù)庫中更好地利用EXPLAIN關(guān)鍵字，優(yōu)化查詢性能，提升數(shù)據(jù)庫應(yīng)用的效率和穩(wěn)定性。

詳解explain對應(yīng)關(guān)鍵字

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

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

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

s2與s1結(jié)構(gòu)一致，這里就不多做介紹，對應(yīng)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語句的執(zhí)行計劃信息。

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

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

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

explain select * FROM  s1 union select * from s2;

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

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

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

從執(zhí)行計劃就可以看出，s1就是驅(qū)動表，s2就是被驅(qū)動表：

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

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

但是SQL優(yōu)化器經(jīng)過分析發(fā)現(xiàn)這句可以被優(yōu)化為連接查詢，即下面這句SQL：

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

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

table字段

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

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

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

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

explain select * from s1 inner join s2;

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

select_type

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

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

explain select * from s1

對應(yīng)的執(zhí)行計劃如下所示：

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

explain select * FROM  s1 union select * from s2;

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

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

explain select * FROM  s1 union select * from s2;

對應(yīng)執(zhí)行計劃如下圖所示：

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

(5) DEPENDENT SUBQUERY：如下SQL所示，在SQL優(yōu)化器明確指明子查詢無法轉(zhuǎn)為半連接查詢的情況下，第一個select的子查詢對應(yīng)的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';

對應(yīng)的執(zhí)行計劃如下所示，可以看到s2的執(zhí)行類型就是DEPENDENT SUBQUERY：

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

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

這一點，從執(zhí)行計劃中就可以看出，子查詢內(nèi)部的s2查詢的類型就是DEPENDENT UNION

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

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

對應(yīng)的我們可以在執(zhí)行計劃中印證這一點：

type字段(重點)

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

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

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

explain select * FROM  s1 WHERE id=1;

對應(yīng)執(zhí)行計劃如下：

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

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

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

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

對應(yīng)的執(zhí)行計劃截圖如下圖所示：

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

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

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

(7) unique_subquery：即子查詢被優(yōu)化為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：和上述查詢類似，只不過子查詢內(nèi)部返回的是普通二級索引：

(9) range：范圍查詢。

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

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

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

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

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

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

對應(yīng)執(zhí)行計劃如下圖所示：

(12) ALL：全表掃描。

extra

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

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

possible_keys

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

key(用到的索引名稱)

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

key_len

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

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

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

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

ref

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

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

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

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

當然進行關(guān)聯(lián)查詢時被驅(qū)動表得到的就是驅(qū)動表的id，如下返回的就是s1.id:

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

rows

rows意味著我們查詢時大體需要掃描多少行，對于單表查詢沒什么，但是對于多表查詢，從這個數(shù)據(jù)我們可以得知關(guān)聯(lián)查詢哪個作為驅(qū)動表：

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

因為cb的rows為1，可知這張表變?yōu)楸或?qū)動表走索引定位：

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';

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

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

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

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

小結(jié)

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

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

在實際應(yīng)用中，不斷深入學(xué)習(xí)和理解EXPLAIN的輸出結(jié)果，結(jié)合實際場景進行優(yōu)化實踐，將為我們的數(shù)據(jù)庫應(yīng)用帶來明顯的性能改善和優(yōu)勢。通過不斷優(yōu)化查詢性能，我們可以提升數(shù)據(jù)庫系統(tǒng)的整體效率，提供更好的用戶體驗和服務(wù)質(zhì)量。

在今后的數(shù)據(jù)庫開發(fā)和維護工作中，讓我們繼續(xù)積極運用EXPLAIN關(guān)鍵字，不斷優(yōu)化查詢執(zhí)行計劃，提升數(shù)據(jù)庫性能，為應(yīng)用程序的穩(wěn)定性和可靠性打下堅實的基礎(chǔ)。