Mysql优化——分析查询语句：EXPLAIN（1）

目录

1、概述

1.1

1.2 基本语法

2、实操

2.1 数据准备

2.2 EXPLAIN各列作用

1. table

2. id

3. select_type

4、partitions 关于分区 此文不做详细讲解

5、type （重要指标）

6、possible_keys和key

7、key_len

---

1、概述

1.1

定位了查询慢的 SQL 之后，我们就可以使用 EXPLAIN 或 DESCRIBE 工具做针对性的分析查询语句。DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。
MySQL中有专门负责优化SELECT语句的优化器模块，主要功能: 通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的 执行计划 (他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)。
这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。MySQL为我们提供了 EXPLAIN 语句来帮助我们查看某个查询语句的具体执行计划，大家看懂EXPLAIN 语句的各个输出项，可以有针对性的提升我们查询语句的性能。

官网介绍

MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format

MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format

1.2 基本语法

EXPLAIN 或 DESC

EXPLAIN SELECT select_options
或者
DESCRIBE SELECT select_options

RIBE语句的语法形式如下：

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个 EXPLAIN ，就像这样：

mysql> EXPLAIN SELECT 1;

EXPLAIN 语句输出的各个列的作用如下：

 

2、实操

2.1 数据准备

1. 建表

CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8

CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;

2. 设置参数 log_bin_trust_function_creators 创建函数，

假如报错，需开启如下命令：允许创建函数设置：

set global log_bin_trust_function_creators=1; # 不加global只是当前窗口有效。

3. 创建函数

DELIMITER //
CREATE FUNCTION rand_string1(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

4. 创建存储过程

创建往s1表中插入数据的存储过程：

DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

创建往s2表中插入数据的存储过程：

DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s2 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;

5. 调用存储过程 加入1万条记录：

CALL insert_s1(10001,10000);

CALL insert_s2(10001,10000);

2.2 EXPLAIN各列作用

1. table

不论我们的查询语句有多复杂，里边儿 包含了多少个表 ，到最后也是需要对每个表进行 单表访问 的，所 以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该 表的表名（有时不是真实的表名字，可能是简称）。

查询的每一行记录都对应一个单表。

2. id

我们写的查询语句一般都以 SELECT 关键字开头，比较简单的查询语句里只有一个 SELECT 关键字，比 如下边这个查询语句：

 稍微复杂一点的连接查询中也只有一个 SELECT 关键字，比如

 在一个大的查询语句中，每个SELECT关键字都对应一个唯一的id 

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

两行记录，表示两个表，查询的过程中，涉及到几个表，就有几行记录(包括临时表)

mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;

 #s1:驱动表  s2:被驱动表

 

#子查询的情况，EXPLAIN结果有两个id

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

 

##查询优化器可能对涉及子查询的查询语句 进行重写，转换为多表查询的操作####

子查询其实是一种嵌套的查询方式，所以 时间复杂度 是 On2 (外表循环*内表循环)

而多表连接是一种查询结果集的数学运算 可以理解为 n+n ，时间复杂度为On（表1循环+表二循环。。）

所以多表连接相对来说，效率更高

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field
= 'a');

 

#union 去重(取并集，再去重，在这个过程中，会建立临时表，Extra—Using temporary)

结果发现多了一条记录

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

 

 EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

小结： 

id如果相同，可以认为是一组，从上往下顺序执行

在所有组中，id值越大，优先级越高，越先执行

关注点：id号每个号码，表示一趟独立的查询, 一个sql的查询趟数越少越好

3. select_type

一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表(这些表用来做连接查询)，每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的.
MysQL为每一个SELECT关键字代表的小查询都定义了一个称之为 select_type 的属性，意思是我们只要知道了某个小查询的 select_type属性，就知道了这个 小查询在整个大查询中扮演了一个什么角色，我们看一下select_type都能取哪些值，请看官方文档:

 

