前言

之前文章介紹索引失效的場景（聊一聊MySQL索引失效的問題），用到了explain執行計劃，執行計劃返回執行過程中每一步的資訊，而不是執行它。

透過返回的一行或多行資訊，顯示出執行計劃中的每一部分和執行的次序，從而可以從分析結果中，找到查詢語句或是表結構的效能瓶頸。

今天我們舉一些實際的例子，來解釋一下explain執行計劃各欄位的含義。


CREATE 
TABLE table_user(


id 
INT AUTO_INCREMENT,


user 
VARCHAR(
30),

pwd 
VARCHAR(
30),

description 
VARCHAR(
90),

PRIMARY 
KEY (
id));




CREATE 
TABLE table_role(


id 
INT AUTO_INCREMENT,


name 
VARCHAR(
30),

description 
VARCHAR(
90),

PRIMARY 
KEY (
id));




CREATE 
TABLE table_relation(


id 
INT AUTO_INCREMENT,

user_id 
INT,

role_id 
INT,


FOREIGN 
KEY (user_id) 
REFERENCES table_user (
id),


FOREIGN 
KEY (role_id) 
REFERENCES table_role (
id),

PRIMARY 
KEY (
id));




CREATE 
TABLE table_partitions(


id 
INT AUTO_INCREMENT,


name 
VARCHAR(
30),

age 
INT,

address 
VARCHAR(
30),

PRIMARY 
KEY (
id))
PARTITION 
BY 
HASH(
id) 
PARTITIONS 
2;

首先建立使用者，角色，關係，分割槽表。

插入一些資料。




insert 

into table_user(

user,pwd,description) 

value(

'tony',

'abc123',

'admin');



insert 

into table_user(

user,pwd,description) 

value(

'tom',

'123456',

'general user');



insert 

into table_user(

user,pwd,description) 

value(

'jerry',

'123456',

'general user');







insert 

into table_role(

name,description) 

value(

'admin',

'admin role');



insert 

into table_role(

name,description) 

value(

'general',

'general role');







insert 

into table_relation(user_id,role_id) 

value(

1,

1);



insert 

into table_relation(user_id,role_id) 

value(

2,

2);



insert 

into table_relation(user_id,role_id) 

value(

3,

2);







insert 

into table_partitions(

name,age,address) 

value(

'wang',

21,

'shenzhen');



insert 

into table_partitions(

name,age,address) 

value(

'zhang',

23,

'shanghai');



insert 

into table_partitions(

name,age,address) 

value(

'li',

26,

'beijing');

建立索引。

CREATE INDEX index_age ON table_partitions(age);CREATE INDEX index_name_age ON table_partitions(name,age);

一、id欄位

select查詢的序列號，表示的是查詢中執行select子句或者是操作表的順序，id值越大優先順序越高，越先被執行。

explain select * from table_role,table_user; #因為排版問題，去掉了一些資訊+----+-------------+------------+------------+------+---------------+------+| id | select_type | table      | partitions | type | possible_keys | key  |+----+-------------+------------+------------+------+---------------+------+|  1 | SIMPLE      | table_role | NULL       | ALL  | NULL          | NULL ||  1 | SIMPLE      | table_user | NULL       | ALL  | NULL          | NULL |+----+-------------+------------+------------+------+---------------+------+

explain select * from table_relation where role_id=(select id from table_role where name='admin');+----+-------------+----------------+------------+------+---------------+---------+| id | select_type | table          | partitions | type | possible_keys | key     |+----+-------------+----------------+------------+------+---------------+---------+|  1 | PRIMARY     | table_relation | NULL       | ref  | role_id       | role_id ||  2 | SUBQUERY    | table_role     | NULL       | ALL  | NULL          | NULL    |+----+-------------+----------------+------------+------+---------------+---------+

二、select_type欄位

查詢的型別，主要是區別普通查詢和聯合查詢、子查詢之類的複雜查詢。

2.1 simple：簡單查詢，簡單的select查詢，查詢中不包含子查詢或者union查詢，請參考上一步執行結果。

2.2 primary：主鍵查詢，查詢中若包含任何複雜的子部分，最外層查詢則被標記為primary，請參考上一步執行結果。

2.3 subquery：子查詢，在select或者where列表中包含子查詢，請參考上一步執行結果。

2.4 derived：臨時表，在from表中包含臨時表的子查詢被標記為derived(衍生)。

explain select * from (select version())temp;+----+-------------+------------+------------+--------+---------------+------+| id | select_type | table      | partitions | type   | possible_keys | key  |+----+-------------+------------+------------+--------+---------------+------+|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL ||  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL |+----+-------------+------------+------------+--------+---------------+------+

2.5 union：聯合查詢，第二個select出現被標記為union查詢。

