飞利浦·斯塔克|庖丁解牛｜图解 MySQL 8.0 优化器查询转换篇( 五 ) ie|MySQL|删除

) [AND outer-cond
[GROUP BY ...
[HAVING ...
[ORDER BY ...
=SELECT ... FROM (ot1 ... otN) SJ (it1 ... itK) ON (oe1 ... oeM) = (ie1 ... ieM) [AND inner-cond
[WHERE outer-cond
[GROUP BY ...
[HAVING ...
[ORDER BY ...
EXISTS谓词 SELECT ... FROM ot1 ... otN WHERE EXISTS (SELECT expressions FROM it1 ... itK [WHERE inner-cond
) [AND outer-cond
[GROUP BY ...
[HAVING ...
[ORDER BY ...
=SELECT ... FROM (ot1 ... otN) SJ (it1 ... itK) [ON inner-cond
[WHERE outer-cond
[GROUP BY ...
[HAVING ...
[ORDER BY ...
NOT EXISTS谓词 SELECT ... FROM ot1 ... otN WHERE NOT EXISTS (SELECT expressions FROM it1 ... itK [WHERE inner-cond
) [AND outer-cond
[GROUP BY ...
[HAVING ...
[ORDER BY ...
=SELECT ... FROM (ot1 ... otN) AJ (it1 ... itK) [ON inner-cond
[WHERE outer-cond AND is-null-cond(it1)
[GROUP BY ...
[HAVING ...
[ORDER BY ...
NOT IN谓词 SELECT ... FROM ot1 ... otN WHERE (oe1 ... oeM) NOT IN (SELECT ie1 ... ieM FROM it1 ... itK [WHERE inner-cond
) [AND outer-cond
[GROUP BY ...
[HAVING ...
[ORDER BY ...
=SELECT ... FROM (ot1 ... otN) AJ (it1 ... itK) ON (oe1 ... oeM) = (ie1 ... ieM) [AND inner-cond
[WHERE outer-cond
[GROUP BY ...
[HAVING ...
[ORDER BY ...
查找可以插入semi-join嵌套和其生成的条件的位置，比如对于 t1 LEFT JOIN t2 embedding_join_nest为t2 ， t2也可以是nested ，如t1 LEFT JOIN (t2 JOIN t3)) 生成一个新的semijoin嵌套的TABLE_LIST表处理Antijoin 将子查询中潜在的表合并到上述join表（TABLE_LIST::merge_underlying_tables）将子查询的叶子表插入到当前查询块的叶子表后面，重新设置子查询的叶子表的序号和依赖的外表。将子查询的叶子表重置。如果是outer join的话，在join链表中传递可空性（propagate_nullability）将内层子查询中的关联条件去关联化，这些条件被加入到semijoin的列表里。这些条件必须是确定的，仅支持简单判断条件或者由简单判断条件组成的AND条件（Query_block::decorrelate_condition）判断左右条件是否仅依赖于内外层表，将其表达式分别加入到semijoin内外表的表达式列表中（decorrelate_equality）解关联内层查询的join条件（Query_block::decorrelate_condition）移除该子查询表达式在父查询的AST（Query_express::exclude_level）根据semi-join嵌套产生的WHERE/JOIN条件更新对应的table bitmap（Query_block::fix_tables_after_pullout）将子查询的WHERE条件上拉，更新使用表的信息（Item_cond_and::fix_after_pullout()）根据semijoin的条件列表创建AND条件，如果有条件为常量True ，则去除该条件；如果常量为False ，则整个条件都去除（Query_block::build_sj_cond）将创建出来的semijoin条件加入到外层查询的WHERE条件中最后遍历排序后的子查询列表，对于没有转换的子查询，对于Subquery_strategy::UNSPECIFIED的策略，执行IN-EXISTS改写（select_transformer），如果确实原有的子查询已经有替代的Item ，调用replace_subcondition解析并把他们加入到合适的WHERE或者ON子句。清除所有的sj_candidates列表 Semi-join有5中执行方式，本文并不介绍Optimizer和Execution过程，详细可以参考引用文章中关于semijoin的介绍，最后引入下控制semijoin优化和执行的优化器开关，其中semijoin=on/off是总开关。SELECT @@optimizer_switch\\G*************************** 1. row ***************************@@optimizer_switch: ...... materialization=onsemijoin=onloosescan=on firstmatch=on subquery_materialization_cost_based=on ...... 下图举例说明该转换过程： SELECT * FROM t1 WHERE t1.a in (SELECT t2.c1 FROM t2 where t2.c10);=/* select#1 */SELECT `t1`.`a` AS `a`FROM `t1`SEMI JOIN (`t2`)WHERE ((`t1`.`a` = `t2`.`c1`) and (`t2`.`c1`0))执行计划如下：explain SELECT * FROM t1 WHERE t1.a in (SELECT t2.c1 FROM t2 where t2.c10);+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Start temporary || 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where; End temporary; Using join buffer (hash join) |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------------------------------------------+