Semi-join 语义

在MySQL中,semi-join是专门针对SPJ(没有group by/order by等操作) 的IN/Exists子查询进行优化的一种join语义,起到了对外层表的过滤作用,通过将相关/非相关subquery转换为semi join来充分利用join reordering的灵活性,以期获取最高的执行效率。

MySQL的开发历史中重度依赖于nested-loop join这唯一一种join方式,这导致了其在做join ordering优化时采用了greedy search的深度优先的递归搜索策略。而semi-join的优化策略也嵌入在了这个递归搜索的过程中,其实现非常灵活多变,共有4种不同的方式。

本文主要针对semi-join(antijoin处理类似),描述其整体处理流程和具体细节代码,虽然复杂,但MySQL对于semi-join的处理并不像对于group by/distinct的实现逻辑如此杂乱,还是比较有章可循,因此允许我们顺着rewrite -> optimize的代码流程进行分析。这篇文章对新手不太友好,比较适合对MySQL优化器源码比较了解的同学,如果大家对学习MySQL优化器源码有兴趣,可以用这篇作为一个guideline,顺着文中的路线去看代码会更容易理解些。

代码分析基于MySQL 8.0.18

Notation

我们称在外层查询当中的相关表为outer table,记作ot1, ot2 ... ,分为2种:

  1. 对于其中在子查询的where 条件中依赖的外表相关表,MySQL中称为non-trivially correlated outer table,记作ct1, ct2...
  2. 不相关的外侧表,称为non-correlated outer table, 记作nt1, nt2....

内层子查询中的表,记为inner table, 记作it1, it2....

select * 
from t1, t2 on t1.a = t2.a 
where exist (
  select 1 from t3 where t3.c < 100
  and t3.b = t1.b
);

以上面这个简单查询为例,t1,t2都属于外层表,而在内层的exist子查询中,存在与t1.b列的相关性条件 "t3.b = t1.b",因此t1是一个相关表,而t2则是非相关表。

Semi join执行方式

从本质上,semi-join的执行是为了去掉内层表中所有重复的join行。以如下语句为例

select * from Country 
where 
  Country.Code in 
(select City.country 
                   from City 
                   where City.Population>1*1000*1000);

有以下4种方式

  • DuplicateWeedout

使用临时表针对join序列中,join内表产生的重复部分,做消除处理。

mysql join on 的条件_mysql join on 的条件

city表是内层子查询的表,通过在country表的rowid上建立唯一索引来对重复生成的country行数据做去重。

  • FirstMatch

在选中内部表的第1条与外表匹配的记录后,就跳过后续的匹配过程,从外层表的下一条记录重新开始。

mysql join on 的条件_mysql_02

如上图,在country的一行match到city的第一行数据(Berlin)后,直接跳过后续的match,直接回到country表获取后续行进行match。

  • LooseScan

把inner-tables中的第一个表,其数据基于索引进行分组,取每组第一条数据向后做匹配。

对如下语句

select * from Country  
where 
  Country.code in (select country_code from Satellite)

mysql join on 的条件_mysql_03

如上图,Satellite表是第一个(唯一一个)inner table,通过在index扫描时跳转来避免Satellite中产生相关列上是重复值的行,从而与外层join时不产生重复数据。

  • Materialize

这个是想法上最直观的,通过将inner-table去重,并固化成临时表,遍历outer-table,然后在固化表上去寻找匹配。

mysql join on 的条件_MySQL_04

  • Materializelookup

去重后在物化表上,针对相关列建立唯一索引,与外表通过index lookup做join。

  • Materializescan

去重后在物化表作为nest-loop join的驱动表(左表),与后续的表做join。

代码流程

Rewrite phase

  • SELECT_LEX::resolve_subquery

这个函数subquery item进行解析,收集能够unnesting为semi-join的所有subquery block,这里有很多的严格限制条件,基本来说就是只允许SPJ的subquery进行unnesting,具体条件可详见函数中的代码及注释。 可以做unnesting,会把这个subquery的item对象,加入到外层select_lex::sj_candidates中后续使用,无法做unnesting的,则调用select_transformer,尝试做IN->EXIST的转换。

  • SELECT_LEX::flatten_subqueries

