MySQL的查询性能分析神器：explain命令的使用详解

在MySQL的SQL查询性能分析当中，主要使用explain命令对查询SQL语句的执行情况进行分析，包含查询所涉及的表，查询索引使用情况，排序情况等，用法的使用很简单，示例如下：

mysql> EXPLAIN SELECT DISTINCT(trade_date) FROM order WHERE user_id=1 ORDER BY trade_date DESC LIMIT 10;
+----+-------------+-------------------+------------+------+----------------------+--------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+----------------------+--------------+---------+-------+------+----------+--------------------------+
| 1 | SIMPLE | order | NULL | ref | PRIMARY,idx_user_trade|idx_user_trade| 768 | const | 20 | 100.00 | Using where; Using index |
+----+-------------+-------------------+------------+------+----------------------+--------------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.03 sec)

• 以上示例对应order表的索引情况：在user_id和trade_date两个字段建立了一个联合索引。

KEY `idx_user_trade` (`user_id`,`trade_date`) USING BTREE

以上示例的explain命名输出的各参数含义如下：

1.id：执行的序号，这个语句没有子查询，故只有1，值越大越先执行；

2.select_type：数据读取类型，这里只是针对单表，也没有UNION操作，故是SIMPLE，其他类型包括：PRIMARY, UNION RESULT, SUBQUERY等；

3.table：查询涉及的表；

4.partitions：查询涉及的表的哪些分区；

5.type：访问类型，指明了MySQL以何种方式查找表中符合条件的行，这个也是需要重点关注的一项指标，包含的类型为：ALL, index, range, ref, eq_ref, const/system, NULL，性能依次变好，其中：

• ALL：为全表扫描，性能最差；
• index：为全索引扫描，性能通常也是不够理想；
• range：为对索引进行范围扫描，然后返回对应的数据行，如SQL包含BETWEEN，>=，IN()等语句时就是range；
• ref：为索引查找，返回匹配单个索引值的数据行，如果不是范围查询，则需要至少达到这个级别；
• eq_ref：也是索引查找，不过最多只返回一条记录，通常是在主键或者唯一性索引上，性能较好；
• const/system：为表只有最多一个匹配行，直接读取对应的数据行，不需要查找索引再根据索引的结果读取数据行，通常是主键或唯一性索引上有固定值的情形；
• NULL：在执行阶段不需要访问表，直接从索引返回需要的值。

6.possible_keys：该次查询可以使用的索引；

7.key：该次查询实际使用的索引；

8.key_len：使用索引时，所使用的索引值的最大字节数，当type为NULL时，该值也为NULL；

9.ref：哪些字段或常量配合key将数据行从表中获取出来；

10.rows：估计查找到所需要的行，大概需要扫描读取多少数据行，这个值越小越好；即存储引擎大概需要读取并返回rows的值这么多行数据给server层；通常与下面的filtered一起分析，即如果rows为5，而实际需要的查询结果为1条记录，则filtered的值为大概为1/5；如下：

mysql> explain SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3 AND price BETWEEN 80 AND 80.999;
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
| 1 | SIMPLE | test			 | NULL | ref | idx_ul_type | idx_id_type | 769 | const,const | 5 | 11.11 | Using where |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

SQL执行情况：这条SQL实际返回了一条记录。

mysql> SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3 AND price BETWEEN 80 AND 80.999;
省略具体内容
1 row in set (0.00 sec)

如果把price BETWEEN 80 AND 80.999的条件去掉，则返回5条记录：

mysql> SELECT type, expire_date, id FROM test WHERE id='11111' AND type=3;
省略具体内容
5 rows in set (0.00 sec)

索引情况如下：在id和type的两个列建了一个联合索引，不包含price列，故由于price没有索引，实际存储引擎会读取并返回5条记录给server层，server层在根据 price BETWEEN 80 AND 80.999再过滤最后剩下一条记录。

KEY `idx_id_type` (`id`,`type`) USING BTREE

如果把price加到idx_id_type索引中，即idx_id_type (id,type,price) ，则执行情况如下：

mysql> explain SELECT type, expire_date, id FROM test WHERE ul='11111' AND type=3 AND price BETWEEN 80 AND 80.999;
+----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test			 | NULL | range | idx_id_type | idx_id_type | 778 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-------------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

11.filtered：此查询条件所过滤的数据的百分比，表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例；越大表示存储引擎返回给server层的都是有用的，故说明效率较高，通常都是100%；

12.Extra：此处执行的额外信息，一次SQL查询的Extra可以包含以下的一条或多条，包括：

• Using where：表示SQL语句存在WHERE条件，不是获取全表数据，一般使用了WHERE条件都会有这个；
• Using index：表示使用覆盖索引返回数据，不需要访问表，通常是指该查询性能较好；
• Using index condition：这个是在5.6版本后加入的新特性，主要作用是当WHERE中的某个条件对应的字段，如a，是加了索引的，但是无法使用，如使用了 a like ‘%abc%’ 这种SQL，在没有这个特性之前，存储引擎此时无法使用这个字段对应的索引了，需要回表找到符合WHERE其他条件的数据行，即full row正行数据，然后传给server层，最后在server层处理a like '%abc%'这个条件，即使a这个字段加了索引，这样就存储引擎就需要读取较多的数据行和传给server层较多的数据。拥有这个特性之后，存储引擎层会使用索引来处理a like '%abc%'这个SQL，进一步过滤，从而减少回表查询的次数和传给server层的数据量；详见官方文档：8.2.1.5 Index Condition Pushdown Optimization
• ，如下为详细例子：

• 
  
• Using temporary：表示使用了临时表来进行分组、排序或者多表join，通常表示查询效率不高，需要优化；
• Using filesort：表示需要使用一个外部文件索引来对结果进行排序，而不是直接根据内部索引顺序从表中读取数据，这个过程通常需要消耗比较大的CPU资源，需要优化。