MySql Explain详解

Colin791
151 阅读8分钟

简介

Explain是Mysql提供的一个性能优化工具，它可以对select语句进行分析，并输出select执行的详细信息，以供开发人员针对性对查询语句进行优化。

Explain的使用非常简单，只需要在select语句前加上EXPLAIN关键字即可，如下图所示： 加上explain执行查询后会返回执行计划的信息，而不是真的执行这条SQL。

ExplainSample1.png

Explain 输出各列参数说明

列名说明
idselect的执行编号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行
select_type表示对应行是简单还是复杂的查询
table表示explain的一行正在访问哪个表
partitions
type表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围（ALL、index、range、ref、eq_ref、const/system、NULL）
possible_keys显示查询可能使用哪些索引来查找，有利于高效的查找
key显示mysql实际采用哪个索引来优化对该表的访问
key_len显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列
ref显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const(常量),字段名(例：film.id)
rows为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数
filtered
Extra额外信息，如using index、filesort等

select_type 列

select_type表示对应是简单还是复杂查询。

  • simple: 简单查询，查询不包含子查询和union.

  • primary: 复杂查询中最外层的select

  • subquery: 包含在select中的子查询（不在from字句中）

  • derived: 包含在from字句中的子查询，MySQL会将结果存放在一个临时表中，也称为派生表。

    以下例子演示了primary\subquery\derived 类型：

select_type_primary_subquery_derived.png

  • union: 在union中第二个和随后的select

select_type_union.png

table 列

当from字句中有子查询时，table列是格式，表示当前查询依赖id=N的查询，于是先执行id=N的查询。当有union时，UNION RESULT的table 列的值为<union1,2>,1和2表示参与union的select行id.

type 列

这一列表示访问类型，是较为重要的一个指标，结果值从好到坏依次是:

system > const > eq_ref > ref > range > index > ALL.

一般来说，得保证查询达到range 级别，最好达到ref.

  • 该列的值还可能为NULL，意味说mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表：

null_type.png

  • const,system: Mysql 能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings的结果）。用于primary key 或 unique key 的所有列与常数比较时，所以表最多有一行匹配，读取一次，速度比较快。system是const的特例，表里只有一条元组匹配时为system.

select_type_system_const.png

  • eq_ref : primary key 或 unique key 索引的所有部分被连接使用，最多只返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的select查询不会出现这种type.

type_eq_ref.png

  • ref :相比eq_ref,不使用唯一索引，而是使用普通索引或者唯一索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

    简单的select查询，film_name 是普通索引（非唯一索引）

ref_1.png

关联表查询，idx_film_actor_id 是 film_id 和 actor_id 的联合索引，这里使用了film_actor的左边前缀film_id 部分。

ref_2.png

  • range: 范围扫描通常出现在in(),between,>,<,>=等操作中，使用一个索引来检索给定范围的行。

range.png

  • index: 扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，索引这种通常比All快一些。

index.png

  • ALL: 即全表扫描，扫描聚集索引的所有叶子节点。通常情况下这需要增加索引来进行优化。

all.png

possible_keys 列

这一列显示查询可能使用哪些索引来查找。 Explain时可能出现possible_keys 有值，而key 显示NULL的情况，这种情况是因为表中数据不多，mysql 认为索引对此查询帮助不大，选择了全表查询。

如果该列为NULL，则表示没有相关索引，在这种情况下，可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能，然后用explain查看效果。

key 列

这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，该列为NULL，如果想强制mysql使用或忽略possible_keys列中的索引，在查询中使用force index 、ignore index.

key_len 列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。举例来说，film_actor的联合索引idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4个字节，通过结果中key_len = 4 可以推断出查询使用了第一个列：film_id 来执行索引查找。

key_len.png

key_len 的计算规则如下：

  • 字符串：char(n)和varchar(n), 5.0.3之后版本，n均代表字符数，而不是字节数，如果是utf-8,一个数字或字母占用一个字节，一个汉字占用3个字节
    • char(n):如果存汉字长度就是3n字节
    • varchar(n):如果存汉字则长度是3n+2字节，加的2字节用来存储字符串长度，因为varchar是可变长字符串。
  • 数值类型：
    • tinyint: 1 字节
    • smallint: 2字节
    • int: 4字节
    • bigint: 8字节
  • 时间类型：
    • date：3字节
    • timestamp: 4 字节
    • datetime: 8 字节
  • 如果字段运行为NULL，需要1字节记录是否为NULL

