《MySQL高级篇》七、性能分析工具的使用（慢查询日志 | EXPLAIN | SHOW PROFILING | 视图分析 ）（三）

6.4.3 select_type

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

再看下连接查询，可以看到 连接查询也算是SIMPLE类型

②：Union 联合查询。其左边的查询是 Primary，右边的查询类型是 Union，去重的临时表查询类型是： Union Result

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

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

UNION RESULT

对应子查询的大查询来说，子查询是外边的那个是PRIMARY

③：不会被优化成多表连接的子查询

如果包含子查询的查询语句不能够转为多表连接的形式(也就是不会被优化器进行自动的优化)，并且该子查询是不相关的子查询

该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY。也就是外层查询是 Primary，内层查询是 SUBQUERY

如果子查询不能被转换为多表连接的形式，并且该子查询是相关子查询。

比如下面的查询在内部子查询使用了外部的表。则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。 外层查询是Primary，内层查询是DEPENDENT SUBQUERY

需要注意的是 DEPENDENT SUBQUERY 的查询语句可能会被执行多次，因为内层查询依赖于外层的查询，因此可能会是外层传一个值，内层就执行一次的模式。

子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为相关子查询。

子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条 件进行执行，那么这样的子查询叫做不相关子查询。

④：包含UNION或者UNION ALL的子查询

在包含 Union或者 Union All 的子查询 sql 中，如果各个小查询都依赖于外查询，那么除了最左边的小查询外，各个小查询的类型都是 DEPENDENT UNION

外查询是 Primary，最左边的子查询是 DEPENDENT SUBQUERY，后面的子查询是 DEPENDENT UNION，临时去重表的类型是 Union Result。这里大家可能要困惑，第一个子查询中也没有看到依赖 s1 啊。这其实也是优化器会在执行时进行优化，将 IN 改成 Exist，并且把外部的表移到内部去。这里我们了解就行，以后会有文章给大家介绍优化器的。

⑤：关于派生表的子查询

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

⑥：子查询的物化后与外层连接查询

当优化器在执行子查询时选择把子查询优化成为一张物化表，与外层查询进行连接查询时。

从下往上看，子查询的查询类型是 MATERIALIZED；物化过程是基于 id 为 2 的查询结果表进行的，其 table 是 subquery 2，查询类型是 SIMPLE，而外层也相当于是与固定的直接值进行查询，其类型也是 SIMPLE

上面的介绍都是一些基本的情况，还没有真正的介绍与索引相关的情况哦。觉得是不是晕晕的了，我们用一个表格进行下总结吧

6.4.4 partitions (可略)

代表分区表中的命中情况，非分区表，该项为 NULL。一般情况下我们的查询语句的执行计划的partitions 列的值都是NULL

官方文档：https://dev.mysql.com/doc/refman/8.0/en/alter-table-partition-operations.html

如果想详细了解，可以如下方式测试。创建分区表：

-- 创建分区表，
-- 按照id分区，id<100 p0分区，其他p1分区
CREATE TABLE user_partitions (id INT auto_increment,
    NAME VARCHAR(12),PRIMARY KEY(id))
    PARTITION BY RANGE(id)(
    PARTITION p0 VALUES less than(100),
    PARTITION p1 VALUES less than MAXVALUE
 );

查询 id 大于200（200>100，p1分区）的记录，查看执行计划，partitions 是 p1，符合我们的分区规则

6.4.5 type ☆

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

我们详细解释一下：

1️⃣system

当表中只有一条记录，并且该表中存储引擎统计数据是精确的，比如 MYISAM，Memory，那么其访问方法就是System。这种方式几乎是性能最高的，当然我们几乎用不上。

CREATE TABLE t(i int) Engine=MyISAM;#创建表
INSERT INTO t VALUES(1);# 插入第一条记录
EXPLAIN SELECT * FROM t;# 查看性能

但凡我们再插入一条数据，其访问方式就变成了性能最差的全表扫描 ALL。

如果存储引擎是InnoDB，即使只有一条数据，其访问方式也是ALL，这是因为 InnnoDB 访问数据不是精确的

2️⃣Const

当我们根据主键或者唯一的二级索引，与常数进行等值匹配时，对单表的访问方法就是 const。这个访问方式的效率低于 system，但也是很高效的。

比如对主键与常数匹配，进行等值查询

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

比如对Unique标识的唯一二级索引key2与常数匹配，进行等值查询。

EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

当我们把where后面的关键字改成key3 普通索引时，由于key3的字段值是可重复的。type类型就变成了 All，全表查询

注意：此时可能有细心的小伙伴可能注意到我们的key3不是也有索引idx_key3，性能再查也不至于全表查询吧~ 但是忘记了一个细节，key3本身是varchar类型的， key3 = 10066会进行一个隐式类型转换，从而会导致 索引失效，我们也可以看到 key列对应的为空。

