本文实战使用Mysql 8

在日常开发或生产中，我们都会遇到一些需要优化的SQL【因为慢或者结构不好等原因】，那么在优化之前我们还得要分析这个SQL是不是慢的，到底为什么会慢，定位到问题才能够对症下药解决问题。而这种时候我们就需要用到Explain关键字。

Explain是什么？

Explain是mysql的一个关键字，使用explain可以模拟优化器执行SQL语句，从而知道MySQL是如何处理SQL语句的，分析查询语句或结构的性能瓶颈

也就是我们可以根据Explain输出的内容来分析我们的SQL问题出在哪里。

Explain用法

普通用法

很简单，只需要在目标sql语句【SELECT|INSERT|UPDATE|DELETE】之前加上explain关键字即可，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）

explain select * from sys_user where age = 20

扩展用法

Explain有两个变体：

1）**explain extended

会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。

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

explain extended select * from sys_user where age = 20;
SHOW WARNINGS;

值得注意的是，mysql8.0之后，使用该用法会报错，因为实现了不需要添加extended都可以做到上述效果

2）**explain partitions**

相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

而在mysql8.0后也已经整合到了explain里面，不需要再另加partitions关键字，否则会报错

Explain-执行计划 - 图4

Explain返回执行计划字段

id

select 的执行序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。

id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行

如下述语句，先执行select 1 from sys_dept，后执行select xxx from sys_user

explain select (select 1 from sys_dept) from sys_user;

select_type

表示对应sql是简单还是复杂的查询

MySQL将 select 查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。

复杂查询分为三类：简单子查询(SUBQUERY)、派生表（from语句中的子查询）、union 查询。

1）简单查询—- SIMPLE

**
查询不包含子查询和union

explain select * from sys_user where id = 1

2）复杂查询之最外层查询—-PRIMARY

复杂查询（简单子查询、派生表查询，union查询）的最外层的select语句

如下述语句的select xx from sys_user**

explain select (select 1 from sys_dept) from sys_user;

Explain-执行计划 - 图7

3）复杂查询之简单子查询—-SUBQUERY

explain select (select 1 from sys_dept) from sys_user;

或者这种形式也是：

explain select * from sys_user where id = (select user_id from sys_user_dept as sud where user_id >10);

4）复杂查询之派生表查询—-DERIVED

在from子句中的子查询。

MySQL会将该子查询结果存放在一个临时表中，也称为派生表（derived的英文含义）

explain select * from (select * from sys_user where id = 1) as der;

由于**mysql5.7之后使用了派生合并，会把临时表合并到外层查询,得出的explain执行计划结果就只有一条记录

可以使用set optimizer_switch='derived_merge=off'关闭派生合并

下图中的table列，指代id为2的派生表

5）复杂查询之union查询—-UNION与UNION RESULT

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

UNION操作符会将结果存放到临时表，UNION RESULT 则代表从 union 临时表检索结果的 select

explain select id from sys_user union select user_id from sys_user_dept;

union结果【UNION RESULT】总是放在一个匿名临时表[]中，临时表不在SQL中出现，因此它的id是NULL。

table

对应sql涉及的表
**

partitions

匹配到的分区信息

type

表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：**system > const > eq_ref > ref > range > index > ALL**

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

NULL

mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引，即**不访问任何表和索引

比如：查找最小id，可以单独查找索引来完成，不需要在执行时访问表

system与const

sql语句最优情况，基本不用再去优化

const：单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询

explain select temp.* from (select * from sys_user_dept where id = 1) as temp;
show WARNINGS;

system是const的特例，表里只有一条元组匹配时为system，如上图中的derived2临时表中就只有一条数据

eq_ref

primary key 或 unique key 索引的**所有部分被多表连接使用，**最多只会返回一条符合条件的记录。

这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type

# sys_user.id为主键索引，sys_user_dept.user_id 有唯一索引
explain select su.* from sys_user su left join sys_user_dept sud on su.id = sud.user_id;
show WARNINGS;

ref

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

简单查询：

# sys_user.score 有普通索引
explain select su.* from sys_user su where su.score = 40;
show WARNINGS;

关联表查询：

# union_index 为sys_user_dept表中dept_id和userid的联合普通索引，此处仅使用了前缀
explain select sd.* from sys_dept sd left join sys_user_dept sud on sud.dept_id = sd.dept_id;
show WARNINGS;

range

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

explain select * from sys_user where id >0;
show WARNINGS;

index

索引树扫描，扫描全表索引，这通常比ALL快一些。（index是从索引中读取的，而all是从硬盘中读取）

explain select count(*) from sys_user;
show WARNINGS;

走score字段索引

ALL

即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

possible_keys

显示查询可能使用哪些索引来查找

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

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

key

实际查询使用的索引
如果没有使用索引，则该列是 NULL。

如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

Explain-执行计划 - 图24

key_len

mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

ref

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

rows

mysql估计要读取并检测的行数，注意这个不是结果集里的行数

filtered

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

Extra

额外信息，常用重要值如下：

Using index：select查询的列被索引覆盖，并且where筛选条件是索引的前导列，是性能高的表现

# select后面的查询列被索引union_index(age,sex)所覆盖，且where条件中使用了union_index(age,sex)的前导列
explain select age,sex from sys_user where age = 0;
show WARNINGS;

Using index condition：查询的列不完全被索引覆盖，where条件中是一个索引前导列的范围比较；**

# select后面的查询列没有被索引union_index(age,sex)所覆盖【多了个score】
# 且where条件中使用了union_index(age,sex)的前导列的范围查询
explain select age,sex,score from sys_user where age > 0;
show WARNINGS;

Using where: select查询的列未被索引覆盖，where筛选条件非索引的前导列**

# select后面的查询列没有被索引union_index(age,sex)所覆盖【多了个score】
# 且where条件中没有使用union_index(age,sex)的前导列
explain select age,sex,score from sys_user where sex ='女';
show WARNINGS;

Using where Using index：查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据

# select后面的查询列被索引union_index(age,sex)所覆盖
# 且where条件中没有使用union_index(age,sex)的前导列
explain select age,sex from sys_user where sex = 10;
show WARNINGS;

NULL：查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引

# select后面的查询列没有被索引union_index(age,sex)所覆盖【多了个score】
# 且where条件中使用了union_index(age,sex)的前导列
explain select age,sex,score from sys_user where age =20;
show WARNINGS;

Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

Using filesort：当查询中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

Impossible where：这个值强调了where语句会导致没有符合条件的行（where子句的值总是false，不能用来获取任何元组）。

Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

No tables ：查询语句中使用from dual 或不含任何from子句