如何用ROW_NUMBER窗口函数实现SQL高效分页查询

为什么ROW_NUMBER()分页比传统的OFFSET/LIMIT更可控？核心就在于它生成的是按逻辑顺序排列的唯一序号，而非依赖物理偏移量。深分页时OFFSET 10000 LIMIT 20会强制数据库扫描前一万行再跳过，效率极低；而ROW_NUMBER()直接通过序号过滤，避免无谓扫描。更关键的是，当排序字段存在重复值时，OFFSET可能因索引顺序微调导致漏行或重复，而ROW_NUMBER()在排序后分配稳定序号，结果一目了然。

ROW_NUMBER() 分页为什么比 OFFSET/LIMIT 更可控？

因为 ROW_NUMBER() 在排序后为每一行分配唯一序号，分页逻辑完全由你自己定义——不依赖数据库执行计划对偏移量的估算，避免 OFFSET 10000 LIMIT 20 那种“先跳过一万行再取20行”的低效扫描。尤其在深分页或排序字段存在重复值时，ROW_NUMBER() 能保证结果稳定，而 OFFSET 可能因索引顺序微调导致漏行或重复。

怎么写一个安全、可复用的 ROW_NUMBER() 分页查询？

核心是把 ROW_NUMBER() 放进子查询或 CTE，再在外层过滤序号范围。必须注意三点：

• 排序字段必须包含唯一性字段（如主键 id），否则相同排序值会触发非确定性编号，导致同一页数据每次查询不一致
• ORDER BY 子句在 ROW_NUMBER() 内部和外部必须完全一致，否则编号顺序与最终输出顺序可能错位
• 不要在 WHERE 中直接对 ROW_NUMBER() 别名做条件——它不能在同级查询中被引用，必须套一层子查询

正确写法示例（PostgreSQL/SQL Server）：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC, id DESC) AS rn
  FROM posts
  WHERE status = 'published'
) t
WHERE t.rn BETWEEN 21 AND 40;

MySQL 8.0+ 和旧版 MySQL 的写法差异

MySQL 8.0+ 原生支持窗口函数，写法同上；但 MySQL 5.7 或更早版本不支持 ROW_NUMBER()，强行模拟会导致性能灾难（比如用变量自增 + 多次排序）。如果无法升级，建议：

• 改用基于游标的分页（WHERE created_at < 上一次的最大值），前提是排序字段有索引且业务允许“下一页”模式
• 用应用层缓存前几页结果，避免反复查深页
• 拒绝提供“跳转到第100页”这种功能——用户真要翻那么远，大概率是在找特定内容，应该引导用搜索代替分页

为什么加了索引还是慢？常见陷阱

即使给 ORDER BY 字段建了索引，ROW_NUMBER() 查询仍可能全表扫描，原因通常是：

• WHERE 条件中的字段没包含在联合索引里，导致数据库放弃使用排序索引（例如 ORDER BY created_at 有索引，但 WHERE status = 'published' 没覆盖）
• 排序字段类型和索引字段类型不一致（如 created_at 是 TIMESTAMP，但索引建在 DATETIME 上）
• 使用了函数包裹排序字段（ORDER BY DATE(created_at)），使索引失效

验证方式：执行 EXPLAIN，重点看 key 是否命中预期索引、rows 是否接近总行数。

实际部署时，最易被忽略的是排序字段的唯一性补全——哪怕业务上认为 created_at 不会重复，也要显式加上 id 或其他主键字段，否则生产环境一旦出现毫秒级时间重复，分页就会错乱。