找到 A 不存在于 B 的记录，not in, except ,not exists ,left join + is null 大比拼

作者： buddyyuan 原文来源：https://tidb.net/blog/32bdae81

前言

大家好，今天的主题是 << 找到集合 A 中所有不在集合 B 中的数据 >>。我们来比较一下 TiDB 中的各种写法和执行计划，基于 6.5.x 版本。

首先创建 2 张表，t1 这张表 5000 行，t2 这张表 30000 行，然后在 t2 中删除三条在 5000 行之前的记录。

create table t1 (id1 int);create table t2 (id1 int);​insert into t1 WITH RECURSIVE t3 (id1) AS( SELECT 1 UNION ALL SELECT id1+1 FROM t3 WHERE id1<5000)  SELECT * FROM t3;insert into t2 WITH RECURSIVE t3 (id1) AS( SELECT 1 UNION ALL SELECT id1+1 FROM t3 WHERE  id1<30000)  SELECT * FROM t3;​delete from t2 where id1=2345;delete from t2 where id1=3456;delete from t2 where id1=4567;​create index idx_1 on t1 (id1);create index idx_2 on t2 (id1);​analyze table t1;analyze table t2;

一号选手 not in

找到集合 A 中所有不在集合 B 中的数据，使用 not in 这是最接近于语义的，所见即所得，这也是所有开发用的最多的方式，

mysql> SELECT id1 FROM t1 WHERE id1 NOT IN (SELECT id1 FROM t2);+------+| id1  |+------+| 2345 || 4567 || 3456 |+------+3 rows in set (6.33 sec)

执行计划如图所示：

很难想象，最简单朴素的方式竟然要 6 秒。这里直接采用了 CARTESIAN anti semi join 的方式，最终过滤结果为 3 行。为什么会走 CARTESIAN 呢？这主要是因为字段上没有 not null 约束导致的。当前优化器在 6.3 版本后支持 NAAJ（Null-aware Anti Join）, 可以在 Anti Join 操作的时候实现对 null 的感知。

但是当前版本参数默认值是关闭的，我们尝试打开一下。再次尝试后这次只需要 0.03 秒就出结果了。

从 6 秒到 0.03 秒堪称火箭速度提升，但是需要注意的是，这个功能在 6.5 版本默认是关闭的，看了一下 https://github.com/pingcap/tidb/issues/42271 这个 pr 的说明，应该是 tiflash 在 7.x 之前还没有完全匹配。所以在 6.5 版本默认还是关闭的。而在 7.0 版本之后默认打开了。所以想要稳定使用这个功能还是先考虑升级至 7.x 版本再说。

二号选手 except

mysql> SELECT id1 FROM t1 EXCEPT SELECT id1 FROM t2;+------+| id1  |+------+| 2345 || 3456 || 4567 |+------+3 rows in set (0.03 sec)

TiDB 支持三种集合运算的，并集 (UNION)，差集 (EXCEPT) 和交集 (INTERSECT)。我们可以使用 except 来直接进行结果集相减处理。

执行计划如图所示：

这次的效果非常棒，时间来到了 0.01 秒，自动给咱们转成了 anti semi join。

三号选手 not exists

使用 not exists 也行，它的原理也很简单，每次从 t1 中拿出一行数据，然后去 t2 中探测，找不到的就返回。

mysql> SELECT t1.id1 FROM t1    ->   WHERE not EXISTS (SELECT id1 FROM t2 WHERE t1.id1 = t2.id1);+------+| id1  |+------+| 2345 || 3456 || 4567 |+------+3 rows in set (0.01 sec)

执行计划如图所示：

这里同样是不错的，优化器采用了 anti semi join，和上面的一致，不同的是使用了 merge join 的方式。其实这也很好理解，通过索引读取的数据都是顺序的，我一次取一批去 t2 表进行探测。

四号选手 lefe join + is null

使用 left join 在配上 is null 等效于 not exists。

mysql> SELECT t1.id1    ->   FROM t1    ->   LEFT JOIN t2 ON (t1.id1 = t2.id1)    ->   WHERE t2.id1 IS NULL;+------+| id1  |+------+| 2345 || 3456 || 4567 |+------+3 rows in set (0.01 sec)

执行计划如图所示：

这里前面的步骤和 not exists 类似，从执行计划上是两张表先做 merge join 关联，关联后的结果集还需要额外再做一步 isnull 去去除 null 的数据。

结论

今天演示了上述 4 种方式，大家认为那种情况比较好呢 ？我先说一下我的结论吧。根据我们上述的数据分布情况是 not exists > left join + is null > except > not in 。

为什么呢？因为 t1 表数据量很少，我只要 t2 表上有索引，我每取一批然后去 t2 表中进行探测，不用循环多少次就能执行完毕。从执行计划上来看，也是最简单的 anti semi join 方式，没有额外的操作。

如果 t1 表和 t2 表的数据量都很多的情况并且没有索引，可以尝试 left join。毕竟通过关联可以采用 hash join 的方式，对于两张大表来说效率还是高一些。

总而言之，针对《找到 A 不存在于 B 的记录》这种需求，要根据数据量和索引来灵活进行选择，基本上 not exists 和 left join + is null 是等价的，且效率不相上下。在实际应用中，最好对两种方法都进行测试，以确定在特定环境下哪种方法的性能更好？