这个函数做展开的rewrite,由于MySQL在一个query block中能够join的tables数是有限的(MAX_TABLES),因此做改写的这些sj_candidates,也要有一个优先级决定的先后顺序,保证重要的先转换为semi-join,后续如果table满了,则停止转换,优先级规则集合如下:

1. 相关 > 非相关
2. inner tables多 > inner tables少
3. 可以提前完成的subq > 晚完成的subq

另外,由于rewrite这个phase本身是递归完成的,因此flatten的过程是自内到外,依次把下层的subquery展开到外层query block中。

具体来说,对每个subquery

  • replace_subcondition()

替换掉外层query block中,IN-subquery item所对应的谓词表达式,替换为对应Item_func_true/Item_func_false

  • SELECT_LEX::convert_subquery_to_semijoin

将真正可以展开的(内层有table),建立sj-nest这个TABLE_LIST对象, 基本思路就是想将inner table放到外层的join list中, 内层的(oe1,... oeM)=(ie1,..., ieM)/ inner-cond 都放在外层对应的ON/WHERE条件上,这里分为3种情况:

1. subq_pred->embedding_join_nest->nested_join 存在,对应形式是

... [LEFT] JOIN  ( ... ) ON (subquery AND condition) ...

这种形式,sj-nest这个TABLE_LIST会放到JOIN后面的 ( ... ) 当中

2. subq_pred->embedding_join_nest->outer_join 不为true,形式是

... INNER JOIN tblX ON (subquery AND condition) ...

没有nested join(只有一个tblX表)且是INNER JOIN,sj-nest直接append到tblX所在这层的join list中

3. subq_pred->embedding_join_nest->outer_join 为true,形式是

... LEFT JOIN tbl ON (subquery AND condition) ...  
            ( tbl SJ (subq_tables) )
 	    |                                |
	    |<---- wrap_nest --->|

没有nested join,是left join on tbl的形式,为保证正确性,需要把tbl替换为上面这个(wrap-nest)的TABLE_LIST对象,如下

... LEFT JOIN ( tbl SJ (subq_tables) ) ON (condition AND subq_cond) ...

sj-nest是后续优化semi-join的一个重要结构,会用子查询SELECT_LEX中的内容对其进行填充,具体包括如下这些:

  1. 把subq_select中的leaf_table这个list,链接到外层的leaf table/next local链上,这样后续才能join reordering
  2. 将subq中的相关条件,也放入sj_nest->nested_join->sj_outer_exprs/sj_nest->nested_join->sj_inner_exprs中,统一设置到外层的condtition中
  3. 设置nested_join->sj_depends_on/sj_corr_tables,sj_depends_on是ot + ct,而sj_corr_tables只表示了ct,这个在选择有效的join order时会使用到。
  • SELECT_LEX::simplify_joins()

做join结构的简化和展平,outer->inner等一系列等价逻辑优化,这个函数比较复杂,其中和semi-join相关的是,它会去掉嵌套在sj-nest中的任何子sj-nest,把他们展开到一个sj-nest结构中,保证MySQL不用去处理嵌套sj-nest的情况


Optimize Phase

  • JOIN::make_join_plan()
  • pull_out_semijoin_tables :

检查sj-nest中的function dependency(EQ_REF(outer_table)),对于这种table,从sj-nest中抽取出来,放到外层join nest->join_list中,对于EQ_REF,是保证能join到,且只能join到一条的,所以对于semi-join语义中的存在性这个问题,这个it表是没有用的,抽取出来后,sj-nest的相关字段都要调整,sj-nest将可能被标记为correlated(内层条件变为了相关条件)

  • JOIN::set_semijoin_embedding

设置每个join_tab->emb_sj_nest为其table所在的sj-nest对象

  • SELECT_LEX::update_semijoin_strategies

