目前 OceanBase 数据库支持 NESTED LOOP JOIN、MERGE JOIN、HASH JOIN 三种不同的联接算法。HASH JOIN 和 MERGE JOIN 只适用于等值的联接条件，但是 NESTED LOOP JOIN 是用于任意的联接条件。

NESTED LOOP JOIN

NESTED LOOP JOIN 就是扫描一个表（外表），每读到该表中的一条记录，就去“扫描”另一张表（内表）找到满足条件的数据。这里的“扫描”可以是利用索引快速定位扫描，也可以是全表扫描。通常来说，全表扫描的性能是很差的，所以如果连接条件的列上没有索引，优化器一般就不会选择 NESTED LOOP JOIN。在 OceanBase 数据库中，计划中展示了是否能够利用索引快速定位扫描。

如下例所示，第一个计划对于内表的扫描是全表扫描，因为联接条件是 t1.c = t2.c，而 t2 没有在 c 上面的索引。第二个计划对于内表的扫描能够使用索引快速找到匹配的行，主要原因是因为联接条件是 t1.b = t2.b, 而且 t2 选择了创建在 b列上的索引 k1 作为访问路径，这样的话对于 t1 中的每一行的每个 b 值，t2 都可以根据索引快速找到满足条件的匹配行。

  1. obclient> create table t1(a int primary key, b int, c int, key k1(b));
  2. Query OK, 0 rows affected (0.24 sec)
  3. obclient> create table t2(a int primary key, b int, c int, key k1(b));
  4. Query OK, 0 rows affected (0.29 sec)
  5. obclient> explain extended_noaddr select/*+use_nl(t1 t2)*/ * from t1, t2 where t1.c = t2.c;
  6. | ===========================================
  7. |ID|OPERATOR        |NAME|EST. ROWS|COST  |
  8. -------------------------------------------
  9. |0 |NESTED-LOOP JOIN|    |1980     |623742|
  10. |1 | TABLE SCAN     |t1  |1000     |455   |
  11. |2 | TABLE SCAN     |t2  |2        |622   |
  12. ===========================================
  14. Outputs & filters:
  15. -------------------------------------
  16.   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
  17.       conds(nil), nl_params_([t1.c])
  18.   1 - output([t1.c], [t1.a], [t1.b]), filter(nil),
  19.       access([t1.c], [t1.a], [t1.b]), partitions(p0),
  20.       is_index_back=false,
  21.       range_key([t1.a]), range(MIN ; MAX)always true
  22.   2 - output([t2.c], [t2.a], [t2.b]), filter([? = t2.c]),
  23.       access([t2.c], [t2.a], [t2.b]), partitions(p0),
  24.       is_index_back=false, filter_before_indexback[false],
  25.       range_key([t2.a]), range(MIN ; MAX)
  26. obclient> explain extended_noaddr select/*+use_nl(t1 t2)*/ * from t1, t2 where t1.b = t2.b;
  27. | ============================================
  28. |ID|OPERATOR        |NAME  |EST. ROWS|COST |
  29. --------------------------------------------
  30. |0 |NESTED-LOOP JOIN|      |1980     |94876|
  31. |1 | TABLE SCAN     |t1    |1000     |455  |
  32. |2 | TABLE SCAN     |t2(k1)|2        |94   |
  33. ============================================
  35. Outputs & filters:
  36. -------------------------------------
  37.   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
  38.       conds(nil), nl_params_([t1.b])
  39.   1 - output([t1.b], [t1.a], [t1.c]), filter(nil),
  40.       access([t1.b], [t1.a], [t1.c]), partitions(p0),
  41.       is_index_back=false,
  42.       range_key([t1.a]), range(MIN ; MAX)always true
  43.   2 - output([t2.b], [t2.a], [t2.c]), filter(nil),
  44.       access([t2.b], [t2.a], [t2.c]), partitions(p0),
  45.       is_index_back=true,
  46.       range_key([t2.b], [t2.a]), range(MIN ; MAX),
  47.       range_cond([? = t2.b])

NESTED LOOP JOIN 可能会对内表进行多次全表扫描，因为每次扫描都需要从存储层重新迭代一次，这个代价相对是比较高的，所以 OceanBase 数据库支持对内表进行一次扫描并把结果物化在内存中，这样的话下次就可以直接在内存中扫描相关的数据，而不需要从存储层进行多次扫描。但是物化在内存中是有代价的，所以 OceanBase 数据库的优化器基于代价去判断是否需要物化内表。

NESTED LOOP JOIN 的一个优化变种是 BLOCKED NESTED LOOP JOIN，它的区别在于每个从外表中读取一个 block 大小的行，然后再去扫描内表找到满足条件的数据。这样的一个好处是可以减少内表的读取次数。

NESTED LOOP JOIN 通常在内表行数比较少，而且外表在联接条件的列上有索引的时候会比较好，因为内表中的每一行都可以快速的使用索引定位到相对应的匹配的数据。

MERGE JOIN

MERGE JOIN 首先会按照联接的字段对两个表进行 SORT (如果内存空间不够，就需要进行外排)，然后开始扫描两张表进行 merge。Merge 的过程会从每个表取一条记录开始匹配，如果符合关联条件，则放入结果集中；否则，将关联字段值较小的记录抛弃，从这条记录对应的表中取下一条记录继续进行匹配，直到整个循环结束。