explain select description from table_user union select description from table_role;+------+--------------+------------+------------+------+---------------+------+| id   | select_type  | table      | partitions | type | possible_keys | key  |+------+--------------+------------+------------+------+---------------+------+|  1   | PRIMARY      | table_user | NULL       | ALL  | NULL          | NULL ||  2   | UNION        | table_role | NULL       | ALL  | NULL          | NULL || NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL |+------+--------------+------------+------------+------+---------------+------+

2.6 union result：聯合查詢中查詢的結果，從union表獲取結果的select查詢，請參考2.5執行結果。

三、talbe欄位

表示 explain 的一行需要查詢的表名。可能為臨時表<derived N>，或者聯合查詢的結果<union M,N>。如果不涉及對資料表的操作，顯示為NULL。請參考前幾步執行結果。

四、partitions欄位

表示 explain 的一行需要訪問哪個表的分割槽。

explain select * from table_partitions where id=1;+----+-------------+------------------+------------+-------+---------------+---------+| id | select_type | table            | partitions | type  | possible_keys | key     |+----+-------------+------------------+------------+-------+---------------+---------+|  1 | SIMPLE      | table_partitions | p1         | const | PRIMARY       | PRIMARY |+----+-------------+------------------+------------+-------+---------------+---------+

五、type欄位

表示關聯型別或訪問型別，該欄位是sql查詢最佳化中一個很重要的指標。

5.1 null：不訪問任何表和索引，直接返回結果

explain select version();+----+-------------+-------+------------+------+---------------+------+| id | select_type | table | partitions | type | possible_keys | key  |+----+-------------+-------+------------+------+---------------+------+|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL |+----+-------------+-------+------------+------+---------------+------+

5.2 system：只有一條資料的系統表或衍生表只有一條資料的主查詢，請參考2.4執行結果。

5.3 const：表示透過primary key 或者 unique 索引一次就找到了。

explain select * from table_partitions where id=1;+----+-------------+------------------+------------+-------+---------------+---------+| id | select_type | table            | partitions | type  | possible_keys | key     |+----+-------------+------------------+------------+-------+---------------+---------+|  1 | SIMPLE      | table_partitions | p1         | const | PRIMARY       | PRIMARY |+----+-------------+------------------+------------+-------+---------------+---------+

5.4 eq_ref：使用的是唯一索引，使用主鍵的關聯查詢，關聯查詢出的記錄只有一條。

explain select * from table_relation join table_user where table_user.id=table_relation.user_id;+----+-------------+----------------+------------+--------+---------------+---------+| id | select_type | table          | partitions | type   | possible_keys | key     |+----+-------------+----------------+------------+--------+---------------+---------+|  1 | SIMPLE      | table_relation | NULL       | ALL    | user_id       | NULL    ||  1 | SIMPLE      | table_user     | NULL       | eq_ref | PRIMARY       | PRIMARY |+----+-------------+----------------+------------+--------+---------------+---------+

5.5 ref：使用普通索引或者唯一性索引的部分字首，可能會找到多個符合條件的行。

explain select * from table_partitions where name='zhang';+----+-------------+------------------+------------+------+----------------+----------------+| id | select_type | table            | partitions | type | possible_keys  | key            |+----+-------------+------------------+------------+------+----------------+----------------+|  1 | SIMPLE      | table_partitions | p,p1      | ref  | index_name_age | index_name_age |+----+-------------+------------------+------------+------+----------------+----------------+

5.6 range：索引範圍掃描，常見於使用>,<,is null,between ,in ,like等運算子的查詢中。

explain select * from table_partitions where name like 'zhang';+----+-------------+------------------+------------+-------+----------------+----------------+| id | select_type | table            | partitions | type  | possible_keys  | key            |+----+-------------+------------------+------------+-------+----------------+----------------+|  1 | SIMPLE      | table_partitions | p,p1      | range | index_name_age | index_name_age |+----+-------------+------------------+------------+-------+----------------+----------------+

5.7 index：索引全表掃描，把索引從頭到尾掃一遍。

explain select name from table_partitions;+----+-------------+------------------+------------+-------+---------------+----------------+| id | select_type | table            | partitions | type  | possible_keys | key            |+----+-------------+------------------+------------+-------+---------------+----------------+|  1 | SIMPLE      | table_partitions | p,p1      | index | NULL          | index_name_age |+----+-------------+------------------+------------+-------+---------------+----------------+

5.8 all：掃描全表資料檔案。

explain select * from table_partitions;+----+-------------+------------------+------------+------+---------------+------+| id | select_type | table            | partitions | type | possible_keys | key  |+----+-------------+------------------+------------+------+---------------+------+|  1 | SIMPLE      | table_partitions | p,p1      | ALL  | NULL          | NULL |+----+-------------+------------------+------------+------+---------------+------+

六、possible_keys欄位