设置每个sj_nest->nested_join->sj_enabled_strategies,为可以考虑的SJ策略。

  • optimize_semijoin_nests_for_materialization对每个sj-nest对象: 判断其是否选择做物化的这种执行方式:
  • sj_corr_tables不为空,相关子查询,不能物化
  • 调用semijoin_types_allow_materialization函数,根据其sj_outer_exprs/sj_inner_exprs的类型,判断是否可以做materialized scan 或者 materialized lookup
  • 如果可以物化,则调用Optimize_table_order::choose_table_order,对内层子查询对应的partial join list做join reordering,获取最优的执行顺序
  • calculate_materialization_costs 计算物化的相关信息,包括cost + rowcount
  • get_partial_join_cost 计算这部分join的cost/rowcount 估计物化后+去重之后行数,计算物化相关的代价,填充到 Semijoin_mat_optimize 这个针对semi-join的优化结构中。

  • Optimize_table_order::choose_table_order

外层query block的join reordering过程,这里会处理所有semi-join的可能执行策略,计算其代价,并选择最优方案,核心函数是 advance_sj_state。关于greedy search的具体流程就不描述了,由于MySQL早期无法支持hash join,它对semi-join的实现方式更多的耦合了其原有的这种left-deep, nested-loop的执行方式,为了提升效率,需要尽量的允许不同的join order可以被考虑到,因此在reordering的过程中,具体就是best_access_path()完成时,对semi-join的可能状态进行考量,我们关注在当前level选定一个table之后。

  • Optimize_table_order::advance_sj_state
  • 在POSITION结构中,包含了每种可能的sj strategy的状态变量,这个函数更新这些变量。
  • 如果在当前的join序列前提下,某种semi-join strategy所要求的结构可以被满足(所有需要的tables都已经在join前缀中),对前缀中一定范围内的tables+positions,重新考虑某种特定的semi-join执行策略下,重算cost + rowcount,替换掉原有的POSITION信息,并设置POSITION::sj_strategy为当前所考虑的这种semi-join执行策略。
  • Firstmatch :
  • POSITION::first_firstmatch_table : 表示第一个可能的first match table对象 POSITION::firstmatch_need_tables firstmatch : 需要的inner tables POSITION::first_firstmatch_rtbl : 优化中间状态,表示remaining_tables
  • 当所有sj_depends_on的outer table都在join prefix中,且当前table是第一个inner table,则标记进入FirstMatch的考虑范围,当所有inner table也都在prefix中时,得到一个完整的 duplicate_generating_range: 调用semijoin_firstmatch_loosescan_access_paths,重算整个range中的rowcount/cost
  • Loosescan :
  • POSITION::first_loosescan_table : 执行loosescan的driving table POSITION::loosescan_need_tables : 包括sj_inner_tables | sj_depends_on
  • 当所有sj_corr_tables的outer table都在join prefix中,其余outer table在后面,当前是第一个sj inner table且当前table使用index时,标记进入LooseScan的考虑范围,当所有inner tables + outer tables都在join prefix之后,得到一个完整的 duplicate_generating_range: 调用semijoin_firstmatch_loosescan_access_paths, 重算整个range中的rowcount/cost
  • Materialize :
  • semijoin_order_allows_materialization() 判断要使用的物化策略: 必须所有inner tables在join prefix上紧邻在一起,基于heuristic,如果后续表中还有outer tables,则使用Scan调
  • semijoin_mat_scan_access_paths/semijoin_mat_lookup_access_paths,更新相关POSITION的cost/rowcount,这里可以利用上optimize_semijoin_nests_for_materialization中,已经得到的物化cost + rowcount
  • DuplicateWeedOut :
  • POSITION::first_dupsweedout_table : 第一个sj inner table POSITION::dupsweedout_tables : 包括sj_inner_tables | sj_depends_on 一旦当前table是first inner table,就可以开始考虑这个策略了(最为灵活)
  • 当所有sj_inner_tables + sj_depends_on outer tables都在join prefix当中时,我们得到一个有效的duplicate_generating_range: semijoin_dupsweedout_access_paths,重算整个range内的rowcount/cost