索引最大长度是768字节，当字符串过长时，mysql 会做一个类似最前缀索引的处理，将前半部分的字符提取出来做索引。

Extra 列

这一列展示的是额外信息，常见的重要值如下：

  • Using index: 使用覆盖索引

    覆盖索引的定义：mysql 执行计划explain结果里的key有使用索引,如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra 里一般都有using index; 覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键索引树里获取其他字段值。

fugaiindex.png

  • Using where: 使用where语句来处理结果，并且查询的列未被索引覆盖。

usingwhere.png

  • Using index condition: 查询的列不完全被索引覆盖，where 条件中是一个前导列的范围

usingindexcondition.png

  • Using temporary: mysql 需要创建一张临时表来处理查询,出现这种情况一般是要进行优化的，首先是想到用索引来优化。
    • actor.actor_name 没有索引，此时创建了临时表来distinct

usingtemporary_1.png

  • film.film_name创建了idx_film_name索引，此时查询时extra是using index，没有用临时表

usingindex_2.png

  • Using filesort: 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也要考虑使用索引来优化的。
    • actor.actor_name 未创建索引，会浏览actor整个表，保存排序关键字actor_name和对应的id,然后排序actor_name并检索行记录

usingfilesort.png

  • film.film_name建立了idx_film_name 索引，此时查询extra是using index

usingindex2.png

  • Select tables optimized away: 使用某聚合函数(比如max、min)来访问存在索引的某个字段时

optimizedaway.png

附：

DROP TABLE IF EXISTS actor;

CREATE TABLE actor(
id INT(11) NOT NULL,
actor_name VARCHAR(45) DEFAULT NULL,
update_time DATETIME DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO actor(id, actor_name, update_time) VALUE (1, 'a', '2021-11-16 14:46:18'), (2, 'b', '2021-11-16 14:46:18'), (3, 'c', '2021-11-16 14:46:18');

SELECT * FROM actor;

DROP TABLE IF EXISTS film;

CREATE TABLE film(
id INT(11) NOT NULL AUTO_INCREMENT,
film_name VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_film_name (film_name)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO film(film_name) VALUE ('film0'), ('film1'), ('film2');

SELECT * FROM film;

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);

SELECT * FROM film_actor;

EXPLAIN SELECT * FROM actor;

SET SESSION optimizer_switch = 'derived_merge=off'; # 关闭MySQL新特性对衍生表的合并优化
EXPLAIN SELECT (SELECT 1 FROM actor WHERE id=1) FROM (SELECT * FROM film WHERE id=1) der;
SET SESSION optimizer_switch = 'derived_merge=on'; # 还原默认配置

EXPLAIN SELECT 1 UNION ALL SELECT 1;

EXPLAIN SELECT MIN(id) FROM film;

EXPLAIN SELECT * FROM (SELECT * FROM film WHERE id =1) tmp_table;
SHOW WARNINGS;

EXPLAIN SELECT * FROM film_actor LEFT JOIN film ON film_actor.film_id = film.id;

EXPLAIN SELECT * FROM film WHERE film_name = 'film1';

EXPLAIN SELECT film_id FROM film LEFT JOIN film_actor ON film.id = film_actor.film_id;

EXPLAIN SELECT * FROM actor WHERE id > 1;

EXPLAIN SELECT * FROM film;

EXPLAIN SELECT * FROM actor;

EXPLAIN SELECT * FROM film_actor WHERE film_id = 2;

EXPLAIN SELECT film_id FROM film_actor WHERE film_id = 1;

EXPLAIN SELECT * FROM actor WHERE actor_name = 'a';

EXPLAIN SELECT * FROM film_actor WHERE film_id > 1;

EXPLAIN SELECT DISTINCT actor_name FROM actor;

EXPLAIN SELECT DISTINCT film_name FROM film;

EXPLAIN SELECT * FROM actor ORDER BY actor_name;

EXPLAIN SELECT * FROM film ORDER BY film_name;

EXPLAIN SELECT MIN(id) FROM film;
avatar
文章
阅读
粉丝