分页查询如何优化索引扫描

分页查询在大数据量场景下，索引扫描往往是性能瓶颈。下面从原理 → 常见问题 → 优化策略 → 示例几个层面系统说明。

一、分页查询的性能本质

常见分页 SQL：

SELECT * FROM orders
ORDER BY create_time
LIMIT 20 OFFSET 100000;

问题根源

• OFFSET 100000 表示先扫描并跳过前 10 万行
• 即使有索引，数据库仍需：
  • 按索引顺序定位
  • 逐条“跳过”这些记录
• 时间复杂度接近 O(offset + limit)

✅ 索引能避免全表扫描，但无法避免 OFFSET 带来的成本

二、分页优化的核心思路

避免 OFFSET，用“游标 / 位置”代替“跳过”

核心原则

• ✅ 利用索引的有序性
• ✅ 用上一页最后一条记录的值作为起点
• ✅ 让数据库“直接定位”，而不是“跳过”

三、常见优化方案

✅ 方案一：基于索引的游标分页（推荐）

原 SQL（慢）

SELECT *
FROM orders
ORDER BY id
LIMIT 20 OFFSET 100000;

优化后（快）

SELECT *
FROM orders
WHERE id > 100000
ORDER BY id
LIMIT 20;

✅ 利用 主键 / 唯一索引 ✅ 时间复杂度接近 O(limit)

✅ 方案二：复合索引 + 游标分页（最常见）

场景

SELECT *
FROM orders
WHERE user_id = 123
ORDER BY create_time DESC
LIMIT 20 OFFSET 1000;

优化

SELECT *
FROM orders
WHERE user_id = 123
  AND create_time < '2024-01-01 10:00:00'
ORDER BY create_time DESC
LIMIT 20;

✅ 前提：

• 有索引：

(user_id, create_time)

✅ 用上一页最后一条的 create_time 作为游标

✅ 方案三：覆盖索引（减少回表）

如果只需要部分字段：

SELECT id, create_time
FROM orders
WHERE user_id = 123
ORDER BY create_time DESC
LIMIT 20;

索引：

(user_id, create_time, id)

✅ 索引覆盖查询
✅ 避免回表，性能提升明显

✅ 方案四：延迟关联（大表分页神器）

问题 SQL

SELECT *
FROM orders
ORDER BY create_time
LIMIT 20 OFFSET 100000;

优化（先查 ID，再回表）

SELECT o.*
FROM orders o
JOIN (
    SELECT id
    FROM orders
    ORDER BY create_time
    LIMIT 20 OFFSET 100000
) t ON o.id = t.id;

✅ 子查询只扫描索引
✅ 主表只回表 20 条

⚠ 但 OFFSET 仍然存在，不如游标分页

✅ 方案五：避免 ORDER BY 非索引字段

❌ 慢：

ORDER BY create_time
-- 无索引

✅ 快：

ORDER BY create_time
-- 有索引

⚠ 排序字段必须在索引中

四、分页索引设计建议（非常重要）

✅ 正确索引示例

❌ 错误示例

ORDER BY create_time
INDEX (user_id)

❌ 索引无法用于排序

五、不同数据库注意点

MySQL

• 支持索引跳跃扫描（8.0+）
• LIMIT + OFFSET 无法走索引跳过
• 游标分页效果最好

PostgreSQL

• 支持 WHERE id > ?
• 支持 FETCH NEXT 20 ROWS ONLY
• 同样推荐游标分页

Oracle

• 使用 ROWNUM 或 FETCH FIRST
• 推荐 ROWID 或业务主键游标

六、什么时候不能用游标分页？

❌ 需要：

• 随机跳页（第 1、5、10 页）
• 总页数、总条数

✅ 只能：

• 上一页 / 下一页
• 时间轴、消息流、订单流

七、总结一句话

分页查询优化的本质：用索引定位，而不是用 OFFSET 跳过

优先级

1. ✅ 游标分页（最佳）
2. ✅ 覆盖索引 + 延迟关联
3. ⚠ OFFSET 分页（只适合小数据）

如果你愿意，可以：

• 贴出 具体 SQL + 表结构 + 数据量
• 或说明是 MySQL / PostgreSQL / Oracle

我可以帮你设计最优分页索引方案。