Explain查看select执行计划
EXPLAIN
EXPLAIN可以查询SQL的执行计划(QEP)，根据执行计划可以查看是否存在全表扫描，是否有无效的索引等，我们可以针对性的优化SQL，写出较高性能的查询。
|
|
执行计划有十列
- id
- select_type
- table
- type
- possible_keys
- key
- key_len
- ref
- rows
- Extra
分别看一下他们的含义。
id
id列显示执行计划中的操作顺序。相同id序列的，从上向下线性执行，不同id序列，序列值大的先执行。
先执行id为3的查询，再执行id为2和1的查询。
select_type
select_type表示查询的类型，常见类型有SIMPLE,PRIMARY,DERIVED,UNION
- SIMPLE - 这种类型最常见，指不包含子查询和UNION查询
- PRIMARY - 复杂查询中最外层的查询
- DERIVED - 非物理表；FROM语句中子查询
- UNION - Union之后的SELECT查询或第二个SELECT查询
UNION RESULT - Union操作的结果集，此时table的值是
123456789mysql> explain SELECT * FROM sanitytj.subcaseinfo WHERE subtaskid=16787 union select * from subcaseinfo;+----+------------+----------+----+-------------+-------+-------+-----+-------+---------------+|id |select_type |table |type|possible_keys|key |key_len|ref |rows |Extra |+----+------------+----------+----+-------------+-------+-------+-----+-------+---------------+|1 | PRIMARY |subinfo |ref |sub_idx |sub_idx|3 |const|151 |NULL ||2 | UNION |subinfo |ALL |NULL |NULL |NULL |NULL |1879711|NULL ||NULL|UNION RESULT|<union1,2>|ALL |NULL |NULL |NULL |NULL |NULL |Using temporary|+----+------------+----------+----+-------------+-------+-------+-----+-------+---------------+3 rows in set (0.00 sec)DEPENDENT UNION - Union中的第二个或之后的SELECT查询,依 赖于外部查询
DEPENDENT SUBQUERY - 子查询中的第一个SELECT查询，依赖于外部查询
12345678910mysql> explain select * from subtask where id in(select distinct subtaskid from subcaseinfo WHERE subtaskid=16787 unionall select distinct subtaskid from subcaseinfo);+---+-----------------+-------+------+-------------+--------+-------+------+-----+------------+|id | select_type | table |type |possible_keys|key |key_len| ref |rows |Extra |+---+-----------------+-------+------+-------------+--------+-------+------+-----+------------+|1 |PRIMARY |subtask| ALL |NULL |NULL |NULL |NULL |12491|Using where ||2 |DEPENDENT SUBQUERY|subinfo| ref |sub_idx |sub_idx |3 |const | 151 |Using where ||3 |DEPENDENT UNION |subinfo| ref |sub_idx |sub_idx |3 |func | 178 |Using index |+---+-----------------+-------+------+-------------+--------+-------+------+-----+------------+3 rows in set (0.00 sec)UNCACHEABLE UNION - Union中第二个或之后的SELECT查询,属于不可缓存的子查询
- SUBQUERY - 子查询中的第一个SELECT查询
- UNCACHEABLE SUBQUERY - 一个子查询的结果不能被缓存，必须重新评估外层查询
table
table表示该行引用的是哪张表的数据，table有两类，一类是物理表，即真实的表，一类是关联的结果行。
- unionM,N：指查询序列M到N的Union查询行。
- derivedN：指refer一个ID为N的派生表查询结果集，如From中的子查询。
- subqueryN：指refre一个ID为N的物化子查询(Materialization subquery)结果集。
|
|
上面的执行计划，第一和第二序列都不是物理表，序列1(id为1的行)的查询依赖于序列2的查询结果，序列2又依赖于序列3的查询结果。
type
type为join type，常见类型有system，const，eq_ref，ref等，type显示的连接方式可以看出SQL语句的质量，type按照性能从高到低分别说明：
- system - 当查询的表只有一行时使用system，且system 是 const 连接类型的一个特例。
const - 用于用常数值比较 PRIMARY KEY 时，当查询的表仅有一行时,使用 System。
123456789mysql> explain select * from (select * from ( select * from subtask where id=16787) a) b;+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | NULL || 2 | DERIVED | <derived3> | system | NULL | NULL | NULL | NULL | 1 | NULL || 3 | DERIVED | subtask | const | PRIMARY | PRIMARY | 3 | const | 1 | NULL |+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+3 rows in set (0.00 sec)eq_ref - 使用唯一索引，表中数据唯一，在多表连接中使用PRIMARY KEY或UNIQUE KEY作为匹配条件。
12345678mysql> explain select * from task,subtask where task.id=subtask.taskid;+----+-------------+--------+--------+---------------+---------+--------+-----------+------+-------+| id | select_type | table | type | possible_keys | key | key_len| ref | rows | Extra |+----+-------------+---------+--------+--------------+---------+--------+-----------+------+-------+| 1 | SIMPLE | subtask | ALL | NULL | NULL | NULL | NULL | 12491| NULL || 1 | SIMPLE | task | eq_ref | PRIMARY | PRIMARY | 2 | sub.taskid| 1| NULL |+----+-------------+---------+--------+--------------+---------+--------+-----------+------+-------+2 rows in set (0.00 sec)ref - 所有和索引列匹配的行，ref连接通常出现在索引列使用
=或<=>操作符。12345678mysql> explain select * from subtask,subcaseinfo where subtask.id=subcaseinfo.subtaskid;+----+-------------+---------+------+---------------+---------+---------+-----------+-----+-------+| id | select_type | table | type | possible_keys | key | key_len | ref |rows | Extra |+----+-------------+---------+------+---------------+---------+---------+-----------+-----+-------+| 1 | SIMPLE | subtask | ALL | PRIMARY | NULL | NULL | NULL |12491| NULL || 1 | SIMPLE | subinfo | ref | sub_idx | sub_idx | 3 | subtask.id| 178 | NULL |+----+-------------+---------+------+---------------+---------+---------+-----------+-----+-------+2 rows in set (0.01 sec)ref_or_null - 同ref, 但是MySQL必须在初次查找的结果里找出 null 条目,然后进行二次查找。
index_merge - 出现index_merge说明MySQL优化索引查询，将多个索引合并查询数据。
1234567mysql> explain select * from subinfo where subtaskid=16787 or id=1976445;+---+-----------+-------+-----------+-------------+---------+-------+----+----+---------------------+|id |select_type|table |type |possible_keys|key |key_len|ref |rows|Extra |+---+-----------+-------+-----------+-------------+---------+-------+----+----+---------------------+| 1 | SIMPLE |subinfo|index_merge| p,sub_id |sub_idx,p| 3,4 |NULL|152|Using union(sub_idx,p)|+---+-----------+-------+-----------+-------------+---------+-------+----+----+---------------------+1 row in set (0.00 sec)其中key p = PRIMARY
- unique_subquery - 在某些 IN 查询中使用此种类型,而不是常规的 ref。
- index_subquery - 在某些 IN 查询中使用此种类型和unique_subquery类似,但是查询的是非唯一性索引。
range - 使用索引来查询指定范围的数据，如使用
=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符。1234567mysql> explain select * from subinfo where subtaskid=16787 or subtaskid=1976445;+---+------------+--------+------+--------------+--------+--------+-----+-----+---------------------+|id |select_type |table |type |possible_keys |key |key_len |ref |rows |Extra |+---+------------+--------+------+--------------+--------+--------+-----+-----+---------------------+| 1 | SIMPLE |subinfo |range |sub_idx |sub_idx | 3 |NULL | 152 |Using index condition|+---+------------+--------+------+--------------+--------+--------+-----+-----+---------------------+1 row in set (0.00 sec)index - 和all类似，扫描索引树。
- all - 从头到尾，全表扫描，性能最差。
possible_keys
possible_keys指在查询过程中，MySQL能应用感到的索引，如果为NULL，则表示没有可用索引。可以为其创建索引，提高查询性能。
key
key指MySQL在查询过程中实际使用的索引，如果没有使用索引，则该列为NULL。MySQL优化器虽然会在查询时选择最佳索引，但有时也会选择不太高效的索引，此时可以在SQL语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引。
key_len
key_len指使用的索引长度，如果没有使用索引，该列为NULL。在索引杂谈中提到了索引的选择性，通常在保证查询准确度情况下，索引长度约小越好。
ref
ref指哪些字段或常量被用来查找索引列上的值。下面的ref列显示用到了subtask.id字段来查询数据。
rows
rows 根据表、索引等信息，估算出查询出目标结果的检索行数。
extra
extra表示查询的额外信息，有很多描述信息，如
const row not foundDistinctFirstMatchImpossible WHERENo matching min/max rowNot existsSelect tables optimized away
等等30多个item，常见的有以下几个：- Using temporary：用于查询结果排序时生成的临时表，多出现在对结果集进行order by。
- Using filesort：MySQL无法应用索引需要额外操作进行排序。
- Using where：MySQL使用Where子句来限制哪些行与下表匹配或发送到客户端。
explain只能用于查看SELECT的执行计划，并且不能显示具体的优化操作，如果想查看MySQL具体的优化操作，可以使用optimizer trace来查看。
关于更多Explain内容，请参见MySQL官网的9.8.2 EXPLAIN Output Format