這篇文章帶給大家的內容是關於MySQL中EXPLAIN解釋指令的介紹（附範例），有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。
1 EXPLAIN概念
EXPLAIN會提供我們一些MySQL是執行sql的資訊：
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 |
| mysql認為執行sql時必須被校驗的行數 | ##filtered | filtered |
|---|---|---|
| Extra | #None | 附加資訊 |
| #2.1 id | SELECT識別碼。 SELECT在查詢中的序號，可以為空。 | |
| SELECT類型，所有類型在下表中展示，JSON格式的EXPLAIN將SELECT類型公開為query_block的屬性，除非它是SIMPLE或PRIMARY。 JSON名稱(不適用為None)也顯示在表中。 | select_type Value | |
| Meaning | ||
| None | 簡單SELECT(不使用UNION或子查詢等) | |
| None | #嵌套查詢時最外層的查詢 | |
| None | UNION中的第二或後面的SELECT語句 | |
| dependent (true) | UNION中的第二或以後的SELECT語句，取決於外部查詢 | |
| union_result | UNION的結果 | |
| None | #子查詢中的第一個選擇 | |
| #dependent (true) | 子查詢中的第一個選擇，取決於外部查詢 |
表格資訊（後面示範用）：
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)
顯示這一行的資料是關於哪張表的，有時是真實的表名字，有時也可能是以下幾種結果
查詢的記錄所屬於的分割區，對於未分割表，該值為NULL。
連接使用了哪個類別,有無使用索引,常用的類型有：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索引執行連線
在下面的範例中，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)
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)
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)
ALL：對於每個來自先前的表格的行組合，進行完整的表格掃描。如果表是第一個沒有標記const的表，這通常不好，並且通常在它情況下很差。通常可以增加更多的索引而不要使用ALL，使得行能基於前面的表中的常數值或列值被檢索出。
possible_keys欄位指出MySQL能使用哪個索引在該表中找到行。請注意，此列完全獨立於EXPLAIN輸出所示的表的順序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。
如果該欄位是NULL，則沒有相關的索引。在這種情況下，可以透過檢查WHERE子句看是否它引用某些欄位或適合索引的欄位來提高你的查詢效能。如果是這樣，創建一個適當的索引並且再次用EXPLAIN檢查查詢
key列顯示MySQL實際決定使用的鍵（索引）。如果沒有選擇索引，鍵是NULL。若要強制MySQL使用或忽略possible_keys欄位中的索引，在查詢中使用FORCE INDEX、USE INDEX或IGNORE INDEX。
key_len欄位顯示MySQL決定使用的鍵長度。如果鍵是NULL，則長度為NULL。
所使用的索引的長度。在不損失精確性的情況下，長度越短越好
ref列顯示使用哪個列或常數與key一起從表中選擇行。
rows列顯示MySQL認為它執行查詢時必須檢查的行數。
該欄位包含MySQL解決查詢的詳細資訊,下面詳細資訊.
【相關推薦：MySQL影片教學】
以上是MySQL中EXPLAIN解釋指令的介紹（附範例）的詳細內容。更多資訊請關注PHP中文網其他相關文章！
