MySQL调优三部曲（二）EXPLAIN

EXPLAIN

MySQL Query Optimizer通过执行EXPLAIN命令来告诉我们它将使用一个怎样的执行计划优化Query。所以，通过Explain可以帮助我们选择更好的索引和写出更优化的查询语句

Explain各种信息的解释

PS：下面列举的例子有些是无意义的，只是为了展示explain的效果

1. id

查询序列号，id大的先执行，相同的id按从上往下顺序依次执行，id列为NULL表示一个结果集，不是查询

2. select_type（查询中每个select子句的类型）

• simple： 除子查询或UNION之外的其他查询

mysql> explain select * from focus;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | focus | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   33 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• primary： UNION或者含有子查询的select，位于最外层的查询就是primary

mysql> explain select object_id from focus a where id = 1 union select object_id from focus b  where id = 3;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | a          | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  2 | UNION        | b          | NULL       | const | PRIMARY       | PRIMARY | 4       | 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.00 sec)

• union： UNION语句第二个select开始后面所有的select，第一个select是primary

mysql> explain select object_id from focus a where id = 1 union select object_id from focus b  where id = 3;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | a          | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  2 | UNION        | b          | NULL       | const | PRIMARY       | PRIMARY | 4       | 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.00 sec)

• dependent union： 和 union 类似，出现在UNION语句中，但是这个查询依赖于外部查询的结果集。在下面的sql语句中，依赖于外部查询的结果集的意思是，MySQL会先执行select * from focus，得到所有结果之后，再一条一条地去与子查询SQL组成新的查询语句，可想而知，这种查询类型是非常慢的

mysql> explain select status from focus a where id in (select id from favour where object_id = 19931224 union select id from favour where object_id = 19931226);
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type        | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
|  1 | PRIMARY            | a          | NULL       | ALL    | NULL          | NULL    | NULL    | NULL |   33 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | favour     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 |    10.00 | Using where     |
|  3 | DEPENDENT UNION    | favour     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 |    10.00 | Using where     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

• union result： union的结果，因为它不需要参与查询，所以id字段为null

mysql> explain select status from focus a union select status from favour where object_id = 19931224;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | a          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   33 |   100.00 | NULL            |
|  2 | UNION        | favour     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   31 |    10.00 | Using where     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.01 sec)

• subquery： 子查询内层查询的第一个select，结果不依赖于外部查询结果集

mysql> explain select status from focus a where id = (select id from favour where object_id = 19931224);
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------------+
|  1 | PRIMARY     | NULL   | NULL       | NULL  | NULL          | NULL       | NULL    | NULL | NULL |     NULL | no matching row in const table |
|  2 | SUBQUERY    | favour | NULL       | index | NULL          | infoByUser | 13      | NULL |   31 |    10.00 | Using where; Using index       |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+--------------------------------+
2 rows in set, 1 warning (0.01 sec)

• dependent subquery： 子查询内层查询的第一个select，结果依赖于外部查询结果集（比如SELECT XXX FROM TABLE1 WHERE status = 1 AND id IN (SELECT XXX FROM TABLE2 WHERE SID IN (1,3,5,7,9))，在这条语句中，table2的查询就是dependent subquery，MySQL首先根据SELECT XXX FROM TABLE1 WHERE status = 1得到一个大的结果集，再将大的结果集中的每一条记录，都与子查询SQL组成新的查询语句，这就是结果依赖于外部查询结果的意思，慢查优化-DEPENDENT SUBQUERY）

• uncacheable subquery： 结果集无法缓存的子查询

• derived： 用于from子句里有子查询的情况，MySQL会递归执行这些子查询，把结果放在临时表中（例：SELECT g1.gid,count(1) FROM shop_goods g1, (select gid from shop_goods WHERE sid in (1519066,1453929)) g2 where g1.status=0 and g1.gid=g2.gid GROUP BY g1.gid;）

3. table：

查询的表名

4. type （表示MySQL在表中找到所需行的方式，又称“访问类型”，从最佳类型到最差类型依次列举）

• const： 用到 primary key 或者unique 索引（表最多只有一个匹配行），const是最优化的

mysql> explain select * from focus where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | focus | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• eq_ref： 唯一索引或者主键索引作为两个表的联接方式，可以使用=比较两个索引列

mysql> explain select a.id from focus a,favour b where a.id = b.id;
+----+-------------+-------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key        | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | b     | NULL       | index  | PRIMARY       | infoByUser | 13      | NULL       |   31 |   100.00 | Using index |
|  1 | SIMPLE      | a     | NULL       | eq_ref | PRIMARY       | PRIMARY    | 4       | drama.b.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

