一文搞懂MySQL中EXPLAIN解釋命令

本文主要給大家簡單講講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時輸出中顯示的等效屬性名稱 ,第三列：字段含義

2.1 id

SELECT標識符。SELECT在查詢中的序列號，可以為空。

2.2 select_type

SELECT類型，所有類型在下表中展示，JSON格式的EXPLAIN將SELECT類型公開為query_block的屬性，除非它是SIMPLE或PRIMARY。 JSON名稱(不適用為None)也顯示在表中。

表信息（后面演示用）：

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

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

• <unionM,N>: 指id為M,N行結果的并集
• <derivedN>: 該行是指id值為n的行的派生表結果。派生表可能來自例如from子句中的子查詢。
• <subqueryN>: 該行是指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?？梢蕴鎿QIN子查詢，但只適合下列形式的子查詢中的非唯一索引：

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的表，這通常不好，并且通常在它情況下很差。通?？梢栽黾痈嗟乃饕灰褂肁LL，使得行能基于前面的表中的常數值或列值被檢索出。

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中EXPLAIN解釋命令就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。