具体分析如下：（结果参考上面id的内容）

 # 查询语句中不包含`UNION`或者子查询的查询都算作是`SIMPLE`类型

EXPLAIN SELECT * FROM s1;

 #连接查询也算是`SIMPLE`类型 

 EXPLAIN SELECT * FROM s1 INNER JOIN s2;

PRIMARY

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

UNION
 #对于包含`UNION`或者`UNION ALL`的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的`select_type`值就是`UNION`

 #`MySQL`选择使用临时表来完成`UNION`查询的去重工作，针对该临时表的查询的`select_type`就是`UNION RESULT

比如：
 

 EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
 
 EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

#子查询：

SUBQUERY
 #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式（多表连接），并且该子查询是不相关子查询。 #该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是 `SUBQUERY`

 

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

DEPENDENT SUBQUERY

 #如果包含子查询的查询语句不能够转为对应的`semi-join`的形式，并且该子查询是相关子查询，
 #则该子查询的第一个`SELECT`关键字代表的那个查询的`select_type`就是`DEPENDENT SUBQUERY`

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

#注意的是，select_type为`DEPENDENT SUBQUERY`的查询可能会被执行多次。

#在包含`UNION`或者`UNION ALL`的大查询中，如果各个小查询都依赖于外层查询的话，那除了
 #最左边的那个小查询之外，其余的小查询的`select_type`的值就是`DEPENDENT UNION`。

此处 查询优化器对语句进行优化，将不相关子查询变成了相关子查询(exist 关键字)

 EXPLAIN SELECT * FROM s1 
 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');

 

DERIVED

 #对于包含`派生表`的查询，该派生表对应的子查询的`select_type`就是`DERIVED`

 EXPLAIN SELECT * 
 FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;

 MATERIALIZED

#当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，
 #该子查询对应的`select_type`属性就是`MATERIALIZED`

 EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2); #子查询被转为了物化表

 

4、partitions 关于分区 此文不做详细讲解

5、type （重要指标）

执行计划的一条记录就代表着MySQL对某个表的 执行查询时的访问方法，又称“访问类型”，其中的 type 列就表明了这个访问方法是啥，是较为重要的一个指标。比如，看到 type 列的值是 ref，表明MySQL 即将使用 ref 访问方法来执行对 s1表的查询。

(越往后，效率越差)

完整的访问方法如下： system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

system

当表中`只有一条记录`并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，
 #那么对该表的访问方法就是`system`。

#myISAM 有变量记录了cout(*),并不会遍历全表

所以 MyISAM中 select cout(*) from t； 他的访问级别就是 system，

 CREATE TABLE t(i INT) ENGINE=MYISAM;
 INSERT INTO t VALUES(1);
 
 EXPLAIN SELECT * FROM t;

如果换成InnoDB 引擎，type访问级别 变成了 ALL（全表遍历）

const
 #当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是`const`

id是主键索引，key2是一个 唯一的二级索引(UNIQUE INDEX idex_Key2(key2) )

 EXPLAIN SELECT * FROM s1 WHERE id = 10005;
 
 EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

## 注意：如果：（Key3 是一个普通索引，按照我们的理解，它应该是一个 ref,但实际的type是 ALL）

EXPLAIN SELECT * FROM s1 WHERE key3 = 10066;

因为，Key3是 varchar 但此时我们去比较的内容是一个数字常量，Mysql底层会进行一次隐式转换，等于给索引Key3上使用了函数，一旦索引使用函数，索引失效，通过后面的Key关键字也能看出来
 

解决方案： 等值比较应该传入和Key3 相同的数据类型： 

 EXPLAIN SELECT * FROM s1 WHERE key3 = '10066';

eq_ref

  #在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的
 #（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则
 #对该被驱动表的访问方法就是`eq_ref`

 #对该被驱动表的访问方法就是`eq_ref`
 EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

 这里补充一下驱动表与被驱动表的判断

