Explain 工具介绍
使用 EXPLAIN 关键字可以模拟优化器执行 SQL 语句, 分析查询语句或是结构的性能瓶颈. 在 select 语句之前增加 explaion 关键字, MySQL 会在查询上设置一个标记, 执行查询会返回执行计划的信息, 而不是执行 SQL.
Explaion 分析示例
-- 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
-- film 建表语句:
- CREATE TABLE `film` (
- `id` int(11) NOT NULL,
- `name` varchar(10) NOT NULL,
- PRIMARY KEY (`id`),
- KEY `idx_name` (`name`)
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- 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
执行 explain:
explain select * from actor;
如果是 select 语句返回的是执行结果, 在 select 语句前面加上 explain 返回的是这条查询语句的执行 SQL.
EXPLAIN 两个变种
explain extended
会在 explain 的基础上额外提供一些查询优化的信息. 紧随其后通过 show warnings 命令可以得到优化后的查询语句, 从而看出优化器优化了什么. 额外还有 filtered 列, 是一个半分比的值, rows*filtered / 100 可以估算出将要和 explain 中前一个表进行连接的行数(前一个表指 explain 中的 id 值比当前表 id 值小的表).
explain EXTENDED select * from actor where id = 1;
explain partitions
相比 explain 多了个 partitions 字段, 如果查询是基于分区表的话, 会显示查询将访问的分区.
Explain 中的列
id 列
id 列的编号是 select 的序列号, 有几个 select 就有几个 id, 并且 id 的顺序是按 select 出现的顺序增长的.
id 越大执行优先级越高, id 相同则从上往下执行, id 为 NULL 最后执行.
- explain select (select 1 from actor where id = 1) from (select * from film
- where id = 1) der;
select type 列
select type 表示对应行是简单还是复杂的查询.
simple: 简单查询. 查询不包含子查询和 union.
explain select * from film where id=1
primary: 复杂查询中最外层的 select
subquery: 包含在 select 中的子查询(不在 from 子句中)
derived: 包含在 from 子句中的子查询. MySQL 会将结果存放在一个临时表中, 也称为派生表.
- explain select (select 1 from actor where id = 1) from (select * from film
- where id = 1) der;
union: 在 union 关键字随后的 selelct.
EXPLAIN select 1 union all select 1;
table 列
这一列表示 explain 的一行正在访问哪个表.
当 from 子句中有子查询时, table 列是格式, 表示当前查询依赖 id=N 的查询, 于是先执行 id=N 的查询.
当有 union 时, UNION RESULT 的 table 列的值为 < union 1,2>,1 和 2 表示参与 union 的 select 行 id.
type 列
这一列表示关联类型或访问类型, 即 MySQL 决定如何查找表中的行, 查找数据行对应的大概范围.
依次从最优到最差的分别为: system>const>eq_ref>ref>range>index>All
一般来说, 得保证查询达到 range 级别, 最好达到 ref.
NULL:MySQL 能够在优化阶段分解查询语句, 在执行阶段用不着在访问表或索引. 例如: 在索引列中选取最小值, 可以单独查找索引来完成, 不需在执行时访问表.
EXPLAIN select min(id) from film;
const,system:MySQL 能对查询的某部分进行优化并将其转换成一个常量(可看成是 show warnings 的结果). 用于 primay key 或 unique key 的所有列与常数比较时, 所以表最多有一个匹配行, 读取 1 次, 速读较快. system 是 const 的特例, 表中只有一行元素匹配时为 system.
EXPLAIN select * from (select * from film where id= 1) as tmp;
eq_ref:primay key 或 unique key 索引的所有部分被连接使用, 最多只会返回一条符合条件的记录. 这可能是 const 之外最好的联接类型, 简单的 select 查询不会出现这种 type.
EXPLAIN select * from (select * from film where id= 1) as tmp;
ref: 相比 eq_ref, 不适用唯一索引, 而是使用普通索引或者唯一索引的部分前缀, 索引要和某个值相比较, 可能会找到多个符合条件的行.
简单 select 查询, name 是普通索引(非主键索引或唯一索引)
EXPLAIN select * from film where name='film1';
关联表查询, 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.film_id;
range: 范围扫描通常出现在 in(), between,>,<,>= 等操作中. 使用一个索引来检索给定范围的行.
EXPLAIN select * from actor WHERE id>1;
index: 扫描全表索引, 通常比 All 快一些
EXPLAIN select * from film;
all: 即全表扫描, 意味着 MySQL 需要从头到尾去查找所需要的行. 这种情况下需要增加索引来进行优化.
EXPLAIN SELECT * from actor;
possible_keys 列
这一列显示 select 可能会使用哪些查询来查找.
explain 时可能会出现 possible_keys 有列, 而 key 显示为 NULL 的情况, 这种情况是因为表中的数据不多, MySQL 认为索引对此查询帮助不大, 选择了全表扫描.
如果该列为 NULL, 则没有相关的索引. 这种情况下, 可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能, 然后用 explain 查看效果.
EXPLAIN SELECT * from film_actor where film_id =1;
key 列
这一列显示 MySQL 实际采用哪个索引对该表的访问.
如果没有使用索引, 则改列为 NULL. 如果想强制 MySQL 使用或忽视 possible_keys 列中的索引, 在查询中使用 force index, ignore index.
key_len 列
这一列显示了 MySQL 在索引里使用的字节数, 通过这个值可以估算出具体使用了索引中的哪些列.
EXPLAIN SELECT * from film_actor where film_id =1;
film_actor 的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 id 列组成, 并且每个 int 是 4 字节. 通过结果中的 key_len=4 可推断出查询使用了第一个列: film_id 列来执行索引查找.
ken_len 计算规则如下:
字符串
char(n):n 字节长度
varchar(n):n 字节存储字符串长度, 如果是 utf-8, 则长度是 3n+2
数值类型
tinyint:1 字节
smallint:2 字节
int:4 字节
bigint:8 字节
时间类型
date:3 字节
timestamp:4 字节
datetime:8 字节
如果字段允许为 NULL, 需要 1 字节记录是否为 NULL
索引最大长度是 768 字节, 当字符串过长时, MySQL 会做一个类似做前缀索引的处理, 将前半部分的字符串提取出来做索引.
ref 列
这一列显示了在 key 列记录的索引中, 表查找值所用到的列或常量, 常见的有: const(常量), 字段名等. 一般是查询条件或关联条件中等号右边的值, 如果是常量那么 ref 列是 const, 非常量的话 ref 列就是字段名.
EXPLAIN SELECT * from film_actor where film_id =1;
row 列
这一列是 MySQL 估计要读取并检测的行数, 注意这个不是结果集的行数.
Extra 列
这一列是额外信息.
Using index: 使用覆盖索引(结果集的字段是索引, 即 select 后的 film_id)
explain select film_id from film_actor where film_id=1;
Using index condition: 查询的列不完全被索引覆盖, where 条件中是一个前导的范围
explain select * from film_actor where film_id> 1;
Using where: 使用 where 语句来处理结果, 查询的列未被索引覆盖
explain select * from actor where name ='a'
Using temporary:MySQL 需要创建一张临时表来处理查询. 出现这种情况一般要进行优化, 首先要想到是索引优化.
explain select DISTINCT name from actor;
actor.name 没有索引, 此时创建了临时表来处理 distinct.
explain select DISTINCT name from film;
file.name 建立了普通索引, 此时查询时 Extra 是 Using index, 没有用到临时表.
Using filesort: 将用外部排序而不是索引排序, 数据较小时从内存排序, 否则需要在磁盘完成排序. 这种情况下一般也是要考虑使用索引来优化的.
explain select * from actor order by name;
actor.name 未创建索引, 会浏览 acotr 整个表, 保存排序关键字 name 和对应 id, 然后排序 name 并检索行记录.
explain select * from film order by name;
film.name 建立了 idx_name 索引, 此时查询时 extra 是 Using index.
select tables optimized away: 使用某些聚合函数 (比如: max,min) 来访问存在索引的某个字段
explain select min(id) from film ;
来源: http://www.linuxidc.com/Linux/2019-08/160340.htm