如何通过ROW_NUMBER()函数在SQL中实现分页查询，并优雅地处理长尾词查询？

本文共计1065个文字，预计阅读时间需要5分钟。

在SQL Server或PostgreSQL中，使用`ROW_NUMBER()`是一种跨版本、跨场景稳定的实现带排序的精确分页的方式。这种方法看起来简洁，但遇到重复排序字段值时可能会漏行或重复读取——例如按`created_at`排序分页，同一秒插入多条记录，使用`OFFSET 100 ROWS`可能跳过或重复某些记录。

用 ROW_NUMBER() 的核心逻辑是：先排序、再编号、最后过滤。它不依赖物理偏移，只认逻辑序号，所以结果可重现、可翻页、可缓存。

• 必须搭配 ORDER BY 使用，否则语法报错：Window function 'ROW_NUMBER' requires an OVER clause with ORDER BY
• ORDER BY 字段最好有唯一性兜底，例如 ORDER BY created_at DESC, id DESC，避免因排序不稳定导致分页抖动
• 别在子查询外直接写 WHERE rn BETWEEN 101 AND 120 —— 多数数据库无法下推谓词，全表编号后再过滤，性能爆炸

怎么写一个安全可复用的 ROW_NUMBER 分页模板

最简但实用的结构是三层嵌套：最内层查原始数据 + 排序，中间层加 ROW_NUMBER()，最外层过滤序号。别图省事压成两层，否则执行计划容易崩。

示例（SQL Server）：

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

• WHERE 条件（如 status = 'published'）一定要写在内层，让索引能生效；写在外层等于全表扫描后才过滤
• rn BETWEEN 比 rn >= 101 AND rn 更易读，语义一致，无性能差异
• 如果分页参数来自用户输入，务必校验 page_size 上限（比如 ≤ 100），防拖库式大范围扫描

MySQL 8.0+ 和 PostgreSQL 怎么适配这个模式

MySQL 8.0+ 和 PostgreSQL 都支持标准 ROW_NUMBER()，语法完全一致，但要注意默认排序行为差异：PostgreSQL 对 NULL 默认排在最前，MySQL 排在最后，若 ORDER BY 字段可能为 NULL，得显式写 ORDER BY col DESC NULLS LAST（PostgreSQL）或 ORDER BY IFNULL(col, '1970-01-01') DESC（MySQL）。

• MySQL 5.7 及更早版本不支持窗口函数，只能用变量模拟，但并发下不可靠，别碰
• PostgreSQL 如果用了 LIMIT + OFFSET，记得加 FOR UPDATE 或快照隔离，否则高并发下可能看到幻读分页
• 所有数据库中，ROW_NUMBER() 的 OVER 子句不能包含子查询，也不能引用外层列，否则报错：Invalid column reference in window definition

为什么 COUNT(*) 和 ROW_NUMBER() 分页不能共用一个查询

因为 COUNT(*) 要扫全量满足条件的行，而 ROW_NUMBER() 分页只要扫到最大序号那一行即可——两者驱动路径完全不同。强行合并（比如用 CTE 先算总数再编号），数据库优化器大概率放弃并行，且内存占用翻倍。

• 真实业务里，总页数往往不需要实时精确，可用缓存值（比如每小时更新一次 COUNT(*)）代替每次查询
• 如果非要查总数，单独跑 SELECT COUNT(*) FROM posts WHERE status = 'published'，走覆盖索引，比塞进窗口函数快得多
• 注意：有些 ORM 自动生成的“分页带总数”SQL 会把 COUNT 和 ROW_NUMBER 套进同一个 CTE，这是典型反模式，得手动拆开

真正麻烦的不是写法，而是排序字段的业务语义是否允许去重、是否被索引覆盖、以及用户是否真的需要跳转到任意页——这些比函数本身更决定分页能不能跑稳。