Explain分析全面介绍加实例

1、概念

①MySQL 内优化器

MySQL 体系结构中，包含 SQL 解析器、优化器等组件。SQL 解析器解析 SQL 之后，生成解析树。经过验证，解析树正确后，由优化器进一步优化解析树，最终生成一个执行计划（profile）。

②Explain 分析

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

列名	重要	描述
id	○	用来告诉我们整个SQL语句在执行过程中，有哪些大的步骤和小的步骤。
select_type	○	SELECT关键字对应的那个查询的类型
table		每个具体步骤对应的表名。可能是实际的表，也可能是保存中间数据的临时表。
partitions		匹配的分区信息
type	●	针对单表的访问方法
possible_keys		可能用到的索引
key	●	实际上使用的索引
key_len	●	实际使用到的索引长度
ref		当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows		预估的需要读取的记录条数
filtered		某个表经过搜索条件过滤后剩余记录条数的百分比
Extra		一些额外的信息

2、准备测试数据

create database db_hr;

use db_hr;

CREATE TABLE t1
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
CREATE TABLE t2
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
CREATE TABLE t3
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
CREATE TABLE t4
(
    id      INT(10) AUTO_INCREMENT,
    content VARCHAR(100) NULL,
    PRIMARY KEY (id)
);
INSERT INTO t1(content)
VALUES (CONCAT('t1_', FLOOR(1 + RAND() * 1000)));
INSERT INTO t2(content)
VALUES (CONCAT('t2_', FLOOR(1 + RAND() * 1000)));
INSERT INTO t3(content)
VALUES (CONCAT('t3_', FLOOR(1 + RAND() * 1000)));
INSERT INTO t4(content)
VALUES (CONCAT('t4_', FLOOR(1 + RAND() * 1000)));

3、id 字段

①id 字段含义

• 用来表示整个SQL语句执行过程中，分为哪些大步骤、小步骤。
• 一个 select 对应一个 id 唯一的值
• id 值每出现一次，就代表执行 SQL 语句的一个大步骤
• 总体的 SQL 语句执行的『大步骤』越少越好
• 多个 id 值
  • id 值相同：相同 id 值中包含的多条记录可以理解为执行这个大步骤时的各个『小步骤』。按照从上到下的顺序依次执行
  • id 值不同：代表执行这条 SQL 语句需要有多个大步骤，这些大步骤按照 id 值从大到小的顺序值。

②举例

[1]id 值相同的情况

SQL 本身：

select t1.id,t2.id,t3.id,t4.id from t1,t2,t3,t4

应用 Explain 分析：

explain select t1.id,t2.id,t3.id,t4.id from t1,t2,t3,t4

部分执行结果：

 

[2]id 值不同的情况

EXPLAIN
SELECT t1.id
FROM t1
WHERE t1.id = (SELECT t2.id FROM t2 WHERE t2.id = (SELECT t3.id FROM t3 WHERE t3.content = 't3_354'))

 

[3]相同的 id 值和不同的 id 值都有

EXPLAIN
SELECT t1.id, (select t4.id from t4 where t4.id = t1.id) id4
FROM t1,
     t2

[4]有子查询但是 id 值相同

explain
select t1.id
from t1
where t1.id in (select t2.id from t2);

 

这是因为查询优化器将子查询转换为了连接查询。

4、select_type字段

①字段含义介绍

一条 SQL 语句总体来看：其中可能会包含很多个 select 关键字。每一个 select 代表整个 SQL 语句执行计划中的一次小的查询，而每一个 select 关键字的每一次查询都有可能是不同类型的查询。

select_type 字段就是用来描述每一个 select 关键字的查询类型，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。

而通过查看各个小查询部分扮演的角色，我们可以了解到整体 SQL 语句的结构，从而判断当前 SQL 语句的结构是否存在问题。

②总体介绍字段取值含义

取值	含义
SIMPLE	简单的 select 查询，查询中不包含子查询或者 UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为 primary
SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询
DEPENDENT SUBQUERY	在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层
UNCACHEABLE SUBQUREY	表示这个 subquery 的查询要受到外部表查询的影响
DERIVED	在 FROM 列表中包含的子查询被标记为 DERIVED(衍生)。<br>MySQL 会递归执行这些子查询，把结果放在临时表里
UNION	这是 UNION 语句其中的一个 SQL 元素
UNION RESULT	从 UNION 表获取结果的 SELECT，也就是在 UNION 合并查询结果的基础上，不使用全部字段，选取一部分字段。

