MySQL explain使用

概述

MySQL 中的 explain 命令显示了 mysql 如何使用索引来处理 select 语句以及连接表。explain 显示的信息可以帮助选择更好的索引和写出更优化的查询语句。

MySQL explain详解

语法

explain + select 语句;

比如：

explain select * from tb_haicoder;

explain的作用

1. 描述 MySQL 如何执行查询操作、执行顺序，使用到的索引，以及 MySQL 成功返回结果集需要执行的行数。
2. 可以帮助我们分析 select 语句，让我们知道查询效率低下的原因，从而改进我们的查询，让查询优化器能够更好的工作。

查询优化器的作用：

1. 优化 select 语句，分析哪些是常量表达式（例如 id = 1），以及分析哪些表达式可以直接转换成常量的。
2. 对 where 条件进行简化和转换，如去掉无用条件，调整条件结构等。
3. 读取涉及的表的统计信息，并计算分析（例如返回的行数，索引信息等），最终得出执行计划

实例

我们使用如下语句，创建数据表：

CREATE TABLE course(
    id int,
    name varchar(40),
    url varchar(128)
);

并使用如下语句，插入数据：

INSERT INTO course(id, name, url)values(1, "python", "https://haicoder.net/python/python-tutorial.html");
INSERT INTO course(id, name, url)values(2, "golang", "https://haicoder.net/golang/golang-tutorial.html");
INSERT INTO course(id, name, url)values(3, "java", "https://haicoder.net/java/java-development.html");
INSERT INTO course(id, name, url)values(4, "javascript", "https://haicoder.net/javascript/javascript-tutorial.html");

现在，我们使用 explain 对 sql 语句进行查询分析，具体语句如下：

EXPLAIN SELECT * FROM course WHERE name="python";

执行完毕后，如下图所示：

explain参数详解

参数列表

Columns	Meaning
id	每个 select 子句的标识 id
select_type	select 语句的类型
table	当前表名
partitions	匹配的分区
type	当前表内访问方式 join type
possible_keys	可能使用到的索引
key	经过优化器评估最终使用的索引
key_len	使用到的索引长度
ref	引用到的上一个表的列
rows	rows_examined，要得到最终记录索要扫描经过的记录数
filtered	按表条件过滤行的百分比
Extra	额外的信息说明

id

SELECT 识别符。这是 SELECT 查询序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序。

• id 相同： 执行顺序由上至下。
• id 不同： 如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行。
• id相同又不同： id 如果相同，可以认为是一组，从上往下顺序执行，在所有组中，id 值越大，优先级越高，越先执行。

select_type

select 类型，它有以下几种:

select_type value	Meaning
SIMPLE	简单的 SELECT 语句（不包括 UNION 操作或子查询操作）
PRIMARY	PRIMARY：查询中最外层的 SELECT（如两表做 UNION 或者存在子查询的外层的表操作为 PRIMARY，内层的操作为 UNION）
UNION	UNION：UNION 操作中，查询中处于内层的 SELECT（内层的 SELECT 语句与外层的 SELECT 语句没有依赖关系）
DEPENDENT UNION	DEPENDENT UNION：UNION 操作中，查询中处于内层的 SELECT（内层的 SELECT 语句与外层的 SELECT 语句有依赖关系）
UNIOIN RESULT	UNION RESULT：UNION 操作的结果，id 值通常为 NULL
SUBQUERY	SUBQUERY：子查询中首个 SELECT（如果有多个子查询存在）
DEPENDENT SUBQUERY	DEPENDENT SUBQUERY：子查询中首个 SELECT，但依赖于外层的表（如果有多个子查询存在）
DERIVED	DERIVED：被驱动的 SELECT 子查询（子查询位于 FROM 子句）
MATERIALIZED	MATERIALIZED：被物化的子查询
UNCACHEABLE SUBQUERY	UNCACHEABLE SUBQUERY：对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
UNCACHEABLE UNION	UNCACHEABLE UNION：UNION 操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）

table

