【华为云MySQL技术专栏】MySQL 8.0 子查询物化机制详解

SELECT * FROM t1 WHERE t1.m IN (SELECT t2.m FROM t2);

mysql> EXPLAIN SELECT * FROM t1 WHERE a IN (SELECT MAX(a) FROM t1 GROUP BY id);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           
|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using where     |
|  2 | SUBQUERY    | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
2 rows in set, 1 warning (0.01 sec)

(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)

oe [NOT] IN (SELECT ie ...)

|--> SELECT_LEX::prepare  // 当前SELECT_LEX为子查询
  ...
  |--> SELECT_LEX::remove_redundant_subquery_clauses
        1. 对于表子查询谓词（IN/ANY/ALL/EXISTS等），删除ORDER BY子句、DISTINCT子
        2. 对于 无 LIMIT 的标量子查询，删除ORDER BY子句
  |--> SELECT_LEX::resolve_subquery
    |--> 对于IN/EXIST subselect，判断是否可以转换为semi-join/anti-join，如果可以，设置strategy为CANDIDATE_FOR_SEMIJOIN
    |--> 否则，判断IN/EXIST subselect是否可以转换为derived_table，如果可以，设置strategy为CANDIDATE_FOR_DERIVED_TABLE
    |--> Item_in_subselect::select_transformer  // 上面的转换条件都不满足，是适用于子查询（IN/ANY/ALL/SOME）
      |--> Item_in_subselect::select_in_like_transformer
        |--> 创建Item_in_optimizer，即Item_in_subselect的封装类
        |--> 设置strategy为 CANDIDATE_FOR_IN2EXISTS_OR_MAT，表示尚不确定其执行方式是EXISTS还是物化
        |--> Item_in_subselect::single_value_transformer实现ANY/ALL转换为MIN/MAX子查询，以及IN转换为EXIST的准备
        |--> 如果left_expr只有一列，调用Item_in_subselect::single_value_transformer
               1. 实现ANY/ALL转换为MIN/MAX子查询
               2. IN转换为EXIST的准备
        |-->如果left_expr有多列，就变成一个row而不是一个标量，调用Item_in_subselect::row_value_transformer
            逻辑与Item_in_subselect::single_value_transformer一致
  |--> SELECT_LEX::flatten_subqueries 
        对于之前收集的可以转换为semi-join/anti-join/derived_table的subquery，尝试转换

|--> SELECT_LEX_UNIT::optimize  // 子查询对应的query expression
  |--> SELECT_LEX::optimize    
    |--> JOIN::optimize
      ...
      |--> JOIN::make_join_plan
        |--> 如果是子查询，调用JOIN::decide_subquery_strategy
          |--> 如果是IN/ANY/ALL子查询，且CANDIDATE_FOR_IN2EXISTS_OR_MAT，调用JOIN::compare_costs_of_subquery_strategies
            |--> 如果子查询包含非确定性元素、外部引用、不可物化结构等，则不能采用物化方式执行
            |--> Item_in_subselect::subquery_allows_materialization  // 硬性限制，无法做物化
            |--> calculate_materialization_costs // 计算物化的代价
            |--> calculate_subquery_executions   // 计算exists子查询的执行次数
            |--> 对比cost_mat和cost_exists，如果cost_mat < cost_exists，选择物化执行，并设置strategy为SUBQ_MATERIALIZATION
          |--> Item_in_subselect::finalize_materialization_transform  // 选择物化执行
            |--> Item_in_subselect::remove_in2exists_conds // 删除在prepare阶段，为exist转换注入的额外cond
            |--> 创建 subselect_hash_sj_engine
            |--> subselect_hash_sj_engine::setup
              |--> 创建Query_result_union
              |--> Query_result_union::create_result_table // 创建物化的临时表，保存在Query_result_union::table上
              |--> 在物化的临时表上建索引
              |--> 构建对应QEP_TAB/TABLE/TABLE_REF对象，便于后续读取
  |--> Item_subselect::create_iterators
    |--> Item_subselect::create_iterators
      |--> subselect_hash_sj_engine::create_iterators
          创建访问物化表的迭代访问计划和子查询的迭代访问计划，分别保存在MaterializeIterator::m_table_iterator和MaterializeIterator::QueryBlock::subquery_iterator。

|--> Item_in_optimizer::val_int
  |--> 计算左表达式的值存入缓存，标记是否为 NULL
  |--> 左表达式的缓存值为 NULL，做特殊处理
  |--> Item_in_subselect::val_bool_naked
    |--> Item_in_subselect::exec
       |--> 如果left_expr_cache未初始化，调用Item_in_subselect::init_left_expr_cache
           为 IN 子查询的左表达式（left_expr）初始化缓存机制，避免在外层查询的多次迭代中重复计算左表达式的值。
       |--> 如果left_expr_cache已初始化，调用update_item_cache_if_changed函数
           检查左表达式（left_expr）单行缓存中的项是否发生变化，并更新缓存
       |--> 如果当前新值与left_expr_cache缓存的旧值一样，则直接复用缓存的前一次子查询执行结果，提前返回
       |--> Item_subselect::exec
         |--> subselect_hash_sj_engine::exec
           |--> 如果subquery未物化，调用MaterializeIterator::Init完成物化操作
             |--> 遍历m_query_blocks_to_materialize这个数组，针对其中每个query block，调用MaterializeIterator::MaterializeQueryBlock
               |--> 每个query block对象也包含一个subquery_iterator，是对应子查询优化后的root iterator，触发执行流程，结果写入到物化table中
           |--> ExecuteExistsQuery  // subquery已物化，判断是否存在至少一行匹配结果
             |--> MaterializeIterator::Read // 检查左表达式（left_expr）的结果值是否在物化临时表中，触发执行MaterializeIterator::m_table_iterator。
    |--> 当子查询的执行过程中存在NULL值，且子查询的原始结果为false时，将最终结果标记为NULL