具体分析如下：

③SIMPLE

查询语句中不包含UNION、不包含子查询的查询都算作是SIMPLE类型，比方说下边这个单表查询的select_type的值就是SIMPLE：

mysql> EXPLAIN SELECT * FROM s1;

当然，连接查询也算是SIMPLE类型，比如：

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

④PRIMARY

对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY，比方说：

mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

从结果中可以看到，最左边的小查询SELECT * FROM s1对应的是执行计划中的第一条记录，它的select_type值就是PRIMARY。

⑤union

对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION，可以对比上一个例子的效果，这就不多举例子了。

⑥union result

MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT，例子上边有。

⑦subquery

如果包含子查询的查询语句不能够转为对应的semi-join的形式（不用管什么是 semi-join，只需要知道这是进一步优化），并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY，比如下边这个查询：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';

可以看到，外层查询的select_type就是PRIMARY，子查询的select_type就是SUBQUERY。需要大家注意的是，由于 select_type 为 SUBQUERY 的子查询会被物化（将子查询结果作为一个临时表来加快查询执行速度），所以只需要执行一遍。

⑧DEPENDENT SUBQUERY

如果整体 SQL 语句执行的顺序是：

• 先执行外层查询（假设返回1万条记录）
• 再执行内层子查询（假设返回1万条记录）
• 然后外层查询结果中的每一条再去匹配内层子查询结果的每一条（外层1万条记录×内层1万条记录=1亿条记录）

这样，内外两层的查询结果就是相乘的关系。相乘就有可能导致总的查询操作次数非常大。所以经过 explain 分析后，如果发现查询类型是 DEPENDENT SUBQUERY 就需要引起各位注意了——这是一个危险的信号，通常是需要修复的一个问题！

当然，就实际工作中来说：别说 DEPENDENT SUBQUERY，就连 SUBQUERY 都不应该出现。

EXPLAIN
SELECT t1.id, (select t4.id from t4 where t4.id = t1.id) id4
FROM t1,
     t2;

5、table 字段

显示当前这一步查询操作所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是别名。不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所以 MySQL 规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。

6、partitions 字段

代表分区表中的命中情况。如果是非分区表，该项为 null。逻辑上是一个整体的数据，可以在物理层保存时，拆分成很多个分片。分片在分区中保存。数据分片的好处是：

• 方便在很多个不同分区之间方便的移动分片，分摊负载，弹性扩容。
• 给主分片创建复制分片可以增强数据安全性。

7、type 字段 [重要]

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。具体取值参见下表（从上到下，性能越来越好）：

取值	含义
ALL	全表扫描，完全没有用到任何索引，效率最低的一种情况
index	对索引表（聚簇索引、非聚簇索引都算）进行整体遍历，虽然用到了索引但效率仍然较低
range	在一定范围内查询索引表（例如：where age between 20 and 30，当然此时要求age字段上有索引）
ref	通过普通的二级索引列与常量进行等值匹配时来查询某个表<br>例如：where age=20，当然此时要求age字段上有索引
eq_ref	在关联查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref<br>from emp left join dept on emp.dept_id=dept.id
const	根据主键或者唯一二级索引列与常数进行等值匹配<br>where emp_id=5
system	表仅有一行记录，这是const类型的特例，查询起来非常迅速
null	MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。<br>select user();

在上述查询方式中，从 eq_ref 开始，条件就很苛刻了，不容易达到。所以实际开发时要求，至少能达到 range 水平，最好能达到 ref。

下面是可以参考的例子：

# type字段的值：all
# 原因：没有用到任何索引，执行全表扫描
explain select * from emp where age>50;

# type字段的值：index
# 原因：用到了索引，但是没有指定任何查询条件，对索引进行了遍历
explain select id from emp;

# 创建索引
create index idx_emp_age on emp(age);

# 查询 age 字段：范围查询
# type 字段的值：range
# 原因：在索引字段上做范围查询
explain select * from emp where age>50;

# 查询 age 字段：和常量进行等值匹配
# type 字段的值：ref
# 原因：在索引字段上做和常量的等值匹配
explain select * from emp where age=50;

