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

文章图片

文章图片

文章图片

文章图片

一背景和架构在《庖丁解牛-图解MySQL 8.0优化器查询解析篇》一文中我们重点介绍了MySQL最新版本8.0.25关于SQL基本元素表、列、函数、聚合、分组、排序等元素的解析、设置和转换过程，本篇我们继续来介绍更为复杂的子查询、分区表和JOIN的复杂转换过程，大纲如下：
Transformation
remove_redundant_subquery_clause :Permanently remove redundant parts from the query if 1) This is a subquery 2) Not normalizing a view. Removal should take place when a query involving a view is optimized not when the view is created. remove_base_options:Remove SELECT_DISTINCT options from a query block if can skip distinct resolve_subquery :Resolve predicate involving subquery perform early unconditional subquery transformations Convert subquery predicate into semi-join or Mark the subquery for execution using materialization or Perform IN-EXISTS transformation or Perform more/less ALL/ANY -MIN/MAX rewrite Substitute trivial scalar-context subquery with its value transform_scalar_subqueries_to_join_with_derived:Transform eligible scalar subqueries to derived tables. flatten_subqueries :Convert semi-join subquery predicates into semi-join join nests. Convert candidate subquery predicates into semi-join join nests. This transformation is performed once in query lifetime and is irreversible. apply_local_transforms : delete_unused_merged_columns : If query block contains one or more merged derived tables/views walk through lists of columns in select lists and remove unused columns. simplify_joins : Convert all outer joins to inner joins if possible. prune_partitions ：Perform partition pruning for a given table and condition. push_conditions_to_derived_tables :Pushing conditions down to derived tables must be done after validity checks of grouped queries done by apply_local_transforms(); Window::eliminate_unused_objects:Eliminate unused window definitions redundant sorts etc. 二详细转换过程 1 解析子查询（resolve_subquery）
解析条件中带有子查询的语句，做一些早期的无限制的子查询转换，包括：
标记subquery是否变成semi-join 转换判断条件
检查OPTIMIZER_SWITCH_SEMIJOIN和HINT没有限制子查询是IN/=ANY和EXIST subquery的谓词子查询是简单查询块而不是UNION 子查询无隐形和显性的GROUP BY 子查询没有HAVING、WINDOW函数 Resolve的阶段是Query_block::RESOLVE_CONDITION和Query_block::RESOLVE_JOIN_NEST并且没有用到最新的Hyper optimizer优化器。外查询块可以支持semijoins 至少要一个表，而不是类似\"SELECT 1\" 子查询的策略还没有指定Subquery_strategy::UNSPECIFIED 父查询也至少有一个表父查询和子查询都不能有straight join 父查询块不禁止semijoin IN谓词返回值是否是确定的，不是RAND 根据子查询判断结果是否需要转成true还是false以及是否为NULL ，判断是可以做antijoin还是semijoin Antijoin是可以支持的，或者是semijoin offset和limit对于semjoin是有效的， offset是从第一行开始， limit也不是0 设置Subquery_strategy::CANDIDATE_FOR_SEMIJOIN并添加sj_candidates
标记subquery是否执行时采用materialization方案如果不符合转换semijoin ，尝试使用物化方式，转换判断条件 Optimzier开关subquery_to_derived=on 子查询是IN/=ANY or EXISTS谓词子查询是简单查询块而不是UNION 如果是[NOT