执行计划看着没问题，但是查询非常慢

OceanBase 技术问题
#1

【 使用环境 】测试环境
【 OB or 其他组件 】Oceanbse 4.2.5.bp1
【 使用版本 】4.2.5.bp1
【问题描述】
该SQL在MySQL中执行时间为0.79s

image
image1977×127 50 KB

执行计划如下：
MySQL执行计划.txt (11.3 KB)

在Oceanbase在执行时间为120s

image
image1952×453 102 KB

执行计划如下：
Oceanbase执行计划.txt (13.2 KB)

看着Oceanbase的执行计划没有什么问题，但是却执行很慢。
对比表结构也是一致的。
所以，能麻烦看看是否是什么bug吗？

【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

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

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

#3

根据文档库中的信息，对于您提到的在 OceanBase 中执行时间显著长于 MySQL 的情况，可以从以下几个方面进行分析和处理：

  1. 慢查询日志分析：
    根据文档信息，可以通过比较 SQL 执行时间与 trace_log_slow_query_watermark 值来判断是否为慢查询。如果 SQL 执行时间超过了 trace_log_slow_query_watermark 值，那么可以认为这是一个慢查询。在这种情况下，OceanBase 数据库将在 OBServer 日志中打印 Slow Query 消息，从而可以通过 OBServer 日志来查找慢 SQL（参考文档片段 2）。

  2. 计划缓存机制的影响：
    OceanBase 数据库引入了计划缓存（Plan Cache）机制供相同的 SQL 共享执行计划。虽然这通常能提升性能，但在某些情况下也可能导致问题。例如，SQL 第一次请求进来的参数值并不一定满足大多数请求的场景，可能是个“少数派”，反而可能会导致“多数派”性能不佳。另外，如果数据分布变化了，老计划不适用于新场景，也会导致 SQL 性能下降（参考文档片段 3）。建议检查是否首次执行时生成了次优的执行计划，并考虑绑定正确的计划。

  3. 连接算法的选择：
    在 OceanBase 数据库 V4.0 之前的版本中，由于优化器的二阶段架构，通常会出现分布式计划选错连接算法的问题，大多数场景都是错误选择了 Nested Loops Join、Merge Join。遇到此类慢 SQL 问题，需要 DBA 手动控制优化器选择 Hash Join 算法（大多数场景都可以通过这个方式解决慢 SQL 问题）。尽管您使用的是 4.2.5 版本，但仍然可以检查当前执行计划是否选择了合适的连接算法（参考文档片段 6）。

  4. 统计信息的准确性：
    统计信息收集过程对执行计划的生成至关重要。如果统计信息不够准确或缺失，可能导致优化器生成次优的执行计划。从 V4.2.0 版本开始，OceanBase 支持统计信息收集监控诊断功能，主要用于监控统计信息的收集状态，反馈统计信息的收集情况（参考文档片段 7）。请确保统计信息是最新的，并且没有收集失败的情况。

  5. 资源密集型查询的影响：
    复杂的 SQL 查询可能导致数据库集群性能波动明显，使得集群的并发能力降低，业务不可控。复杂的 SQL 查询可能导致数据库集群性能波动明显，使得集群的并发能力降低，业务不可控。尽量简化 SQL 语句，避免使用复杂函数如正则表达式相关的 regexp_substr 等，这些函数执行效能通常较低（参考文档片段 9）。

建议操作步骤：

  • 检查慢查询日志：确认该 SQL 是否被标记为慢查询，并查看具体的执行细节。
  • 验证计划缓存：确保首次执行时生成的执行计划是合理的，必要时手动绑定正确的执行计划。
  • 审查连接算法：检查当前执行计划是否选择了最优的连接算法，如 Hash Join。
  • 更新统计信息：确保所有相关表的统计信息是最新的，并且没有收集失败的情况。
  • 简化查询逻辑：尽量减少复杂函数的使用，优化 SQL 语句以提高执行效率。

如果经过上述检查后问题仍未解决，建议使用 OceanBase 敏捷诊断工具 obdiag 收集更详细的诊断信息，并联系 OceanBase 技术支持获取进一步帮助。

检索到的文档如下:

  1. 计划缓存概述
  2. trace_log_slow_query_watermark
  3. SQL 调优典型场景和案例
  4. 定位和分析慢查询（二）
  5. 设置并行执行参数
  6. 常见的业务问题
  7. 统计信息收集监控诊断
  8. 与 MySQL 兼容性对比
  9. JOIN 表使用建议
  10. SQL 查询导致的异常
#4

推荐使用obdiag采集sql可以分析出sql具体是哪一步慢了
obdiag收集SQL性能问题信息
obdiag gather scene run --scene=observer.perf_sql --env “{db_connect=’-hxx -Pxx -uxx -pxx -Dxx’, trace_id=‘xx’}”

