SQL优化:利用 batch join 优化分布式中的 resc

在分布式数据库中，关联查询不可避免地对分布在不同节点的表、分区进行跨节点的关联操作，数据在不同节点间的分发方式对关联查询的性能至关重要。在 NLJ(nested-loop join) 和 SPF(subplan filter) 中，需要针对驱动表中的每一行重新扫描被驱动表中的数据，如果被驱动表是分区表且分布在不同的机器上, 在分布式重新扫描(rescan)的过程中会包括资源释放、调度重启等过程，这些操作常常伴随着消息等待、同步以及网络传输操作, 影响执行效率。

• 分布式交互包括释放上一次子协调节点执行资源的控制消息、调度子协调节点的控制消息等，这些若干控制消息的 RPC 交互会带来额外的开销
• 调度重启的代价高是由于 Nested Loop Join 被驱动表可能是分布式复杂子计划，需要重新建立子计划调度关系，这个重新建立的过程会带来调度的开销
• 另外大量的网络交互也会极大的增加响应时间

下面通过一个案例分享在 OceanBase 中利用 batch join 优化分布式中的 rescan 性能问题。

问题描述

这个问题出现在一个测试环境的 4.2.1 BP3 版本中，表 a 驱动表 f 做 nested-loop join，a、f 表不在同一个 observer 并且都是非分区表。驱动表输出结果5万行左右，被驱动表 f 走高效索引，最终结果输出 11万行左右，这个SQL执行耗时要 60秒：

select  a.ah,f.aj
from db1.t1 a
join db2.t2 f on a.ah=f.ah 
where a.sxrq>=date'2020-01-01' 
and aydm in (3029,6209,6210,6365,6366,6469,8179,8180,8271,8294,8789)
and ajxzdm='2'
and jafsmc like '%脱敏%';

在本案例中，对被驱动表做了5万次rescan，产生了5万次 rpc 访问，网络上两个 observer 之间一次 rtt 平均 0.8ms，因此非常慢。OceanBase 优化器是支持 batch join 的，会减少 rescan 次数提升效率，但这里似乎没有，下文基于此进行解析。

分析过程

1. 分析 gv$ob_sql_audit

主要目的是获取两个信息：trace_id、执行耗时 61秒

+----------------------------+----------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+--------------+--------------+
| usec_to_time(request_time) | svr_ip         | plan_type | query_sql                                                                                                                                                                                                                                  | trace_id                          | elapsed_time | execute_time |
+----------------------------+----------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+--------------+--------------+
| 2024-03-26 16:04:53.124344 | xxx            |         1 | select /*+ monitor */
a.ah
from db1.t1 a
join db2.t2 f on a.ah=f.ah 
where a.sxrq>=date'2020-01-01' 
and aydm in (3029,6209,6210,6365,6366,6469,8179,8180,8271,8294,8789)
and ajxzdm='2'
and jafsmc like '%脱敏%';   | YB4289116C6E-000613F0D09FF63D-0-0 |     61845207 |     61782771 |
+----------------------------+----------------+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------+--------------+--------------+
1 row in set (1.564 sec)

2. 分析执行计划

从执行计划来看，NESTED-LOOP JOIN 算子中被驱动表走了非常高效的索引，每次查询只扫1行，要挑毛病只有驱动表 A 没走索引了、以及没使用 batch join 了（use_batch=false）：

3. 分析gv$sql_plan_monitor

执行计划看着没问题，这个 SQL 不应该这么慢，因此需要通过 gv$sql_plan_monitor 看下真实的执行情况，查询命令如下：

SELECT op_id, op, output_rows, rescan, threads,
       close_time - open_time AS open_dt,
       last_row_eof_time - first_row_time AS row_dt,
       open_time, close_time, first_row_time, last_row_eof_time
FROM (
        SELECT plan_line_id AS op_id,
               concat(lpad(' ', max(plan_depth), ' '), plan_operation) AS op,  --算子名称
               sum(output_rows) AS output_rows,  --算子输出行数
               sum(STARTS) AS rescan, --算子被 rescan 的次数
               min(first_refresh_time) AS open_time,  --算子开始(监控)时间
               max(last_refresh_time) AS close_time,  --算子结束(监控)时间
               min(first_change_time) AS first_row_time,  --算子吐出首行数据时间
               max(last_change_time) AS last_row_eof_time, --算子吐出最后一行数据时间
               count(1) AS threads  --线程数量
        FROM gv$sql_plan_monitor
        WHERE trace_id = '替换成trace_id'
        GROUP BY plan_line_id, plan_operation, plan_depth
        ORDER BY plan_line_id
) a;

结果如下：

这里可以看出几个信息：

• 驱动表输出 49557 行，近5万行，等于被驱动表 rescan 次数，被驱动表输出 11万行左右；
• 每个算子的耗时都是 61 秒，这是正常的，因为这里只做了 nested-loop join，过程是：
  • 驱动表取一行数据，开始计时，这是1号算子的开始吐行时间
  • 到被驱动表进行查询，开始计时，这是2号算子的开始吐行时间
  • 直到循环结束，1、2号算子的最后吐行时间是一样的
    因此并不能简单的认为1号算子即驱动表就耗时了 61 秒，这是不对的。需要从 5万次 rescan 入手。

4. 确认 rpc_count 和表位置

通过 GV$CDB_OB_TABLE_LOCATIONS 确认了 db1.t1、db2.t2 两张表不在一个 unit 上，因此 join 时需要跨 observer，通过 gv$ob_sql_audit 确认这个SQL执行时产生了49557 次 rpc（rpc_count=49557）。
对应的执行计划中，join 算子 use_batch=false，同样说明被驱动表 f 进行了 49557 次 rescan。

5. 开启 batch join

要减少被驱动表的 rescan 次数，需要让 batch join 生效， 这由租户的隐藏变量 _NLJ_BATCHING_ENABLED 来控制， 通过 CDB_OB_SYS_VARIABLES 可以查询隐藏变量的设置情况:

select TENANT_ID,NAME,VALUE,INFO,DEFAULT_VALUE from CDB_OB_SYS_VARIABLES where name='_NLJ_BATCHING_ENABLED';

结果如下，发现 batch join 默认关闭了：

将 batch join 开启：SET global _NLJ_BATCHING_ENABLED=true;，然后再执行SQL，耗时从61秒下降到 2.8秒，执行计划中 use_batch=true。

总结

本案例中，SQL执行慢的原因是被驱动表与驱动表不在一个 observer 上，当前使用版本 4.2.1 BP3，_NLJ_BATCHING_ENABLED 默认关闭，因此没有使用 DAS Group rescan 优化，即没有使用 batch join，导致被驱动表 rescan 次数太多，并且是跨 observer rescan，所以非常慢。

本案例中有几个注意事项：

1. gv$sql_plan_monitor 中的 rescan 不能代表真实的 rescan 次数，应该恒等于驱动表的输出行数，即使优化器进行了 DAS Group rescan 优化
2. gv$ob_sql_audit 中 rpc_count 字段可以用来确认 NLJ 算子中被驱动表的 rescan 次数
3. gv$sql_plan_monitor 中的查询出来的算子输出行的总时间结果有可能多个算子一样，这个要看具体算子的执行逻辑来解读
4. batch join 并不像官方文档说的一样总是默认开启的，后来在 release note 中找到了原因：因为一些 bug 在V4.2.1 BP2 Hotfix1 版本中，默认关闭了该优化功能，后面又在V4.2.1 BP5 默认打开该优化开关。