使用 explain 优化你的 mysql 性能

本文是关于在学习《高性能 Mysql》附录 D 中关于 Explain 如何获取执行计划信息相关总结。MySQL 提供了一个 EXPLAIN 命令,它可以对 SELECT 语句进行分析，获取优化器对当前查询的执行计划，以供开发人员针对相关 SQL 进行优化。在 SELECT 语句前加上 Explain 就可以查看到相关信息, 例如:

EXPLAIN SELECT * from user_info WHERE id < 300;

复制代码

Explain 特点

• explain 返回的结果是以表为粒度的，每个表输出一行，这里的表示广义上的表，可以是一个子查询，也可以是一个 UNION 后的结果。
• 并不是所有的 explain 都不执行查询，如果 FROM 子句里包含了子查询，那么 MySql 实际上会执行子查询以外层对于外层查询的优化。
• explain 无法告诉我们触发器，存储过程以及 UDF 是如何影响查询的
• explain 对于内存排序和临时文件 排序 都使用 “filesort”
• explain 对于磁盘上的临时表和内存上的临时表都使用 “Using temporary”
• explain 只能解析 Select 查询，对于 update，insert 等都不支持，我们可以使用 select 来模拟 update 操作近似获取 update 的执行过程

Explain 中的列

id

SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.

select_type

select_type 表示了查询的类型, 它的常用取值有:

• SIMPLE：表示此查询不包含 UNION 查询或子查询
• SUBQUERY：包含在 Select 列表中的子查询，也就是不在 FROM 子句中的子查询
• DERIVED：表示包含在 From 子句中的 Select 查询
• UNION：表示此查询是 UNION 的第二和随后的查询
• UNION RESULT： 从 UNION 匿名临时表检索结果的 SELECT
• PRIMARY, 表示此查询是最外层的查询
• DEPENDENT UNION： UNION 中的第二个或后面的查询语句, DEPENDENT 意味着 Select 依赖于外层查询中发现的数据
• DEPENDENT SUBQUERY: 包含在 Select 列表中的子查询， 但子查询依赖于外层查询的结果.

table

查询的是哪个表，mysql 查询优化器执行的关联顺序并不和我们写 SQL 时关联的顺序一致，下面我们讲一下 Mysql 是如何对关联查询作优化的：

• Mysql 查询执行计划总是按照左侧深度优先树的规则去执行，也就是从一个表开始一直嵌套循环，并不会类似平衡二叉树一样两个分支同时执行
• 在多表关联时，可以通过多种不同的关联顺序获取相同的执行结果，查询优化器会评估不同的顺序选择一个代价最小的关联查询
• 如果你不想要优化器改变你的关联顺序，可以使用 STRAIGHT JOIN 关键字强制使用你的关联顺序去执行
• 如果关联表特别多时，超过 optimizer_search_depth 的限制时，优化器评估每一种关联顺序的执行成本太高，这时会选择“贪婪”的搜索模式

type

type 字段比较重要, 它提供了判断查询是否高效的重要依据依据. 通过 type 字段, 我们判断此次查询是全表扫描还是索引扫描等，type 类型的性能比较，通常来说, 不同的 type 类型的性能关系如下:

ALL < index < range < ref < eq_ref < const < system < NULL

• NULL：这种访问意味着 Mysql 能在优化阶段分解查询语句，在执行阶段不需要访问表或者索引
• system: 预先知道整个表中只有一条数据. 这个类型是特殊的 const 类型

# 因为表中backend_user是主键，所以子查询里最多可以选出一条数据，所以最外层查询的type是system，里层查询的type是const
explain select * from (select * from backend_user where id = 1) a;
复制代码

• const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
• eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高.
• ref：此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询，可能会查询出多个值
• range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。但是对于同样的 type = range 的查询，性能上还是有区别的：

# 虽然是都是范围查询，其实第二个查询时多个等值条件查询
# 对于第一个查询，mysql 无法再使用该列后面的其它查询索引了，而第二个则可以继续使用索引
select id from actor where id > 45 and class_id = 3;
select id from actor where id in (44, 47, 48) and class_id = 3;
复制代码

• index: 表示通过索引进行全表扫描和 ALL 类型类似, 有点是避免了排序，确定是需要承担按照索引次序读取表的开销。如果 Extra 列中出现了 “Using index” 表明是用了覆盖索引，此时开销非常小。
• ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一。一般情况下都会从头到尾扫描所有行，除非使用了 Limit 或者 Extra 列中显示 “Using distinct/not exists”。

possible_keys

此次查询中可能选用的索引，这些索引列是根据查询的列以及比较操作符来判断的，可能在后续的真实查询中没有用到也有可能

key

此次查询中确切使用到的索引，如果在 possible_keys 中没有出现而在 key 中出现，说明优化器可能出于另外原因比如选择覆盖索引，所以 possiable_keys 揭示了哪一个索引有助于高效进行查找，而 key 显示了采用哪一个索引可以最小化查询成本。

key_len

表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到，比如我们建了一个组合索引(col1, col2)，那么如下两条查询虽然用到的都是这个组合索引，但是对应的key_len的只是不一样的。key_len 显示了在索引字段中可能的最大长度，而不是数据使用的实际字节数

select * from table1 where col1 = 1;
select * from table1 where col1 = 1 and col2 = 2;
复制代码

ref

这一列显示了之前的表在 key 列记录的索引中查找值所用的列或者常量

rows

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。

• 通过把每个表的 rows 值相乘可以粗略的估算出整个查询要检查的行数
• 这个值只是一个估算的值，不是实际查出来的值

filtered

filtered 是在 MYSQL 5.1 中加进来的，在使用 EXPLAIN EXTENDED 时出现，表示此查询条件所过滤的数据的百分比，将 rows 除以 filtered 可以估算出整个表数据行数。

Extra

EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:

• Using filesort

当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大。 但是 Explain 不会告诉你 Mysql 将使用文件排序还是内存排序：

-- 比如我们建立索引为：KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)，那么如下两个查询

EXPLAIN SELECT * FROM order_info ORDER BY product_name；    -- Using filesort，不能通过索引进行排序，需要优化
EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name；-- 无 Using filesort，通过索引进行排序，优化成功
复制代码

• Using index

"覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

• Using where

这意味着 Mysql 服务器在存储引擎检索行后再进行过滤，一般出现 “Using where” 会受益于不同的索引

• Using temporary

查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 临时表可能是内存临时表或者文件临时表