没有Where条件的时候：

        左外连接查询时前表是驱动表，后面是被驱动表

        右连接相反

        内连接时，哪张表的数据少哪张就是驱动表

有where条件时

        带有where条件的是驱动表，否则是被驱动表

记住一个要点：SQL优化的特点就是 小表驱动大表

驱动表与被驱动表的概念

驱动表是表连接中的基础表，也就是通过驱动表的数据结果集，作为循环基础数据，

然后一条一条的通过这个结果集的数据作为过滤条件，到被驱动表中查找数据，然后合并

 ref
 #当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是`ref`

EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

 

ref_or_null

#当对普通二级索引进行等值匹配查询，该索引列的值也可以是`NULL`值时，那么对该表的访问方法就可能是`ref_or_null`

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;

 

index_merge

#单表访问方法时在某些场景下可以使用`Intersection`、`Union`、`Sort-Union`这三种索引合并的方式来执行查询

注意，这里必须是OR ，用AND 会导致Key3失效，type = ref,优化器会自动选择成本最低的索引

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

 

unique_subquery
 #`unique_subquery`是针对在一些包含`IN`子查询的查询语句中，如果查询优化器决定将`IN`子查询转换为`EXISTS`子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的`type`列的值就是`unique_subquery`

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

 

 range
 #如果使用索引获取某些`范围区间`的记录，那么就可能使用到`range`访问方法

 EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

 同理

 EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b'

index

 #当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是`index`

索引覆盖：（要查询的字段，和条件判断字段，都在联合索引内,不需要回表）

idx_key_part(key_part1,key_part2,key_part3)

 EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

 如果不是索引覆盖，此处索引不能被使用，考虑到最左原则

比如：

EXPLAIN SELECT key1,key_part2 FROM s1 WHERE key_part3 = 'a';

 

ALL
 #最熟悉的全表扫描，全表遍历
 

 EXPLAIN SELECT * FROM s1;

小结： 结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来（见上图中的蓝 色）。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts级别。（阿里巴巴 开发手册要求）

6、possible_keys和key

在EXPLAIN语句输出的执行计划中，possible_keys 列表示在某个查询语句中，对某个表执行 单表查询时可能用到的索引有哪些。一般查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。key 列表示实际用到的索引 有哪些，如果为NULL，则没有使用索引。比方说下边这个查询:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';

 查询优化器最终选择了Key3 这个索引，因为前面的查询条件是 Key1>'z' 是一个范围，而Key3='a'

显然后者查询效率更高，成本更低。

如果改成这样

 EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' OR key3 = 'a';

 

 type 变成了index_merge 两个索引都被用上

7、key_len

  key_len：实际使用到的索引长度(即：字节数)
# 帮你检查`是否充分的利用上了索引`，`值越大越好`,主要针对于联合索引，有一定的参考意义。

注意 这个越大越好，并不和其他语句去比较，而是联合索引中，能否用到更多的索引

分析key_len 我们要结合建表语句里，各个列的真实类型 大小

 

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

id 为主键索引 Int型 key_len =4

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

用到的索引是 UNIQUE idx_key2 ，唯一二级索引，并未说非空，null占一个字节，所以 共

Int+null =5字节

 EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';

用到的索引是 idx_key1 普通索引 VARCHAR(100)=100  在utf8 中，一个字符占3个字节

有可能为null，还是变长字符串（ 记录占2字节） ：所以 共 300+1+2=303

### 使用联合索引的情况，此时比较大小，有意义

 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';

同上 303

 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';

此时 是606，因为 idx_keypart1 和idx_keypart2 都被使用到了，当前情况就别上一种要好

 EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

909 道理同上

EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';

此时没有用到索引，联合索引，遵循最左前缀原则 

#练习：
#varchar(10)变长字段且允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

#varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段)

#char(10)固定字段且允许NULL    = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)

#char(10)固定字段且不允许NULL  = 10 * ( character set：utf8=3,gbk=2,latin1=1)