# 关联查询：emp 表关联 dept 表
# 被驱动表：dept 表
# 被驱动表参与连接条件的字段：主键字段（或设置了唯一性要求的二级索引）
# type 字段：eq_ref
explain
select emp.id,emp.name,emp.age,dept.id,dept.deptName from emp
    left join dept on emp.deptId=dept.id;

# 根据主键字段查询 emp 表
# type 字段：const
# 原因：主键索引或唯一二级索引字段和常量做等值匹配
explain select * from emp where id=5;

8、possible_keys 字段

在查询中有可能会用到的索引列。如果没有任何索引显示 null。

9、key字段

key 列显示 MySQL 实际决定使用的键（索引），包含在 possible_keys 中。

10、key_len 字段[重要]

key_len 表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要，值越大索引的效果越好——因为值越大说明索引被利用的越充分。

字节数计算方式：

• 索引对应字段类型：
  • 数值类型：最终结果就是数值类型字段宽度（我们关心的是字节数）本身
    • int(11)：我们关心的是 int 类型占 4 个字节，而不关心 11
    • double(10,5)：我们关心的是 double 类型占 8 个字节，而不关心 10,5
  • 字符串类型：查看字符集类型
    • UTF-8：需要给字段长度 × 3
    • GBK：需要给字段长度 × 2
  • 如果是 varchar 这样的变长字符串类型：再 + 2
  • 如果是允许为空的字段：再 + 1

举例：customer_name 字段声明的类型是 varchar(200)，允许为空。

200 × 3 + 2 + 1 = 603

举例：

# 下面分析结果的 key_len 字段的值是 310，我们来看看是怎么算出来的
# 先看 emp_name
# emp_name 是字符串类型，它的字段宽度是 100，字符集是 UTF-8 需要乘 3，是定长字段不需要 +2，允许为空需要 +1，所以：100×3+1 = 301
# emp_salary 是数值类型，本身占 8 个字节，允许为空需要 + 1，所以：8 + 1 = 9
# 总和：301 + 9 = 310
explain select emp_name,emp_salary from t_emp
where emp_name = '李四' or emp_salary = 1000;

11、ref 字段

表示查询条件中，我们的索引列和谁去比较，是常量还是另一张表的字段。

①const

explain select emp_id,emp_name,emp_salary from t_emp where emp_id=5;

 

②某个字段

常规的例子：

explain
select emp_id, emp_name, emp_salary
from t_emp e
         left join t_dept d on d.dept_id = e.dept_id;

故意让部门表的dept_id和员工表的emp_id比较：

explain
select emp_id, emp_name, emp_salary
from t_emp e
         left join t_dept d on d.dept_id = e.dept_id
where emp_id = d.dept_id;

12、rows字段

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。从优化 SQL 语句的角度来说，这个值通常越小越好。

13、filtered 字段

通过存储引擎从硬盘加载数据到服务层时，受限于内存空间，有可能只能加载一部分数据。filtered 字段显示的值是：已加载数据 / 全部数据 的百分比。只是不显示百分号。

14、extra 字段

顾名思义，Extra 列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑比较重要的额外信息介绍给大家。

下面橙色字体需要适当留意一下：

取值	含义
using where	不用读取表中所有信息，仅通过索引就可以获取所需数据。 <br>言外之意是 select 查询的字段都带有索引。 <br>不管 select 查询多少个字段，这些字段都在索引中。
Using temporary	表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort	当语句中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序” <br>这里的文件指的是保存在硬盘上的文件。 <br>之所以会用到硬盘，是因为如果查询的数据量太大，内存空间不够，需要在硬盘上完成排序。 <br>如果确实是很大数据量在硬盘执行排序操作，那么速度会非常慢。
Using join buffer	buffer 指缓冲区，该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。 <br>举例来说：where t_name like "%xxx%"，这个条件中的 t_name 字段没有加索引
Impossible where	where 子句中指定的条件查询不到数据的情况
Select tables optimized away	这个值表示目前的查询使用了索引，然后经过优化器优化之后，最终执行的是一个聚合函数，从而让最终的查询结果只返回一行
No tables used	查询语句中使用 from dual 或不含任何 from 子句

15、使用建议

• EXPLAIN 不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN 不考虑各种 Cache
• EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划