MySQL explain命令分析

explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。

explain执行效果

mysql> explain SELECT * FROM `db_goods`.`t_shopcart`;
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | t_shopcart | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 69193 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set (0.04 sec)

id select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
select_type 查询类型
table 正在访问哪个表
partitions 匹配的分区
type 访问的类型
possible_keys 显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
key 实际使用到的索引，如果为NULL，则没有使用索引
key_len 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
ref 显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
rows 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
filtered 查询的表行占表的百分比
Extra 包含不适合在其它列中显示但十分重要的额外信息

字段详解

id字段

一组数字，表示查询中执行select子句或操作表的顺序。如果有多行时，id值相同，执行顺序从上至下顺序执行；如果值不同，id的值越大优先级越高，越先被执行。

select_type字段

查询中每个select子句的类型;

(1) SIMPLE(简单SELECT,不使用UNION或子查询等)

(2) PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

(5) UNION RESULT(UNION的结果)

(6) SUBQUERY(子查询中的第一个SELECT)

(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

(8) DERIVED(派生表的SELECT, FROM子句的子查询)

(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table字段

查询访问的表

partitions字段

匹配的分区

type字段（重要）

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（8种，从左到右，性能从差到好）

①ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

mysql> explain SELECT * FROM `db_goods`.`t_shopcart`;
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | t_shopcart | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 69193 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
1 row in set (0.04 sec)

②index：Full Index Scan，index与ALL区别为index类型只遍历索引树，主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引。

③range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range

mysql>  explain SELECT * FROM `db_goods`.`t_activity` where activity_Id < 11;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_activity | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.07 sec)

④ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，属于查找和扫描的混合体。

⑤eq_ref：最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生（高效）

⑥const：当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）

mysql>  explain SELECT * FROM `db_goods`.`t_activity` where activity_Id = 1;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_activity | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.06 sec)

⑦system：表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略

⑧NULL：MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引

mysql>  explain SELECT min(activity_id) FROM `db_goods`.`t_activity` ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set (0.06 sec)

possible_keys字段

显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到

key字段（重要）

实际使用到的索引，如果为NULL，则没有使用索引

key_len字段

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确度的情况下，长度越短越好

ref字段

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows字段

大致估算出找到所需的记录所需读取的行数

filtered字段

查询的表行占表的百分比

Extra字段（重要）

不适合在其它列中显示但十分重要的额外信息。有以下几种情况：

①Using where：使用了where条件。

②Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序order by 和分组查询group by；

③Using filesort：MySQL中无法利用索引完成的排序操作称为“文件排序”。

④Using join buffer：该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

⑤Impossible where：这个值强调了where语句会导致没有符合条件的行，where子句的值总是false，不能用来获取任何元组。

⑥Select tables optimized away：SELECT操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了）

⑦Using index：表示相应的select操作中使用了覆盖索引（Covering Index）,避免访问了表的数据行，效率不错！

⑧distinct：一旦mysql找到了与行相联合匹配的行，就不再搜索了