mysql join 反_MySQL 8.0反连接(antijoin)

引言

在MySQL8.0.16版本之前，对于IN和EXISTS的处理，优化器可以将IN由子查询方式优化为semi join,但EXITS只能采用子查询的方式，所以在执行计划中看到的是DEPENDENT SUBQUERY。在8.0.16版本中，对EXISTS进行了优化，使其可以像IN一样支持转换为semi join:Beginning with MySQL 8.0.16, the semijoin optimizations used with IN subqueries can now be applied to EXISTS subqueries as wellhttps://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.htm

在MySQL8.0.17版本中，对NOT IN,NOT EXISTS进行了优化，将其转变为anti join:

In short, any negation of a subquery of the form IN (SELECT ... FROM ...) or EXISTS (SELECT ... FROM ...) is transformed into an antijoinhttps://dev.mysql.com/doc/refman/8.0/en/semijoins.html

环境准备：192.168.56.101 MySQL8.0.20

192.168.56.102 MySQL5.7.19

192.168.56.103 SYSBENCH

database:sysbench

tables:sbtest1,sbtest2

表结构如下均一致：

数据量：

操作明细：

5.7.19中的IN操作：SQL>explain select * from sbtest1 a where a.id in (select b.id from sbtest2 b);

可见IN操作转换为了semi join

5.7.19中的EXISTS操作：SQL>explain select * from sbtest1 a where exists (select 1 from sbtest2 b where a.id=b.id);

可见EXISTS操作在5.7.19中没有做优化，依然为子查询方式

5.7.19中的NOT IN操作：SQL>explain select * from sbtest1 a where a.id not in (select b.id from sbtest2 b);

可见5.7.19中NOT IN操作没有做优化处理

5.7.19中的NOT EXISTS操作:SQL>explain select * from sbtest1 a where not exists (select 1 from sbtest2 b where a.id=b.id);

可见5.7.19中NOT EXISTS操作没有做优化处理

8.0.20中的IN操作：SQL>explain select * from sbtest1 a where a.id in (select b.id from sbtest2 b);

与5.7.19一致

8.0.20中的EXISTS操作：SQL>explain select * from sbtest1 a where exists (select 1 from sbtest2 b where a.id=b.id);

可见，8.0.16版本后，EXISTS操作与IN操作优化一致

8.0.20中的NOT IN操作：SQL>explain format=tree select * from sbtest1 a where a.id not in (select b.id from sbtest2 b);

PS:使用format=tree可以查看执行计划的详细预估成本信息，这边为了看清antijoin，如果需要查看实际执行花费，可以用analyze

可见，8.0.17版本后，NOT IN采用antijoin方式进行优化

8.0.20中的NOT EXISTS操作：SQL>explain format=tree select * from sbtest1 a where not exists (select 1 from sbtest2 b where a.id=b.id);

可见，8.0.17版本后，NOT EXISTS同样采用antijoin方式进行优化

结果分析：

我们知道子查询方式，需要遍历表a的记录，每一条记录都需要检索一遍表b的子查询，当数据量增大的情况下，效率会越来越差。所以优化器会将in,exists转换为semi join,将not in,not exists转换为antijoin。但我们可以看到，antijoin在执行计划中表现形式之一为MATERIALIZED(anti join的策略选择基于成本考虑有多种，具体可见https://dev.mysql.com/doc/refman/8.0/en/semijoins.html)，也即是会对子查询创建一个物化临时表，会消耗一定的时间，内存空间甚至磁盘空间，所以不能说使用了antijoin一定比子查询方式效率高，尽量需要保证not in,not exists后的where条件可以精确筛选出小部分结果，性能相关的结果可以自行测试。

--------------------------------

END

--------------------------------