MySQL 8.0.18 Optimizer adds AntiJoin Anti-connection Optimization

In MySQL 8.0.18, the optimization of NOT IN/ exists subquery statements is supported, and the query is automatically rewritten into AntiJoin disjoin query SQL statements within the optimizer.

Usually, we want to complete the query results in the inner table first from the inside to the outside, and then drive the outer query table to complete the final query, but the subquery will first scan all the data in the appearance, and each piece of data will be passed to the inner table to be associated with it. If the appearance is very large, then the performance will be very poor.

Let's look at an example.

Explain select * from T1 where id not in (select id from T2)

Inside the optimizer, the not in subquery is rewritten as the following statement

Explain select T1 * from T1 left join T2 on t1.id=t2.id where t2.id is null

Comparing the two implementation plans, the result is the same.