在多对多的两张表上进行 merge 时，通常需要使用临时空间进行操作。例如 A JOIN B 使用 MERGE JOIN 时，如果对于关联字段的某一组值，在 A 和 B 中都存在多条记录 A1、A2…An、B1、B2…Bn，则为A中每一条记录 A1、A2…An，都必须在 B 中对所有相等的记录 B1、B2…Bn 进行一次匹配。这样，指针需要多次从 B1 移动到 Bn，每一次都需要读取相应的 B1…Bn 记录。将 B1…Bn 的记录预先读出来放入内存临时表中，比从原数据页或磁盘读取要快。在一些场景中，如果连接字段上有可用的索引，并且排序一致，那么可以直接跳过排序操作。

通常来说，MERGE JOIN 比较适合两个输入表已经有序的情况，否则 HASH JOIN 会更加好。下图展示了两个 MERGE JOIN 的计划，其中第一个是需要排序的，第二个是不需要排序的(因为两种表都选择了 k1 这两个索引访问路径，这两个索引本身就是按照 b 排序的)。

  1. obclient> create table t1(a int primary key, b int, c int, key k1(b));
  2. Query OK, 0 rows affected (0.24 sec)
  3. obclient> create table t2(a int primary key, b int, c int, key k1(b));
  4. Query OK, 0 rows affected (0.29 sec)
  5. obclient> explain select/*+use_merge(t1 t2)*/ * from t1, t2 where t1.c = t2.c;
  6. | =====================================
  7. |ID|OPERATOR    |NAME|EST. ROWS|COST|
  8. -------------------------------------
  9. |0 |MERGE JOIN  |    |1980     |6011|
  10. |1 | SORT       |    |1000     |2198|
  11. |2 |  TABLE SCAN|t1  |1000     |455 |
  12. |3 | SORT       |    |1000     |2198|
  13. |4 |  TABLE SCAN|t2  |1000     |455 |
  14. =====================================
  16. Outputs & filters:
  17. -------------------------------------
  18.   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
  19.       equal_conds([t1.c = t2.c]), other_conds(nil)
  20.   1 - output([t1.a], [t1.b], [t1.c]), filter(nil), sort_keys([t1.c, ASC])
  21.   2 - output([t1.c], [t1.a], [t1.b]), filter(nil),
  22.       access([t1.c], [t1.a], [t1.b]), partitions(p0)
  23.   3 - output([t2.a], [t2.b], [t2.c]), filter(nil), sort_keys([t2.c, ASC])
  24.   4 - output([t2.c], [t2.a], [t2.b]), filter(nil),
  25.       access([t2.c], [t2.a], [t2.b]), partitions(p0)
  26. obclient> explain select/*+use_merge(t1 t2),index(t1 k1),index(t2 k1)*/ * from t1, t2 where t1.b = t2.b;
  27. | =======================================
  28. |ID|OPERATOR   |NAME  |EST. ROWS|COST |
  29. ---------------------------------------
  30. |0 |MERGE JOIN |      |1980     |12748|
  31. |1 | TABLE SCAN|t1(k1)|1000     |5566 |
  32. |2 | TABLE SCAN|t2(k1)|1000     |5566 |
  33. =======================================
  35. Outputs & filters:
  36. -------------------------------------
  37.   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
  38.       equal_conds([t1.b = t2.b]), other_conds(nil)
  39.   1 - output([t1.b], [t1.a], [t1.c]), filter(nil),
  40.       access([t1.b], [t1.a], [t1.c]), partitions(p0)
  41.   2 - output([t2.b], [t2.a], [t2.c]), filter(nil),
  42.       access([t2.b], [t2.a], [t2.c]), partitions(p0)

HASH JOIN

HASH JOIN 就是用两个表中相对较小的表(通常称为 build table )根据联接条件创建 hash table，然后逐行扫描较大的表（通常称为 probe table）并通过探测 hash table 找到匹配的行。 如果 build table 非常大，构建的 hash table 无法在内存中容纳时，Oceanbase 数据库会分别将 build table 和 probe table 按照连接条件切分成多个分区（partition），每个 partition 都包括一个独立的、成对匹配的 build table 和 probe table，这样就将一个大的 HASH JOIN 切分成多个独立、互相不影响的 HASH JOIN，每一个分区的 HASH JOIN 都能够在内存中完成。在绝大多数情况下，HASH JOIN 效率比其他 JOIN 方式效率更高。

如下是 HASH JOIN 计划的示例。

  1. obclient> create table t1(a int primary key, b int, c int, key k1(b));
  2. Query OK, 0 rows affected (0.24 sec)
  3. obclient> create table t2(a int primary key, b int, c int, key k1(b));
  4. Query OK, 0 rows affected (0.29 sec)
  5. obclient> explain select/*+use_hash(t1 t2)*/ * from t1, t2 where t1.c = t2.c;
  6. | ====================================
  7. |ID|OPERATOR   |NAME|EST. ROWS|COST|
  8. ------------------------------------
  9. |0 |HASH JOIN  |    |1980     |4093|
  10. |1 | TABLE SCAN|t1  |1000     |455 |
  11. |2 | TABLE SCAN|t2  |1000     |455 |
  12. ====================================
  14. Outputs & filters:
  15. -------------------------------------
  16.   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil),
  17.       equal_conds([t1.c = t2.c]), other_conds(nil)
  18.   1 - output([t1.c], [t1.a], [t1.b]), filter(nil),
  19.       access([t1.c], [t1.a], [t1.b]), partitions(p0)
  20.   2 - output([t2.c], [t2.a], [t2.b]), filter(nil),
  21.       access([t2.c], [t2.a], [t2.b]), partitions(p0)