偏移分页与窗口计数

示例说明

偏移分页的两个痛点：总数需要额外 COUNT 查询（两次往返）；参数无上限会被恶意构造（LIMIT 十万或 OFFSET 二十亿拖垮库）。COUNT(*) OVER () 窗口函数把总数附着在每行上——单次查询带回数据与元数据；上限约束在生成 SQL 的应用层强制执行。

技术要点

• 窗口 COUNT 免去独立总数查询
• 页大小与深度双重硬上限
• 整数除法直接算总页数

代码解读

主查询的 COUNT(*) OVER () 不带分区——对全部匹配行计数并复制到每行，应用层从首行取值；total_pages 用整数除法加每页大小减一向上取整。注释里明确两道防线：单页不超一百条、偏移不超一万——超出走键集分页或搜索引擎。

使用建议

COUNT OVER 会让部分执行计划物化全部行，大数据量拆两次查询反而更快。总数展示可以缓存或异步。管理后台之外的接口尽量用游标分页。

代码示例

-- 单次往返：数据页与总数用窗口函数一并取回
SELECT
  id,
  name,
  email,
  status,
  COUNT(*) OVER () AS total_matching
FROM customers
WHERE status = 'active'
ORDER BY created_at DESC, id DESC
LIMIT 25 OFFSET 50;

-- 分页参数带硬上限（防恶意大页与深翻页）
-- 应用层生成的 SQL 遵循：
--   page_size <= 100
--   page_number * page_size <= 10000
SELECT id, name
FROM customers
ORDER BY id
LIMIT 100 OFFSET 0;

-- 跳转特定页（第 4 页，每页 25 条）
SELECT
  id, name,
  COUNT(*) OVER () AS total,
  (COUNT(*) OVER () + 24) / 25 AS total_pages
FROM customers
ORDER BY id
LIMIT 25 OFFSET 75;