EXPLAIN 语句提供了有关 MySQL 如何执行语句的信息,我们可以通过分析 EXPLAIN 结果来优化查询语句。EXPLAIN 适用于 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句。
执行计划详解
假设现在我们使用 EXPLAIN 命令查看当前 SQL 是否使用了索引,先通过 SQL EXPLAIN 导出相应的执行计划如下: 以下对返回结果每一个字段进行说明:
- id:每个执行计划都有一个 id,如果是一个联合查询,这里还将有多个 id。
- select_type:表示 SELECT 查询类型,常见的有 SIMPLE(普通查询,即没有联合查询、子查询)、PRIMARY(主查询)、UNION(UNION 中后面的查询)、 SUBQUERY(子查询)等。
- table:当前执行计划查询的表,如果给表起别名了,则显示别名信息。
- partitions:访问的分区表信息。
- type:表示从表中查询到行所执行的方式,查询方式是 SQL 优化中一个很重要的指标, 结果值从好到差依次是:system > const > eq_ref > ref > range > index > ALL。
- possible_keys:可能使用到的索引。
- key:实际使用到的索引。
- key_len:当前使用的索引的长度。
- ref:关联 id 等信息。
- rows:查找到记录所扫描的估算行数。
- filtered:查找到所需记录占总扫描记录数的比例。
- Extra:额外的信息。
注意:在 MySQL 5.7 以前的版本中,想要显示 partitions 需要使用 explain partitions
命令;想要显示 filtered 需要使用 explain extended
命令。在5.7版本后,默认 explain 直接显示 partitions 和 filtered 中的信息。
id
select 查询的序列号,表示查询中执行 select 子句或者操作表的顺序。序列号分为三种情况:
1、如果 id 相同,那么执行顺序从上到下
EXPLAIN SELECT * FROM business_order bo INNER JOIN business_order_goods bog ON bo.out_trade_no = bog.out_trade_no INNER JOIN business_order_log bol ON bo.out_trade_no = bol.out_trade_no;
2、如果 id 不同,如果是子查询,id 的序号会递增,id 值越大优先级越高,越先被执行
EXPLAIN SELECT * FROM business_order bo WHERE bo.out_trade_no IN(SELECT bog.out_trade_no FROM business_order_goods bog WHERE bog.goods_id = 108);
3、id 相同和不同的,同时存在:相同的可以认为是一组,从上往下顺序执行,在所有组中,id 值越大,优先级越高,越先执行
EXPLAIN SELECT * FROM business_order bo INNER JOIN business_order_goods bog ON bo.out_trade_no = bog.out_trade_no INNER JOIN business_order_log bol ON bo.out_trade_no = bol.out_trade_no WHERE bo.out_trade_no IN(SELECT bog.out_trade_no FROM business_order_goods bog WHERE bog.goods_id = 108);
select_type
主要用来分辨查询的类型,是普通查询还是联合查询还是子查询。
值 | 含义 |
---|---|
SIMPLE | 普通 SELECT,没有 UNION 查询或子查询 |
PRIMARY | 最外层的 SELECT,即主查询 |
UNION | 第二个或之后的 UNION SELECT |
DEPENDENT UNION | 与 UNION 类似,DEPENDENT 表示 UNION 或 UNION ALL 联合而成的结果会受外部查询影响 |
UNION RESULT | 从 UNION 表获取结果的 SELECT |
SUBQUERY | 子查询中的第一次 SELECT |
DEPENDENT SUBQUERY | 在子查询中的第一次 SELECT,子查询会受外部查询影响 |
DERIVED | 派生表,即 FROM 子句中出现查询 |
DEPENDENT DERIVED | 派生表,受外部查询影响 |
MATERIALIZED | 物化子查询 |
UNCACHEABLE SUBQUERY | 无法缓存结果的子查询,必须为外部查询的每一行重新评估 |
UNCACHEABLE UNION | 第二个或之后在属于不可缓存子查询的 UNION 中选择 |
1、SIMPLE
普通查询,查询中不包含子查询或者union等任何复杂查询。
EXPLAIN SELECT * FROM business_order WHERE order_status = 2;
执行结果为:
2、PRIMARY
查询中若包含任何复杂的子查询,最外层查询则被标记为 PRIMARY。
EXPLAIN SELECT id,out_trade_no,order_type FROM (SELECT id,out_trade_no,order_type,order_status FROM business_order WHERE order_status=2) t WHERE order_type = 2;
在 MySQL 5.6 中执行结果为: 在 MySQL 5.7 后做了优化,执行结果为:
3、UNION
若第二个 SELECT 出现在 UNION 之后,则被标记为 UNION。
EXPLAIN SELECT id,out_trade_no,order_type FROM business_order WHERE order_status = 2 UNION SELECT id,out_trade_no,order_type FROM business_order WHERE order_type = 2;
执行结果为:
4、DEPENDENT UNION
与 UNION 类似,DEPENDENT 表示 UNION 或 UNION ALL 联合而成的结果会受外部查询影响。
EXPLAIN SELECT * FROM business_order WHERE id IN (SELECT id FROM business_order WHERE order_status = 2 UNION SELECT id FROM business_order WHERE order_type = 2);
执行结果为:
5、UNION RESULT
从 UNION 表获取结果的 SELECT。
EXPLAIN SELECT id,out_trade_no,order_type FROM business_order WHERE order_status = 2 UNION SELECT id,out_trade_no,order_type FROM business_order WHERE order_type = 2;
执行结果为:
6、SUBQUERY
子查询中的第一次 SELECT。
EXPLAIN SELECT * FROM business_order WHERE id = ( SELECT id FROM business_order WHERE out_trade_no = '2009281548302880')
执行结果为:
7、DEPENDENT SUBQUERY
在子查询中的第一次 SELECT,子查询会受外部查询影响。
EXPLAIN SELECT out_trade_no FROM business_order WHERE out_trade_no IN ( SELECT out_trade_no FROM business_order UNION SELECT out_trade_no FROM business_order )
执行结果为:
8、DERIVED
派生表,即 FROM 子句中出现的子查询。
EXPLAIN SELECT * FROM ( SELECT id FROM business_order t ) temp
在 MySQL 5.6 中执行结果为: 在 MySQL 5.7 后版本做了优化,不再标记为 DERIVED:
9、DEPENDENT DERIVED
派生表,在 DERIVED 的基础上,依赖于外部查询。
10、MATERIALIZED
物化子查询。将子查询结果集中的记录保存到临时表的过程称之为物化(Materialize)。那个存储子查询结果集的临时表称之为物化表。
EXPLAIN SELECT * FROM business_order WHERE out_trade_no IN (SELECT out_trade_no FROM business_order_log WHERE operator_id = 1002)
执行结果为:
11、UNCACHEABLE SUBQUERY
无法缓存结果的子查询,必须为外部查询的每一行重新计算结果。
12、UNCACHEABLE UNION
union 中第二个或以后的 select ,属于不可缓存的子查询。
table
对应行正在访问那一个表,表名或者别名,可能是临时表或者 union 合并结果集。
- 如果是具体的表名,则表明从实际的物理表中获取数据,当然也可以是表的别名。
- 表名是 derived N 的形式,表示使用了 id 为 N 的查询产生的衍生表。
- 当有 union result 的时候,表名是 <union n1,n2> 等的形式,n1,n2 表示参与union的 id。
partitions
访问的分区表信息。版本 5.7 以前,该项是 explain partitions 显示的选项,5.7 以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空(null)。
type
表示从表中查询到行所执行的方式,查询方式是 SQL 优化中一个很重要的指标。官方全称是join type,意为:连接类型。Mysql 8.0 中 type 类型达到了12种,常见的有6种,它们结果值从好到差依次是:system > const > eq_ref > ref > range > index > ALL。
system
表中只有一行数据匹配,此时根据索引查询一次就能找到对应的数据。
const
表中最多只有一行数据匹配,此时根据索引查询一次就能找到对应的数据。 如果是 B + 树索引,我们知道此时索引构造成了多个层级的树,当查询的索引在树的底层时,查询效率就越低。const 表示此时索引在第一层,只需访问一层便能得到数据。
# 主键ID
EXPLAIN SELECT * FROM business_order WHERE id = 3;
# 唯一索引
EXPLAIN SELECT * FROM business_order WHERE out_trade_no = '2011151834337286';
执行结果为:
eq_ref
使用唯一索引扫描,常见于多表连接中使用主键和唯一索引作为关联条件。
EXPLAIN SELECT * FROM business_order WHERE out_trade_no IN (SELECT out_trade_no FROM business_order_log WHERE operator_id = 1002)
执行结果为:
ref
非唯一索引扫描,还可见于唯一索引最左原则匹配扫描。
EXPLAIN SELECT * FROM business_order WHERE store_id = 60;
执行结果为:
range
表示利用索引查询的时候限制了范围,在指定范围内进行查询,这样避免了index的全索引扫描,适用的操作符: <>, >, >=, <, <=, IS NULL, BETWEEN, LIKE, or IN() 。
EXPLAIN SELECT * FROM business_order WHERE order_time>'2022-01-01 00:00:00' AND order_time <'2022-12-01 00:00:00';
执行结果: 只查ID:
EXPLAIN SELECT * FROM business_order WHERE order_time>'2022-01-01 00:00:00' AND order_time <'2022-12-01 00:00:00';
执行结果:
index
索引全表扫描,此时遍历整个索引树。这个比 ALL 的效率要好,主要有两种情况,一种是当前的查询时覆盖索引,即我们需要的数据在索引中就可以索取,或者是使用了索引进行排序,这样就避免数据的重排序。
EXPLAIN SELECT out_trade_no,COUNT(*) FROM business_order GROUP BY out_trade_no;
执行结果为:
ALL
表示全表扫描,需要遍历全表来找到对应的行。
EXPLAIN SELECT * FROM business_order;
执行结果为:
possible_keys
显示可能应用在这张表中的索引,一个或多个,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。
key
实际使用的索引,如果为null,则没有使用索引,查询中若使用了覆盖索引,则该索引和查询的 select 字段重叠。
key_len
表示索引中使用的字节数,可以通过 key_len 计算查询中使用的索引长度,在不损失精度的情况下长度越短越好。
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数。
rows
根据表的统计信息及索引使用情况,大致估算出找出所需记录需要读取的行数,此参数很重要,直接反应的 sql 找了多少数据,在完成目的的情况下越少越好。
filtered
查找到所需记录占总扫描记录数的比例。 在完成目的的情况下越大越好。5.7 之前使用 explain extended 时会出现这个列,5.7之后的版本默认就有这个字段,不需要使用 explain extended 了。这个字段表示存储引擎返回的数据在 server 层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数。
Extra
包含额外的信息。以下为常见的信息,其中特别要注意 using filesort
和 using temporary
非常消耗性能:
using filesort
说明 mysql 无法利用索引进行排序,只能利用排序算法进行排序,会消耗额外的位置。
EXPLAIN SELECT * FROM business_order ORDER BY order_status;
执行结果为:
using temporary
建立临时表来保存中间结果,查询完成之后把临时表删除。
EXPLAIN SELECT order_status,COUNT(*) FROM business_order GROUP BY order_status LIMIT 10;
执行结果为:
using index
这个表示当前的查询时覆盖索引的,直接从索引中读取数据,而不用访问数据表。如果同时出现 using where 表面索引被用来执行索引键值的查找,如果没有,表面索引被用来读取数据,而不是真的查找。
EXPLAIN SELECT out_trade_no,COUNT(*) FROM business_order GROUP BY out_trade_no LIMIT 10;
执行结果为:
using where
使用 where 进行条件过滤。
EXPLAIN SELECT * FROM business_order WHERE order_status = 3;
执行结果如下:
using join buffer
使用连接缓存。未复现。
impossible where
where 语句的结果总是false。
EXPLAIN SELECT * FROM business_order WHERE id = 1000000000000;
执行结果为:
no matching row in const table
存在一个空表,或者没有行能够满足唯一索引条件。
EXPLAIN SELECT * FROM business_order WHERE id = 0;
执行结果为:
评论区