关联复制表，不同查询条件，查询路径不一样。

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB or 其他组件 】OB
【 使用版本 】4.2.2.0-CE
【问题描述】
表plat_order_main 不是复制表。刚刚手动执行了plat_order_main的统计信息收集。
表sys_sales_plat_acct、sys_sales_acct_config 、sys_user 都是复制表，数据量不大。
节点位置如图

image
image1108×176 9.74 KB

explain EXTENDED
SELECT
date_format( pom.order_time_cn, ‘%Y-%m-%d’ ) AS “order_time”
,sspa.id
,ssac.id
,su.id
FROM
plat_order_main pom
LEFT JOIN sys_sales_plat_acct sspa ON pom.store_acct_id = sspa.id
LEFT JOIN sys_sales_acct_config ssac ON pom.store_acct_id = ssac.store_acct_id
LEFT JOIN sys_user su ON ssac.leader_id = su.id
WHERE
pom.process_status NOT IN ( 501, 503, 504, 500 )
AND pom.order_time_cn BETWEEN DATE_ADD( curdate(), INTERVAL - 10 DAY )
AND DATE_ADD( now(), INTERVAL 0 DAY )
GROUP BY
date_format( pom.order_time_cn, ‘%Y-%m-%d’ ),
pom.store_acct_id,
pom.shipping_country_code,
pom.logis_type_id
;
执行结果如下

image
image1117×291 8.81 KB

同样的sql。 只是将时间范围从-10.改为 -1。 查询耗时反而增加了10倍以上

image
image1153×843 45 KB

下面的sql执行结果如下附件
explain.txt (8.3 KB)

1 个赞
#4

你把第一个的详细执行计划 也发一下 这几个的表结构也发一下

#5

目前从这里看的信息

  1. DISTRIBUTED TABLE RANGE SCAN（分布式表范围扫描）：

分布式表范围扫描是指在分布式环境下，针对整个分布式表进行的范围扫描。在这种扫描方式下，OceanBase 会根据分区键将数据分布在多个节点上，并且在每个节点上执行范围扫描操作。这种方式可以充分利用集群的计算和存储资源，提高查询性能。

  1. TABLE RANGE SCAN（表范围扫描）：

表范围扫描是指在一个单个的表上进行范围扫描。这种扫描方式通常用于非分布式环境或者只涉及到一个分区的数据查询。在这种情况下，查询只会在一个节点上执行。

#6

explain1.txt (7.6 KB)

#7

由于使用了复制表，从执行计划看这里并没有跨节点的请求。
所以这个问题只是单纯的由于查询条件传值不一样，导致执行计划不一样，执行性能也就不一样了。

只要研究一下为什么第二个执行计划的性能不好。如果觉得第一个执行计划好，那就用outline 绑定它。

可以发一下 第二个执行计划的详细信息 explain extended

#8

由于你用的是复制表 查询计划会将远程或分布式执行计划优化为本地执行计划 第一个没有走分布式扫描 就是一个单表的全扫描 第二个你走了分布式的范围扫描 你提供一下表结构 看看

#9

表结构.txt (2.1 KB)

删除了大量未使用到的字段。 仅保留了sql中使用到的字段

#10

explain.txt (8.3 KB)
问题中发了第二个执行计划，我再发一遍

#11

时间改成-1以后执行计划 4号算子走的是本地的范围扫描 5、6、7算子都是跨节点的分布式扫描 这个还有回表操作 时间为-10走的本地索引 还不用回表操作 所以时间要比-1耗时少

#12

从理论上来说， -1筛选的数据要更少。查询耗时要更少才正确。
现在查询10天的数据反而需要时间更少

#13

恩。不好意思，刚才没看到那个文件。

还请教一下，前后时间对比是多大？

另外可以用下面方法重新跑一下两个查询结果，发完整的执行过程文本。
用 obclient 或 mysql 命令行跑。

  1. 重新开启一个会话窗口。
  2. set session ob_enable_show_trace = on;
  3. 跑第一个 -10 的 SQL
  4. 跑 show trace;
  5. 跑第二个 -1 的 SQL
  6. 跑 show trace;

中间注意不要报错。

#16

这样执行后，时间变得更长了。

image
image1318×285 9.13 KB

第一个trace

ob_proxy 2024-07-17 19:00:19.285899 3183010.335 ms
├── ob_proxy_partition_location_lookup 2024-07-17 19:00:19.285901 0.007 ms
├── ob_proxy_server_process_req 2024-07-17 19:00:19.286249 0.136 ms
├── ob_proxy_server_process_req 2024-07-17 19:00:19.286451 0.642 ms
├── ob_proxy_server_process_req 2024-07-17 19:00:19.287172 0.229 ms
├── ob_proxy_server_process_req 2024-07-17 19:00:19.287427 0.403 ms
├── ob_proxy_server_process_req 2024-07-17 19:00:19.287861 3179908.120 ms
└── com_query_process 2024-07-17 19:00:19.287950 3182767.497 ms
└── mpquery_single_stmt 2024-07-17 19:00:19.287952 3182767.488 ms
└── sql_execute 2024-07-17 19:00:19.288099 3182767.313 ms
├── response_result 2024-07-17 19:00:19.288658 3182766.632 ms
│ └── px_schedule 2024-07-17 19:53:22.055266 0.000 ms
└── close 2024-07-17 19:53:22.055294 0.086 ms
├── close_das_task 2024-07-17 19:53:22.055310 0.047 ms
└── end_transaction 2024-07-17 19:53:22.055371 0.002 ms

