MySQL中EXPLAIN解釋指令的介紹（附範例）

這篇文章帶給大家的內容是關於MySQL中EXPLAIN解釋指令的介紹（附範例），有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。

1 EXPLAIN概念

EXPLAIN會提供我們一些MySQL是執行sql的資訊：

1. EXPLAIN可以解釋說明SELECT, DELETE, INSERT, REPLACE, and UPDATE 等語句.
2. 當EXPLAIN與可解釋的語句一起使用時，mysql會顯示一些來自於優化器的關於sql執行計劃的資訊。即mysql解釋它是如何處理這些語句的，和表格之間是如何連接的。想取得更多關於EXPLAIN如何取得執行計劃資訊的。
3. 當EXPLAIN後面是一個會話的connection_id 而不是一個可執行的語句時，它會顯示會話的資訊。
4. 對於SELECT語句，EXPLAIN會產生額外的執行計劃訊息，這些訊息可以用SHOW WARNINGS顯示出來。
5. EXPLAIN對於檢查設計分區表的查詢時非常有用。
6. FORMAT選項可以用來選擇輸出格式，如果沒有設定FORMAT選項，預設已表格形式輸出。 JSON 選項讓資訊已json格式展示。

2 EXPLAIN 輸出列資訊

EXPLAIN輸出的欄位資訊
第一列:列名, 第二列:FORMAT = JSON時輸出中顯示的等效屬性名稱,第三列：欄位意義

Column	#JSON Name	#Meaning
#id	select_id	select識別號碼
select_type	None
		# select類型
table	table_name	這行資料是關於哪一張表格的
partitions	partitions	符合的分區，對於未分區表，該值為空
type	access_type	使用的連接類別,有無使用索引
possible_keys	possible_keys	MySQL能使用哪個索引在該表中找到行
key	key	MySQL實際決定使用的鍵（索引）
key_len	key_length	MySQL決定使用的鍵長。如果鍵是NULL，長度為NULL

ref

ref

與索引關聯的欄位

rowsrows##filteredfiltered表示此查詢條件所篩選的資料的百分比Extra#None 附加資訊2.2 select_typeselect_type ValueJSON NameMeaning SIMPLENone簡單SELECT(不使用UNION或子查詢等)PRIMARYNone #嵌套查詢時最外層的查詢UNIONNoneUNION中的第二或後面的SELECT語句DEPENDENT UNIONdependent (true)UNION中的第二或以後的SELECT語句，取決於外部查詢#UNION RESULTunion_resultUNION的結果##SUBQUERYDEPENDENT SUBQUERYDERIVED

mysql認為執行sql時必須被校驗的行數
	#2.1 id	SELECT識別碼。 SELECT在查詢中的序號，可以為空。
SELECT類型，所有類型在下表中展示，JSON格式的EXPLAIN將SELECT類型公開為query_block的屬性，除非它是SIMPLE或PRIMARY。 JSON名稱(不適用為None)也顯示在表中。
None	#子查詢中的第一個選擇
#dependent (true)	子查詢中的第一個選擇，取決於外部查詢

None######衍生表（子查詢中產生的臨時表）############MATERIALIZED ######materialized_from_subquery######物化子查詢############UNCACHEABLE SUBQUERY######cacheable (false)#######無法快取結果的子查詢，必須對外部查詢的每一行進行重新計算############UNCACHEABLE UNION######cacheable (false)######UNION中屬於不可快取子查詢的第二或以後的選擇(請參UNCACHEABLE SUBQUERY)#############

表格資訊（後面示範用）：

mysql> show create table t_a;
------+
| t_a   | CREATE TABLE `t_a` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `age` int(20) DEFAULT NULL,
  `code` int(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  KEY `age_key` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------
------+
1 row in set (0.03 sec)

SIMPLE：簡單SELECT(不使用UNION或子查詢等)

mysql> explain select * from t_a where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_a   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)

PRIMARY：嵌套查詢時最外層的查詢

mysql> explain select * from t_a where num >(select num from t_a where id = 3);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY     | t_a   | NULL       | range | num_key       | num_key | 5       | NULL  |    6 |   100.00 | Using where; Using index |
|  2 | SUBQUERY    | t_a   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
2 rows in set, 1 warning (0.03 sec)

UNION：UNION中的第二或後面的SELECT語句

mysql> explain select * from t_a where id =9 union all select * from t_a;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | t_a   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL        |
|  2 | UNION       | t_a   | NULL       | index | NULL          | num_key | 5       | NULL  |    9 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.04 sec)

DEPENDENT UNION：UNION中的第二或以後的SELECT語句，取決於外部查詢

