很多同学在MySQL时期养成了limit offset, size 的习惯，换到OceanBase后依然这么写。结果数据量到了5000万+，翻到第100页直接超时——这是分布式数据库最典型的新手病 。
今天我带你把三种主流分页优化方案 一次性讲透，包含原理剖析+SQL写法+性能实测数据 。
方案一：传统limit offset （反面教材）
SQL写法：
sql
SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;
执行原理：
OB执行器会扫描前100020行，丢弃前100000行，只返回最后20行 。扫描行数随着页码线性增长，翻到1000页时扫描百万行，必慢。
实测： 5000万表，翻到第500页（offset=10000）→ 耗时约8秒
翻到第5000页（offset=100000）→ 耗时45秒+
结论： 严禁在生产环境使用大offset分页 。
方案二：游标分页（业界标准答案）
核心思想： 不计算“跳过的行数”，而是记住“上一页的最后一条”。
SQL写法：
sql
– 第一页：正常查询 SELECT id, order_no, amount, create_time FROM orders WHERE status = ‘COMPLETED’ ORDER BY id ASC LIMIT 20; – 第二页：传入上一页的最大id SELECT id, order_no, amount, create_time FROM orders WHERE status = ‘COMPLETED’ AND id > 10086 – 上一页最后一条的id ORDER BY id ASC LIMIT 20;
性能特征：
- 无论翻到多少页，每次扫描行数=20+索引快速定位
- 索引使用完美，响应稳定在10ms以内
适用场景：
- 瀑布流加载（App首页、消息列表）
- 按主键/唯一索引排序
缺点： 不支持指定页码跳转（用户说“我要直接去第100页”就没辙）。
方案三：覆盖索引+延迟关联（解决大宽表+跳页）
适用场景：
- 必须支持页码跳转
- 表很宽（几十个字段），不能只查索引
- 但你不能接受
limit 100000,20的45秒
原理：
先利用覆盖索引快速查出20条记录的主键ID ，再用主键回表取完整数据。覆盖索引层完全不回表，速度极快 。
SQL写法：
sql
– 步骤1：覆盖索引查出主键（只需扫描索引，不回表） SELECT id FROM orders WHERE status = ‘COMPLETED’ ORDER BY create_time DESC LIMIT 100000, 20; – 步骤2：用这20个id回表取完整字段 SELECT * FROM orders WHERE id IN (上一步查出的20个id) ORDER BY create_time DESC;
实测对比（5000万表，status过滤后200万行）：
| 方案 | offset=10000 | offset=100000 |
|---|---|---|
直接limit 10000,20
|
8.2秒 | 45.6秒 |
| 覆盖索引+延迟关联 | 0.09秒 | 0.11秒 |
结论： 延迟关联方案将45秒的查询压到了0.1秒 ，性能提升450倍。
方案四：分区裁剪（OceanBase独家优化）
如果order_time 是分区键，并且你的分页条件带时间范围，一定要开启分区裁剪 。
参数确认：
sql
SHOW VARIABLES LIKE ‘ob_enable_partition_pruning’; – 确保为 ON
原理： OB只扫描命中分区，其他分区根本不碰。
分区设计建议：
sql
CREATE TABLE orders ( id BIGINT NOT NULL, order_time DATETIME NOT NULL, … ) PARTITION BY RANGE (UNIX_TIMESTAMP(order_time)) ( PARTITION p202601 VALUES LESS THAN (UNIX_TIMESTAMP(‘2026-02-01’)), PARTITION p202602 VALUES LESS THAN (UNIX_TIMESTAMP(‘2026-03-01’)), … );
总结：分页场景选型速查表
| 业务场景 | 推荐方案 | 核心要点 |
|---|---|---|
| App首页下拉加载 | 游标分页 | WHERE id > 上一页最大id |
| 管理后台带页码跳转 | 覆盖索引+延迟关联 | 先查ID，再回表 |
| 带时间范围的大表翻页 | 游标分页+分区裁剪 | 时间分区 + 边界传递 |
| 极小表（<10万） | 传统limit offset | 没必要折腾 |
留言区互动 ：你踩过最深的翻页坑是什么？我用具体SQL帮你分析优化点。
【标签】 #SQL优化 #分页查询 #索引设计 #性能调优