第 48 期：EXPLAIN TYPE 列的 JOIN 常见场景详解（下）
第 48 期：EXPLAIN TYPE 列的 JOIN 常见场景详解（下）
作者：杨涛涛，爱可生技术专家。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
专栏序回顾
对于关系型数据库来说，表的设计 以及 SQL 的写法 尤为重要，占据性能的 90% 都不为过，所以这次专门针对这两大类知识领域，为您抽丝剥茧详细分析。
本专栏语言通俗易懂，选取大量示例为您详细说明个中奥妙~
面向的对象：
- DBA
- 数据库开发者
第 48 期正文
本篇是 上一篇《EXPLAIN TYPE 列的 JOIN 常见场景详解（上）》的续篇，继续介绍执行计划 type 栏的 JOIN 类型。
1type: ALL
对于大部分开发规范来讲，都会把 ALL 放在禁止的第一位。
一般 SQL 语句执行计划的 type 栏为 ALL 时，我们都会觉得它效率都非常差，执行时间很慢。比如以下 SQL，命名为 SQL 1：
SQL 1: select * from t1 limit 100
SQL 1 没有任何字段进行过滤， 所以必须走全表扫描，看下执行计划：type 栏内容为 ALL，rows 为表总行数，其他栏基本全是 NULL。
debian-ytt1:ytt>desc select * from t1 limit 100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 106313
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
看到这儿，其实已经注意到了优化技巧。在上一篇我介绍的内容里有 type='index' 的选项，而且还介绍了此类选项的简单优化方法，对于 type='all' 有着类似的优化方法。来小改下 SQL 1 ，加上排序子句：
SQL 2: select * from t1 order by f0,f1 limit 100
执行计划变为：type="all" --> type='index'， 扫描记录数变为 100 行，而且排序子句顺序和主键顺序一致，避免额外的排序开销。
debian-ytt1:ytt>desc select * from t1 order by f0,f1 limit 100\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index
possible_keys: NULL
key: PRIMARY
key_len: 8
ref: NULL
rows: 100
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
接下来看下 SQL 1 和 SQL 2 的执行成本对比：
debian-ytt1:ytt>desc analyze select * from t1 limit 100 \G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 100 row(s) (cost=10871.80 rows=100) (actual time=0.061..0.285 rows=100 loops=1)
-> Table scan on t1 (cost=10871.80 rows=106313) (actual time=0.061..0.279 rows=100 loops=1)
1 row in set (0.00 sec)
debian-ytt1:ytt>desc analyze select * from t1 order by f0,f1 limit 100 \G
*************************** 1. row ***************************
EXPLAIN: -> Limit: 100 row(s) (cost=0.24 rows=100) (actual time=0.072..0.100 rows=100 loops=1)
-> Index scan on t1 using PRIMARY (cost=0.24 rows=100) (actual time=0.071..0.088 rows=100 loops=1)
1 row in set (0.00 sec)
SQL 2 的成本明显小很多倍。
其实可以总结为 type='index' 为特殊的 type=all`，在有的场景下两者之间可以互相替换。
当然，对于小表来讲，即使 type='all' 也不需要特别关注。比如说配置表、路由表等记录数非常小的表。
2type: index_merge
在讲 Index_merge 之前，先来看下这条 SQL ，命名为 SQL 3：
SQL 3:
select * from t1 where r1 =1
union all
select * from t1 where r2 = 1
union all
select * from t1 where r3 = 1;
SQL 3 是对表 t1 扫描三次后做一个并集，每次给的过滤条件不一样，这样的 SQL 会涉及到一个写临时表的过程，临时表的大小由表记录数以及过滤条件决定，显然这种不够优化。index_merge 就是 MySQL 用来解决此类场景的一个可优化项，在索引层先把数据合并，之后再回表过滤。
index_merge 表示 MySQL 在执行一条 SQL 时，如果 SQL 语句涉及的表有多个可用索引，MySQL 会考虑走多个索引一起来输出结果。把 SQL 3 改为 SQL 4：
SQL 4：select * from t1 where r1 = 1 or r2 = 1 or r3 =1
SQL 4 过滤字段有三个，相互之间是或的关系，而且三个字段都有各自的索引， MySQL 此时会考虑将这三个索引一起来用。来看下执行计划：
debian-ytt1:ytt>desc select * from t1 where r1 = 1 or r2 = 1 or r3 =1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index_merge
possible_keys: idx_r1,idx_r2,idx_r3
key: idx_r1,idx_r2,idx_r3
key_len: 5,5,5
ref: NULL
rows: 34104
filtered: 100.00
Extra: Using union(idx_r1,idx_r2,idx_r3); Using where
1 row in set, 1 warning (0.01 sec)
还有一种排序后的并集优化，类似 UNION ALL 转为 UNION 操作。比如 SQL 5:
SQL 5: select * from t1 where r2 < 2 or r3 < 2
SQL 5 是对多个字段进行过滤，并且每个字段过滤类型都是一个固定范围，这样的场景可以用到 SORT UNION ，执行计划如下：
debian-ytt1:ytt>desc select * from t1 where r2 < 2 or r3 < 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index_merge
possible_keys: idx_r2,idx_r3
key: idx_r2,idx_r3
key_len: 5,5
ref: NULL
rows: 1986
filtered: 100.00
Extra: Using sort_union(idx_r2,idx_r3); Using where
1 row in set, 1 warning (0.01 sec)
index_merge 除了可以做并集外，对于多个索引过滤的交集，也可以提前做，比如 SQL 6：
SQL 6：select * from t1 where r1 = 1 and r2 = 1 and r3 =1
三个字段 r1,r2,r3 都有各自索引，所以在这种存在交集的场景下，也可以直接在索引层 index_merge，减少回表的记录数。
看下执行计划：type 栏为 index_merge， 并且 extra 栏里用索引 idx_r2,idx_r3 提前做交集，最终扫描行数仅为 9 行。如果不用 index_merge，扫描行数将会很大。
debian-ytt1:ytt>desc select * from t1 where r1 = 1 and r2 = 1 and r3 =1 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index_merge
possible_keys: idx_r1,idx_r2,idx_r3
key: idx_r3,idx_r2
key_len: 5,5
ref: NULL
rows: 9
filtered: 31.60
Extra: Using intersect(idx_r3,idx_r2); Using where
1 row in set, 1 warning (0.00 sec)
读到这里，未免会有一个新的问题：是不是对于 MySQL 来讲，各种列的任意组合，只要每个列有自己单独索引，都能用到 INDEX_MERGE 优化算法？
答案是否定的！ INDEX_MERGE 的使用条件非常简单，对索引列的过滤要么是并集组合，要么是差集组合，不能有其他复杂的组合。比如下面 SQL 7，就无法用到这一特性。
SQL 7: select * from t1 where (r1 =1 or r2 = 1) and r3 = 1
至此，常见的执行计划 JOIN 栏输出内容也就这些，欢迎大家继续关注。
腾讯云开发者