当前表名。

partitions

匹配的分区。

type

当前表内访问方式，性能由好到坏排序：

type value	Meaning
system	表中只有一行
const	单表中最多有一个匹配行，primary key 或者 unique index 的检索
eq_ref	多表连接中被驱动表的连接列上有 primary key 或者 unique index 的检索
ref	与 eq_ref 类似，但不是使用 primary key 或者 unique index，而是普通索引。也可以是单表上 non-unique 索引检索
fulltext	使用 FULLTEXT 索引执行连接
ref_or_null	与ref类似，区别在于条件中包含对 NULL 的查询
index_merge	索引合并优化，利用一个表里的 N 个索引查询,key_len 表示这些索引键的和最长长度
unique_subquery	in 的后面是一个查询 primary key\unique 字段的子查询
index_subquery	in 的后面是一个查询普通 index 字段的子查询
range	单表索引中的范围查询,使用索引查询出单个表中的一些行数据。ref 列会变为 null
index	等于 ALL。它有两种情况：(1)覆盖索引 (2)用索引的顺序做一个全表扫描。
all	全表扫描

一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

possible_keys

提示使用哪个索引会在该表中找到行。

key

MYSQL 使用的索引。

key_len

key_len 大小的计算规则:

1. 一般地，key_len 等于索引列类型字节长度，例如 int 类型为 4-bytes，bigint 为 8-bytes；
2. 如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8 则 key_len 至少是 90-bytes；
3. 若该列类型定义时允许 NULL，其 key_len 还需要再加 1-bytes；
4. 若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引，也被视为动态列类型），其 key_len 还需要再加 2-bytes;

ref

ref 列显示使用哪个列或常数与 key 一起从表中选择行。

rows

rows_examined，要得到最终记录索要扫描经过的记录数，这个数越小越好。

filterrd

按表条件过滤行的百分比。

Extra

Extra 是对执行计划的额外说明，包含重要信息：

type value	Meaning
const row not found	所要查询的表为空。
Distinct	mysql 正在查询 distinct 值，因此当它每查到一个 distinct 值之后就会停止当前组的搜索，去查询下一个值。
Impossible WHERE	where 条件总为 false，表里没有满足条件的记录。
Impossible WHERE noticed after reading const tables	在优化器评估了 const 表之后，发现 where 条件均不满足。
no matching row in const table	当前 join 的表为 const 表，不能匹配。
Not exists	优化器发现内表记录不可能满足 where 条件。
Select tables optimized away	在没有 group by 子句时，对于 MyISAM 的 select count(*) 操作，或者当对于 min(),max() 的操作可以利用索引优化，优化器发现只会返回一行。
Using filesort	使用 filesort 来进行 order by 操作。
Using index	覆盖索引。
Using index for group-by	对于 group by 列或者 distinct 列，可以利用索引检索出数据，而不需要去表里查数据、分组、排序、去重等等。
Using join buffer	之前的表连接在 nested loop 之后放进 join buffer，再来和本表进行 join。适用于本表的访问 type 为 range，index 或 all。
Using sort_union,using union,using intersect	index_merge 的三种情况。
Using temporary	使用了临时表来存储中间结果集，适用于 group by，distinct，或 order by 列为不同表的列。
Using where	在存储引擎层检索出记录后，在 server 利用 where 条件进行过滤，并返回给客户端。

总结

如果还是觉得 EXPLAIN 执行计划列太多了，也记不住呀，那么请重点关注以下几列：

第1列： ID 越大，执行的优先级越高；ID 相等，从上往下优先顺序执行。

第2列： select_type 查询语句的类型，SIMPLE 简单查询，PRIMARY 复杂查询，DERIVED 衍生查询(from 子查询的临时表)，派生表。

第4列： 请重点掌握，type 类型，查询效率优先级：system->const->eq_ref->ref->range->index->ALL。

ALL 是最差的，system 是最好的，性能最佳，阿里巴巴开发规约中要求最差也得到 range 级别，而不能有 index、ALL。