可能使用到的索引。

explain select * from table_partitions where name='zhang' and age=20;+----+-------------+------------------+------------+------+--------------------------+-----------+| id | select_type | table            | partitions | type | possible_keys            | key       |+----+-------------+------------------+------------+------+--------------------------+-----------+|  1 | SIMPLE      | table_partitions | p,p1      | ref  | index_age,index_name_age | index_age |+----+-------------+------------------+------------+------+--------------------------+-----------+

七、keys欄位

實際使用到的索引，參考上一步執行結果。

八、key_len欄位

表示索引中使用的位元組數。顯示的值為索引欄位的最大可能長度，並非實際使用長度，理論上越短越好。

explain select name from table_partitions;+----+-------+----------------+---------+------+------+----------+-------------+| id | type  | key            | key_len | ref  | rows | filtered | Extra       |+----+-------+----------------+---------+------+------+----------+-------------+|  1 | index | index_name_age | 128     | NULL |    1 |   100.00 | Using index |+----+-------+----------------+---------+------+------+----------+-------------+

explain select age from table_partitions;+----+-------+-----------+---------+------+------+----------+-------------+| id | type  | key       | key_len | ref  | rows | filtered | Extra       |+----+-------+-----------+---------+------+------+----------+-------------+|  1 | index | index_age | 5       | NULL |    1 |   100.00 | Using index |+----+-------+-----------+---------+------+------+----------+-------------+

透過對索引欄位index_name_age,index_age的對比，可以看出INT型欄位索引長度短了很多。

九、ref欄位

顯示用什麼內容來和索引列比較，可能是空，或者某個表的列，或者常量。

這個欄位很多介紹的文章經常寫錯，以為是比較用到的列，比如描述成：顯示索引的那一列被使用了，如果可能，是一個常量。

上一步我們沒有用到比較欄位，顯示就為NULL，如果對索引比較加上常量欄位，顯示的就是常量。

explain select name from table_partitions where name='zhang';+----+------+----------------+---------+-------+------+----------+-------------+| id | type | key            | key_len | ref   | rows | filtered | Extra       |+----+------+----------------+---------+-------+------+----------+-------------+|  1 | ref  | index_name_age | 123     | const |    1 |   100.00 | Using index |+----+------+----------------+---------+-------+------+----------+-------------+

如果對索引比較某個表的列，顯示的就是某個表的列。

explain select table_relation.id from table_relation,table_role where role_id=table_role.id;+----+-------+---------+---------+--------------------+------+----------+-------------+| id | type  | key     | key_len | ref                | rows | filtered | Extra       |+----+-------+---------+---------+--------------------+------+----------+-------------+|  1 | index | PRIMARY | 4       | NULL               |    2 |   100.00 | Using index ||  1 | ref   | role_id | 5       | mydb.table_role.id |    1 |   100.00 | Using index |+----+-------+---------+---------+--------------------+------+----------+-------------+

十、row欄位

根據表統計資訊及索引選用情況，估算出找到所需的記錄，需要讀取的行數。

explain select age from table_partitions where age>18;+----+-------+-----------+---------+------+------+----------+--------------------------+| id | type  | key       | key_len | ref  | rows | filtered | Extra                    |+----+-------+-----------+---------+------+------+----------+--------------------------+|  1 | index | index_age | 5       | NULL |    3 |   100.00 | Using where; Using index |+----+-------+-----------+---------+------+------+----------+--------------------------+

十一、filtered欄位

表示儲存引擎返回的資料過濾後，剩下多少滿足查詢的記錄數量的比例。單位是百分比，100%表示資料沒有被過濾。

explain select * from table_user where description='admin';+----+------+---------------+------+---------+------+------+----------+-------------+| id | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |+----+------+---------------+------+---------+------+------+----------+-------------+|  1 | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |+----+------+---------------+------+---------+------+------+----------+-------------+

十二、extra欄位

顯示額外的資訊。

可能值包括:

Using index查詢到的列被索引覆蓋，實際上就是覆蓋索引的使用。
Using where查詢未用到可用的索引，透過where條件過濾資料。
Using where,Using index透過where條件過濾資料，並且查詢用到了覆蓋索引。
Using index condition查詢使用到了索引，但是需要回表查詢。
Using temporary查詢後結果需要使用臨時表來儲存，一般在排序或者分組查詢時用到。
Using filesort無法利用索引完成的排序操作，也就是ORDER BY的欄位沒有索引。
Using join buffer在我們聯表查詢的時候，如果表的連線條件沒有用到索引，需要有一個連線緩衝區來儲存中間結果。
Impossible where在我們用不太正確的where語句，導致沒有符合條件的行。
No tables used我們的查詢語句中沒有FROM子句，或者有FROM DUAL子句。

END

舉例解釋一下explain各欄位的含義

相關文章