explain ref_使用 explain 优化你的 mysql 性能

最新推荐文章于 2022-03-10 14:21:01 发布

weixin_39830313

最新推荐文章于 2022-03-10 14:21:01 发布

阅读量52

点赞数

文章标签： explain ref mysql触发器输出提示信息为什么使用join 代替子查询

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

EXPLAIN

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

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

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`)，那么如下两个查询

Using index

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

Using where

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

Using temporary

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

partitions

在 Mysql 5.1 版本中引入了 EXPLAIN PARTITIONS 可以显示查询将访问的分区情况

参考文献

MySQL 性能优化神器 Explain 使用分析