在SQL Server中分页结果的最佳方式是什么?
如果您还希望获得结果总数(在分页之前),在SQL Server 2000,2005,2008,2012中对结果进行分页的最佳方式(性能明智)是什么?
获得结果总数和分页是两种不同的操作。 为了这个例子,我们假设你正在处理的查询是
SELECT * FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
在这种情况下,您可以使用以下方法确定结果总数:
SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'
...这看起来效率不高,但实际上是非常高效的,假设所有索引等都已正确设置。
接下来,为了以分页方式获得实际结果,以下查询将是最有效的:
SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= 1
AND RowNum < 20
ORDER BY RowNum
这将返回原始查询的1-19行。 这里很酷的事情,尤其是网络应用程序,你不必保持任何状态,除了要返回的行号。
我也很好奇微软为什么不支持在MySQL或PostgreSQL中使用offset/limit
简单查询。 最后,发布Microsoft SQL Server 2012 ,我真的很喜欢它的分页简单性,你不必使用像这里回答的复杂查询。
为了获得下一个10行,只需运行这个查询:
SELECT * FROM TableName ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
http://technet.microsoft.com/en-us/library/gg699618.aspx
使用它时要考虑的要点:
令人难以置信的是,没有其他答案提到了在所有SQL Server版本中进行分页的最快方法。 按照此处的基准,偏移量对于大页面数可能非常缓慢。 在SQL中执行分页有一个完全不同的,更快的方法。 这通常被称为这里的博客文章中描述的“寻找方法”或“键集分页”。
SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC
“寻求谓词”
@previousScore
和@previousPlayerId
值是上一页最后一条记录的相应值。 这使您可以获取“下一页”页面。 如果ORDER BY
方向是ASC
,则只需使用>
代替。
使用上述方法,您不能立即跳到第4页,而没有先取得前面的40条记录。 但通常情况下,你不想跳得那么远。 相反,您可以获得更快的查询,以便能够在固定时间内获取数据,具体取决于您的索引。 此外,无论基础数据是否更改(例如,第1页,而第4页),您的页面仍保持“稳定”状态。
例如,当在Web应用程序中延迟加载更多数据时,这是实现分页的最佳方式。
请注意,“查找方法”也称为键集分页。
分页之前的总记录
COUNT(*) OVER()
窗口功能将帮助您计算“分页之前”的记录总数。 如果您使用的是SQL Server 2000,则必须对COUNT(*)
进行两次查询。