通用查询性能问题：对于一个分区表上亿的数据，分页查询 offset1000W，limit100，这种有没有本质解决的方法

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 OB or 其他组件 】
【 使用版本 】ob4.3.5
【问题描述】通用查询性能问题：对于一个分区表上亿的数据，分页查询 offset1000W，limit100，这种有没有本质解决的方法
【复现路径】问题出现前后相关操作
【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

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

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

2 个赞
#3

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

为了更好地帮助您解决分页查询性能较差的问题，建议您使用上述 obdiag 命令进行日志采集和根因分析，并将采集到的日志提供给我们以便确认问题所在。这些命令可以帮助我们收集与 SQL 性能相关的详细信息，从而定位性能瓶颈。

另外，为了更准确地诊断问题，请您提供以下信息：

  1. 当前执行的分页查询语句是否可以提供？例如，具体的表结构、索引设计以及查询条件等信息。
  2. 是否已经为分区表创建了合适的索引？如果有，请提供索引的设计情况。
  3. 数据库当前的负载情况如何？是否存在其他高并发查询或写入操作影响性能？
  4. 查询计划（EXPLAIN 输出）是否可以提供？这有助于了解查询执行的具体路径。
  5. OceanBase 集群的硬件配置和资源使用情况（如 CPU、内存、磁盘 I/O 等）是否可以简单描述？
  6. 是否尝试过优化查询方式，例如改用 keyset pagination 或其他替代方案？如果尝试过，请说明效果如何。

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

3 个赞
#4

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

麻烦参考小助手的方法使用obdiag收集一下相关信息。

2 个赞
#5

这种MySQL的性能好像也不咋滴吧，关键还是业务这样设计是否合理，个人挺反感研发这种深分页的操作 :rofl: :joy:

2 个赞
#6

就是发出来 大家讨论下 有没有好的方法 解决这种 翻页很大的场景。

2 个赞
#7

根据文档库中的信息，对于分页查询中 offset 值非常大的场景（例如 offset 1000W, limit 100），确实存在性能问题。这是因为 OceanBase 数据库在处理分页时，并不是直接跳过 offset 行，而是先取出 offset + limit 行数据，再丢弃前 offset 行，最终返回 limit 行数据。当 offset 特别大时，这种操作会导致性能显著下降。

以下是针对这种场景的优化方法：

1. 控制返回的总页数

  • 在业务逻辑中限制用户可以访问的最大页数。例如，如果一页显示 100 条记录，可以限制用户最多只能翻到第 1000 页（即 offset = 1000 * 100 = 100000）。超过这个范围的请求可以直接返回空结果或提示用户无法加载更多内容。
  • 参考文档：优化分页场景

2. 使用唯一键分页（Keyset Pagination）

  • 使用唯一键（如主键或联合索引）进行分页查询，而不是依赖 OFFSET 和 LIMIT。这种方式通过记录上一页最后一条记录的主键值，作为下一页查询的起点条件。
  • 示例 SQL：
    SELECT * FROM tb WHERE id > 'last_id' LIMIT 100;
    其中，last_id 是上一页最后一条记录的主键值。
  • 注意：
    • 需要确保查询字段上有索引支持，以提高查询效率。
    • 如果数据分布不均匀，可能导致某些页面的数据量不足 100 条。
  • 参考文档：根据唯一键分页

3. 利用子查询或延迟关联分页

  • 先通过子查询快速定位需要获取的 ID 段，然后再进行关联查询。这种方式可以减少不必要的数据扫描。
  • 示例 SQL：
    SELECT a.name 
FROM a, (SELECT id FROM a WHERE id LIMIT 100000, 20) b 
WHERE a.id = b.id;
  • 参考文档：利用延迟关联或子查询分页

4. 分页保序

  • 如果需要保证分页结果的顺序一致，可以在查询中显式添加排序条件。例如：
    SELECT * FROM tb ORDER BY id ASC LIMIT 100 OFFSET 10000000;
    但需要注意，这种方式仍然会受到 OFFSET 性能问题的影响，因此建议结合其他优化方法一起使用。
  • 参考文档：分页保序

5. 从业务架构层面优化

  • 如果上述方法仍无法满足性能需求，可以考虑从业务架构层面进行优化。例如：
    • 将大数据量表按业务维度拆分为多个小表，减少单表数据量。
    • 对大小账号数据进行垂直拆分，避免大账号查询对系统性能造成过大影响。
    • 在业务层实现分页逻辑，将数据库的压力转移到应用层。
  • 参考文档：SQL 调优典型场景和案例

总结

对于 offset 值特别大的分页查询场景，推荐优先使用 Keyset Pagination 方法，因为它可以有效避免 OFFSET 带来的性能问题。同时，结合业务需求和数据特点，选择合适的优化策略。如果仍有疑问或需要进一步帮助，建议参考 OceanBase 官方问答区 或联系技术支持团队。

更多信息请查看:

  1. 分页查询
  2. use_large_pages
  3. 遇到问题如何在官网上进行自主排查
  4. 章节介绍
  5. SQL 调优典型场景和案例
  6. 集群管理 FAQ

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

2 个赞
#8

得看看什么租户类型吧