当修改成key3 = 10066后，结果如下图：

3️⃣eq_ref

再进行连接查询时，如果被驱动表是通过主键或者唯一二级索引等值匹配的方式进行查询的，那么被驱动表的访问方式是 eq_ref。这也是一种性能很不错的方式。

EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;

上面连接查询语句，对于驱动表来说，就是对s1全表进行扫描，找到符合条件的数据，因此其type是All,对被驱动表来说，相于直接访问驱动表查询到的数据进行等值查询，因此其访问方式是eq_ref

4️⃣ref

当使用普通的二级索引与常量进行等值匹配时，type 是 ref。

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

下面考考你。以下 sql 的引用类型是什么呢？

EXPLAIN SELECT * FROM s1 WHERE key3 = 10066;

看看答案。你是不是猜错了。是 All。这是因为 key3 的字段 varchar 类型，但是我们这里常量值是整形，因此需要使用函数进行隐式的类型转换，一旦使用函数，索引就失效了，因此访问类型变成了全表扫描 All

当我们常量使用对应的类型，就是期望的ref访问类型了

5️⃣ref_or_null

当使用普通的二级索引进行等值匹配时，当索引值可以是 Null 时，type 是 ref_or_null。

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

6️⃣index_merge

当进行单表访问时，如果多个查询字段分别建立了单列索引，使用 OR 连接，其访问类型是 index_merge。同时还可以看到 key 这一字段，是使用了多个索引

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

猜猜下面 sql 的引用类型

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

猜对了吗？答案是 ref，这是因为用 AND 连接两个查询时，实际上只使用了 key1 的索引。

7️⃣unique_subquery

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

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

8️⃣range

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

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

9️⃣index

当我们可以使用索引覆盖，但是需要扫描的全部的索引记录时，该表的访问方式就是 index。索引覆盖后面文章介绍优化器时会详细介绍，为了便于大家理解，先简单介绍如下。比如下面 sql 语句中，key_part2 ，key_part2 都属于联合索引 idx_key_part(key_part1, key_part2, key_part3) 的一部分，在查找数据时可以用上这个联合索引，而不用进行回表操作，这种情况即索引覆盖

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

1️⃣0️⃣ALL

最熟悉的全表扫描 ALL

EXPLAIN SELECT * FROM s1;

❤温馨提示：这里很多小伙伴会觉得记不住，其实您可以收藏这篇博客，执行 EXPLAIN 时对应结果，反向查找博文对应内容，毕竟咱们只需要能够读懂性能分析的结果

小结

结果值从最好到最坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 其中比较重要的几个提取出来（见绿色部分）

SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 consts 级别（阿里巴巴开发手册要求）

6.4.6 possible_keys 和 key

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

对应优化器来说，可以选择的 possible_keys 越少越好，因为选项越多，进行过滤花的时间也就对应更多。另外，优化器会对各个索引进行查询的效率进行评估，以此来选择实际使用的 key。而且由于优化器会对 sql 进行优化，完全可能会出现 possible_keys 是 null，但是 key 不为 null 的情况

6.4.7 key_len ☆

实际使用的索引的长度，单位是字节。可以帮助你检查是否充分利用了索引，主要针对联合索引具有一定的参考，对同一索引来说，key_len 值越大越好（与自己比较，后面将解释）。

① 下面SQL执行结果是 4，这个结果怎么算出来的呢？

这是因为使用的是主键 id 作为索引，其类型是 int，占 4 个字节

② 再来猜猜下面的 key_len 是多少~

EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;

什么？你猜的是 4，而答案是 5~

这是因为虽然 key2 也是 int 类型，但是它被 unique 修饰，并没有标识非空（而主键都是非空的），因此加上空值标记，一共是5字节

③ 字符类型的索引长度为多少呢

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

答案是 303，因为类型是 varchar(100)，100 个字符，utf-8 每个字符占 3 个字节，共 300 个字节，加上变长列表 2 个字节与一个空值标识占一个字节，共 303 字节。

④ 看看联合索引的情况

看下面的联合索引，key_len还是303，不需要解释了吧

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

再看看下面这个联合索引，其结果是 606

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

这个查询的 key-len 比上面的查询大，性能就比上面的好，怎么理解呢？其实只要你看过我之前介绍B+树的文章就很容易理解了。因为在目录页我除了考虑 key_part1 ，还会考虑 key_part2，定位到的数据就更加精准，范围更小，需要加载 I/O 的数据页数量就会更少，这样是不是性能就比较好啊~

猜猜下面的 sql 执行后 key_len 是多少

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

是空哦，因为都不会使用到索引，这就是我们一直在提的最左前缀原则，后面会详细介绍的。

📚练习：key_len的长度计算公式：

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)