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

但目前不支持HAVING表达式中包含该子查询，其实也是可以转换的。SELECT SUM(a) (SELECT SUM(b) FROM t3) scalarFROM t1HAVING SUM(a)scalar;转换为=SELECT derived0.summ derived1.scalarFROM (SELECT SUM(a) AS summ FROM t1) AS derived0 LEFT JOIN (SELECT SUM(b) AS scalar FROM t3) AS derived1 ON TRUEWHERE derived0.sumderived1.scalar; 接下来遍历所有可以转换的子查询，把他们转换成derived tables ，并替换相应的表达式变成列（transform_subquery_to_derived）。生成derived table的TABLE_LIST（synthesize_derived）。将可以移动到derived table的where_cond设置到join_cond上。添加derived table到查询块的表集合中。decorrelate_derived_scalar_subquery_pre 添加非相关引用列（NCF）到SELECT list ，这些条件被JOIN条件所引用，并且还有另外一个fields包含了外查询相关的列，我们称之为'lifted_where' 添加COUNT(*)到SELECT list ，这样转换的查询块可以进行cardinality的检查。比如没有任何聚合函数在子查询中。如果确定包含聚合函数，返回一行一定是NCF同时在GROUP BY列表中。添加NCF到子查询的GROUP列表中，如果已经在了，需要加到最后，如果发生GROUP BY的列由于依赖性检查失败，还要加Item_func_any_value（非聚合列）到SELECT list 。对于NCF会创建 derived.field和derived.`count(field)`。设置物化的一些准备（setup_materialized_derived）。decorrelate_derived_scalar_subquery_post：创建对应的'lifted_fields' 。更新JOIN条件中相关列的引用，不在引用外查询而换成Derived table相关的列。代替WHERE、JOIN、HAVING条件和SELECT list中的子查询的表达式变成对应的Derived Table里面列。下面图解该函数的转换过程和结果：
3 扁平化子查询（flatten_subqueries）
该函数主要是将Semi-join子查询转换为nested JOIN ，这个过程只有一次，并且不可逆。
简单来讲步骤可以简化理解为：创建SEMI JOIN (it1 ... itN)语以部分，并加入到外层查询块的执行计划中。将子查询的WHERE条件以及JOIN条件，加入到父查询的WHERE条件中。将子查询谓词从父查询的判断谓词中消除。由于MySQL在一个query block中能够join的tables数是有限的(MAX_TABLES) ，不是所有sj_candidates都可以做因此做flatten_subqueries 的，因此需要有优先级决定的先后顺序先unnesting掉，优先级规则如下：相关子查询优先于非相关的 inner tables多的子查询大于inner tables少的位置前的子查询大于位置后的 subq_item-sj_convert_priority = (((dependent * MAX_TABLES_FOR_SIZE) + // dependent subqueries first child_query_block-leaf_table_count) * 65536) + // then with many tables (65536 - subq_no); // then based on position 另外，由于递归调用flatten_subqueries是bottom-up ，依次把下层的子查询展开到外层查询块中。for SELECT#1 WHERE X IN (SELECT #2 WHERE Y IN (SELECT#3)) : Query_block::prepare() (select#1) -fix_fields() on IN condition -Query_block::prepare() on subquery (select#2) -fix_fields() on IN condition -Query_block::prepare() on subquery (select#3)- Query_block::prepare()- fix_fields() -flatten_subqueries: merge #3 in #2- flatten_subqueries- Query_block::prepare()- fix_fields() -flatten_subqueries: merge #2 in #1 遍历子查询列表，删除Item::clean_up_after_removal标记为Subquery_strategy::DELETED的子查询，并且根据优先级规则设置sj_convert_priority 。根据优先级进行排序。遍历排序后的子查询列表，对于Subquery_strategy::CANDIDATE_FOR_DERIVED_TABLE策略的子查询，转换子查询([NOT
{IN EXISTS)为JOIN的Derived table（transform_table_subquery_to_join_with_derived） FROM [tables
WHERE ... AND/OR oe IN (SELECT ie FROM it) ...=FROM (tables) LEFT JOIN (SELECT DISTINCT ie FROM it) AS derived ON oe = derived.ie WHERE ... AND/OR derived.ie IS NOT NULL ... 设置策略为Subquery_strategy::DERIVED_TABLE semijoin子查询不能和antijoin子查询相互嵌套，或者外查询表已经超过MAX_TABLE ，不做转换，否则标记为Subquery_strategy::SEMIJOIN策略。判断子查询的WHERE条件是否为常量。如果判断条件永远为FALSE ，那么子查询结果永远为空。该情况下，调用Item::clean_up_after_removal标记为Subquery_strategy::DELETED ，删除该子查询。如果无法标记为Subquery_strategy::DELETED/设置Subquery_strategy::SEMIJOIN策略的重新标记会Subquery_strategy::UNSPECIFIED继续下一个。替换外层查询的WHERE条件中子查询判断的条件（replace_subcondition）子查询内条件并不永远为FALSE ，或者永远为FALSE的情况下，需要改写为antijoin（antijoin情况下，子查询结果永远为空，外层查询条件永远通过）。此时将条件改为永远为True 。子查询永远为FALSE ，且不是antijoin 。那么将外层查询中的条件改成永远为False 。Item_subselect::EXISTS_SUBS不支持有聚合操作 convert_subquery_to_semijoin函数解析如下模式的SQL IN/=ANY谓词如果条件满足解关联，解关联decorrelate_condition 添加解关联的内表表达式到 SELECT list 收集FROM子句中的外表相关的 derived table或join条件去掉关联标识UNCACHEABLE_DEPENDENT ，更新used table Derived table子查询增加SELECT_DISTINCT标识转换子查询成为一个derived table ，并且插入到所属于的查询块FROM后（transform_subquery_to_derived）创建derived table及其join条件遍历父查询块的WHERE ，替换该子查询的Item代替成derived table（replace_subcondition）遍历排序后的子查询列表，对于Subquery_strategy::CANDIDATE_FOR_SEMIJOIN策略的子查询。判断是否可以转换为semijoin 遍历排序后的子查询列表，对于Subquery_strategy::SEMIJOIN的子查询，开始转换为semijoin/antijoin（convert_subquery_to_semijoin） convert_subquery_to_semijoin函数解析如下模式的SQL IN/=ANY谓词 SELECT ... FROM ot1 ... otN WHERE (oe1 ... oeM) IN (SELECT ie1 ... ieM FROM it1 ... itK [WHERE inner-cond