第二个trace

ob_proxy 2024-07-17 19:53:22.943116 706635.376 ms
├── ob_proxy_partition_location_lookup 2024-07-17 19:53:22.943119 0.008 ms
├── ob_proxy_server_process_req 2024-07-17 19:53:22.943171 706228.862 ms
└── com_query_process 2024-07-17 19:53:22.943590 706486.827 ms
└── mpquery_single_stmt 2024-07-17 19:53:22.943592 706486.815 ms
└── sql_execute 2024-07-17 19:53:22.943761 706486.603 ms
├── response_result 2024-07-17 19:53:22.944276 706485.870 ms
│ └── px_schedule 2024-07-17 20:05:09.430129 0.000 ms
└── close 2024-07-17 20:05:09.430158 0.154 ms
├── close_das_task 2024-07-17 20:05:09.430171 0.116 ms
└── end_transaction 2024-07-17 20:05:09.430299 0.003 ms

完整sql.txt (3.3 KB)

#17

从你这个结果看第一个跑了 3182s，第二个跑了 706s。那还是第二个快。这就是你期望的吧，范围短的更快。 实际上时间长短并不完全是看记录数多少，也看实际的执行计划。

你是否有条件在 mysql命令行下跑？
完整sql-2.txt (6.5 KB)

#18

mysql命令行执行， 返回的行数太多~~

image
image1342×481 14.2 KB

explain1.txt (13.3 KB)
trace1.txt (2.7 KB)

explain2.txt (14.1 KB)
trace2.txt (808 字节)

#19

恩。确实。
这又引入另外一个有趣的问题，很多图形化工具在返回记录数的时候是加了数量限制，所以有些查询在图形化客户端工具里的时间跟应用跑出来的时间也不完全一样。命令行里测试最接近真实情况。不过这个跟你这个问题没有什么必然联系。

还是上面那句，从你的结果看，是符合预期的吧。
当传入的时间范围少的时候，OB SQL引擎看到 pom表上的条件返回的记录数更少，所以就使用了 Nested-loop join，而第一种范围很大的那个查询，估计涉及到的记录数很多，所以采取 hash right outer join。

#20

感觉你这个结果跟上面发的结果又不一样了。这次这个结果还是第一次快

#21

不应该查询范围越小，越快吗~~
而且这个sql的执行效率很不稳定~~
当前库基本没有其他业务会进行查询。只是做了个生产dts同步。
从我了解的explain认知里，

HASH RIGHT OUTER JOIN是做的本地关联
ABLE FULL SCAN 本地扫描

NESTED-LOOP OUTER JOIN 会做远程关联
DISTRIBUTED TABLE GET 远程扫描
；
通常本地关联和扫描是比远程要快的

#22

  1. SQL不变时只是条件变化（这里是范围变小），通常认为范围越小应该越快。这个需求是合理的，实际上快不快跟这个时间范围有一定关系，但不是必然的关系。还跟 SQL 的执行计划有关。这里你的执行计划已经发生变化了 所以你不能基于范围大小去比较执行时间。而只能综合进行比较。

  2. 在第二个执行计划下性能反不如第一个是不符合常理的，这个是需要研究的。你的环境变化的因素太多。研究这个就要控制一些变量一点点对比分析。比如说强制第二个SQL走第一个执行计划看看。

  3. DISTRIBUTED TABLE GET 是 DAS 方法，应该不等同于 远程访问主键。这里看不出来有远程访问。而且已经是复制表了，远程访问也是不合理的（除非是 BUG ）
    NESTED-LOOP OUT JOIN 是外连接算法，不是远程访问。

1 个赞
#23

第一个查询10天内的那个sql。有程序会定时每天查询1遍。有这个原因在嘛。刚开始查询时也是很慢，要2600s+

第二个查询1天的sql。是我后面想优化ob的内存使用。缩减了查询时间范围。 然后就不对劲了

#24

你查询一下 这些信息 贴一下 我看看
– 查询维护窗口执行信息
select WINDOW_NAME, LAST_START_DATE, NEXT_RUN_DATE
from OCEANBASE.DBA_SCHEDULER_WINDOWS
where LAST_START_DATE is not null order by LAST_START_DATE;
– 查询所有调度作业的信息
select JOB_NAME, REPEAT_INTERVAL, LAST_START_DATE, NEXT_RUN_DATE, MAX_RUN_DURATION
from OCEANBASE.DBA_SCHEDULER_JOBS
where LAST_START_DATE is not null order by LAST_START_DATE;
– 查看某段时间内执行时间 TOP N 的请求
select /*+ parallel(15) */ sql_id, elapsed_time, trace_id, substr(query_sql, 1, 6) – 这里为了展示方便，对 query_sql 做了截断
from oceanbase.gv$ob_sql_audit
where tenant_id = 1
and IS_EXECUTOR_RPC = 0
and request_time > (time_to_usec(now()) - 10000000)
and request_time < time_to_usec(now())
order by elapsed_time desc
limit 10;