每种strategy的策略信息,会记录在对应POSITION上相关字段中 完成greedy_search之后。

  • Optimize_table_order::fix_semijoin_strategies

在完成join order优化后,由于sj的策略是每递归到新level,添加一个新table时判断一次,有可能出现前后不同table使用不同的策略情况,这里要从后->前的遍历(后的总是最新的) ,确定最终的策略。

Note : POSITION::sj_strategy 总是记在有效range的最后一个表上的,这个函数会将最终选中的strategy信息,记录到第一个inner table上,主要是n_sj_tables / sj_strategy字段,这里n_sj_tables不止是inner table的数量,而是整个duplicate_generating_range的tables数量,由第一个inner table + n_sj_tables,即可找到整个range的最后一个table。

  • JOIN::get_best_combination

根据得到的best_positions,设置join_tabs,这里会调整sjm的相关结构,把inner tables放到primary tables的后面(tmp table之后),把sjm放到primary tables当中,并创建并创建Semijoin_mat_exec结构,放在表示sjm的那个JOIN_TAB上。

  • JOIN::setup_semijoin_materialized_table : 在该materialize table上,设置必要的结构,并创建tmp table,使用sj_inner_exprs作为table的field list并标记distinct,这样在物化完成时也就完成了内表的去重。
  • create_keyuse_for_table() 利用Semijoin_mat_optimize::mat_fields + sj_outer_exprs 创建keyuse对象,做lookup时要使用。根据是lookup/scan,计算read_cost/row_fetched这些代价信息,记录在sjm所在的JOIN_TAB对应的POSITION上
  • JOIN::set_semijoin_info() 在JOIN_TAB数组中,对于属于选定sj strategy的执行range中每个JOIN_TAB,设置m_first_sj_inner/m_last_sj_inner。

Execution Phase

在完成基本的优化后,最重要的函数就是setup_semijoin_dups_elimination,它会创建具体的semi-join执行结构,这个函数的注释中包含了非常重要的信息,描述了每种执行策略,各自可以产生怎样的QEP_TAB序列,这里的描述也将以此为基础,对各个策略的执行结构和必要函数/结构/字段做描述,并分析下MySQl是怎么保证semi-join结果正确性的。

make_join_readinfo函数中

setup_semijoin_dups_elimination

  • SJ_OPT_MATERIALIZE_LOOKUP/SJ_OPT_MATERIALIZE_SCAN

物化策略的思路是对内表做去重,其可能的执行结构

MaterializeLookup

(ot|nt)*  [ it (it)* ]  (nt)*
+------+  +==========+  +---+
   (1)         (2)        (3)

所有inner table必须邻接排列,且在所有outer tables的后面

MaterializeScan

(ot|nt)*  [ it (it)* ]  (ot|nt)*
 +------+  +==========+  +-----+
    (1)         (2)         (3)

所有inner table必须邻接排列,且在所有outer tables的前面 由于sj_strategy都标记在了第一个inner table上,而物化的inner table不在primary tables中,这里只有sjm table,因此这里无需做处理,具体执行时,在sjm所对应的QEP_TAB开始执行时,会先通过preprare_scan函数完成inner tables的物化+去重,后续无需特殊处理。

  • SJ_OPT_LOOSE_SCAN

loosescan的思路也是对内表做去重

(ot|ct|nt) [ loosescan_tbl (ot|nt|it)* it ]  (ot|nt)*
+--------+   +===========+ +=============+   +------+
   (1)           (2)          (3)              (4)

