sql-server – 通过数百万行的可自定义排序进行分页性能

对于30行的第一页,实现如下所示：

DECLARE 
    @TopRows bigint = 30,@Minimum integer;

SELECT TOP (1)
    @Minimum = Filtered.widgetSize
FROM 
(
    SELECT * FROM 
    (
        SELECT
            WS.widgetSize,WS.NumRows,-- SQL Server 2012 or later
            SumNumRows = SUM(WS.NumRows) OVER (
                ORDER BY WS.widgetSize DESC)
        FROM dbo.WidgetSizes AS WS WITH (NOEXPAND)
    ) AS RunningTotal
    WHERE 
        RunningTotal.SumNumRows >= @TopRows
) AS Filtered
ORDER BY 
    Filtered.SumNumRows ASC;

SELECT TOP (@TopRows)
    T.id,T.widgetSize
FROM dbo.Test AS T
WHERE T.widgetSize >= @Minimum
ORDER BY
    T.widgetSize DESC,T.id ASC;

执行计划：

这显着改善了执行时间,大部分剩余成本与表扫描和下推过滤器相关联.通过创建非聚簇列存储索引(SQL Server 2012以后)可以进一步提高性能：

CREATE NONCLUSTERED COLUMNSTORE INDEX 
    NCCI_Test_id_widgetSize 
ON dbo.Test (id,widgetSize);

在我的笔记本电脑上,在列存储索引上以批处理模式执行扫描和过滤将执行时间从大约300毫秒减少到仅20毫秒：

下一页

第一页查询返回的最后一行包含widgetSize = 2903和id = 327：

查找接下来的30行(第2页)只需要对上一个查询进行简单修改：

DECLARE 
    @TopRows bigint = 30,SumNumRows = SUM(WS.NumRows) OVER (
                ORDER BY WS.widgetSize DESC)
        FROM dbo.WidgetSizes AS WS WITH (NOEXPAND)
        WHERE
            -- Added
            WS.widgetSize < 2903
    ) AS RunningTotal
    WHERE 
        RunningTotal.SumNumRows >= @TopRows
) AS Filtered
ORDER BY 
    Filtered.SumNumRows ASC;

SELECT TOP (@TopRows)
    T.id,T.widgetSize
FROM dbo.Test AS T
WHERE 
    T.widgetSize >= @Minimum
    AND 
    (
        -- Added
        T.widgetSize < 2903
        OR (widgetSize = 2903 AND id > 327)
    )
ORDER BY
    T.widgetSize DESC,T.id ASC;

这会产生与原始查询的明显扩展相同的结果：

SELECT TOP 30
    * -- (Pretend that there is a list of columns here)
FROM Test
    WHERE widgetSize < 2903
    OR (widgetSize = 2903 AND id > 327)
ORDER BY
    widgetSize DESC,id ASC;

使用索引视图和非聚簇列存储索引的查询在25ms内完成,而原始版本则超过2000ms.

传统的索引方案

或者,如果您要创建(最小的,非覆盖的)非聚簇索引以支持最常见的排序请求,那么查询优化器将使用它们来满足TOP(30)查询的可能性非常大.索引压缩可用于最小化这些附加索引的大小.

（编辑：晋中站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!