mysql> explain select * from t_a where id in (select id from t_a where id >8 union all select id from t_a where id =5);
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY            | t_a   | NULL       | index  | NULL          | num_key | 5       | NULL  |    9 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t_a   | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | func  |    1 |   100.00 | Using where; Using index |
|  3 | DEPENDENT UNION    | t_a   | NULL       | const  | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using index              |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.08 sec)

UNION RESULT：UNION的結果

mysql> explain select num from t_a where id = 3 union select num from t_a where id =4;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | t_a        | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL            |
|  2 | UNION        | t_a        | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.03 sec)

SUBQUERY：子查詢中的第一個選擇

mysql> explain select * from t_a where num >(select num from t_a where id = 3);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY     | t_a   | NULL       | range | num_key       | num_key | 5       | NULL  |    6 |   100.00 | Using where; Using index |
|  2 | SUBQUERY    | t_a   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL                     |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
2 rows in set, 1 warning (0.03 sec)

DEPENDENT SUBQUERY：子查詢中的第一個選擇，取決於外部查詢

mysql> explain select * from t_a where num in(select num from t_a where id = 3 union select num from t_a where id =4);
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type        | table      | partitions | type  | possible_keys   | key     | key_len | ref   | rows | filtered | Extra                    |
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY            | t_a        | NULL       | index | NULL            | num_key | 5       | NULL  |    9 |   100.00 | Using where; Using index |
|  2 | DEPENDENT SUBQUERY | t_a        | NULL       | const | PRIMARY,num_key | PRIMARY | 8       | const |    1 |   100.00 | NULL                     |
|  3 | DEPENDENT UNION    | t_a        | NULL       | const | PRIMARY,num_key | PRIMARY | 8       | const |    1 |   100.00 | NULL                     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL   | NULL            | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary          |
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
4 rows in set, 1 warning (0.12 sec)

DERIVED：派生表（子查詢中產生的臨時表）

mysql> explain select a.id from (select id from t_a where id >8 union all select id from t_a where id =5) a;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    3 |   100.00 | NULL                     |
|  2 | DERIVED     | t_a        | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL  |    1 |   100.00 | Using where; Using index |
|  3 | UNION       | t_a        | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | Using index              |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.12 sec)

2.3 table

顯示這一行的資料是關於哪張表的，有時是真實的表名字，有時也可能是以下幾種結果

• : 指id為M,N行結果的並集
• : 該行是指id值為n的行的衍生表結果。派生表可能來自例如from子句中的子查詢。
• : 該行是指id值為n的行的物化子查詢的結果。

2.4 partitions

查詢的記錄所屬於的分割區，對於未分割表，該值為NULL。

2.5 type

連接使用了哪個類別,有無使用索引,常用的類型有：system,  const, eq_ref, ref, range, index, ALL（從左到右，效能越來越差），詳情查看EXPLAIN Join Types

NULL：MySQL在最佳化過程中分解語句，執行時甚至不用存取表或索引，例如從索引列中選取最小值可以透過單獨索引尋找完成

system：這個表（也可能是查詢出來的臨時表）只有一行資料(= system table). 是const中的一個特例

const ：表最多有一個匹配行，它將在查詢開始時讀取。因為僅有一行，在這行的列值可被最佳化器剩餘部分認為是常數。 const表很快，因為它們只讀取一次！ const用於查詢條件為PRIMARY KEY或UNIQUE索引並與常數值進行比較時的所有部分。
 在下面的查詢中，tbl_name可以用於const表：

SELECT * from tbl_name WHERE primary_key=1；
SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2；

--例子
mysql> explain select * from t_a where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_a   | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.07 sec)

eq_ref：對於前幾個表中的每一行組合，從該表中讀取一行。除了system和const，這是最好的連接類型。當連接使用索引的所有部分，並且索引是主鍵或唯一非空索引時，將使用它。 eq_ref可以用來使用= 運算子比較的帶索引的列。比較值可以是常數或一個使用在該表前面所讀取的表的列的表達式。
在下面的範例中，MySQL可以使用eq_ref聯結去處理ref_tables：

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

--例子（t_b为t_a的复制表，表结构相同）
mysql> explain select * from t_a,t_b where t_a.code=t_b.code;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
|  1 | SIMPLE      | t_a   | NULL       | ALL    | uk_code       | NULL    | NULL    | NULL          |    9 |   100.00 | NULL  |
|  1 | SIMPLE      | t_b   | NULL       | eq_ref | uk_code       | uk_code | 4       | test.t_a.code |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 1 warning (0.03 sec)

ref對於每個來自前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。如果聯接只使用鍵的最左邊的前綴，或者如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯接不能基於關鍵字查詢結果為單一行的話），則使用ref。如果使用的鍵僅符合少量行，則該連接類型是不錯的。 ref可以用來使用=或<=>運算子的帶索引的欄位。
在下面的範例中，MySQL可以使用ref聯結來處理ref_tables：

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

