MySQL 8.0 中的 anti-join

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%左右。

原文地址：

https://mytecdb.com/blogDetail.php?id=108

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，优化器根据成本模型进行选择。