SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题

时间:2021-07-05 13:55:46

1. 背景

最近有一个客户遇到一个奇怪的问题,以前使用ROW_NUMBER来分页结果是正确的,但是替换为SQL SERVER 2012的OFFSET...FETCH NEXT来分页出现了问题,因此,这里简单分析一下原因,更深层次的原因还没有确切的结论,但可以提供解决办法。 在升级数据库后并且应用新功能时,这个问题可能会困扰一些同学。

2. 现象

为了复现在这个问题 ,我们使用SQL SERVER 2012的示例库AdventureWorks2012,因为只复现功能问题,其他性能问题忽略,只需要能够正常运行就好了。我们以Sales.SalesOrderHeader为例。

最开始语句是这样的:

USE AdventureWorks2012
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,DueDate,
ROW_NUMBER() OVER (ORDER BY OrderDate ) AS RowNumber
FROM Sales.SalesOrderHeader
)
SELECT SalesOrderID, OrderDate,DueDate
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;

运行的结果是这样的:
SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题

在SQL SERVER 2012上是这样的:

USE AdventureWorks2012
GO SELECT
SalesOrderID, OrderDate,DueDate
FROM Sales.SalesOrderHeader
ORDER BY OrderDate
OFFSET 49 ROWS FETCH NEXT 11 ROWS ONLY

运行的结果是这样的:
SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题

3. 分析与解决

好,我们来看看,因为表数据少,我们看看整个表的数据库 ,从50到60条数据的值

SELECT
SalesOrderID, OrderDate,DueDate
FROM Sales.SalesOrderHeader

SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题

这里可以看出来, 业务想要的数据和ROW_NUMBER分页产生的数据是一致的,也就是正确的数据。但是与OFFSET来分页的做对比,就出现问题了。

我们可以看到OrderDate = 2005-07-03 00:00:00.000的有5条数据,取了前3天,后2条丢弃了,正常是取后3条。这个地方发生了什么? 我们可以看看实际执行计划。

ROW_NUMBER:
SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题

这个地方的物理操作和逻辑操作都是Sort。
而用OFFSET的实际执行计划:
SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题
这个地方的物理操作是Sort,但逻辑操作都是TOP N Sort。
而我最初以为由于Sort和TOP N Sort的导致的,我们再看看他们的准确定义:

Sort:Sort 运算符可对所有传入的行进行排序。Argument 列包含 DISTINCT ORDER BY:()谓词(如果此操作删除重复项)或 ORDER BY:()谓词(如果对逗号分隔的列列表进行排序)

TOP N Sort:Top N Sort 与 Sort 迭代器类似,差别仅在于前者需要前 N 行,而不是整个结果集。如果 N 的值较小,SQL Server 查询执行引擎将尝试在内存中执行整个排序操作。如果 N 的值较大,查询执行引擎将使用更通用的排序方法(该方法不采用 N 作为参数)重新排序。

其实看得出来,似乎关系不大,但也可能是这个原因导致。为何OFFSET方式只随机取了OrderDate的某个值其中的一些?,这个准确原因也不是很清楚,至少我现在为看到有这类解释,或许TOP N SORT的算法本省就是这样,这个留在后面再调查一下。

而实际上,微软的帮助文档说得很清楚,要实现结果的唯一性或者持久一致性,必须满足下列条件:
1. 查询使用的基础数据不能发生变化
2. ORDER BY 子句包含保证是唯一的列或列组合

嗯,确实这个说法没错,如果更将主键列加入ORDER BY ,确实可以解决:

USE AdventureWorks2012
GO SELECT
SalesOrderID, OrderDate,DueDate
FROM Sales.SalesOrderHeader
ORDER BY SalesOrderID,OrderDate
OFFSET 49 ROWS FETCH NEXT 11 ROWS ONLY

SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题

这时候,实际的执行计划其实已经发生改变:
SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题

SORT操作不再需要,因为通过cluster index san取出来的数据已经是排序过了(ORDERED为1)。

我们还可以看到,OrderDate上是没有INDEX的,如果我们加上IDNEX,会是怎么样呢?

CREATE INDEX IX_SalesOrderHeader_OrderDate
ON Sales.SalesOrderHeader(OrderDate)
WITH(ONLINE=ON)

SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题

然后,我们再看看执行计划 :
SQLServer · 最佳实践 · SQL Server 2012 使用OFFSET分页遇到的问题

很明显,也是没有问题的。

4. 最佳实践

遇到这类问题,提供两个建议:
1. ORDER BY 子句包含保证是唯一的列或列组合
2. ORDER BY 子句的列或列组合可以利用INDEX进行排序(实际的执行计划必须是排序过的操作)