MySQL 8.0 新特性-antijoin

MySQL 8.0.17版本引入了一个antijoin的优化,这个优化能够将where条件中的not in(subquery), not exists(subquery),in(subquery) is not true,exists(subquery) is not true,在内部转化成一个antijoin(反连接),以便移除里面的子查询subquery,这个优化在某些场景下,能够将性能提升20%左右。

1. antijoin适用场景

antijoin适用的场景案例通常如下:

  1. 找出在集合A且不在集合B中的数据
  2. 找出在当前季度里没有购买商品的客户
  3. 找出今年没有通过考试的学生
  4. 找出过去3年,某个医生的病人中没有进行医学检查的部分

上面这些场景,以第4个为例,转换成一个SQL,通常如下:

select * from patients where not exists(
    select * from exams where  
    exams.type='check-up' and 
    exams.date>=date_sub(now(), interval 3 year) and 
    exams.patient_id=patients.patient_id
);

如果SQL按照这种形式去写,通常没有太多的优化空间。我们需要从patients表读取每条记录,对于每条记录,带到子查询中,检查是否满足条件。因为子查询的where子句依赖patients.patient_id,patients 表的每一条记录遍历,都会导致子查询被重复执行,严重影响性能。

优化这种SQL的第一步就是打破上层查询与子查询之间的边界,将后者也就是子查询合并到上层查询里面。

看一下优化之后的SQL,如下:

select * from patients antijoin exams on 
    exams.type='check-up' and 
    exams.date>=date_sub(now(), interval 3 year) and 
    patients.patient_id=exams.patient_id;

上面的SQL使用了关键字antijoin,实际上这个关键字是不存在的,或者说它只在MySQL内部使用,用户并不知道它,它和join操作类似,join通常寻找满足匹配条件的记录,而antijoin寻找不匹配的记录。更准确地说,它从左边表选择记录,然后检查右边表,根据on条件,检查是否没有记录能够匹配上,如果没有记录匹配,那么左边的这条记录就可以作为结果返回。

查看SQL的执行计划,加上 format=tree,就能看到执行计划中使用了antijoin,如下:

 -> Nested loop anti-join
    -> Table scan on tb1  (cost=850482.22 rows=8372128)
    -> Single-row index lookup on <subquery2> using <auto_distinct_key> (id=tb1.id)
        -> Materialize with deduplication
            -> Index scan on tb2 using PRIMARY  (cost=1.85 rows=16)

2. antijoin内部优化策略

MySQL有两种策略用于执行antijoin。

  • First Match
  • Materialization
2.1 First Match策略

First Match 策略,从patients表中读取一条记录,然后在exams表中寻找匹配,如果没有匹配上,则将这条记录作为结果返回。这种方式与使用子查询并没有太大的区别。

2.2 Materialization策略

Materialization策略,对于上述SQL例子,ON子句有3个子条件,分别是

  • exams.type=’check-up’
  • exams.date>=date_sub(now(), interval 3 year)
  • patients.patient_id=exams.patient_id

3个条件中只有一个依赖patients表,所以MySQL可以创建一个临时表tmp,这个临时表由exams表按照前两个条件过滤后的数据组成,就像下面这样:

create table tmp 
    select patient_id from exams where 
    exams.type='check-up' and 
    exams.date>=date_sub(now(), interval 3 year);

MySQL优化器会自动在tmp的patient_id字段上添加索引,然后从patients表读取记录,使用索引匹配tmp表中的记录,如果没有匹配上,则返回这条记录。

相对于First Match策略,Materialization策略主要有以下优势:

  1. exams表只读取一次,用于创建tmp表。
  2. tmp表相对于exams表,有更少的记录,访问tmp表要比exams表更快。
  3. tmp表上由于创建了索引,访问起来更快,而原始表exams很可能没有索引。

当然,这种策略创建临时表,也有一些前期的成本消耗,比如需要申请内存来存储临时表数据,比如临时表非常大,需要将临时表存储在磁盘上。因此两种策略哪一种更好,依赖于具体的场景。幸运的是,MySQL有一个基于成本的优化器,通过计算两种策略基于数据行数的成本,条件的选择性,索引的使用,最终选择成本最低的那个策略。

3. 总结

  • antijoin优化适用于not exists(subquery), not in(subquery)这类查询,在某些场景下,能够对这类SQL进行很好的优化和性能提高。
  • antijoin有两种执行策略,First Match和Materialization,优化器根据成本模型进行选择。

发表评论