为什么 OB 分页查询 limit 大偏移量会特别慢，怎么优化？

OceanBase 技术问题
#1

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

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

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

4 个赞
#3

用户问题: “OceanBase 分页查询时，使用大偏移量的 limit 导致查询特别慢，如何优化？”

对于这个问题，虽然 obdiag 目前没有直接针对分页查询优化的根因分析场景，但是可以使用 obdiag 收集 SQL 性能相关的信息，以便我们进一步分析为什么大偏移量会导致查询变慢。

推荐使用 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’}”

说明：在上述命令中，请将 -h127.0.0.1 -P2881 -utest@test -p****** -Dtest 替换为您的实际数据库连接信息，trace_id 用于指定跟踪 ID，如果您不清楚具体的 trace_id，可以暂时忽略这一参数。

另外，为了确定具体的问题，在此需要您提供一些信息：

  1. 您当前使用的 OceanBase 版本是什么？
  2. 可以提供导致查询变慢的具体 SQL 语句吗？
  3. 在执行该 SQL 语句时，是否有大量的并发请求或数据更新操作？
  4. 能否提供查询计划 (EXPLAIN 输出) 和表结构信息 (DESCRIBE 表名)?
  5. 您是否已经尝试过优化索引或重写 SQL 语句来解决这个问题？

以上信息将有助于我们更准确地定位和解决问题。谢谢！

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

3 个赞
#4

mark~

1 个赞
#5

加油

3 个赞
#6

加油

2 个赞
#7

第一种，创建覆盖索引，单纯的优化索引，来加速，减少回表带来的随机io，很多种情况下不适合，适合中小型的分页
SELECT id, user_id, create_time
FROM tbl
ORDER BY create_time
LIMIT 100000, 20;
CREATE INDEX idx_create_time ON tbl
(create_time, id, user_id);

第二种， 延时关联，物化的方式优化ORDER BY id LIMIT的问题
SELECT * FROM
(SELECT id FROM tbl WHERE ORDER BY id LIMIT 100000, 10) temp
LEFT JOIN tbl t ON temp.id = t.id

第三种，分段式范围分页 适合超大的数据的分页
SELECT * FROM tbl
WHERE create_time >= ‘2025-01-01 00:00:00’
AND create_time < ‘2025-02-01 00:00:00’
ORDER BY id
LIMIT 0, 20;
下一页：
SELECT * FROM tbl
WHERE create_time >= ‘2025-01-01 00:00:00’
AND create_time < ‘2025-02-01 00:00:00’
AND id > 上一页最大ID
ORDER BY id
LIMIT 20;
第四种 主键定位分页
第一页
SELECT id, user_id, create_time
FROM tbl
ORDER BY id
LIMIT 20;
第二页
假设最后一条是id = 10000，第二页：
SELECT id, user_id, create_time
FROM tbl
WHERE id > 10000
ORDER BY id
LIMIT 20;

你上面的发的这样的方式 几乎不适合大分页

#8

大偏移 LIMIT 是先扫再丢数据，改用主键游标分页是最优解法

1 个赞