SQL Server中ROW_NUMBER()分区分页查询性能优化方法

方案1：创建适配窗口函数的覆盖索引（改动最小，无需修改表结构） #

你当前的窗口函数逻辑为PARTITION BY Color ORDER BY Name，需要创建复合非聚集索引直接匹配该逻辑，同时包含所有查询需要返回的列避免回表IO开销：

CREATE NONCLUSTERED INDEX IX_Orders_Color_Name_INCLUDE_ALL ON Orders (Color, Name)
INCLUDE (RowId, -- 把你SELECT *需要返回的所有字段依次列在这里
         字段1,
         字段2,
         ...);

该索引可以让SQL Server直接按顺序读取索引数据，无需额外排序即可生成每组内的行号，IO开销会下降90%以上。

方案2：预存分组行号（性能最高，适配静态表特性） #

因为你的表数据永久不变，可以直接新增字段存储每组内的排序行号，一次计算永久使用：

1. 新增字段并初始化值：

ALTER TABLE Orders ADD GroupRowNum INT NULL;
GO
WITH t AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Color ORDER BY Name) AS rn
    FROM Orders
)
UPDATE t SET GroupRowNum = rn;
GO

2. 为该字段创建联合索引：

CREATE NONCLUSTERED INDEX IX_Orders_GroupRowNum ON Orders (GroupRowNum, Color, Name);

3. 改写查询逻辑，直接走索引筛选：

DECLARE @PageNum int = 1;
DECLARE @PageSize int = 2;
SELECT *
FROM Orders
WHERE GroupRowNum BETWEEN ((@PageNum-1)*@PageSize+1) AND (@PageNum*@PageSize)
ORDER BY Color,Name;

该方案可把查询耗时从10分钟降到毫秒级，完全避免每次查询时的窗口函数计算和排序开销。

方案3：改写查询为APPLY模式（适合Color枚举值较少的场景） #

如果Color的不同取值数量不多，可以用CROSS APPLY分批按组取数，避免全表级别的计算：

DECLARE @PageNum int = 1;
DECLARE @PageSize int = 2;
DECLARE @Start int = (@PageNum -1)*@PageSize + 1;
DECLARE @End int = @PageNum * @PageSize;

SELECT o.*
FROM (SELECT DISTINCT Color FROM Orders) c
CROSS APPLY (
    SELECT * FROM Orders 
    WHERE Color = c.Color 
    ORDER BY Name
    OFFSET @Start -1 ROWS FETCH NEXT @PageSize ROWS ONLY
) o
ORDER BY Color, Name;

该方案结合方案1的覆盖索引使用，性能也会远高于原查询。

内容的提问来源于stack exchange，提问作者Ali Bdeir