第六章 Explain 分析
1、概念
①MySQL 内优化器
MySQL 体系结构中，包含 SQL 解析器、优化器等组件。SQL 解析器解析 SQL 之后，生成解析树。经过验证，解析树正确后，由优化器进一步优化解析树。
②Explain 分析
使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的 SQL 语句的。 分析你的查询语句或是表结构的性能瓶颈。
| 列名 | 描述 |
|---|---|
id | 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id |
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 字段含义
- 一个 select 对应一个 id 唯一的值
- id 值每出现一次，就代表 SQL 语句要执行一趟
- 总体的 SQL 语句执行的『趟数』越少越好
- 多个 id 值
- id 值相同：按照从上到下的顺序依次执行
- 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 代表一次小的查询，而每一个 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(衍生)。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 RESULTMySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT，例子上边有。 -
SUBQUERY如果包含子查询的查询语句不能够转为对应的
semi-join的形式（不用管什么是 semi-join，只需要知道这是进一步优化），并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY，比如下边这个查询：mysql> 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 语句执行的顺序是：
- 先执行外层查询
- 再执行内层子查询
- 然后外层查询结果中的每一条再去匹配内层子查询结果的每一条
这样，内外两层的查询结果就是相乘的关系。相乘就有可能导致总的查询操作次数非常大。所以经过 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 | 在一定范围内查询索引表 |
| ref | 通过普通的二级索引列与常量进行等值匹配时来查询某个表 |
| eq_ref | 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref |
| const | 根据主键或者唯一二级索引列与常数进行等值匹配 |
| system | 表仅有一行记录，这是const类型的特例，查询起来非常迅速 |
| null | MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。 |
在上述查询方式中，从 eq_ref 开始，条件就很苛刻了，不容易达到。所以实际开发时要求，至少能达到 range 水平，最好能达到 ref。
下面是可以参考的例子：
# 创建数据库表
create table t_emp
(
emp_id int auto_increment primary key,
emp_name char(100),
emp_salary double(10, 5),
dept_id int
);
create table t_dept
(
dept_id int auto_increment primary key,
dept_name char(100)
);
# emp_id 主键索引
# emp_name 唯一索引
create unique index idx_emp_name_unique on t_emp (emp_name);
# dept_name 普通索引
create index idx_dept_name on t_dept (dept_name);
# 情况一：type 的值是 all
# 原因：由于没有用到任何索引，所以执行全表扫描
explain
select emp_salary
from t_emp;
# 情况二：type 的值是 index
# 原因：查询的是建立了索引的字段，而且没有指定 where 条件。
# 在执行查询时扫描索引的整个 B+Tree
explain
select emp_id
from t_emp;
explain
select emp_name
from t_emp;
explain
select dept_name
from t_dept;
# 情况三：type 的值是 range
# 原因：在一定范围内访问索引 B+Tree
# emp_salary 普通索引
create index idx_emp_salary on t_emp (emp_salary);
explain
select emp_id, emp_name
from t_emp
where emp_salary between 1000 and 5000;
# 情况四：type 的值是 ref
# 原因：通过普通的二级索引列与常量进行等值匹配时来查询
explain
select dept_name
from t_dept
where dept_name = '研发部';
# 情况五：对 t_dept 表的查询中，type 的值是 eq_ref
# 原因：在进行关联查询时，被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问
explain
select emp_id, emp_name, emp_salary
from t_emp e
left join t_dept td on e.dept_id = td.dept_id;
# type 的值是 const
# 原因：使用常量值查询一个唯一索引，返回唯一一条记录
explain
select emp_id, emp_name, emp_salary
from t_emp
where emp_name = 'aaa';
8、possible_keys 字段
在查询中有可能会用到的索引列。如果没有任何索引显示 null。
9、key 字段
key 列显示 MySQL 实际决定使用的键（索引），包含在 possible_keys 中。
10、key_len 字段[重要]
key_len 表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要，值越大索引的效果越好——因为值越大说明索引被利用的越充分。
字节数计算方式：
- 索引对应字段类型：
- 数值类型：最终结果就是数值类型字段宽度本身
- 字符串类型：查看字符集类型
- 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
where emp_id = d.dept_id;
12、rows 字段
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。通常来说越小越好。
13、filtered 字段
通过存储引擎从硬盘加载数据到服务层时，受限于内存空间，有可能只能加载一部分数据。filtered 字段显示的值是：已加载数据 / 全部数据 的百分比。只是不显示百分号。
14、extra 字段
顾名思义，Extra 列是用来说明一些额外信息的，包含不适合在其他列中显示但十分重要的额外信息。我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个，我们就不一个一个介绍了，所以我们只挑比较重要的额外信息介绍给大家。
下面蓝色字体需要适当留意一下：
| 取值 | 含义 |
|---|---|
| using where | 不用读取表中所有信息，仅通过索引就可以获取所需数据。 言外之意是 select 查询的字段都带有索引。 不管 select 查询多少个字段，这些字段都在索引中。 |
| Using temporary | 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询 |
| Using filesort | 当语句中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序” 这里的文件指的是保存在硬盘上的文件。 之所以会用到硬盘，是因为如果查询的数据量太大，内存空间不够，需要在硬盘上完成排序。 如果确实是很大数据量在硬盘执行排序操作，那么速度会非常慢。 |
| Using join buffer | buffer 指缓冲区，该值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。 举例来说：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 后查看执行计划
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
