在PostgreSQL 15中如何通过SQL子查询加速大数据分页？-SQL-PHP中文网

键集分页和延迟关联可大幅提升分页性能；标量子查询无法优化，反而使LIMIT/OFFSET更慢，因其需为每行执行嵌套循环，复杂度达O(n×m)。

在postgresql 15中如何通过sql子查询加速大数据分页？

直接用子查询加速分页，基本行不通——它反而会让 LIMIT/OFFSET 更慢。真正有效的做法是绕开子查询，改用键集分页（Keyset Pagination）或延迟关联（Deferred Join），这两者在 PostgreSQL 15 中能稳定压到毫秒级响应。

为什么标量子查询会让分页更卡

很多人试图用类似这样的写法“优化”分页：

SELECT * FROM (
  SELECT *, (SELECT COUNT(*) FROM orders o2 WHERE o2.user_id = o1.user_id) AS order_count
  FROM orders o1
  ORDER BY o1.id
  LIMIT 10 OFFSET 100000
) t;

这看似只查一页，但 PostgreSQL 15 的执行器仍需为 OFFSET 100000 扫描并计算前 100000 行的子查询结果——每个标量子查询都触发一次嵌套循环，实际代价是 O(n × m)，不是 O(n)。

相关子查询无法被物化或提前剪枝，优化器不会把它转成 JOIN 或 LATERAL 自动优化
即使外层加了 WHERE id > ?，只要子查询还在 SELECT 列表里，就仍要为每一行执行
如果子查询涉及聚合或多表关联，内存和 CPU 开销会指数级上升

用延迟关联替代子查询分页

核心思路：先用轻量查询拿到目标页的主键（比如 id），再用这些主键回查完整数据。这样避免了在排序/分页主流程中执行任何子查询。

人工智能数字技术机器人全息大脑大数据分析矢量素材(EPS)

这是一款人工智能数字技术机器人全息大脑大数据分析矢量素材，格式为 EPS，含 JPG 预览图。

下载

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

内层子查询只查 id，走索引即可，不读行数据，I/O 极小
外层 JOIN 是等值匹配，走主键索引，回表成本可控
必须确保 ORDER BY 字段有高效索引（如 CREATE INDEX idx_orders_status_id ON orders(status, id)）
注意：若 status 选择性差（比如 90% 都是 'completed'），这个方案收益会打折扣

用键集分页彻底摆脱 OFFSET

这是 PostgreSQL 15 下最推荐的方案，完全不依赖子查询，也不需要知道总页数。它靠上一页最后一条记录的排序字段值来定位下一页：

-- 第一页（无 WHERE）
SELECT id, user_id, amount, created_at 
FROM orders 
WHERE status = 'completed' 
ORDER BY id 
LIMIT 20;
<p>-- 第二页（用上一页最后 id，比如上一页最后 id 是 10042）
SELECT id, user_id, amount, created_at 
FROM orders 
WHERE status = 'completed' AND id > 10042 
ORDER BY id 
LIMIT 20;</p>

每次查询都命中索引范围扫描（Index Scan using xxx on orders），跳过所有前置行
不受数据插入/删除影响（比如有人在第 1 页和第 2 页之间插入新订单，键集分页不会漏或重）
不能支持“跳到第 87 页”这种随机页码；适合无限滚动、下拉加载场景
复合排序时（如 ORDER BY created_at DESC, id DESC），WHERE 条件要写成 WHERE created_at ，否则会漏数据