MySQL 8.0.16及以后版本中，任何具有EXISTS子查询谓词的语句都与具有等效子查询谓词的语句具有相同的半连接转换。
简单理解为 not exists 和 not in 子查询变成半连接。
这里有疑问，内连接(join)与半连接(semi join)的有什么区别：
从以上能理解到join 和 semi join 的差别。
EXPLAIN分析
在 NOT EXIST和NOT IN 条件下先看看执行计划。
MySQL5.7.30：
exists只有一种运算就是DEPENDENT SUBQUERY
官方含义为：
SUBQUERY:子查询中的第一个 select 查询,不依赖于外 部查询的结果集
DEPENDENT SUBQUERY:子查询中的第一个 select 查询,依赖于外部 查询的结果集
执行步骤着两步：
第一步，MySQL 根据 t_temp 得到一个结果集，其数据量是rows=1000行。
第二步，上面的结果集t_temp中的每一条记录，都将与子表进行loop循环。大数据量少时，对整体性能没问题，但要是第一个结果集表要100W级别，即使这两步查询都用到了索引，也会非常慢。DEPENDENT SUBQUERY 随着外层表的结果集的数据量的增大而执行时间增大。
如此一来，子查询的执行效率居然受制于外层查询的记录数。
现解决方案是：
- 改成join方式
- 拆成两个独立查询顺序执行
- 临时生成 temp表 在进行join
MySQL8.0.20的实现方式
format=tree方式：
Materialize：优化器使用物化来实现更高效的子查询处理。物化通过将子查询结果生成为一个临时表(通常在内存中)来加速查询的执行。MySQL第一次需要子查询结果时，它将该结果物化到一个临时表中。以后任何需要结果的时候，MySQL都会再次引用临时表。优化器可能使用散列索引索引表，以使查找快速而廉价。
子查询物化在可能的情况下使用内存中的临时表，如果表变得太大，则返回到磁盘上存储。
从MySQL 8.0.17开始，下面情况下子查询被转换为antijoins:
- NOT IN (SELECT … FROM …)
- NOT EXISTS (SELECT … FROM …).
- IN (SELECT … FROM …) IS NOT TRUE
- EXISTS (SELECT … FROM …) IS NOT TRUE.
- IN (SELECT … FROM …) IS FALSE
- EXISTS (SELECT … FROM …) IS FALSE.
限制Materialize:
- 内部表达式和外部表达式的类型必须匹配。例如，如果两个表达式都是整数或都是小数，优化器可能能够使用具体化，但如果一个表达式是整数而另一个是小数，则不能使用。
- 内部表达式不能是BLOB
optimizer_switc相关
optimizer_switch系统变量标志来启用或禁用这些策略:
- semijoin 标志控制是否使用半连接。从MySQL 8.0.17开始，可以使用antijoins。
- 如果启用了半连接，则firstmatch、loosescan、duplicateweedout和materialization 可以对允许的半连接策略进行更精细的控制。
- 如果duplicateweedout半连接策略被禁用，除非所有其他适用的策略也被禁用，否则不会使用该策略。
- 如果禁用了duplicateweedout，有时优化器可能会生成一个远非最佳的查询计划。这是由于贪婪搜索过程中的启发式剪枝造成的，可以通过设置optimizer_prune_level=0来避免。
mysql> SHOW VARIABLES LIKE 'optimizer_switch'\G;
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)
因为连贯性问题，没有特殊需求，不建议更改更改优化器的选项；
测试
mysql8.0.20环境，关闭半连接下，status清除，观察handler，tmp相关指标
#清除状态和插卡
FLUSH STATUS;
SHOW STATUS WHERE
Variable_name LIKE 'Handler_read%'
OR Variable_name LIKE 'Handler_write%'
OR Variable_name LIKE '%tmp%';
#优化器semijoin开关
SHOW VARIABLES LIKE 'optimizer_switch';
SET SESSION optimizer_switch="semijoin=on";
SET SESSION optimizer_switch="semijoin=off";
#执行同一个SQL语句
SELECT * FROM t_temp t WHERE NOT EXISTS
(SELECT 1 FROM r_temp e where e.id = t.id) ;
通过以上对比发现，在整个过程中半连接生成内存临时表，又进行了读写等操作。
要是并发量高的情况下，半连接资源方面使用应该会上升。
思考总结
- mysql的子查询一直存在性能问题,虽然8.0版本优化了一些,但还是有缺陷,尽量合理使用。
- AntiJoin新出现的问题是hash缓存，临时表问题 都是要综合考虑的因素
- 另外,在执行sql前,复杂的都尽量的EXPLAIN看下结果集是否可接受.在结果集看到SUBQUERY , DEPENDENT SUBQUERY ,或Using temporary,Using join buffer类似的需要优化；
优化是个漫长而艰巨的过程。
参考：
https://dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html
https://dev.mysql.com/doc/refman/8.0/en/semijoins.html
