多表关联查询比mysql慢一倍

【 使用环境 】测试环境
【 OB or 其他组件 】OB
【 使用版本 】v4.3.1
【问题描述】多表关联查询比mysql慢一倍
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)

我理解慢是因为数据表分布在不同数据节点导致的，这个有什么办法优化吗？

【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

用户问题: “OceanBase v4.3.1 多表关联查询性能比 MySQL 慢一倍，如何优化？”

推荐使用 obdiag 工具进行日志采集，以便我们能够更好地定位问题并提供优化建议。

日志采集场景: SQL性能问题
日志采集命令: obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

请使用上述命令进行日志采集，并将生成的日志压缩包发送给我们。这将帮助我们了解您的查询执行情况和性能瓶颈。

为了更准确地定位问题，请您提供以下信息：

1. 您当前执行的多表关联查询语句是什么？
2. 是否已经检查过统计信息是否准确？是否尝试过重新收集统计信息？
3. 您是否尝试过调整查询的连接顺序或使用不同的连接算法（例如从 Nested Loops Join 切换到 Hash Join）？
4. 您是否可以提供具体的表结构和索引信息？
5. 当前查询的执行计划是什么样的？您可以通过 EXPLAIN 命令来获取查询的执行计划。

具体信息可参考以下文档:

1. What’s New
2. 常见的业务问题

附上敏捷诊断工具 obdiag 使用帮助链接

这个推测是可以有进一步证据去验证的。先看看这个结果。

• 先看解析执行计划

obclient 连接数据库租户，用 explain 命令跑一下你的 SQL。

explain extended_Noaddr 
select * from xxx
;

看一下执行计划了，表和索引信息

建议使用小助手提供的obdiag提取下sql信息，应该是涉及到分布式执行计划导致

创建表组，将相关表添加至同一个表组中，这样他们的主副本就会在同一个LS当中。
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001052198
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001052200

已经创建tablegroup并把三个关联表（非分区表）放入tablegroup，表leader也都在相同ip。
但看查询计划还是有DISTRIBUTED TABLE RANGE SCAN。TABLET_ID也都在同一个LS_ID上

200137 1001
200138 1001
200139 1001

查一下表的分布情况，看是否确实是分布在不同节点上的：
select tenant.tenant_name,meta.table_id, tab.table_name,
partition_id,zone,svr_ip,svr_port, case when role=1 then ‘leader’ when role=2
then ‘follower’ else null end as role, tab.primary_zone from
__all_virtual_meta_table meta inner join __all_tenant tenant on
meta.tenant_id=tenant.tenant_id inner join __all_virtual_table tab on
meta.tenant_id=tab.tenant_id and meta.table_id=tab.table_id where
tenant.tenant_id=1001 and tab.table_name=‘xxx’ order by
tenant.tenant_name,table_name,partition_id,zone;

针对您提到的情况，尽管您已经采取了将非分区表放入同一个Table Group并将这些表的Leader副本放置在同一台机器上的措施，但仍然出现了DISTRIBUTED TABLE RANGE SCAN。这表明OceanBase优化器仍然认为需要跨节点执行查询。这里有几个可能的原因以及相应的优化建议：

1. 查询优化器的选择：OceanBase的查询优化器会根据查询的复杂性和数据分布情况自动生成执行计划。即使表位于同一Table Group中，优化器也可能决定使用分布式扫描，特别是当涉及到跨表的JOIN操作时。 [2]

2. 非分区表的限制：非分区表本质上只有一个分区，这意味着即使它们位于同一个Table Group中，也无法像分区表那样通过分区裁剪来减少数据访问量。因此，对于非分区表，除非显式地使用PARALLEL Hint来启动分区内并行查询，否则不会自动启动并行查询。 [2]

3. Hint的使用：对于非分区表，可以尝试使用/*+ PARALLEL(dop_value) */ Hint来强制启用并行查询。例如，如果您希望设置并行度为4，可以在查询语句前加上/*+ PARALLEL(4) */。这可能会帮助减少查询时间，尤其是在数据量较大的情况下。 [2]

4. 统计信息的准确性：确保统计信息是最新的也很重要。如果统计信息不准确，优化器可能会做出错误的决策。定期运行ANALYZE TABLE命令来更新统计信息可以帮助优化器做出更好的决策。 [2]

5. 查询本身的复杂性：如果查询涉及多个表的JOIN操作，并且这些表之间没有合适的索引，那么即使表位于同一个Table Group中，优化器也可能选择分布式扫描。在这种情况下，考虑添加适当的索引以帮助优化器更好地裁剪数据范围。

6. 硬件资源限制：最后，还需要考虑服务器的硬件资源是否足够。如果资源不足，即使是优化过的查询也可能表现出较差的性能。

综上所述，您可以尝试以下步骤来优化查询性能：

• 在查询语句前添加/*+ PARALLEL(dop_value) */ Hint，尝试不同的并行度值以找到最佳设置。
• 更新统计信息，确保优化器能够做出正确的决策。
• 审查查询语句和表结构，考虑添加合适的索引来帮助优化器进行数据裁剪。
• 检查服务器资源，确保有足够的CPU和内存来支持查询执行。

希望这些建议能对您有所帮助。如果问题依然存在，建议进一步收集详细的诊断信息并通过官方渠道寻求帮助。

详情请参考:

1. 分布式执行和并行查询
2. 启用和关闭并行查询
3. 并行查询的执行

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

执行计划发一下看看

|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|

|0 |HASH JOIN | |22 |126094 |
|1 |e[32m├─e[0mNESTED-LOOP JOIN | |5 |94 |
|2 |e[32m│ e[0me[33m├─e[0mTABLE GET |team |1 |7 |
|3 |e[32m│ e[0me[33m└─e[0mDISTRIBUTED TABLE RANGE SCAN|pms(pomap_mappings_FACTORY_ID_IDX) |5 |87 |
|4 |e[32m└─e[0mTABLE RANGE SCAN |phai(pomap_hse_ats_infos_CREATEDTIME_IDX)|15522 |121914 |

关联表加入tablegroup后，执行速度比原来快了近一倍

如果追求极致，可以创建SHARDING = 'NONE’的表组，让所有leader副本集中在一个机器试试

这个有违背表分组的设计目的。全放在一起还不如啥都不做，直接 primary zone 设置为单 zone。

创建表组，将相关表添加至同一个表组中，是可以解决关联查询慢的问题。
亲测，可以达到mysql的性能水平。
建议，当关联查询出现性能问题时，采用表组解决。
多说一句，通过查询计划分析，创建合适的索引可以大幅提高查询性能。