#5

老师老师，您好，我们最后通过
SELECT /+leading(bmxx) use_hash(friend relation)/ … … 解决了，应该是ob查询优化器可能没选择好执行计划吧。这个可以不知道后续oceanbase可以优化吗？

另外，我们想咨询一下，因为我的生产用的是云oceanbase，我们在云oceanbase上反馈有一个查询效率异常（ob执行不出结果，而mysql只需要几秒）的问题，类似的形态是：
t1 join t2 on t2.id = (select t3.val from t3 where t1.id = t3.id …)

原因是oceanbase 针对这种形态的连接，现在的OB是先做两表的笛卡尔，在计算子查询。
而，MySQL 是先计算子查询，然后再进行连接。

云oceanbase的技术童鞋说会在后续修复，我想咨询一下，社区版也会一并修复的吗？因为云ob是用4.2，但是我们测试环境用了4.3，可以的话，我们是希望4.3也能修复一下这个问题~

#6

关于您提到的两个问题，我将分别进行解答：

1. 关于通过 /*+ leading(bmxx) use_hash(friend relation)*/ 解决执行计划选择问题

根据您的描述，确实是因为 OceanBase 查询优化器没有选择最优的执行计划导致了性能问题。您通过使用 SQL 提示（Hint）手动指定了连接顺序和连接算法，成功解决了这个问题。

后续是否会优化：

  • 社区版与企业版同步更新：OceanBase 社区版和企业版在功能上保持高度一致，尤其是涉及到核心优化器的功能改进。因此，如果企业版中对查询优化器进行了改进，这些改进通常也会被合并到社区版中。
  • 优化器持续改进：OceanBase 团队一直在努力提升查询优化器的能力，包括但不限于更智能地选择连接顺序和连接算法。未来版本中，优化器会更加智能化，减少需要手动干预的情况。

建议：

  • 保持版本更新：建议您定期关注并升级到最新的稳定版本，以确保能够享受到最新的性能优化和功能改进。
  • 反馈问题：如果您发现类似的问题，可以通过官方渠道反馈给 OceanBase 团队，帮助他们更好地了解和改进优化器。

2. 关于云 OceanBase 和社区版对子查询优化的不同处理

您提到的查询形态是：

t1 join t2 on t2.id = (select t3.val from t3 where t1.id = t3.id ...)

当前行为差异：

  • MySQL：先计算子查询，然后再进行连接。
  • OceanBase：目前的行为是先做两表的笛卡尔积，再计算子查询，这可能导致性能问题。

云 OceanBase 的修复计划：

  • 您提到云 OceanBase 技术团队已经在计划修复这个问题，这是一个积极的信号。通常情况下，云 OceanBase 和社区版之间的功能改进会保持同步，尤其是在涉及性能优化的关键领域。

社区版是否会修复：

  • 4.2 版本：由于云 OceanBase 使用的是 4.2 版本，如果云 OceanBase 团队在 4.2 中修复了这个问题，那么这个修复很可能会被合并到社区版 4.2 中。
  • 4.3 版本：虽然您提到测试环境使用的是 4.3 版本，但 4.3 是一个较新的版本，可能还没有包含所有来自 4.2 的修复。不过，考虑到 4.3 是一个长期支持的版本，预计未来的更新中也会包含类似的优化。

建议：

  • 关注官方发布日志：您可以密切关注 OceanBase 官方的发布日志和更新说明，了解是否有针对子查询优化的改进。
  • 参与社区讨论：加入 OceanBase 社区论坛或 GitHub 仓库中的讨论，提出您的需求和建议，这样可以加速相关功能的开发和优化。
  • 提前规划升级：如果您的生产环境中依赖这种查询形态，建议提前规划好升级路径，确保能够在第一时间应用到最新的优化。

总结

  1. 执行计划选择问题：通过使用 Hint 解决了当前问题，未来版本的优化器会更加智能，减少手动干预的需求。
  2. 子查询优化问题：云 OceanBase 已经计划修复，预计社区版也会同步跟进，特别是在 4.2 和 4.3 版本中。

希望这些信息能帮助您更好地理解和解决遇到的问题。如果有更多疑问，欢迎继续咨询！

更多信息请查看:

  1. OceanBase 集群运维管理之用户实操
  2. 常见的业务问题
  3. 用户自定义变量
  4. 系统视图总览
  5. 列存 FAQ
  6. OceanBase 数据库在 MySQL 模式租户下的扩展功能
  7. SQL 性能问题的典型场景和排查思路
  8. OceanBase 数据库和 MySQL 兼容性介绍

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

#7

商业版和社区版bug修复排期是同步进行的，你说的执行计划问题不一定是优化器的问题这个需要考虑多个方面问题。不过ob的优化器随着版本迭代是一直在进行优化升级的。