这里的要求是,所有non-trivially correlated outer tables必须都在inner tables的前面,这是必须的因为loosescan是对内表去重,如果有相关表在外层,它会决定内表的内容,因此必须要在于相关表join完成后再做去重,避免数据错误(如 本可以通过与ct做join过滤掉的tuple,却被保留下来参与了与nt/ot的join ) loosescan要求在第一个inner table上使用Index,对后续range(3)使用了first match策略,从而保证整个inner table范围内,做到了去重,在(3)的范围内,目前MySQL的实现是不允许有ot/nt的,虽然图示如此 相关执行结构: last_sj_tab,也就是整个range的最后一个tab的idx,设置给loose scan driving tab->match_tab driving tab的idx和last_sj_tab的idx,设置给last_sj_tab的firstmatch_return/match_tab loosescan_tbl->loosescan_buf/loosescan_tbl->loosescan_key_len,用来保存looscan keypart的buffer及其长度,用来在上层显式的skip掉重复index entry。

  • SJ_OPT_FIRST_MATCH

其执行思路与loose scan有些类似,也是对内表做去重

(ot|nt)*  [ it ((it|nt)* it) ]  (nt)*
 +------+  +==================+  +---+
   (1)             (2)          (3)

在inner table的range之内,是可以有nt表的,对于这种情况,MySQL会使用一种"split jump"的执行方式,即:

ot -> [it1 -> nt1 -> it3 ]
=>  1  ->  1   ->  1    ->  1
<- jump
2    ->   1
2    ->   2
<- jump
.....					
<- jump
2  ->  1
2  ->  2  ->   1    ->  1
....

这里的序号1/2是第i行的意思 通过这种方式,nt1中的每行都还是正常被join到的,只是通过jump的方式保证了inner table不会有重复行 执行结构: firstmatch_return在每个jump range最后一个inner table上,记录跳回的range的起始tab idx match_tab 记录last inner table的位置,不随jump range变化。

  • SJ_OPT_DUPS_WEEDOUT

DuplicateWeedout的思路是不同的,它基于外表做去重,对join order基本没有限制,只需要将join后的所有数据,针对outer table rowid做一次去重,保证每个outer table的一行row combination,只会join出一行结果,就足够了,这个去重是在first inner table上,创建一个tmp table,将去重使用的各个outer table rowid拼接起来作为distinct key,插入即可完成去重。

(ot|nt)*  [ it ((it|ot|nt)* (it|ot))]  (nt)*
+------+  +=========================+  +---+
  (1)                 (2)               (3)

DuplicateWeedout的执行和MySQL nested-loop的执行方式有比较强的耦合:

如果在range (2)范围内没有使用join buffer,则从range (1)中输入进来的每一行row,可以通过一个have_confluent_row标记来简化执行,也就是判断如果prefix部分到来的是不是新的一行row,则由于已经join过了,所以不再进一步处理,只有当每次时新的行时,才做处理。

这样就保证了 range (1) 范围内的outer table,是没有重复列的,而对于range (2)范围内的ot|nt,则需要将其row id加入到tmp table distinct key中来完成去重。如果在range (2) 范围内使用了join buffer,上面所描述的方案将无法成立,因为没法保证每到来一个range (1) 中的row combination,是新的一行数据,因此只能将整个range (1) + range (2)的所有 ot+nt 的rowid,作为distinct key的一部分,加入到tmp table中,完成去重。

执行结构

  • create_sj_tmp_table:

根据SJ_TMP_TABLE::TAB数组中描述的需要记录rowid的各个table,创建SJ_TMP_TABLE对象,保存在first inner table->flush_weedout_table上,以及range最后一个inner table->check_weed_out_table上。

  • create_duplicate_weedout_tmp_table: 创建实际去重的tmp table + distinct key,这个table保存在SJ_TMP_TABLE::tmp_table字段上。

总结

很抱歉文中涉及的代码细节很多也比较复杂,只能结合实际代码来研究。

另外在调试中发现了社区对于semi-join materialization代价信息的填充中的一个明显bug,具体问题是在完成sjm的优化后,需要将sjm table的代价信息存入到外层join序列的POSITION数组中,而MySQL选择了错误的position序列导致访问了不正确的结构,但这对于社区来说不重要因为这部分代价信息后续不再使用DB在做并行会使用这部分代价。为此向社区提了bug。