暗夜零星
暗夜零星

Mysql Explain工具

Eddie Eddie 收录于 性能调优 和 Mysql调优
 约 3500 字 预计阅读 7 分钟  - 次阅读  

Mysql Explain工具概述、每个字段释义，示例。

在SELECT语句之前增加EXPLAIN关键字 ，MySQL会设置一个标记，执行查询会返回执行计划的信息，而不会执行这条SQL。

注意
如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

更多信息参考官方文档

Explain简单示例

示例表结构：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# 示例表：
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017‐12‐22
15:27:18'), (2,'b','2017‐12‐22 15:27:18'), (3,'c','2017‐12‐22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` int(11) NOT NULL,
`film_id` int(11) NOT NULL,
`actor_id` int(11) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

执行sql:explain select * from actor;，得到以下结果:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE actor ALL 3 100

每个表会输出一行，如果有多个表进行join查询，就会输出多行。

执行EXPLAIN后，通过SHOW WARNINGS查询 MySQL 的 SQL 优化建议，但建议不一定在运行时执行。

1
2

explain select * from film where id = 1;
show warnings;
Level Code Message
Note 1003 /* select#1 */ select 1 AS id,film1 AS name from test,film where 1

Explain中的列

1. Id

id列表示查询的序列号，每个SELECT都有一个id，且编号按查询出现的顺序递增。id越大，执行优先级越高；若id相同，则从上到下执行；无id（即NULL）的查询最后执行。

2. Select_type

select_type 列显示了查询中每个简单 SELECT（不是子查询或 UNION 中的 SELECT）的类型。

  • SIMPLE: 表示简单的 SELECT（不包含子查询或 UNION）。
  • PRIMARY: 如果查询包含 UNION，则最外层的查询会被标记为 PRIMARY。
  • SUBQUERY: 在 SELECT 或 WHERE 子句中的子查询（不在 from 子句中）。
  • DERIVED: 包含在 FROM 子句中的子查询。MySQL 会为每个这样的子查询创建一个临时表，并在外部查询中引用这个临时表。
  • UNION: UNION 中的第二个或后续查询。
其他类型
  • DEPENDENT UNION: 依赖外部查询的 UNION。
  • UNION RESULT: UNION 的结果。
  • DEPENDENT SUBQUERY: 依赖外部查询的子查询。例如，子查询的 WHERE 子句引用了外部查询的列。
  • MATERIALIZED: 对于某些子查询，MySQL 可能会选择将它们的结果存储在一个临时表中，以便在外部查询中多次引用。这些子查询被标记为 MATERIALIZED。
  • UNCACHEABLE SUBQUERY: 表示子查询的结果不能被缓存，并且必须在每次外部查询时重新评估。

3. Table

table 列指明了 EXPLAIN 结果中当前行正在访问的表。对于 FROM 子句中的子查询，table 列显示为 <derivedN>，其中 N 是该子查询的 id 值，表示当前查询依赖于该子查询。对于 UNION 操作，UNION RESULT 的 table 列显示为 <union1,2,…>，指示了参与 UNION 的各个查询的 id 值。

4. Partitions

如果查询涉及分区表，partitions 字段会列出查询将要访问的具体分区。

5. Type

这一列展示了MySQL如何查找表中的行，它反映了查询的效率。最佳到最差的查找方式依次是：system（最快）、const（单条匹配）、eq_ref（唯一索引匹配）、ref（非唯一索引匹配）、range（索引范围匹配）、index（全索引扫描）和ALL（全表扫描）。通常，我们希望查询至少达到range级别，最好能达到ref级别。

字段值 字段释义 示例SQL
NULL MySQL优化器分解查询，对于索引列的最小值，可仅通过索引查找，无需访问表。 explain select min(id) from film;
system,const MySQL优化器能优化查询，将涉及主键或唯一键与常数的比较转化为常量访问，最多读取一行，速度快。当仅有一条匹配时为system，属于const的特例。 explain extended select * from (select * from film where id = 1) tmp;show warnings;
eq_ref 当使用PRIMARY KEY或UNIQUE KEY索引的全部部分进行连接时，查询会高效返回单条记录，这种联接类型接近const，但简单SELECT查询不常见。 explain select * from film_actor left join film on film_actor.film_id = film.id;
ref 相比eq_ref，ref使用非唯一索引或唯一索引的部分前缀进行查找，可能返回多行匹配结果。 示例1：（简单 select 查询，name是普通索引（非唯一索引）。）
explain select * from film where name = 'film1';
示例2 ：.关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。
explain select film_id from film left join film_actor on film.id = film_actor.fi lm_id;
range 关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。 explain select film_id from film left join film_actor on film.id = film_actor.fi lm_id;
index 范围扫描常用于 IN(), BETWEEN, >, <, >= 等，利用索引检索特定范围内的行。 explain select * from actor where id > 1;
ALL 全表扫描涉及聚簇索引的完整遍历，建议通过添加或优化索引来提升性能。 explain select * from actor;

6. Possible_keys

EXPLAIN 的 possible_keys 列显示查询可能使用的索引，但 key 列显示实际使用的索引。如果 possible_keys 有值但 key 为 NULL，说明MySQL认为全表扫描比使用索引更快，可能是因为数据量小或查询条件不利于索引优化。

如果 possible_keys 也是 NULL，则没有相关索引。这时，可以检查 WHERE 子句来确定是否需要添加索引，并使用 EXPLAIN 再次查看效果。

7. Key

这一列告诉你MySQL用了哪个索引来快速查找数据。如果没有索引被用，会显示NULL。可以通过FORCE INDEX或IGNORE INDEX来指定MySQL用哪个索引。

8. Key_len

key_len 显示MySQL在索引中使用的字节数，可判断查询使用了索引的哪些部分。如film_actor表的idx_film_actor_id索引由film_id和actor_id组成，每列4字节。若key_len=4，则查询仅使用了film_id列。

1

explain select * from film_actor where film_id = 2;

key_len 的计算规则简化如下：

字符串类型

  • char(n) 和 varchar(n)：在5.0.3及以后版本中，n 代表字符数。
    • 如果使用UTF-8编码：
      • 数字或字母：1字节/字符
      • 汉字：3字节/字符
    • char(n)：汉字存储长度为 3n 字节
    • varchar(n)：汉字存储长度为 3n + 2 字节（额外的2字节用于存储字符串长度）

数值类型

  • tinyint：1字节
  • smallint：2字节
  • int：4字节
  • bigint：8字节

时间类型

  • date：3字节
  • timestamp：4字节
  • datetime：8字节

NULL值处理

  • 如果字段允许为 NULL，需要 1字节 来记录是否为 NULL。

索引长度限制

  • 索引最大长度是 768字节。当字符串过长时，MySQL会进行类似左前缀索引的处理，只使用字符串的前半部分作为索引。

9. Ref

这一列展示了在索引中，用于查找表中值的列或常量。常见标识有：常量（const）和具体的字段名（如film.id）。

10. Rows

这一列是MySQL预估的需要读取和检查的行数，并不等同于最终结果集中的行数。

11. Filtered

该列是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的id值比当前表id值小的表）。

12. Extra

这一列展示的是额外信息。常见值：

  • Using index：使用覆盖索引
    • mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值
    • explain select film_id from film_actor where film_id = 1;
  • Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖。
    • explain select * from actor where name = 'a';
  • Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
    • explain select * from film_actor where film_id > 1;
  • Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
    • actor.name没有索引，此时创建了张临时表来distinct。
      • explain select distinct name from actor;
    • film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表
      • explain select distinct name from film;
  • Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。
    • actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录
      • explain select * from actor order by name;
    • film.name建立了idx_name索引,此时查询时extra是using index
      • explain select * from film order by name;
  • Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段
    • explain select min(id) from film;
收录于 合集・Mysql调优 9
更新于 2023-05-18 
查看源码编辑此页报告问题
Mysqlexplain
返回 | 主页
0%