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

原创

于 2024-05-31 10:06:54 发布 · 1.4k 阅读

21 ·

CC 4.0 BY-SA版权

文章标签：

#oceanbase #sql #分布式数据库 #性能调优

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

连接调优

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

Nested-Loop Join：首先把 join 左侧的数据扫描出来，然后用左侧的每一行去遍历一次右表的数据，从里面找到所有能连接上的数据行做连接。它的代价 = 左表扫描的代价 + 左表的行数 * 左表每一行遍历右表的代价，即：cost(NLJ) = cost(left) + N(left) * cost(right)，时间复杂度是 O(m * n)。
Merge Join（这个应该也可以叫做 sort merge join）：先对左表和右表的连接键分别排序，然后用类似移动指针的方式不断地调整指针，找到匹配行做连接。它的代价 = 左右表排序的代价 + 左右表扫描的代价，即：cost(MJ) = sort(left) + sort(right) + cost(left) + cost(right)，时间复杂度就是排序的时间复杂度 O(n * logn)。
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 收集一下统计信息。

drop table t1;

drop table t2;

CREATE TABLE t1
WITH RECURSIVE my_cte(a, b, c) AS
(
  SELECT 1, 0, 0
  UNION ALL
  SELECT a + 1, round((a + 1) / 2, 0), round((a + 1) / 3, 0) FROM my_cte WHERE a < 1000
)
SELECT * FROM my_cte;

alter table t1 add primary key(a);

CREATE TABLE t2
WITH RECURSIVE my_cte(a, b, c) AS
(
  SELECT 1, 0, 0
  UNION ALL
  SELECT a + 1, round((a + 1) / 2, 0), round((a + 1) / 3, 0) FROM my_cte WHERE a < 1000
)
SELECT * FROM my_cte;

alter table t2 add primary key(a);

call dbms_stats.gather_table_stats('TEST', 'T1', method_opt=>'for all columns size auto', estimate_percent=>100);

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。

explain select /*+use_nl(t1, t2)*/ * from t1, t2 where t1.b = t2.b;
+---------------------------------------------------------------------------------------+
| Query Plan                                                                            |
+---------------------------------------------------------------------------------------+
| ===================================================                                   |
| |ID|OPERATOR           |NAME|EST.ROWS|EST.TIME(us)|                                   |
| ---------------------------------------------------                                   |
| |0 |NESTED-LOOP JOIN   |    |1877    |11578       |                                   |
| |1 |├─TABLE FULL SCAN  |t1  |1000    |84          |                                   |
| |2 |└─MATERIAL         |    |1000    |179         |                                   |
| |3 |  └─TABLE FULL SCAN|t2  |1000    |84          |                                   |
| ===================================================                                   |
| Outputs & filters:                                                                    |
| -------------------------------------                                                 |
|   0 - output([t1.a], [t1.b], [t1.c], [t2.a], [t2.b], [t2.c]), filter(nil), rowset=256 |
|       conds([t1.b = t2.b]), nl_params_(nil), use_batch=false                          |
|   1 - output([t1.a], [t1.b], [t1.c]), filter(nil), rowset=256                         |
|       access([t1.a], [t1.b], [t1.c]), partitions(p0)                                  |
|       is_index_back=false, is_global_index=false,                                     |
|       range_key([t1.a]), range(MIN ; MAX)always true                                  |
|   2 - output([t2.a], [t2.b], [t2.c]), filter(nil), rowset=256                         |
|   3 - output([t2.a], [t2.b], [t2.c]), filter(nil), rowset=256                         |
|       access([t2.a], [t2.b], [t2.c]), partitions(p0)                                  |
|       is_index_back=false, is_global_index=false,                                     |
|       range_key([t2.a]), range(MIN ; MAX)always true                                  |
+---------------------------------------------------------------------------------------+
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)。

explain select /*+use_nl(t1, t2)*/ * from t1, t2 where t1.a = t2.a;
+---------------------------------------------------------------------------------------+
| Query Plan                                                                            |
+---------------------------------------------------------------------------------------+
| =======================================================                               |
| |ID|