--例子（t_b为t_a的复制表，表结构相同）
mysql> explain select * from t_a,t_b where t_a.age=t_b.age;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref          | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
|  1 | SIMPLE      | t_a   | NULL       | ALL  | age_key       | NULL    | NULL    | NULL         |    9 |   100.00 | Using where |
|  1 | SIMPLE      | t_b   | NULL       | ref  | age_key       | age_key | 5       | test.t_a.age |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)

fulltext：使用FULLTEXT索引執行連線

##ref_or_null：這個聯結類型ref類似，但是新增了MySQL可以專門搜尋包含NULL值的行。在解決子查詢中經常使用該聯結類型的最佳化。

 在下面的範例中，MySQL可以使用ref_or_null連結來處理ref_tables：

SELECT * FROM ref_table
  WHERE key_column=expr OR key_column IS NULL;

--例子
mysql> explain select * from t_a where t_a.age =3 or t_a.age is null;
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t_a   | NULL       | ref_or_null | age_key       | age_key | 5       | const |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)

index_merge：此聯結類型表示使用了索引合併最佳化方法。在這種情況下，key列包含了使用的索引的清單，key_len包含了使用的索引的最長的關鍵元素。

SELECT * FROM ref_table
  WHERE idx1=expr1 OR idx2 =expr2;

--例子
mysql> explain select * from t_a where t_a.code =3 or t_a.age = 3;
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                     |
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
|  1 | SIMPLE      | t_a   | NULL       | index_merge | uk_code,age_key | uk_code,age_key | 4,5     | NULL |    2 |   100.00 | Using union(uk_code,age_key); Using where |
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
1 row in set, 1 warning (0.03 sec)

unique_subquery：這個類型取代了下面形式的IN子查詢的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)  unique_subquery是一個索引查找函數，可以完全取代子查詢，效率更高。
index_subquery：此聯接類型類似於unique_subquery。可以取代IN子查詢，但只適合下列形式的子查詢中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)range：只檢索給定範圍的行，使用一個索引來選擇行。 key列顯示使用了哪個索引。 key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL。使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或IN運算符，用常數比較關鍵字列時，可以使用range

mysql> explain select * from t_a where id > 8;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_a   | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.03 sec)

index：此聯結類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引檔案通常比資料檔案小。當查詢只使用作為單一索引一部分的欄位時，MySQL可以使用該聯結類型。

ALL：對於每個來自先前的表格的行組合，進行完整的表格掃描。如果表是第一個沒有標記const的表，這通常不好，並且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，使得行能基於前面的表中的常數值或列值被檢索出。

2.6 possible_keys

possible_keys欄位指出MySQL能使用哪個索引在該表中找到行。請注意，此列完全獨立於EXPLAIN輸出所示的表的順序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。

如果該欄位是NULL，則沒有相關的索引。在這種情況下，可以透過檢查WHERE子句看是否它引用某些欄位或適合索引的欄位來提高你的查詢效能。如果是這樣，創建一個適當的索引並且再次用EXPLAIN檢查查詢

2.7 key

key列顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL。若要強制MySQL使用或忽略possible_keys欄位中的索引，在查詢中使用FORCE INDEX、USE INDEX或IGNORE INDEX。

2.8 key_len

key_len欄位顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。
 所使用的索引的長度。在不損失精確性的情況下，長度越短越好

2.9 ref

ref列顯示使用哪個列或常數與key一起從表中選擇行。

2.10 rows

rows列顯示MySQL認為它執行查詢時必須檢查的行數。

2.11 Extra

該欄位包含MySQL解決查詢的詳細資訊,下面詳細資訊.

1. Distinct：一旦MYSQL找到了與行相符的行，就不再搜尋了
2. Not exists：MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜尋了
3. Range checked for each：沒有找到理想的索引，因此對於從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，並用它來從表中返回行。這是使用索引的最慢的連接之一
4. Using filesort：看到這個的時候，查詢就需要優化了。 MYSQL需要進行額外的步驟來發現如何對傳回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行
5. Using index：列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表回傳的，這發生在對錶的全部的請求列都是同一個索引的部分的時候
6. Using temporary：看到這個的時候，查詢需要優化了。這裡，MYSQL需要建立一個臨時表來儲存結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上
7. Using where：使用了WHERE從句來限制哪些行將與下一張表匹配或是回傳給用戶。如果不想回傳表中的全部行，並且連接類型ALL或index，這就會發生，或是查詢有問題

【相關推薦：MySQL影片教學】

以上是MySQL中EXPLAIN解釋指令的介紹（附範例）的詳細內容。更多資訊請關注PHP中文網其他相關文章！