mysql优化必会-优化利器EXPLAIN

大家好，我是热心的大肚皮，皮哥。

EXPLAIN

上篇，我们说了下成本计算规则，基于成本会生成一个执行计划。如下。

列名	描述
id	查询中，每个select都对应一个唯一id
select_type	select关键字对应的查询类型
table	表名
partitions	匹配的分区信息
type	单表访问方法
possible_keys	可能用到的主键
key	实际用到的主键
key_len	实际使用的索引长度
ref	使用索引列等值查询时，与索引列进行匹配的对象信息
rows	预估读取的行数
filtered	过滤后剩余数据的百分比
Extra	额外信息

我们由浅入深，挨个讲解一下。

是单表的表名，不管sql多么复杂，执行计划的每一步也是对单表访问。

• 每出现一个select ，则会为它分配一个唯一id。
• 如果连接查询时，id值会相同，在前面的表是驱动表，后面的是被驱动表。
• 如果使用union all，则会出现id为null的情况，第三步代表在内部创建了一个名字是<union 1,2>的临时表，这个null就是代表为了合并结果集去重时创建的。

复杂的查询中select_type 标志着每一个步骤在大查询中扮演什么角色。

• simple：不包含union或者子查询的查询都算simple类型
• primary：对于包含union、union all 或者子查询的大查询来说，它由几个小查询组成，最左边的查询select_type就是primary。
• union：对于包含union、union all 或者子查询的大查询来说，它由几个小查询组成，除了最左边的查询select_type是primary外，其余的是union。
• union result：mysql使用临时表完成union的去重工作，这个操作的类型就是union result。
• subquery：如果包含子查询的sql不能转换成半连接形式，并且是不相关的子查询，查询采用物化方案执行时，那么子查询的第一个select查询的select_type就是subquery。小知识：物化通过将子查询结果作为一个临时表来加快查询执行速度，正常来说是在内存中的，如果数据量过大则会落到磁盘中。
• dependent subquery：如果包含子查询的sql不能转换成半连接形式，并且子查询可以转换为相关的子查询，那么子查询的第一个select查询的select_type就是dependent subquery。需要注意，dependent subquery的子查询会执行多次。
• dependent union：包含union或者union all的大查询中，如果大查询依赖各个小查询的结果，那么除了最左面的小查询外，其余的是dependent union。
• derived：使用物化表的方式查询。
• materialized：不常用，不说了。
• uncacheable subquery：不常用，不说了。
• uncacheable union：不常用，不说了。

分区我们用的少，不多说了。

这个代表着每一条记录执行查询时的访问方法。性能依次递减。

• system：当表中仅一条记录且该表使用的存储引擎（MyISAM，MEMORY）的统计数据是精确的，那么就是system。
• const：根据主键或者唯一二级索引列与常数进行等值匹配。
• eq_ref：连接查询时，如果被驱动表通过主键或者不存储null的唯一二级索引进行等值匹配；如果是联合索引，则必须全部索引列都进行等值匹配。
• ref：根据普通的二级索引列与常量进行等值匹配。
• fulltext：全文索引。
• ref_or_null：对普通二级索引列进行等值匹配且索引列值可以为null。
• index_merge：某些场景会使用索引合并的方式查询，后续会详细说。
• unique_subquery：类似于eq_ref，unique_subquery针对包含in子查询的sql，查询优化器将in子查询转换为exists查询，且转换后使用主键或者非null的唯一二级索引进行等值匹配。
• index_subquery：与unique_subquery类型，区别是子查询的是普通索引。
• range：使用索引获取单点扫描区间的记录或者范围扫描。
• index：可以使用索引覆盖，但需要扫描全部的索引记录。索引覆盖：举个例子，我们在用户表根据name,age建个联合索引，select name, age from user where name ='1' 使用这句sql查询时，则是索引覆盖。
• all：全表扫描。

possible_keys和key

可能用到的索引，和实际用到的索引。

实际用到的索引的长度。

展示的与索引列等值匹配的是什么，例如一个常数或者一个函数。

如果是全表扫描，就代表是表的估计行数；如果是索引查询，则代表预计扫描的索引行数。

根据当前的查询的条件过滤后，符合要求的记录所占的百分比。

说明本次执行的额外信息，简单说一个。其余的可以看看官网。

• Using index：使用覆盖索引时，会提示。

索引合并

我们简单Mysql 一般情况下只会为单个索引生成扫描区间，特殊情况下可以为多个索引生成扫描区间，这是使用多个索引完成一次查询的方式也就是index merge（索引合并）。主要有3种。

1. Intersection索引合并

select * from single_table where key1 ='a' and key3='b';           

上面这个sql正常情况下，会根据idx_key1 与idx_key3两个索引的执行成本挑选最优方案，除了这个还会有新的方案，具体如下。

1. 在idx_key1种扫描key1在['a','a']区间的二级索引，同时在idx_key3中扫描key3值在['b','b']区间的二级索引。
2. 从两者的结果中找出id相同的记录。
3. 根据id进行回表操作。注意：在查询过程中，是找到一条就进行比较一次，如果两者的id相同，则回表查询全部信息。

2.Union索引合并

select * from single_table where key1='a' or key3='b'           

上面这句sql，我们能使用idx_key1或者idx_key3查询吗？不行！以idx_key1举例，对应的扫描区间是全部，需要每一条二级索引都要回表查询。针对这种情况，有一个新的方案，如下。

1. 根据idx_key1扫描值在['a','a']之间的二级索引记录，同时根据idx_key3扫描值在['b','b']的二级索引记录。
2. 将两个结果集进行Union去重，获取出重复的id。
3. 在根据id回表查询数据。注意：Union索引合并的话，用到的索引都是二级索引的话，则要求每个索引获取到的二级索引记录都是按照主键值排序的。

3. Sort-Union索引合并

select * from single_table where key1<'a' or key3>'z'           

Union索引合并使用的条件太苛刻了，那么我们可以这样操作，如下。

1. 根据key1<'a'从idx_key1中获取二级索引记录，并根据主键排序。
2. 根据key3>'z'从idx_key3中获取二级索引记录，并根据主键排序。
3. 上面已经排好序了，剩下的操作就与union一致了。

更多干货可搜索gz号【程序猿日常笔记】