mysql 輸出解釋怎么看_了解MySQL中EXPLAIN解釋命令

1 EXPLAIN概念

EXPLAIN會向我們提供一些MySQL是執行sql的信息：

EXPLAIN可以解釋說明 SELECT, DELETE, INSERT, REPLACE, and UPDATE 等語句.

當EXPLAIN與可解釋的語句一起使用時，mysql會顯示一些來自于優化器的關于sql執行計劃的信息。即mysql解釋它是如何處理這些語句的，和表之間是如何連接的。想獲取更多關于EXPLAIN如何獲取執行計劃信息的。

當EXPLAIN后面是一個會話的connection_id 而不是一個可執行的語句時，它會展示會話的信息。

對于SELECT語句，EXPLAIN會產生額外的執行計劃信息，這些信息可以用SHOW WARNINGS顯示出來。

EXPLAIN對于檢查設計分區表的查詢時非常有用。

FORMAT選項可以用于選擇輸出格式，如果沒有配置FORMAT選項，默認已表格形式輸出。JSON 選項讓信息已json格式展示。

2 EXPLAIN 輸出列信息

EXPLAIN輸出的字段信息

第一列:列名, 第二列:FORMAT = JSON時輸出中顯示的等效屬性名稱 ,第三列：字段含義

Column

JSON Name

Meaning

select_id

select標識號

select_type

None

select類型

table

table_name

這一行數據是關于哪張表的

partitions

匹配的分區，對于未分區表，該值為空

type

access_type

使用的連接類別,有無使用索引

possible_keys

MySQL能使用哪個索引在該表中找到行

key

MySQL實際決定使用的鍵(索引)

key_len

key_length

MySQL決定使用的鍵長度。如果鍵是NULL，長度為NULL

ref

與索引關聯的列

rows

mysql認為執行sql時必須被校驗的行數

filtered

表示此查詢條件所過濾的數據的百分比

Extra

None

附加信息

2.1 id

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

2.2 select_type

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

select_type Value

JSON Name

Meaning

SIMPLE

None

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

PRIMARY

None

嵌套查詢時最外層的查詢

UNION

None

UNION中的第二個或后面的SELECT語句

DEPENDENT UNION

dependent (true)

UNION中的第二個或以后的SELECT語句，取決于外部查詢

UNION RESULT

union_result

UNION的結果

SUBQUERY

None

子查詢中的第一個選擇

DEPENDENT SUBQUERY

dependent (true)

子查詢中的第一個選擇，取決于外部查詢

DERIVED

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;

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

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

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+

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

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

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

+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+

+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+

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

+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

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

+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

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

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+

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

+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+

+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+

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

+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+

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;

+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+

+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+

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

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

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

+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+

+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+

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

+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+

+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+

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

+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+

+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+

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

+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+

+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+

| 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。當使用=、<>、>、>=、、BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range

mysql> explain select * from t_a where id > 8;

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

| 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解決查詢的詳細信息,下面詳細.

Distinct：一旦MYSQL找到了與行相聯合匹配的行，就不再搜索了

Not exists：MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了

Range checked for each：沒有找到理想的索引，因此對于從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，并用它來從表中返回行。這是使用索引的最慢的連接之一

Using filesort：看到這個的時候，查詢就需要優化了。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行

Using index：列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候

Using temporary：看到這個的時候，查詢需要優化了。這里，MYSQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上

Using where：使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會發生，或者是查詢有問題

參考：

mysql 輸出解釋怎么看_了解MySQL中EXPLAIN解釋命令

相關文章

MYSQL數據庫默認latin1字符集轉換為GBK或UTF8

【Post工具】PostMan 他媳婦 PostWoman

MYSQL統計和識別重復值

main spring啟動_SpringBoot學習（一）：為什么main方法啟動類需要放在項目根目錄...

學習筆記~~~~LinkedHashMap

[轉]Mysql Join語法解析與性能分析

css radial-gradient 徑向漸變基本語法與使用

華為鴻蒙系統技術細節盤點

spring boot2.x設置session有效時間_Spring 源碼解析 Scopes 之 Request 、Session 、Application...

[SQLite]使用記錄

學習筆記~~~~~TreeMap

程序員別再迷茫，賺錢，方法比你想的更多

Mac 創建本地Mysql_2018-09-25：mac下創建本地數據庫mysql

.NET Core 學習資料精選：入門

學習筆記~~~~~Set接口實現

15句喬布斯經典語錄（中英文）

HashOperations

mysql一些常用操作_mysql的一些常用操作（一）

關于程序員的腦筋急轉彎（附答案）

怎么確保一個集合不能被修改