SQL Server中ROW_NUMBER分页怎么避免全表排序？-SQL-PHP中文网

row_number()分页必须走索引，否则必然全表排序；需确保order by字段顺序、方向与索引完全一致，where条件须下推至内层子查询，深度分页（>10万页）应改用游标分页。

sql server中row_number分页怎么避免全表排序？

ROW_NUMBER()分页必须走索引，否则必然全表排序

SQL Server 的 ROW_NUMBER() 本身不强制排序，但它依赖 OVER (ORDER BY ...) 子句；而这个 ORDER BY 如果没对应索引，引擎就只能走 Sort 算子——也就是全表读取、内存/TempDB 排序、再编号。870 万行表翻第 500 页卡死，根源就在这儿。

关键不是“能不能避免排序”，而是“能不能让排序变成索引有序扫描”。要做到这点，必须满足：

OVER (ORDER BY) 的字段顺序、方向（ASC/DESC）必须和索引定义完全一致
如果 WHERE 条件里有高频过滤字段（如 status = 'Shipped'），它得放在索引最左列
避免在 ORDER BY 中用表达式，比如 ORDER BY DATEADD(day, 1, created_at) —— 索引直接失效

例如：要执行 ROW_NUMBER() OVER (ORDER BY created_at DESC, id DESC)，就得建联合索引：CREATE INDEX IX_orders_time_id ON orders (created_at DESC, id DESC)；若常加 WHERE status = 'paid'，则改用：CREATE INDEX IX_orders_status_time_id ON orders (status, created_at DESC, id DESC)。

WHERE 条件必须写在内层子查询，别偷懒放外层

这是最容易被忽略的性能断点。很多人图省事写成：

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC, id DESC) AS rn
  FROM orders
) t
WHERE t.rn BETWEEN 10001 AND 10020;

这会导致 SQL Server 先对全表跑一遍 ROW_NUMBER()，再过滤。哪怕你只想要第 500 页的 20 条，它也得给全部 870 万行编号。

正确做法是把业务过滤条件（尤其是能利用索引的）塞进最内层：

SELECT id, title, created_at FROM (
  SELECT id, title, created_at,
    ROW_NUMBER() OVER (ORDER BY created_at DESC, id DESC) AS rn
  FROM orders 
  WHERE status = 'published'  -- ✅ 这里下推，索引生效
) t
WHERE t.rn BETWEEN 10001 AND 10020;

这样执行计划里才可能看到 Index Seek + Top N Sort（实际是 TopN Optimized），而不是全量 Sort。

深度分页（page > 10万）时，ROW_NUMBER() 也扛不住

即使索引对齐、WHERE 下推，当 @page_index 超过 10 万，ROW_NUMBER() 仍要生成前 @page_index * @page_size 行序号——内存压力、TempDB 日志暴涨、执行时间不可控。

这时该换思路，不是优化 ROW_NUMBER()，而是绕过它：

用游标分页（cursor-based pagination）：基于上一页最后一条的 (created_at, id) 值做范围查询，例如 WHERE (created_at, id)
前端禁掉“跳转任意页”输入框，只保留“下一页/上一页”按钮
对导出类场景（必须支持 page=37），可预生成分页锚点表，或用物化视图缓存常用页范围

别指望靠调大 max server memory 或加并行度来硬扛——问题不在资源，而在逻辑路径本身。