MYSQL ANTIJOIN 提高20% 的性能 真的？

从mysql 8.017开始有一个“rumor”, 就是相对于以前的版本查询的执行效率会提高20%，而原因在于antijoin的优化。

用一句话可以解释：

“优化器现在将WHERE条件NOT IN(子查询)、NOT EXISTS(子查询)、IN(子查询)不为真、EXISTS(子查询)不为真转换为反连接，从而删除子查询。”

当然这些逻辑都是从，我不要开始的。

下面有两个例子，从MYSQL 5.7.23 与 MYSQL 8.018 同样的查询看看他们到底是否能走不一样的“优化器”

explain select first_name,last_name

from employees.employees as em

where not exists (

select * from employees.employees as emm

where emm.emp_no = em.emp_no and emm.birth_date < '1960-01-01' and emm.gender='M'

)

上面就是我们要使用的语句，意思是我不要员工表中那些出生年龄小于 1960-01-01 的并且是男性的员工。

我们看一下，两个不同的版本的MYSQL 到底做了什么。

mysql 5.7

mysql 8

当然很明显mysql 8 里面的东西和MYSQL5.7 的执行计划不同。MYSQL 5.7 还处于单纯的状态。类似于每次将子查询的结果返回给上层的表，然后进行过滤，基本上属于傻白甜的状态。

在看看MYSQL 8

上图可以很清晰的看到anti-join 在explain format= tree 的顶部，打破顶部查询和子查询之间的界限，Anti-join 说白了就是将原来匹配的操作符号join，变为了非匹配项，从左侧选择右侧没有匹配的ON 的条件的记录。

而从图中可以看到，Materialize 物化这个是之前MYSQL5.X 没有的东西，MYSQL 自动建立一个临时表tmp 使得将符合子查询的条件的记录进行物化。

这想当建立了一个

create table tmp select emp_no from employees where emm.birth_date < '1960-01-01' and emm.gender='M'

这样的做法比之前好的地方，就是一次建立所有要进行检索的记录，因为符合条件的记录大概率会比整个表的记录要少，这个例子中的过滤条件并不是很好，符合这个条件的人数比较多，占比40%

构建tmp可能会有很大的前期成本:MySQL需要分配内存来存储它的记录(如果有很多记录，甚至可能分配磁盘空间)，它还需要时间将记录写入tmp，所以有的时候并不会向某些传言 , MYSQL 8 的查询会比 MYSQL 5.7 的查询要提高20%，尤其在 not in not exists 之类的操作。

这里对比 MYSQL 5.7 与 MYSQL 8, 在本次的例子里面，MYSQL 5.7 还稍微的快了那么 几个毫秒。

那怎么能体现MYSQL 8 中的antijoin的优势，我们在查询中添加了一个条件

select first_name,last_name from employees.employees as em where not exists ( select * from employees.employees as emm

where emm.emp_no = em.emp_no and emm.birth_date < '1960-01-01' and emm.gender='M' and last_name = 'Facello');

将一个可以过滤多数记录的条件 last_name 放到条件中。

再次查询，MYSQL 8 使用ANTIJOIN 的方式要比MYSQL 5.7 要快3倍

MYSQL 5.7

MYSQL 8

所以通过上面简陋的测试，可以粗略的得出，如果条件不给力，过滤的数据不精准，则MYSQL 5.7 并没有太坏的表现，而MYSQL 8 可能会更慢，而如果条件精准，通过过滤的条件能将一大部分不合格的数据挡在外部，则MYSQL 8 的 antijoin 在 not exists not in 上会有比MYSQL 5.7 更好的表现。

下面链接有比较官方的列子

https://www.oreilly.com/library/view/mysql-stored-procedure/0596100892/ch21s02.html