关系数据库为何避免深分页

在关系数据库中，“深分页”（例如 LIMIT 1000000, 20，即取第 100 万条之后的 20 条）往往性能很差，数据库设计和使用中通常建议避免。原因主要来自执行机制、索引使用和代价模型几个方面：

1. 深分页需要“先扫描再丢弃”

以常见 SQL 为例：

SELECT * FROM orders
ORDER BY create_time
LIMIT 1000000, 20;

数据库实际执行过程通常是：

1. 按 create_time 排序（或沿索引顺序扫描）
2. 读取前 1000020 条记录
3. 丢弃前 1000000 条
4. 返回最后 20 条

也就是说：偏移量越大，扫描和临时丢弃的数据越多。

2. 即便有索引，也逃不过 OFFSET 代价

假设 create_time 有索引：

• 索引可以帮助“有序读取”
• 但 OFFSET 1000000 仍意味着要沿索引走 100 万步

索引减少了“排序”成本，但没有减少跳过的行数。

3. 可能涉及回表（随机 IO）

如果查询不是“覆盖索引”：

SELECT * FROM orders ORDER BY create_time LIMIT 1000000, 20;

数据库需要：

• 从索引拿到主键
• 回表取完整行数据

前 100 万次回表虽然是“顺序索引”，但回表本身是随机 IO，深分页会放大 IO 成本。

4. 排序和临时表开销

如果没有合适索引：

• 数据库必须做 filesort
• 可能使用磁盘临时表
• 数据量越大，排序代价越高

深分页 = 大数据集排序 + 大量丢弃。

5. 结果不稳定（一致性问题）

深分页还容易遇到：

• 数据插入/删除导致“重复或漏读”
• 翻页过程中顺序变化

例如：

• 第 1 页读到 id=100
• 翻页时新数据插入，第 2 页又出现 id=100

常见优化方式（避免深分页）

✅ 游标分页（Cursor / Seek 分页）

SELECT * FROM orders
WHERE create_time < :last_time
ORDER BY create_time DESC
LIMIT 20;

优点：

• 利用索引直接定位
• 不扫描已读过的数据
• 性能稳定

✅ 延迟关联

SELECT * FROM orders
JOIN (
  SELECT id FROM orders
  ORDER BY create_time
  LIMIT 1000000, 20
) t ON orders.id = t.id;

减少回表行数。

✅ 业务层限制

• 不允许跳页
• 只允许“上一页 / 下一页”
• 限制最大页数

一句话总结

关系数据库避免深分页，是因为 OFFSET 越大，扫描、排序和丢弃的数据越多，索引也无法消除这种线性代价，最终导致性能急剧下降。

如果你愿意，我也可以结合 MySQL / PostgreSQL / Oracle 的具体执行计划来进一步说明。