时间戳

首页 > 解决方案 > 使用分页优化 SQL 查询

sql - 使用分页优化 SQL 查询

问题描述

我有一个针对 SQL Server 数据库运行的查询，执行时间超过 10 秒。被查询的表有超过 1400 万行。

我想按给定的日期顺序显示表中的Text列。可能有数千个条目，所以我想将返回的值限制在可管理的水平。NotesServiceUserId

SELECT Text
FROM   
    (SELECT    
         ROW_NUMBER() OVER (ORDER BY [DateDone]) AS RowNum, Text
     FROM      
         Notes
     WHERE     
         ServiceUserId = '6D33B91A-1C1D-4C99-998A-4A6B0CC0A6C2') AS RowConstrainedResult
WHERE   
    RowNum >= 40 AND RowNum < 60
ORDER BY 
    RowNum

以下是上述查询的执行计划。

执行计划

如果我第二次运行相同的查询但使用不同的行号，那么我会以毫秒为单位得到响应，我假设来自缓存的执行计划。运行不同的查询ServiceUserId将需要大约 10 秒。

有关如何加快此查询的任何建议？

标签： sqlsql-serverpaginationquery-optimizationkeyset-pagination

解决方案

您应该查看 Keyset Pagination。

它比行集分页性能要好得多。

它与它根本不同，因为它不是引用特定的行号块，而是引用起始点来查找索引键。

它更快的原因是您不关心特定键之前有多少行，您只需寻找一个键并向前（或向后）移动。

假设您正在按单个ServiceUserId排序，按DateDone. 您需要一个如下索引（INCLUDE如果它太大，您可以忽略它，它不会对数学产生太大影响）：

create index IX_DateDone on Notes (ServiceUserId, DateDone) INCLUDE (TEXT);

现在，当您选择一些行时，不要给出开始和结束行号，而是给出起始键：

SELECT TOP (20)
    Text,
    DateDone
FROM
    Notes
WHERE     
    ServiceUserId = '6D33B91A-1C1D-4C99-998A-4A6B0CC0A6C2'
    AND DateDone > @startingDate
ORDER BY 
    DateDone;

在下一次运行中，您传递DateDone您收到的最后一个值。这会让你获得下一批。

一个小缺点是你不能跳转页面。但是，用户想要跳转到第 327 页的情况比某些人想象的要少得多（从 UI 的角度来看）。所以这并不重要。

密钥必须是唯一的。如果它不是唯一的，则您无法精确地寻找下一行。如果您需要使用额外的列来保证唯一性，它会变得有点复杂：

WITH NotesFiltered AS
(
    SELECT * FROM Notes
    WHERE     
        ServiceUserId = '6D33B91A-1C1D-4C99-998A-4A6B0CC0A6C2'
)
SELECT TOP (20)
    Text,
    DateDone
FROM (
    SELECT
        Text,
        DateDone,
        0 AS ordering
    FROM NotesFiltered 
    WHERE
        DateDone = @startingDate AND NoteId > @startingNoteId
    UNION ALL
    SELECT
        Text,
        DateDone,
        1 AS ordering
    FROM NotesFiltered 
    WHERE
        DateDone > @startingDate
) n
ORDER BY 
    ordering, DateDone, NoteId;

边注

在支持行值比较的 RDBMS 中，多列示例可以通过以下方式简化为原始代码：

WHERE (DateDone, NoteId) > (@startingDate, @startingNoteId)

不幸的是 SQL Server 目前不支持这个。
请为此投票支持Azure 反馈请求

推荐阅读