OceanBase JOIN

2021-06-29 16:42 更新

JOIN 算子用于將兩張表的數(shù)據(jù)，按照特定的條件進(jìn)行聯(lián)接。

JOIN 的類型主要包括內(nèi)聯(lián)接（INNER JOIN）、外聯(lián)接（OUTER JOIN）和半聯(lián)接（SEMI/ANTI JOIN）三種。

OceanBase 數(shù)據(jù)庫支持的 JOIN 算子主要有 NESTED LOOP JOIN (NLJ)、MERGE JOIN (MJ) 和 HASH JOIN (HJ)。

NESTED LOOP JOIN (NLJ)

如下示例中，Q1 和 Q2 查詢使用 HINT 指定了查詢使用 NLJ。其中，0 號(hào)算子是一個(gè) NLJ 算子。這個(gè)算子存在兩個(gè)子節(jié)點(diǎn)，分別是 1 號(hào)算子和 2 號(hào)算子，它的執(zhí)行邏輯為：

  1. 從 1 號(hào)算子讀取一行。

  2. 打開 2 號(hào)算子，讀取所有的行。

  3. 聯(lián)接接 1和 2 號(hào)算子的輸出結(jié)果，并執(zhí)行過濾條件，輸出結(jié)果。

  4. 重復(fù)第一步，直到 1 號(hào)算子迭代結(jié)束。

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 算子用于物化下層算子輸出的數(shù)據(jù)，詳細(xì)信息請(qǐng)參見 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)

上述示例中，執(zhí)行計(jì)劃展示中的 outputs & filters 詳細(xì)展示了 NESTED LOOP JOIN 算子的具體輸出信息如下：

信息名稱

含義

output

該算子輸出的表達(dá)式。

filter

該算子上的過濾條件。

由于示例中 NLJ 算子沒有設(shè)置 filter，所以為 nil。

conds

聯(lián)接條件。

例如 Q1 查詢中 t1.c2 = t2.d2 聯(lián)接條件。

nl_params_

根據(jù) NLJ 左表的數(shù)據(jù)產(chǎn)生的下推參數(shù)。

例如 Q2 查詢中的 t1.c1。

NLJ 在迭代到左表的每一行時(shí)，都會(huì)根據(jù) nl_params 構(gòu)造一個(gè)參數(shù)，根據(jù)這個(gè)參數(shù)和原始的聯(lián)接條件 c1 = d1 ，構(gòu)造一個(gè)右表上的過濾條件： d1 = ?。 這個(gè)過濾條件會(huì)下推到右表上，并抽取索引上的查詢范圍，即需要掃描索引哪個(gè)范圍的數(shù)據(jù)。在 Q2 查詢中，由于存在下推條件 d1 = ?，所以 2 號(hào)算子是 TABLE GET 算子。

如下示例中，Q3 查詢中沒有指定任何的聯(lián)接條件，0 號(hào)算子展示成了一個(gè) NESTED-LOOP JOIN CARTESIAN，邏輯上它還是一個(gè) NLJ 算子，代表一個(gè)沒有任何聯(lián)接條件的 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 號(hào)算子是一個(gè) MJ 算子，它有兩個(gè)子節(jié)點(diǎn)，分別是 1 和 3 號(hào)算子。該算子會(huì)對(duì)左右子節(jié)點(diǎn)的數(shù)據(jù)進(jìn)行歸并聯(lián)接，因此，要求左右子節(jié)點(diǎn)的數(shù)據(jù)相對(duì)于聯(lián)接列是有序的。

以 Q4 查詢?yōu)槔?，?lián)接條件為 t1.c2 = t2.d2，它要求 t1 的數(shù)據(jù)是按照 c2 排序的，t2 的數(shù)據(jù)是按照 d2 排序的。在 Q4 查詢中，2 號(hào)算子的輸出是無序的；4 號(hào)算子的輸出是按照 d2 排序的，均不滿足 MERGE JOIN 對(duì)序的要求，因此，分配了 1 和 3 號(hào)算子進(jìn)行排序。

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 查詢中聯(lián)接條件是 t1.c1 = t2.d1 ，它要求 t1 的數(shù)據(jù)是按照 c1 排序的，t2 的數(shù)據(jù)是按照 d1 排序的。在這個(gè)執(zhí)行計(jì)劃中，t2 選擇了主表掃描，結(jié)果是按照 d1 有序的，因此不需要額外分配一個(gè) SORT 算子。理想情況下，JOIN 的左右表選擇了合適的索引，索引提供的數(shù)據(jù)順序能夠滿足 MJ 的要求，此時(shí)不需要分配任何 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)

上述示例中，執(zhí)行計(jì)劃展示的 outputs & filters 中詳細(xì)展示了 MERGE JOIN 算子的具體輸出信息如下：

信息名稱

含義

output

該算子輸出的表達(dá)式。

filter

該算子上的過濾條件。

由于 MJ 算子沒有設(shè)置 filter，所以為 nil。

equal_conds

歸并聯(lián)接時(shí)使用的等值聯(lián)接條件，左右子節(jié)點(diǎn)的結(jié)果集相對(duì)于聯(lián)接列必須是有序的。

other_conds

其他聯(lián)接條件。

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

HASH JOIN (HJ)

如下示例中，Q6 查詢使用 USE_HASH 的 HINT 指定了查詢使用 HJ。其中，0 號(hào)算子是一個(gè) HJ 算子，它有兩個(gè)子節(jié)點(diǎn)，分別是 1 和 2 號(hào)算子。該算子的執(zhí)行邏輯步驟如下：

  1. 讀取左子節(jié)點(diǎn)的數(shù)據(jù)，根據(jù)聯(lián)接列計(jì)算哈希值（例如 t1.c1），構(gòu)建一張哈希表。

  2. 讀取右子節(jié)點(diǎn)的數(shù)據(jù)，根據(jù)聯(lián)接列計(jì)算哈希值（例如 t2.d1），嘗試與對(duì)應(yīng)哈希表中 t1 的數(shù)據(jù)進(jìn)行聯(lián)接。

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)

上述示例中，執(zhí)行計(jì)劃展示中的 outputs & filters 詳細(xì)展示了 HASH JOIN 算子的輸出信息如下：

信息名稱

含義

output

該算子輸出的表達(dá)式。

filter

該算子上的過濾條件。

由于 HJ 算子沒有設(shè)置 filter，所以為 nil。

equal_conds

等值聯(lián)接，左右兩側(cè)的聯(lián)接列會(huì)用于計(jì)算哈希值。

other_conds

其他聯(lián)接條件。

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


以上內(nèi)容是否對(duì)您有幫助：
在線筆記
App下載
App下載

掃描二維碼

下載編程獅App

公眾號(hào)
微信公眾號(hào)

編程獅公眾號(hào)