深分页变慢因OFFSET需扫描跳过大量数据，优化应避免OFFSET，采用基于索引的键集分页，如用WHERE id > last_id替代OFFSET，结合单列或复合索引，提升查询效率。
在使用 PostgreSQL 进行分页查询时，很多人会发现随着 OFFSET 值增大，查询速度明显变慢。比如执行类似下面的 SQL：
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 100000;
当 OFFSET 是 10 或 100 时很快，但到 10 万甚至百万级时，响应时间急剧上升。这背后的原因和优化策略如下。
为什么 LIMIT + OFFSET 越查越慢？
PostgreSQL 在执行带 OFFSET 的分页时，必须：
- 先扫描并排序前 N + M 条记录（N 是 OFFSET，M 是 LIMIT）
- 跳过前 N 条，只返回后面的 M 条
- 即使有索引，也需要从索引中读取大量条目来定位偏移位置
这意味着，OFFSET 越大，数据库做的“无用功”越多——它得一条条数过去，哪怕最终不返回这些数据。尤其是当表数据量达到百万、千万级别时，性能下降非常明显。
深分页的常见优化策略
要解决深分页性能问题，核心思路是：避免使用 OFFSET，改用基于游标的分页（Cursor-based Pagination）。
1. 使用 WHERE + 索引字段进行“键集分页”（Keyset Pagination）
适用于有序字段（如 ID、创建时间等）已建立索引的情况。
例如，第一页查询：
SELECT * FROM users ORDER BY id LIMIT 10;
假设最后一条记录的 id 是 1000，下一页从大于 1000 的地方开始：
SELECT * FROM users WHERE id > 1000 ORDER BY id LIMIT 10;
这种方式无需跳过任何行，直接利用索引定位起始点，性能稳定，且不会随“页码”加深而变慢。
2. 复合排序场景下的处理
如果排序涉及多个字段（如 created_at + id），需要联合条件过滤：
SELECT * FROM users ORDER BY created_at DESC, id DESC LIMIT 10;
下一页基于上一页最后一条记录的 (created_at, id) 值继续查询：
SELECT * FROM users WHERE (created_at < '2024-01-01 10:00:00' OR (created_at = '2024-01-01 10:00:00' AND id < 5000)) ORDER BY created_at DESC, id DESC LIMIT 10;
确保 WHERE 条件能精准跳过已读数据，同时配合复合索引提升效率。
3. 维护一个轻量中间表或物化视图
对于频繁访问的大表分页，可预先构建只包含主键和排序字段的精简表，并定期更新：
CREATE TABLE users_index AS SELECT id, created_at FROM users ORDER BY created_at DESC;
先在这个小表中定位分页范围，再通过 ID 关联回原表获取完整数据，减少扫描开销。
4. 使用游标（Cursor）进行会话级分页
在应用会话中声明游标，适合一次性大批量拉取数据的后台任务：
BEGIN;
DECLARE user_cursor CURSOR FOR SELECT * FROM users ORDER BY id;
FETCH 10 FROM user_cursor;
-- 后续继续 FETCH
注意：游标占用服务器资源，不适合高并发 Web 分页。
适用场景对比
OFFSET/LIMIT：适合总数据量小、页码靠前的简单分页，如后台管理翻几页就结束的场景。
Keyset 分页：推荐用于前端列表、无限滚动等需要深度翻页的业务，性能好且可预测。
游标方式：适合导出、批处理等非实时交互场景。
基本上就这些。深分页慢不是 PostgreSQL 的缺陷，而是 OFFSET 机制本身的代价。换一种思路，用好索引和条件过滤，就能轻松应对百万级数据的高效分页。
