• 连接调优 ——OceanBase SQL 性能调优实践分享（2）

    接着上篇《索引调优 —— OceanBase SQL 性能调优实践分享（1）》的探讨，本篇文章主要是分享关于连接（join）调优的实践和心得。

    连接调优

    在OceanBase数据库中，关于连接操作，存在三种基本的连接算法，它们分别是：Nested-Loop Join、Merge Join以及Hash Join。

    1. Nested-Loop Join：首先把 join 左侧的数据扫描出来，然后用左侧的每一行去遍历一次右表的数据，从里面找到所有能连接上的数据行做连接。它的代价 = 左表扫描的代价 + 左表的行数 * 左表每一行遍历右表的代价，即：cost(NLJ) = cost(left) + N(left) * cost(right)，时间复杂度是 O(m * n)。
    2. Merge Join（这个应该也可以叫做 sort merge join）：先对左表和右表的连接键分别排序，然后用类似移动指针的方式不断地调整指针，找到匹配行做连接。它的代价 = 左右表排序的代价 + 左右表扫描的代价，即：cost(MJ) = sort(left) + sort(right) + cost(left) + cost(right)，时间复杂度就是排序的时间复杂度 O(n * logn)。
    3. Hash Join：扫描左表并对每一行建哈希表，扫描右表并哈希表中做探测，匹配并连接。它的代价 = 扫描左表的代价 + 左表的行数 * 每一行建哈希表的代价 + 扫描右表的代价 + 右表的行数 * 每一行探测哈希表的代价，即：cost(HJ) = cost(left) + N(left) * create_hash_cost + cost(right) + N(right) * probe_hash_cost。

    Nested-Loop Join

    OceanBase 里的 Nested-Loop Join 有两种执行方式，分别为非条件下压的 Nested-Loop Join 和条件下压的 Nested-Loop Join。

    我们接下来会看一下非条件下压的 NLJ 和条件下压的 NLJ 的开销有什么不同。开始前，我们做一些准备工作，先创建两张表 t1 和 t2，通过 recursive cte 分别插入 1000 行数据，然后通过系统包函数 dbms_stats.gather_table_stats 收集一下统计信息。

    1. drop table t1;
    2.  
    3. drop table t2;
    4.  
    5. CREATE TABLE t1
    6. WITH RECURSIVE my_cte(a, b, c) AS
    7. (
    8.   SELECT 1, 0, 0
    9.   UNION ALL
    10.   SELECT a + 1, round((a + 1) / 2, 0), round((a + 1) / 3, 0) FROM my_cte WHERE a < 1000
    11. )
    12. SELECT * FROM my_cte;
    13.  
    14. alter table t1 add primary key(a);
    15.  
    16. CREATE TABLE t2
    17. WITH RECURSIVE my_cte(a, b, c) AS
    18. (
    19.   SELECT 1, 0, 0
    20.   UNION ALL
    21.   SELECT a + 1, round((a + 1) / 2, 0), round((a + 1) / 3, 0) FROM my_cte WHERE a < 1000
    22. )
    23. SELECT * FROM my_cte;
    24.  
    25. alter table t2 add primary key(a);
    26.  
    27. call dbms_stats.gather_table_stats('TEST', 'T1', method_opt=>'for all columns size auto', estimate_percent=>100);
    28.  
    29. call dbms_stats.gather_table_stats('TEST', 'T2', method_opt=>'for all columns size auto', estimate_percent=>100);

    非条件下压的 Nested-Loop Join

    我们通过指定 hint /*+use_nl(t1, t2)*/ 的方式强制让下面这条 SQL 生成 NESTED-LOOP JOIN 的计划，t2 上没有合适的索引可用，主键中也没有包含 b 列，就需要先扫描 t2 的全部数据，然后通过 material 算子将它物化到内存里。意味着接下来在处理 t1 的每一行时，都要完整地遍历 t2 的所有行，相当于做了笛卡尔积，时间复杂度是 O(m * n)，所以性能非常差。

    OB 中只会出现有条件下压的 NLJ，理论上不应该出现这种非条件下压的 NLJ。

    1. explain select /*+use_nl(t1, t2)*/ * from t1, t2 where t1.b = t2.b;
    2. +---------------------------------------------------------------------------------------+
    3. | Query Plan                                                                            |
    4. +---------------------------------------------------------------------------------------+
    5. | ===================================================                                   |
    6. | |ID|OPERATOR           |NAME|EST.ROWS|EST.TIME(us)|                                   |
    7. | ---------------------------------------------------                                   |
    8. | |0 |NESTED-LOOP JOIN   |    |1877    |11578       |                                   |
    9. | |1 |├─TABLE FULL SCAN  |t1  |1000    |84          |                                   |
    10. | |2 |└─MATERIAL         |    |1000    |179         |                                   |
    11. | |3 |  └─TABLE FULL SCAN|t2  |1000    |84          |                                   |
    12. | ===================================================                                   |
    13. | Outputs & filters:                                                                    |
    14. | -------------------------------------                                                 |
    15. |   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), rowset=256 |
    16. |       conds([t1.b = t2.b]), nl_params_(nil), use_batch=false                          |
    17. |   1 - output([t1.a], [t1.b], [t1.c]), filter(nil), rowset=256                         |
    18. |       access([t1.a], [t1.b], [t1.c]), partitions(p0)                                  |
    19. |       is_index_back=false, is_global_index=false,                                     |
    20. |       range_key([t1.a]), range(MIN ; MAX)always true                                  |
    21. |   2 - output([t2.a], [t2.b], [t2.c]), filter(nil), rowset=256                         |
    22. |   3 - output([t2.a], [t2.b], [t2.c]), filter(nil), rowset=256                         |
    23. |       access([t2.a], [t2.b], [t2.c]), partitions(p0)                                  |
    24. |       is_index_back=false, is_global_index=false,                                     |
    25. |       range_key([t2.a]), range(MIN ; MAX)always true                                  |
    26. +---------------------------------------------------------------------------------------+
    27. 21 rows in set (0.050 sec)

    条件下压的 Nested-Loop Join

    我们改变连接条件为 t1.a = t2.a，并通过指定 hint /*+use_nl(t1, t2)*/ 的方式强制让下面这条 SQL 生成 NESTED-LOOP JOIN 的计划。

    可以看到在 nl_params 里面有 t1.a，意味着执行过程中会首先扫描 join 的左支（t1 表），然后把获取到的 t1 每一行的 a 值当做过滤条件，到右支上利用 t1.a = t2.a 作为 range_cond 去进行的 table get（主键查询）。因为右支 t2 表在 a 列上有主键，所以可以直接通过 table get 快速获取到任何一个具体的值，时间复杂度只有 O(m)。

    1. explain select /*+use_nl(t1, t2)*/ * from t1, t2 where t1.a = t2.a;
    2. +---------------------------------------------------------------------------------------+
    3. | Query Plan                                                                            |
    4. +---------------------------------------------------------------------------------------+
    5. | =======================================================                               |
    6. | |ID|OPERATOR               |NAME|EST.ROWS|EST.TIME(us)|                               |
    7. | -------------------------------------------------------                               |
    8. | |0 |NESTED-LOOP JOIN       |    |1000    |16274       |                               |
    9. | |1 |├─TABLE FULL SCAN      |t1  |1000    |84          |                               |
    10. | |2 |└─DISTRIBUTED TABLE GET|t2  |1       |16          |                               |
    11. | =======================================================                               |
    12. | Outputs & filters:                                                                    |
    13. | -------------------------------------                                                 |
    14. |   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), rowset=256 |
    15. |       conds(nil), nl_params_([t1.a(:0)]), use_batch=true                              |
    16. |   1 - output([t1.a], [t1.b], [t1.c]), filter(nil), rowset=256                         |
    17. |       access([t1.a], [t1.b], [t1.c]), partitions(p0)                                  |
    18. |       is_index_back=false, is_global_index=false,                                     |
    19. |       range_key([t1.a]), range(MIN ; MAX)always true                                  |
    20. |   2 - output([t2.a], [t2.b], [t2.c]), filter(nil), rowset=256                         |
    21. |       access([GROUP_ID], [t2.a], [t2.b], [t2.c]), partitions(p0)                      |
    22. |       is_index_back=false, is_global_index=false,                                     |
    23. |       range_key([t2.a]), range(MIN ; MAX),                                            |
    24. |       range_cond([:0 = t2.a])                                                         |
    25. +---------------------------------------------------------------------------------------+

    在 OceanBase 中，一般情况下都只会选择条件下压的 Nested-Loop Join。除非没有等值连接条件，并且 Nested-Loop Join 也没有合适的索引可用，才有可能会考虑生成非条件下压的 Nested-Loop Join，生成这种非条件下压的 NLJ 的概率非常小，一般都会用 HJ 或 MJ 代替，如果出现，就要仔细分析下是否合理了。

    Subplan Filter

    这里需要多提一句和子查询相关的 subplan filter 算子，这个算子的执行方式跟 Nested Loop Join 类似，和 NLJ 一样，也需要创建合适的索引或者主键，让条件能够下压。

    我们还继续用之前创建的两张表 t1 和 t2，主键都建在两张表的 a 列上。下面这条 SQL 是 subplan filter 没有合适的索引或主键的情况，计划和没有条件下压的 NLJ 几乎一模一样，这里不再赘述了：

    1. explain select /*+no_rewrite*/ a from t1 where b > (select b from t2 where t1.b = t2.b);
    2. +--------------------------------------------------------------------------------------------+
    3. | Query Plan                                                                                 |
    4. +--------------------------------------------------------------------------------------------+
    5. | =================================================                                          |
    6. | |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                                          |
    7. | -------------------------------------------------                                          |
    8. | |0 |SUBPLAN FILTER   |    |334     |45415       |                                          |
    9. | |1 |├─TABLE FULL SCAN|t1  |1000    |60          |                                          |
    10. | |2 |└─TABLE FULL SCAN|t2  |2       |46          |                                          |
    11. | =================================================                                          |
    12. | Outputs & filters:                                                                         |
    13. | -------------------------------------                                                      |
    14. |   0 - output([t1.a]), filter([t1.b > subquery(1)]), rowset=256                             |
    15. |       exec_params_([t1.b(:0)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=false |
    16. |   1 - output([t1.a], [t1.b]), filter(nil), rowset=256                                      |
    17. |       access([t1.a], [t1.b]), partitions(p0)                                               |
    18. |       is_index_back=false, is_global_index=false,                                          |
    19. |       range_key([t1.a]), range(MIN ; MAX)always true                                       |
    20. |   2 - output([t2.b]), filter([:0 = t2.b]), rowset=256                                      |
    21. |       access([t2.b]), partitions(p0)                                                       |
    22. |       is_index_back=false, is_global_index=false, filter_before_indexback[false],          |
    23. |       range_key([t2.a]), range(MIN ; MAX)always true                                       |
    24. +--------------------------------------------------------------------------------------------+

    下面这条 SQL 是 subplan filter 有合适主键的情况，计划和有条件下压的 NLJ 几乎一模一样，这里不再赘述了：

    1. explain select /*+no_rewrite*/ a from t1 where b > (select b from t2 where t1.a = t2.a);
    2. +-------------------------------------------------------------------------------------------+
    3. | Query Plan                                                                                |
    4. +-------------------------------------------------------------------------------------------+
    5. | =======================================================                                   |
    6. | |ID|OPERATOR               |NAME|EST.ROWS|EST.TIME(us)|                                   |
    7. | -------------------------------------------------------                                   |
    8. | |0 |SUBPLAN FILTER         |    |334     |18043       |                                   |
    9. | |1 |├─TABLE FULL SCAN      |t1  |1000    |60          |                                   |
    10. | |2 |└─DISTRIBUTED TABLE GET|t2  |1       |18          |                                   |
    11. | =======================================================                                   |
    12. | Outputs & filters:                                                                        |
    13. | -------------------------------------                                                     |
    14. |   0 - output([t1.a]), filter([t1.b > subquery(1)]), rowset=256                            |
    15. |       exec_params_([t1.a(:0)]), onetime_exprs_(nil), init_plan_idxs_(nil), use_batch=true |
    16. |   1 - output([t1.a], [t1.b]), filter(nil), rowset=256                                     |
    17. |       access([t1.a], [t1.b]), partitions(p0)                                              |
    18. |       is_index_back=false, is_global_index=false,                                         |
    19. |       range_key([t1.a]), range(MIN ; MAX)always true                                      |
    20. |   2 - output([t2.b]), filter(nil), rowset=256                                             |
    21. |       access([GROUP_ID], [t2.b]), partitions(p0)                                          |
    22. |       is_index_back=false, is_global_index=false,                                         |
    23. |       range_key([t2.a]), range(MIN ; MAX)always true,                                     |
    24. |       range_cond([:0 = t2.a])                                                             |
    25. +-------------------------------------------------------------------------------------------+

    在 OceanBase 中，并不是所有的子查询都能被 unnest，有时候根据 sql 的语义，只能用 subplan filter 算子进行计算。subplan filter 的执行方式跟 Nested Loop Join 类似，所以也需要创建合适的索引避免出现非条件下压的 subplan filter。

    Hash Join

    • cost(NLJ) = cost(left) + N(left) * cost(right)
    • cost(HJ) = cost(left) + N(left) * create_hash_cost + cost(right) + N(right) * probe_hash_cost

    上面列出了 NLJ 和 HJ 的代价计算公式，因为我数学水平太低（惭愧），所以这里就先不求甚解，免去数学推导的过程，直接说结论 “ OB 的优化器如果要在 NLJ 和 HJ 中进行选择，在满足下面两个条件时，才会选择 NLJ ” ：

    1. 右表有合适的索引或者主键。
    2. 右表的行数 / 左表的行数超过一定的阈值，在 OB 中，大概是 20 这样（20 是博士和义博他们给出的经验值，实测不止 20，大概在 100 的样子，这里不乱猜测，后面有空儿了会学习一下代价计算这块儿的代码）。

    我们来验证一下上面的结论，先创建两张表：第一张无主键表 t1 有 10 行；第二张有主键表 t2 主键是 a 列，有 1000 行。

    1. drop table t1;
    2.  
    3. drop table t2;
    4.  
    5. CREATE TABLE t1
    6. WITH RECURSIVE my_cte(a, b, c) AS
    7. (
    8.   SELECT 1, 0, 0
    9.   UNION ALL
    10.   SELECT a + 1, round((a + 1) / 2, 0), round((a + 1) / 3, 0) FROM my_cte WHERE a < 10
    11. )
    12. SELECT * FROM my_cte;
    13.  
    14. CREATE TABLE t2
    15. WITH RECURSIVE my_cte(a, b, c) AS
    16. (
    17.   SELECT 1, 0, 0
    18.   UNION ALL
    19.   SELECT a + 1, round((a + 1) / 2, 0), round((a + 1) / 3, 0) FROM my_cte WHERE a < 1000
    20. )
    21. SELECT * FROM my_cte;
    22.  
    23. alter table t2 add primary key(a);
    24.  
    25. call dbms_stats.gather_table_stats('TEST', 'T1', method_opt=>'for all columns size auto', estimate_percent=>100);
    26.  
    27. call dbms_stats.gather_table_stats('TEST', 'T2', method_opt=>'for all columns size auto', estimate_percent=>100);

    当用不上 t2 表的主键时，如果要生成 NLJ，则会生成非条件下压的 NLJ，显然代价会很大，所以这里会生成一个 HJ 的计划：

    1. explain select * from t1, t2 where t1.b = t2.b;
    2. +---------------------------------------------------------------------------------------+
    3. | Query Plan                                                                            |
    4. +---------------------------------------------------------------------------------------+
    5. | =================================================                                     |
    6. | |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                                     |
    7. | -------------------------------------------------                                     |
    8. | |0 |HASH JOIN        |    |1       |4           |                                     |
    9. | |1 |├─TABLE FULL SCAN|t1  |1       |2           |                                     |
    10. | |2 |└─TABLE FULL SCAN|t2  |1       |2           |                                     |
    11. | =================================================                                     |
    12. | Outputs & filters:                                                                    |
    13. | -------------------------------------                                                 |
    14. |   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), rowset=256 |
    15. |       equal_conds([t1.b = t2.b]), other_conds(nil)                                    |
    16. |   1 - output([t1.a], [t1.b], [t1.c]), filter(nil), rowset=256                         |
    17. |       access([t1.a], [t1.b], [t1.c]), partitions(p0)                                  |
    18. |       is_index_back=false, is_global_index=false,                                     |
    19. |       range_key([t1.a]), range(MIN ; MAX)always true                                  |
    20. |   2 - output([t2.b], [t2.a], [t2.c]), filter(nil), rowset=256                         |
    21. |       access([t2.b], [t2.a], [t2.c]), partitions(p0)                                  |
    22. |       is_index_back=false, is_global_index=false,                                     |
    23. |       range_key([t2.__pk_increment]), range(MIN ; MAX)always true                     |
    24. +---------------------------------------------------------------------------------------+

    当能用上 t2 表的主键列 t2.a 去进行 table get，而且右表和左表有明显的大小表关系时（右表 t2 有 1000 行，左表 t1 只有 10 行），这里就会生成一个 NLJ 的计划：

    1. explain select * from t1, t2 where t1.a = t2.a;
    2. +---------------------------------------------------------------------------------------+
    3. | Query Plan                                                                            |
    4. +---------------------------------------------------------------------------------------+
    5. | =======================================================                               |
    6. | |ID|OPERATOR               |NAME|EST.ROWS|EST.TIME(us)|                               |
    7. | -------------------------------------------------------                               |
    8. | |0 |NESTED-LOOP JOIN       |    |10      |165         |                               |
    9. | |1 |├─TABLE FULL SCAN      |t1  |10      |3           |                               |
    10. | |2 |└─DISTRIBUTED TABLE GET|t2  |1       |16          |                               |
    11. | =======================================================                               |
    12. | Outputs & filters:                                                                    |
    13. | -------------------------------------                                                 |
    14. |   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), rowset=256 |
    15. |       conds(nil), nl_params_([t1.a(:0)]), use_batch=true                              |
    16. |   1 - output([t1.a], [t1.b], [t1.c]), filter(nil), rowset=256                         |
    17. |       access([t1.a], [t1.b], [t1.c]), partitions(p0)                                  |
    18. |       is_index_back=false, is_global_index=false,                                     |
    19. |       range_key([t1.__pk_increment]), range(MIN ; MAX)always true                     |
    20. |   2 - output([t2.a], [t2.b], [t2.c]), filter(nil), rowset=256                         |
    21. |       access([GROUP_ID], [t2.a], [t2.b], [t2.c]), partitions(p0)                      |
    22. |       is_index_back=false, is_global_index=false,                                     |
    23. |       range_key([t2.a]), range(MIN ; MAX),                                            |
    24. |       range_cond([:0 = t2.a])                                                         |
    25. +---------------------------------------------------------------------------------------+

    Merge Join

    • cost(MJ) = cost(left) + cost(right) + sort(left) + sort(right)
    • cost(HJ) = cost(left) + N(left) * hash_cost + cost(right) + N(right) * probe_cost

    上面列出了 NLJ 和 HJ 的代价计算公式，它们都需要完整地扫描左表和右表，区别在于 Merge Join 要分别对两侧在连接键上进行排序，而哈希则是对左侧建哈希表、对右侧做哈希探测。相比于构建哈希表和哈希探测（O(n)）来说，做排序的代价会更高（O(nlogn)）。因此，在一般情况下，一定是 Hash Join 优于 Merge Join。

    只有在一些非常特殊的场景下，才会选择 Merge Join 。比如两侧都有序时，就可以省去排序的代价，直接做一次归并就好了。

    还是拿一开始的两张表 t1 和 t2 做实验，t1 和 t2 都有建在 a 列上的主键。

    1. drop table t1;
    2.  
    3. drop table t2;
    4.  
    5. CREATE TABLE t1
    6. WITH RECURSIVE my_cte(a, b, c) AS
    7. (
    8.   SELECT 1, 0, 0
    9.   UNION ALL
    10.   SELECT a + 1, round((a + 1) / 2, 0), round((a + 1) / 3, 0) FROM my_cte WHERE a < 1000
    11. )
    12. SELECT * FROM my_cte;
    13.  
    14. alter table t1 add primary key(a);
    15.  
    16. CREATE TABLE t2
    17. WITH RECURSIVE my_cte(a, b, c) AS
    18. (
    19.   SELECT 1, 0, 0
    20.   UNION ALL
    21.   SELECT a + 1, round((a + 1) / 2, 0), round((a + 1) / 3, 0) FROM my_cte WHERE a < 1000
    22. )
    23. SELECT * FROM my_cte;
    24.  
    25. alter table t2 add primary key(a);
    26.  
    27. call dbms_stats.gather_table_stats('TEST', 'T1', method_opt=>'for all columns size auto', estimate_percent=>100);
    28.  
    29. call dbms_stats.gather_table_stats('TEST', 'T2', method_opt=>'for all columns size auto', estimate_percent=>100);

    如果连接条件都是本来就有序的主键 a 列，则会生成 merge join。

    1. explain select * from t1, t2 where t1.a = t2.a;
    2. +---------------------------------------------------------------------------------------+
    3. | Query Plan                                                                            |
    4. +---------------------------------------------------------------------------------------+
    5. | =================================================                                     |
    6. | |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                                     |
    7. | -------------------------------------------------                                     |
    8. | |0 |MERGE JOIN       |    |1000    |301         |                                     |
    9. | |1 |├─TABLE FULL SCAN|t1  |1000    |84          |                                     |
    10. | |2 |└─TABLE FULL SCAN|t2  |1000    |84          |                                     |
    11. | =================================================                                     |
    12. | Outputs & filters:                                                                    |
    13. | -------------------------------------                                                 |
    14. |   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), rowset=256 |
    15. |       equal_conds([t1.a = t2.a]), other_conds(nil)                                    |
    16. |       merge_directions([ASC])                                                         |
    17. |   1 - output([t1.a], [t1.b], [t1.c]), filter(nil), rowset=256                         |
    18. |       access([t1.a], [t1.b], [t1.c]), partitions(p0)                                  |
    19. |       is_index_back=false, is_global_index=false,                                     |
    20. |       range_key([t1.a]), range(MIN ; MAX)always true                                  |
    21. |   2 - output([t2.a], [t2.b], [t2.c]), filter(nil), rowset=256                         |
    22. |       access([t2.a], [t2.b], [t2.c]), partitions(p0)                                  |
    23. |       is_index_back=false, is_global_index=false,                                     |
    24. |       range_key([t2.a]), range(MIN ; MAX)always true                                  |
    25. +---------------------------------------------------------------------------------------+

    如果连接条件都是无序的 b 列，则会生成 hash join。

    1. explain select * from t1, t2 where t1.b = t2.b;
    2. +---------------------------------------------------------------------------------------+
    3. | Query Plan                                                                            |
    4. +---------------------------------------------------------------------------------------+
    5. | =================================================                                     |
    6. | |ID|OPERATOR         |NAME|EST.ROWS|EST.TIME(us)|                                     |
    7. | -------------------------------------------------                                     |
    8. | |0 |HASH JOIN        |    |1877    |481         |                                     |
    9. | |1 |├─TABLE FULL SCAN|t1  |1000    |84          |                                     |
    10. | |2 |└─TABLE FULL SCAN|t2  |1000    |84          |                                     |
    11. | =================================================                                     |
    12. | Outputs & filters:                                                                    |
    13. | -------------------------------------                                                 |
    14. |   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), rowset=256 |
    15. |       equal_conds([t1.b = t2.b]), other_conds(nil)                                    |
    16. |   1 - output([t1.a], [t1.b], [t1.c]), filter(nil), rowset=256                         |
    17. |       access([t1.a], [t1.b], [t1.c]), partitions(p0)                                  |
    18. |       is_index_back=false, is_global_index=false,                                     |
    19. |       range_key([t1.a]), range(MIN ; MAX)always true                                  |
    20. |   2 - output([t2.a], [t2.b], [t2.c]), filter(nil), rowset=256                         |
    21. |       access([t2.a], [t2.b], [t2.c]), partitions(p0)                                  |
    22. |       is_index_back=false, is_global_index=false,                                     |
    23. |       range_key([t2.a]), range(MIN ; MAX)always true                                  |
    24. +---------------------------------------------------------------------------------------+

    如果连接条件都是无序的 b 列，并通过指定 hint 强制要求生成 merge join 的计划，那么执行计划中一定会被先分配 sort 算子，通过 sort 算子进行排序后再进行 merge join，这种计划的代价往往会比 hash join 高。

    1. explain select /*+ USE_MERGE(t1 t2) */ * from t1, t2 where t1.b = t2.b;
    2. +---------------------------------------------------------------------------------------+
    3. | Query Plan                                                                            |
    4. +---------------------------------------------------------------------------------------+
    5. | ===================================================                                   |
    6. | |ID|OPERATOR           |NAME|EST.ROWS|EST.TIME(us)|                                   |
    7. | ---------------------------------------------------                                   |
    8. | |0 |MERGE JOIN         |    |1877    |750         |                                   |
    9. | |1 |├─SORT             |    |1000    |294         |                                   |
    10. | |2 |│ └─TABLE FULL SCAN|t1  |1000    |84          |                                   |
    11. | |3 |└─SORT             |    |1000    |294         |                                   |
    12. | |4 |  └─TABLE FULL SCAN|t2  |1000    |84          |                                   |
    13. | ===================================================                                   |
    14. | Outputs & filters:                                                                    |
    15. | -------------------------------------                                                 |
    16. |   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), rowset=256 |
    17. |       equal_conds([t1.b = t2.b]), other_conds(nil)                                    |
    18. |       merge_directions([ASC])                                                         |
    19. |   1 - output([t1.a], [t1.b], [t1.c]), filter(nil), rowset=256                         |
    20. |       sort_keys([t1.b, ASC])                                                          |
    21. |   2 - output([t1.a], [t1.b], [t1.c]), filter(nil), rowset=256                         |
    22. |       access([t1.a], [t1.b], [t1.c]), partitions(p0)                                  |
    23. |       is_index_back=false, is_global_index=false,                                     |
    24. |       range_key([t1.a]), range(MIN ; MAX)always true                                  |
    25. |   3 - output([t2.a], [t2.b], [t2.c]), filter(nil), rowset=256                         |
    26. |       sort_keys([t2.b, ASC])                                                          |
    27. |   4 - output([t2.a], [t2.b], [t2.c]), filter(nil), rowset=256                         |
    28. |       access([t2.a], [t2.b], [t2.c]), partitions(p0)                                  |
    29. |       is_index_back=false, is_global_index=false,                                     |
    30. |       range_key([t2.a]), range(MIN ; MAX)always true                                  |
    31. +---------------------------------------------------------------------------------------+

    总结

    这三个 join 方式是数据库最基础的知识点，最后简单地总结一下需要记住的几个点：

    1. 计划里绝大多数的情况都只会选择有条件下压的 Nested-Loop Join，如果选择了非条件下压的 NLJ，需要创建合适的索引让计划变成有条件下压的 NLJ，或者指定 hint 变更 join 的方式。与联接操作相关的 Hint 详见官网链接：OceanBase分布式数据库-海量数据 笔笔算数
    2. subplan filter 的执行方式跟 Nested Loop Join 类似，所以也需要创建合适的索引避免出现非条件下压的 subplan filter。
    3. 计划里如果有 merge join，往往是可以利用下层算子的有序性。如果下层算子都是无序的，计划中在 merge 前还专门分配了一些 sort 进行排序，需要分析下是否需要通过 hint 改成使用 hash join 进行连接。
    4. 如果没有可用的索引和主键，也没有有序性，那么一般 hash join 的代价是最低的。
  • 相关阅读:
    数字IC后端实现 |TSMC 12nm 与TSMC 28nm Metal Stack的区别
    国产管理软件勒索病毒大爆发
    通过el-tree 懒加载树，创建国家地区四级树
    线性代数笔记：三阶矩阵的特征值计算
    NPDP如何续证?操作指南来了！
    2023年9月8日
    package.json scripts 脚本的作用
    ActiveMQ反序列化漏洞复现(CVE-2015-5254)
    面向项目版本差异性的漏洞识别技术研究
    数字先锋 | 农业农村部大数据公共平台基座上线，天翼云擎起乡村振兴新希望！
  • 原文地址：https://blog.csdn.net/OceanBaseGFBK/article/details/138543813