• ref： 使用索引，但不是唯一索引或者主键索引

mysql> explain select * from video where up_id = 123;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | video | NULL       | ref  | up_id         | up_id | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• ref_or_null： 类似于ref，还可以额外查询含有NULL值得行，多用在子查询中

上面5种是比较合理的索引使用情况

• index_merge： 使用多个索引查找后的交集/并集定位数据

mysql> explain select * from video where up_id = 123 or id = 3;
+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                   |
+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
|  1 | SIMPLE      | video | NULL       | index_merge | PRIMARY,up_id | up_id,PRIMARY | 4,4     | NULL |    2 |   100.00 | Using union(up_id,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
1 row in set, 1 warning (0.00 sec)

• range： 对索引列进行范围查找，如in操作，between操作，>、<、=操作等

mysql> explain select * from video where id > 123;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | video | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• index: 是全表扫描，但是只select索引列的值，所以只需要扫描索引树即可

mysql> explain select id from video;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | video | NULL       | index | NULL          | up_id | 4       | NULL |   24 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• all： 扫全表，然后再在server层进行过滤返回符合要求的记录

5. possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

6. keys

当前query实际使用的索引

7. key_len

表示使用的索引的长度，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得

8. ref

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

mysql> explain select * from focus a , favour b where a.id = b.id ;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL       |   35 |   100.00 | NULL  |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | drama.a.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

9. rows

执行MySQL查询的行数

10. extra

• distinct： select使用distinct关键字时出现（实际使用distinct操作没作用）

• using index： 当前的SELECT操作使用了覆盖索引，query可以直接利用索引返回SELECT的字段，而不必根据索引再去读取数据文件（覆盖索引：包含所有满足查询需要的数据的索引）

mysql> explain select id from focus;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | focus | NULL       | index | NULL          | object | 9       | NULL |   35 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• using filesort： 无法利用索引完成的排序

mysql> explain select * from favour order by status;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | favour | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

• using temporary： 为了解决查询，MySQL需要创建一个临时表来容纳结果，常见于排序和分组查询，出现这种情况需要优化。如下查询，两条sql语句的区别只在于order by的字段不同，但是一条用到了临时表，一条没用到。MySQL的表关联算法是Nest Loop Join，通过驱动表的结果集作为循环基础数据，然后将该结果集中的数据作为过滤条件到下一个表中去查询数据。通过EXPLAIN的结果，第一行出现的表就是驱动表，驱动表可以直接排序，而非驱动表需要用临时表存储合并结果，然后再进行排序。

mysql> explain select a.status from favour a, favour_1 b where a.id = b.status order by a.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL          |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.b.status |    1 | Using where                     |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+---------------------------------+
2 rows in set (0.00 sec)


mysql> explain select a.status from favour a, favour_1 b where a.id = b.status order by b.id;
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
|  1 | SIMPLE      | b     | index  | NULL          | PRIMARY | 4       | NULL          |    1 | NULL        |
|  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.b.status |    1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
2 rows in set (0.00 sec)

• using where： 使用where子句匹配数据，且where条件列非索引列

mysql> explain select * from favour where status = 1;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | favour | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

• Using sort_union(...), Using union(...),Using intersect(...)： 这些函数说明如何为index_merge联接类型合并索引扫描，如将两个索引结果并集，或者两个索引结果交集

• Using index for group-by： 类似于访问表的Using index方式，区别是Using index采用的是紧凑索引，Using index for group-by采用的是松散索引，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表，既group by字段就是索引列。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目

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

mysql> explain select * from favour a join  favour_1 b on a.status = b.status;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL                                               |
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |    7 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

• Impossible where： 这个值强调了where语句会导致没有符合条件的行

mysql> explain select * from favour where 1 = 2;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

• Select tables optimized away： 该值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

mysql> explain select max(id) from favour;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

优化总结

上文详细解释了explain各输出信息的解释，那哪些参数是我们需要重点关注的呢？

一、select_type

当select_type是dependent union、dependent subquery，这种结果需要依赖外部查询结果集的查询，就需要进行优化了，一般的优化方案有：1、使用临时表联表查询 2、分成两个查询顺序执行

二、type

当type是index_merge，range，index，all时，可以进行选择采用以下几种优化方案：1、采用联合索引 2、对全表扫描的可以采取新建索引

三、extra

当extra是using filesort，using temporary，using where，Using join buffer时，可以采取以下几种优化方案：1、排序尽量用索引字段 2、尽量用索引字段进行多表连接 3、频繁查询的字段建立索引