EXPLAIN TYPE 列的 JOIN 常见场景详解（上）
专栏连载至此，相信读者们已经对一条 SQL 的优化步骤、执行计划等有了一个大概的了解。那接下来我们对 MySQL 的执行计划输出进行详细解释，以便大家对其了解的更加深入。
>作者：杨涛涛，爱可生技术专家。 > >爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
我们这个标题为什么叫做EXPLAIN TYPE 列的JOIN 常见场景详解呢？从MySQL 优化器的角度来看，所有SQL都是JOIN查询（单表检索可以看成过滤字段和主键做JOIN的特殊类型）。由于内容太多，我分成了上下两部分，今天我们来从第一部分开始。
还是表t1，不过我对表结构做了少许变更，更改原来的自增主键为联合主键(f0,f1)，表记录数不变，还是10W行。
CREATE TABLE `t1` (
`f0` int NOT NULL,
`f1` int NOT NULL,
`r1` int DEFAULT NULL,
`r2` int DEFAULT NULL,
`r3` int DEFAULT NULL,
`log_date` date DEFAULT NULL,
PRIMARY KEY (`f0`,`f1`)
) ENGINE=InnoDB
接下来，我写了几条简单的SQL，来分别讲讲type列的意义.
第一，type 栏为"const"
这表明排除索引性能的话，这条SQL 一定是最优的。比如 SQL 1： 过滤字段为联合主键，并且是两个固定的常量比对，这种一定是最优化的：
SQL 1： select * from t1 where f0=110 and f1 = 778
执行计划如下：type 栏里是“const"， ref 栏里是const,const。表明扫描表t1，给定两个常量来过滤，同时走的索引是主键，可以联合rows栏一起看，如果type栏相同，那么rows栏数值小的肯定较为优化。
debian-ytt1:ytt>desc select * from t1 where f0=110 and f1 = 778\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
第二，type 栏为"eq_ref"
这其实和const类似，也是优化比率靠前的，不同的是eq_ref用于两张真实的表JOIN，并且两表的JOIN KEY 必须为主键（或者唯一索引）的全部，同时对于被驱动表而言，对它进行检索的过滤条件是驱动表的所有主键，每次只有一行。（关于JOIN 的优化我会另外开篇细讲，这里就不多说了）
比如SQL 2：select * from t1 join t2 using(f0,f1)
SQL 2 是两表做内联，并且联接的键为两表的主键，这样的SQL 语句（仅从SQL 语句角度，不掺杂业务逻辑）是两表联接类型里不带过滤条件的场景下最优的。
那依然看下执行计划： 这里省去表t2的执行计划，只看表t1。 对于表t1来讲，对它的扫描基于主键，并且在扫描主键时，每次给的常量值为表t2的联合主键，而且是非常精确的一行。
debian-ytt1:ytt>desc select * from t1 join t2 using(f0,f1)\G
...
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: ytt.t2.f0,ytt.t2.f1
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
第三，type 栏为"ref"
ref 和eq_ref 类似，不同的是两表的JOIN KEY 非主键、非唯一索引。 这种场景从SQL角度来讲，应该避免掉；如果实在无法避免，可以想办法减少两表JOIN的记录数。
那对SQL2 做些调整，变为SQL 3： JOIN 条件变为字段r1，并且同时给两表字段r1加索引。
SQL 3： select * from t1 a join t2 b using(r1)
再看下查询计划：还是省去表t1，只看表t2的执行计划。 这里对表t2的检索走索引idx_r1，同时每次扫描引用表t1字段r1，可以结合rows栏来看，这条SQL其实并不优化。
debian-ytt1:ytt>desc select * from t1 a join t2 b using(r1)\G
...
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: ref
possible_keys: idx_r1
key: idx_r1
key_len: 5
ref: ytt.a.r1
rows: 19838
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.01 sec)
第四，type 栏为"range"
range 代表范围扫描，和前面三个不同，前面三个都是基于常量。
来看下SQL 4： select * from t1 where f0<120
SQL 4 对表t1的检索条件是一个范围（-INF,120），执行计划如下： 对表t1的扫描走主键，类型为range。
debian-ytt1:ytt>desc select * from t1 where f0<120\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 93
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
SQL 4 是对表t1的范围扫描，有些时候基于一些表记录特殊性（不具备通用性），可以把范围扫描优化为常量扫描。 这里表t1就具有特殊性，对于字段过滤条件为f0<120的结果和过滤条件为f0=110的结果是一样的，所以改SQL 4 为 SQL 5：
SQL 5： select * from t1 where f0=110
看下SQL 5的执行计划： 成功把对表t1的范围扫描变为常量扫描，type 栏由range 变为ref.
debian-ytt1:ytt>desc select * from t1 where f0=110\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 93
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
其实这点从传统的执行计划结果里看不出什么效果，还是得实际执行后，看两条SQL 的执行成本。 我们使用explain analyze 来对比下SQL 4和 SQL 5的执行成本： SQL 4 成本为18.93， SQL 5成本为 9.62， 性能提升很明显。
debian-ytt1:ytt>desc analyze select * from t1 where f0< 120\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.f0 < 120) (cost=18.93 rows=93) (actual time=0.040..0.061 rows=93 loops=1)
-> Index range scan on t1 using PRIMARY (cost=18.93 rows=93) (actual time=0.038..0.047 rows=93 loops=1)
1 row in set (0.00 sec)
debian-ytt1:ytt>desc analyze select * from t1 where f0=110\G
*************************** 1. row ***************************
EXPLAIN: -> Index lookup on t1 using PRIMARY (f0=110) (cost=9.62 rows=93) (actual time=0.065..0.087 rows=93 loops=1)
1 row in set (0.00 sec)
第五，type 栏为"index"
Index 表示覆盖索引扫描，可以简单描述为没有过滤条件的索引扫描；更进一步，如果从索引角度来讲，就是全表扫了。
比如SQL 6：select r1 from t1 limit 10
SQL 6 扫描的列只有r1，而非全部字段，此刻走索引idx_r1即可，不需要回表。
执行计划如下：type 为Index, 使用索引idx_r1, 扫描行数为10W行，刚好表t1总记录数也是10W.
debian-ytt1:ytt>desc select r1 from t1 limit 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index
possible_keys: NULL
key: idx_r1
key_len: 5
ref: NULL
rows: 106313
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
其实对于SQL 6 来讲，有limit 10 子句是可以提前终止扫描的，但是这里MySQL为什么还是扫描所有行？ 这里MySQL虽然走了索引idx_r1, 但是没有排序子句，进而造成MySQL 不知道按照什么顺序输出，只能扫描所有记录。
对于这类的优化，可以加一个排序子句，把现有索引的预排序特性利用上，变为 SQL 7：
SQL 7: select r1 from t1 order by r1 limit 10;
此时再查看查询计划：很显然，MySQL根据利用索引idx_r1的有序性，加上limit 子句，提前终止了扫描。
debian-ytt1:ytt>explain select r1 from t1 order by r1 limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: index
possible_keys: NULL
key: idx_r1
key_len: 5
ref: NULL
rows: 10
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
关于EXPLAIN TYPE 栏 的JOIN常见场景上篇就到这里了，欢迎大家订阅下一篇。
更多技术文章，请访问：https://opensource.actionsky.com/
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。
✨ Github：https://github.com/actiontech/sqle
📚 文档：https://actiontech.github.io/sqle-docs/
💻 官网：https://opensource.actionsky.com/sqle/
👥 微信群：请添加小助手加入 ActionOpenSource
