第 47 期：EXPLAIN TYPE 列的 JOIN 常见场景详解（上）

爱可生开源社区

发布于 2024-12-27 20:39:01

7300

代码可运行

文章被收录于专栏：爱可生开源社区爱可生开源社区

运行总次数：0

代码可运行

作者：杨涛涛，爱可生技术专家。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

专栏序回顾

对于关系型数据库来说，表的设计 以及 SQL 的写法 尤为重要，占据性能的 90% 都不为过，所以这次专门针对这两大类知识领域，为您抽丝剥茧详细分析。

本专栏语言通俗易懂，选取大量示例为您详细说明个中奥妙~

面向的对象：

DBA
数据库开发者

第 47 期正文

专栏连载至此，相信读者们已经对一条 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 列的意义。

1type: 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)

2type: 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)

3type: ref

ref 和 eq_ref 类似，不同的是两表的 JOIN KEY 非主键、非唯一索引。这种场景从 SQL 角度来讲，应该避免掉；如果实在无法避免，可以想办法减少两表 JOIN 的记录数。

那对 SQL 2 做些调整，变为 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)

4type: 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)

5type: 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 常见场景上篇就到这里了，欢迎大家订阅下一篇。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-12-25，如有侵权请联系 cloudcommunity@tencent.com 删除

join