JOIN

JOIN 算子用于将两张表的数据,按照特定的条件进行联接。

JOIN 的类型主要包括内联接(Inner Join)、外联接(Outer Join)和半联接(Semi/Anti Join)三种。

OceanBase 数据库支持的 JOIN 算子主要有 NESTED LOOP JOIN (NLJ)MERGE JOIN (MJ)HASH JOIN (HJ)

NESTED LOOP JOIN (NLJ)

如下示例中,Q1 和 Q2 查询使用 Hint 指定了查询使用 NLJ。其中,0 号算子是一个 NLJ 算子。这个算子存在两个子节点,分别是 1 号算子和 2 号算子,它的执行逻辑为:

  1. 从 1 号算子读取一行。

  2. 打开 2 号算子,读取所有的行。

  3. 联接接 1和 2 号算子的输出结果,并执行过滤条件,输出结果。

  4. 重复第一步,直到 1 号算子迭代结束。

obclient>CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>CREATE TABLE t2 (d1 INT, d2 INT, PRIMARY KEY (d1));
Query OK, 0 rows affected (0.12 sec)

Q1: 
obclient>EXPLAIN SELECT /*+USE_NL(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 
     WHERE c2 = d2\G;
*************************** 1. row ***************************
Query Plan:
===========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST  |
-------------------------------------------
|0 |NESTED-LOOP JOIN|    |9782     |411238|
|1 | TABLE SCAN     |T1  |999      |647   |
|2 | MATERIAL       |    |999      |1519  |
|3 |  TABLE SCAN    |T2  |999      |647   |
===========================================
Outputs & filters:
-------------------------------------
  0 - output([T1.C2 + T2.D2]), filter(nil),
      conds([T1.C2 = T2.D2]), nl_params_(nil)
  1 - output([T1.C2]), filter(nil),
      access([T1.C2]), partitions(p0)
  2 - output([T2.D2]), filter(nil)
  3 - output([T2.D2]), filter(nil),
      access([T2.D2]), partitions(p0)

其中,MATERIAL 算子用于物化下层算子输出的数据,详细信息请参见 MATERIAL

Q2: 
obclient>EXPLAIN SELECT /*+USE_NL(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 
      WHERE c1 = d1\G;
*************************** 1. row ***************************
Query Plan:
| ==========================================
|ID|OPERATOR        |NAME|EST. ROWS|COST |
------------------------------------------
|0 |NESTED-LOOP JOIN|    |990      |37346|
|1 | TABLE SCAN     |T1  |999      |669  |
|2 | TABLE GET      |T2  |1        |36   |
==========================================
Outputs & filters:
-------------------------------------
  0 - output([T1.C2 + T2.D2]), filter(nil),
      conds(nil), nl_params_([T1.C1])
  1 - output([T1.C1], [T1.C2]), filter(nil),
      access([T1.C1], [T1.C2]), partitions(p0)
  2 - output([T2.D2]), filter(nil),
      access([T2.D2]), partitions(p0)

上述示例中,执行计划展示中的 outputs & filters 详细展示了 NESTED LOOP JOIN 算子的具体输出信息如下:

信息名称

含义

output

该算子输出的表达式。

filter

该算子上的过滤条件。

由于示例中 NLJ 算子没有设置 filter,所以为 nil

conds

联接条件。

例如 Q1 查询中 t1.c2 = t2.d2 联接条件。

nl_params_

根据 NLJ 左表的数据产生的下推参数。

例如 Q2 查询中的 t1.c1

NLJ 在迭代到左表的每一行时,都会根据 nl_params 构造一个参数,根据这个参数和原始的联接条件 c1 = d1 ,构造一个右表上的过滤条件: d1 = ?。 这个过滤条件会下推到右表上,并抽取索引上的查询范围,即需要扫描索引哪个范围的数据。在 Q2 查询中,由于存在下推条件 d1 = ?,所以 2 号算子是 TABLE GET 算子。

如下示例中,Q3 查询中没有指定任何的联接条件,0 号算子展示成了一个 NESTED-LOOP JOIN CARTESIAN,逻辑上它还是一个 NLJ 算子,代表一个没有任何联接条件的 NLJ。

Q3: 
obclient>EXPLAIN SELECT t1.c2 + t2.d2 FROM t1, t2\G;
*************************** 1. row ***************************
Query Plan:
| =====================================================
|ID|OPERATOR                  |NAME|EST. ROWS|COST  |
-----------------------------------------------------
|0 |NESTED-LOOP JOIN CARTESIAN|    |998001   |747480|
|1 | TABLE SCAN               |T1  |999      |647   |
|2 | MATERIAL                 |    |999      |1519  |
|3 |  TABLE SCAN              |T2  |999      |647   |
=====================================================
Outputs & filters:
-------------------------------------
  0 - output([T1.C2 + T2.D2]), filter(nil),
      conds(nil), nl_params_(nil)
  1 - output([T1.C2]), filter(nil),
      access([T1.C2]), partitions(p0)
  2 - output([T2.D2]), filter(nil)
  3 - output([T2.D2]), filter(nil),
      access([T2.D2]), partitions(p0)

MERGE JOIN (MJ)

如下示例中,Q4 查询使用 USE_MERGE 的 Hint 指定了查询使用 MJ。其中,0 号算子是一个 MJ 算子,它有两个子节点,分别是 1 和 3 号算子。该算子会对左右子节点的数据进行归并联接,因此,要求左右子节点的数据相对于联接列是有序的。

以 Q4 查询为例,联接条件为 t1.c2 = t2.d2,它要求表 t1 的数据是按照 c2 列排序的,表 t2 的数据是按照 d2 列排序的。在 Q4 查询中,2 号算子的输出是无序的;4 号算子的输出是按照 d2 列排序的,均不满足 MJ 对序的要求,因此,分配了 1 和 3 号算子进行排序。

Q4: 
obclient>EXPLAIN SELECT /*+USE_MERGE(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 
                WHERE c2 = d2 AND c1 + d1 > 10\G;
*************************** 1. row ***************************
Query Plan:
| ======================================
|ID|OPERATOR    |NAME|EST. ROWS|COST |
--------------------------------------
|0 |MERGE JOIN  |    |3261     |14199|
|1 | SORT       |    |999      |4505 |
|2 |  TABLE SCAN|T1  |999      |669  |
|3 | SORT       |    |999      |4483 |
|4 |  TABLE SCAN|T2  |999      |647  |
======================================
Outputs & filters:
-------------------------------------
  0 - output([T1.C2 + T2.D2]), filter(nil),
      equal_conds([T1.C2 = T2.D2]), other_conds([T1.C1 + T2.D1 > 10])
  1 - output([T1.C2], [T1.C1]), filter(nil), sort_keys([T1.C2, ASC])
  2 - output([T1.C2], [T1.C1]), filter(nil),
      access([T1.C2], [T1.C1]), partitions(p0)
  3 - output([T2.D2], [T2.D1]), filter(nil), sort_keys([T2.D2, ASC])
  4 - output([T2.D2], [T2.D1]), filter(nil),
      access([T2.D2], [T2.D1]), partitions(p0)

如下示例中,Q5 查询中联接条件是 t1.c1 = t2.d1 ,它要求表 t1 的数据是按照 c1 列排序的,表 t2 的数据是按照 d1 列排序的。在这个执行计划中,表 t2 选择了主表扫描,结果是按照 d1 列排序的,因此不需要额外分配一个 SORT 算子。理想情况下,JOIN 的左右表选择了合适的索引,索引提供的数据顺序能够满足 MJ 的要求,此时不需要分配任何 SORT 算子。

Q5: 
obclient>EXPLAIN SELECT /*+USE_MERGE(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 
     WHERE c1 = d1\G;
*************************** 1. row ***************************
Query Plan:
| =====================================
|ID|OPERATOR    |NAME|EST. ROWS|COST|
-------------------------------------
|0 |MERGE JOIN  |    |990      |6096|
|1 | SORT       |    |999      |4505|
|2 |  TABLE SCAN|T1  |999      |669 |
|3 | TABLE SCAN |T2  |999      |647 |
=====================================
Outputs & filters:
-------------------------------------
  0 - output([T1.C2 + T2.D2]), filter(nil),
      equal_conds([T1.C1 = T2.D1]), other_conds(nil)
  1 - output([T1.C2], [T1.C1]), filter(nil), sort_keys([T1.C1, ASC])
  2 - output([T1.C1], [T1.C2]), filter(nil),
      access([T1.C1], [T1.C2]), partitions(p0)
  3 - output([T2.D1], [T2.D2]), filter(nil),
      access([T2.D1], [T2.D2]), partitions(p0)

上述示例中,执行计划展示的 outputs & filters 中详细展示了 MERGE JOIN 算子的具体输出信息如下:

信息名称

含义

output

该算子输出的表达式。

filter

该算子上的过滤条件。

由于 MJ 算子没有设置 filter,所以为 nil

equal_conds

归并联接时使用的等值联接条件,左右子节点的结果集相对于联接列必须是有序的。

other_conds

其他联接条件。

例如 Q4 查询中的 t1.c1 + t2.d1 > 10

HASH JOIN (HJ)

如下示例中,Q6 查询使用 USE_HASH 的 Hint 指定了查询使用 HJ。其中,0 号算子是一个 HJ 算子,它有两个子节点,分别是 1 和 2 号算子。该算子的执行逻辑步骤如下:

  1. 读取左子节点的数据,根据联接列计算哈希值(例如 t1.c1),构建一张哈希表。

  2. 读取右子节点的数据,根据联接列计算哈希值(例如 t2.d1),尝试与对应哈希表中 t1 的数据进行联接。

Q6: 
obclient>EXPLAIN SELECT /*+USE_HASH(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 
      WHERE c1 = d1 AND c2 + d2 > 1\G;
*************************** 1. row ***************************
Query Plan:
| ====================================
|ID|OPERATOR   |NAME|EST. ROWS|COST|
------------------------------------
|0 |HASH JOIN  |    |330      |4850|
|1 | TABLE SCAN|T1  |999      |669 |
|2 | TABLE SCAN|T2  |999      |647 |
====================================
Outputs & filters:
-------------------------------------
  0 - output([T1.C2 + T2.D2]), filter(nil),
      equal_conds([T1.C1 = T2.D1]), other_conds([T1.C2 + T2.D2 > 1])
  1 - output([T1.C1], [T1.C2]), filter(nil),
      access([T1.C1], [T1.C2]), partitions(p0)
  2 - output([T2.D1], [T2.D2]), filter(nil),
      access([T2.D1], [T2.D2]), partitions(p0)

上述示例中,执行计划展示中的 outputs & filters 详细展示了 HASH JOIN 算子的输出信息如下:

信息名称

含义

output

该算子输出的表达式。

filter

该算子上的过滤条件。

由于 HJ 算子没有设置 filter,所以为 nil

equal_conds

等值联接,左右两侧的联接列会用于计算哈希值。

other_conds

其他联接条件。

例如 Q6 查询中的 t1.c2 + t2.d2 > 1