sql-server - Microsoft SQL Server 分页

Question

stackoverflow 上有许多 sql server 分页问题，​​其中许多都讨论使用 ROW_NUMBER() OVER (ORDER BY ...) AND CTE。一旦您进入数十万行并开始添加对非主键值的排序并添加自定义 WHERE 子句，这些方法就会变得非常低效。我有一个包含数百万行的数据集，我试图通过自定义排序和过滤来分页，但即使在我排序和过滤的所有字段上都有索引，我的性能也很差。我什至将我的 SELECT 列包含在每个索引中，但这几乎没有帮助，并且使我的数据库严重膨胀。

我注意到，无论您单击什么排序标准或页码，stackoverflow 分页都只需要大约 500 毫秒。有人知道如何在 SQL Server 2008 中高效地进行数百万行的分页吗？这包括尽可能高效地获取总行数。

我当前的查询与有关分页的 stackoverflow 问题具有完全相同的逻辑: Best paging solution using SQL Server 2005?

Answer 1

Anyone know how to make paging work efficiently in SQL Server 2008 with millions of rows?

如果你想要准确完美的分页，没有什么可以替代为每条记录建立一个索引键(位置行号)。不过，还有其他选择。

(1)总页数(记录)

• 您可以使用 sysindexes.rows 的近似值(几乎是即时的)，假设变化率很小。
• 您可以使用触发器来维护完全准确、精确到秒的表行数

(2)分页

(一)
您可以在记录的任意一侧显示接下来五页内的页面跳转。每面最多需要扫描 {页面大小} x 5。如果您的基础查询适合沿着排序顺序快速移动，那么这应该不会慢。因此，给定一条记录 X，您可以使用以下命令转到上一页(假设排序顺序为 a asc, b desc

select top(@pagesize) t.*
from tbl x
inner join tbl t on (t.a = x.a and t.b > x.b) OR
                    (t.a < a.x)
where x.id = @X
order by t.a asc, t.b desc

(即 X 之前的最后一个 {page size} 记录)

要返回五页，您可以将其增加到 TOP(@pagesize*5)，然后从该子查询进一步增加 TOP(@pagesize)。

缺点:此选项要求您不能直接跳转到特定位置，您的选项只有 FIRST(简单)、LAST(简单)、NEXT/PRIOR、<两边各 5 页

(b)
如果分页总是非常具体且可预测，请维护一个不包含行号间隙的索引 View 或触发器更新表。如果表通常只在范围的一端看到更新，则这可能是一种选择，并且通过移动不多的记录可以轻松快速地填补删除造成的空白。

这种方法为您提供了行计数(最后一行)，并且还可以直接访问任何页面。