MySQL 执行计划分析：EXPLAIN

由于个人能力有限，文中可能存在错误，欢迎批评指正。

1.说明

在日常运维或者开发过程中，我们经常会遇到一些慢查询，不仅消耗资源，也会增加业务响应耗时，这时我们可以通过 explain 命令去了解到 sql 的执行计划，有些重要信息需要去关注，并且可以作为是否需要添加、优化索引或者是改写 sql 的判断依据。

2. EXPLAIN 用法示例

mysql> explain select * from t2 where xxx='20';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_1         | idx_1 | 82      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.02 sec)

各列含义如下：

• id：SELECT 查询的标识符，每个 SELECT 都会自动分配一个唯一的标识符
• select_type：SELECT 查询的类型
• table：查询的是哪个表
• partitions：匹配的分区
• type：本次查询表联接类型，从这里可以看到本次查询大概的效率
• possible_keys：此次查询中可能选用的索引
• key：此次查询中确切使用到的索引，如果没有索引的话，效率通常很差
• key_len：本次查询用于结果过滤的索引实际长度
• ref：哪个字段或常数与 key 一起被使用
• rows：显示此查询预计需要扫描的记录数，预计需要扫描的记录数越小越好
• filtered：表示此查询条件所过滤的数据的百分比
• extra：额外的信息，主要确认是否出现 Using filesort、Using temporary 这两种情况

3.重点关注

总的来说，我们需要重点关注如下几列：

1）type
它提供了判断查询是否高效的重要依据依据。通过 type 字段，我们判断此次查询是全表扫描还是索引扫描等。有如下几种结果：

• system：查询对象表只有一行数据，这是最好的情况
• const：该 table 最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量
• eq_ref：此类型通常出现在多表的 join 查询，表示对于前表的每一个结果,，都只能匹配到后表的一行结果，并且查询的比较操作通常是=，查询效率较高
• ref：针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询
• index_merge：使用索引合并优化，提高查询效率
• unique_subquery：子查询中可以用到唯一索引，效率比 index_subquery 更高些
• index_subquery：子查询中可以用到索引
• range：表示使用索引范围查询，通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中
• index：表示全索引扫描(full index scan)，和 ALL 类型类似，只不过 ALL 类型是全表扫描，而 index 类型则仅仅扫描所有的索引，而不扫描数据
• ALL：表示全表扫描(full table scan)，该类型的查询是性能最差的一种方式。

type 性能排序：
ALL < index < range ~ index_merge < ref < eq_ref < const < system

2）possible_keys
表示 MySQL 在查询时, 能够使用到的索引。

3）key
此字段是 MySQL 在当前查询时所真正使用到的索引。

4）key_len

表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，或只有最左部分字段被使用到。

key_len 的计算规则如下：

• 字符串
  • char(n): n 字节长度
  • varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
• 数值类型:
  • TINYINT: 1字节
  • SMALLINT: 2字节
  • MEDIUMINT: 3字节
  • INT: 4字节
  • BIGINT: 8字节
• 时间类型
  • DATE: 3字节
  • TIMESTAMP: 4字节
  • DATETIME: 8字节

5）rows
估算 SQL 要查找到结果集需要扫描读取的数据行数。这个值非常直观显示 SQL 的效率好坏，原则上 rows 越少越好。

6）Extra
显示额外附加信息，需要注意的几种情况如下：

• Using filesort：表示 MySQL 需额外的排序操作，不能通过索引顺序达到排序效果。一般有 Using filesort，都建议添加合适的索引优化去掉，因为这样的查询 CPU 资源消耗很大。
• Using index：表示使用覆盖索引扫描，查询在索引树中就可查找所需数据，不用扫描表数据文件，一般性能不错。
• Using temporary：表示查询有使用临时表，一般出现于排序、分组和多表 join 的情况，查询效率不高,，建议优化。
• Using where：表示mysql服务器将在存储引擎检索行后再通过where条件进行过滤，需要添加合适的索引。

4.其他

针对 sql 索引优化这块，推荐一个不错的辅助工具 SQLAdvisor
美团点评SQL优化工具SQLAdvisor开源
美团点评开源 SQL 优化工具 SQLAdvisor 测试报告

参考资料

【1】EXPLAIN Statement