怎样优化分页查询速度

优化分页查询速度是数据库性能调优中非常常见的问题。下面从问题根源、常见优化方案、实战示例三个层面系统说明。

一、分页查询慢的根本原因

最常见的分页写法：

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

问题本质：

• 数据库需要先扫描前 1,000,000 条记录
• 再丢弃，只返回最后 20 条
• OFFSET 越大，扫描成本越高

二、常见优化方案（按推荐程度）

✅ 方案 1：基于游标（Keyset / Seek Method）✅ 最推荐

核心思想：
不用 OFFSET，用「上一页最后一条记录的值」作为起点

-- 第一页
SELECT * FROM orders
ORDER BY id
LIMIT 20;

-- 下一页（假设上一页最后 id = 100020）
SELECT * FROM orders
WHERE id > 100020
ORDER BY id
LIMIT 20;

✅ 优点：

• 几乎不受页数影响
• 可利用索引，速度极快

❌ 缺点：

• 不支持“跳到第 N 页”
• 适合「上一页 / 下一页」场景

✅ 方案 2：覆盖索引 + 延迟关联（Delayed Join）

适用于必须 OFFSET 的场景：

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

✅ 原理：

• 子查询只查索引列（id）
• 主表只回表 20 条

✅ 优点：

• 减少回表数据量
• 比 SELECT * 快很多

✅ 方案 3：合理使用索引

分页查询一定要：

• ORDER BY 字段有索引
• 索引顺序与排序一致

ORDER BY create_time DESC
-- 需要
CREATE INDEX idx_orders_create_time ON orders(create_time);

⚠️ 注意：

• ORDER BY + LIMIT 无索引 = 全表排序

✅ 方案 4：预计算 / 冗余表（极端场景）

适合：

• 数据量大（千万级以上）
• 页面固定（如排行榜）

做法：

• 定时任务生成结果表
• 前端直接查小表

✅ 方案 5：限制最大页数（业务层面）

例如：

• 只允许翻前 100 页
• “请联系客服导出全部数据”

简单但非常有效 ✅

三、不同数据库小贴士

MySQL

• 避免 ORDER BY + LIMIT 无索引
• 优先 Keyset 分页

PostgreSQL

• 支持 WHERE id > ? 游标分页
• OFFSET 同样慢

Oracle

WHERE ROWNUM > ?

四、选型建议（总结）

如果你愿意，可以告诉我：

• 使用的数据库（MySQL / PG / Oracle）
• 数据量级别
• 是否必须跳页

我可以给你针对性的 SQL 优化方案。