第 48 期：EXPLAI TYPE 列的 JOI 常见场景详解（下）

第 48 期：EXPLAI TYPE 列的 JOI 常见场景详解（下）

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

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

---

专栏序回顾

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

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

面向的对象：

• DBA
• 数据库开发者

第 48 期正文

本篇是 上一篇《EXPLAI TYPE 列的 JOI 常见场景详解（上）》的续篇，继续介绍执行计划 type 栏的 JOI 类型。

1type: ALL

对于大部分开发规范来讲，都会把 ALL 放在禁止的第一位。

一般 SQL 语句执行计划的 type 栏为 ALL 时，我们都会觉得它效率都非常差，执行时间很慢。比如以下 SQL，命名为 SQL 1：

SQL 1: select * from t1 limit 100

SQL 1 没有任何字段进行过滤， 所以必须走全表扫描，看下执行计划：type 栏内容为 ALL，rows 为表总行数，其他栏基本全是 ULL。

代码语言：javascript代码运行次数：0运行复制

debian-ytt1:ytt>desc select * from t1 limit 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partiti: ULL
         type: ALL
possible_keys: ULL
          key: ULL
      key_len: ULL
          ref: ULL
         rows: 1061
     filtered: 100.00
        Extra: ULL
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 行，而且排序子句顺序和主键顺序一致，避免额外的排序开销。

代码语言：javascript代码运行次数：0运行复制

debian-ytt1:ytt>desc select * from t1 order by f0,f1 limit 100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partiti: ULL
         type: index
possible_keys: ULL
          key: PRIMARY
      key_len: 8
          ref: ULL
         rows: 100
     filtered: 100.00
        Extra: ULL
1 row in set, 1 warning (0.00 sec)

接下来看下 SQL 1 和 SQL 2 的执行成本对比：

代码语言：javascript代码运行次数：0运行复制

debian-ytt1:ytt>desc analyze select * from t1 limit 100 \G
*************************** 1. row ***************************
EXPLAI: -> 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=1061) (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 ***************************
EXPLAI: -> 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 ：

SQL :

select * from t1 where r1 =1

union all

select * from t1 where r2 = 1

union all

select * from t1 where r = 1;

SQL 是对表 t1 扫描三次后做一个并集，每次给的过滤条件不一样，这样的 SQL 会涉及到一个写临时表的过程，临时表的大小由表记录数以及过滤条件决定，显然这种不够优化。index_merge 就是 MySQL 用来解决此类场景的一个可优化项，在索引层先把数据合并，之后再回表过滤。

index_merge 表示 MySQL 在执行一条 SQL 时，如果 SQL 语句涉及的表有多个可用索引，MySQL 会考虑走多个索引一起来输出结果。把 SQL 改为 SQL 4：

SQL 4：select * from t1 where r1 = 1 or r2 = 1 or r =1

SQL 4 过滤字段有三个，相互之间是或的关系，而且三个字段都有各自的索引， MySQL 此时会考虑将这三个索引一起来用。来看下执行计划：

代码语言：javascript代码运行次数：0运行复制

debian-ytt1:ytt>desc select * from t1 where r1 = 1 or r2 = 1 or r  =1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partiti: ULL
         type: index_merge
possible_keys: idx_r1,idx_r2,idx_r
          key: idx_r1,idx_r2,idx_r
      key_len: 5,5,5
          ref: ULL
         rows: 4104
     filtered: 100.00
        Extra: Using union(idx_r1,idx_r2,idx_r); Using where
1 row in set, 1 warning (0.01 sec)

还有一种排序后的并集优化，类似 UIO ALL 转为 UIO 操作。比如 SQL 5:

SQL 5: select * from t1 where r2 < 2 or r < 2

SQL 5 是对多个字段进行过滤，并且每个字段过滤类型都是一个固定范围，这样的场景可以用到 SORT UIO ，执行计划如下：

代码语言：javascript代码运行次数：0运行复制

debian-ytt1:ytt>desc select * from t1 where r2 < 2 or r < 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partiti: ULL
         type: index_merge
possible_keys: idx_r2,idx_r
          key: idx_r2,idx_r
      key_len: 5,5
          ref: ULL
         rows: 1986
     filtered: 100.00
        Extra: Using sort_union(idx_r2,idx_r); 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 r =1

三个字段 r1,r2,r 都有各自索引，所以在这种存在交集的场景下，也可以直接在索引层 index_merge，减少回表的记录数。

看下执行计划：type 栏为 index_merge， 并且 extra 栏里用索引 idx_r2,idx_r 提前做交集，最终扫描行数仅为 9 行。如果不用 index_merge，扫描行数将会很大。

代码语言：javascript代码运行次数：0运行复制

debian-ytt1:ytt>desc  select * from t1 where r1 = 1 and r2 = 1 and r =1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partiti: ULL
         type: index_merge
possible_keys: idx_r1,idx_r2,idx_r
          key: idx_r,idx_r2
      key_len: 5,5
          ref: ULL
         rows: 9
     filtered: 1.60
        Extra: Using intersect(idx_r,idx_r2); Using where
1 row in set, 1 warning (0.00 sec)

读到这里，未免会有一个新的问题：是不是对于 MySQL 来讲，各种列的任意组合，只要每个列有自己单独索引，都能用到 IDEX_MERGE 优化算法？

答案是否定的！ IDEX_MERGE 的使用条件非常简单，对索引列的过滤要么是并集组合，要么是差集组合，不能有其他复杂的组合。比如下面 SQL 7，就无法用到这一特性。

SQL 7: select * from t1 where (r1 =1 or r2 = 1) and r = 1

至此，常见的执行计划 JOI 栏输出内容也就这些，欢迎大家继续关注。

本文参与 腾讯云自媒体同步曝光计划，分享自。原始发表：2025-01-06，如有侵权请联系 cloudcommunity@tencent 删除排序索引优化sqljoin