前言
大家好，今天的主题是<>。我们来比较一下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 是等价的，且效率不相上下。在实际应用中，最好对两种方法都进行测试，以确定在特定环境下哪种方法的性能更好？
