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

群里有一些免费电子书,可以随意自取

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值