开发者社区> 石沫> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介:
+关注继续查看

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

author: 石沫

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;  

运行的结果是这样的:
1

在SQL SERVER 2012上是这样的:

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

运行的结果是这样的:
2

3. 分析与解决

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

SELECT 
        SalesOrderID, OrderDate,DueDate
FROM Sales.SalesOrderHeader 

3

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

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

ROW_NUMBER:
4

这个地方的物理操作和逻辑操作都是Sort。
而用OFFSET的实际执行计划:
5
这个地方的物理操作是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

6

这时候,实际的执行计划其实已经发生改变:
7

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

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

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

8

然后,我们再看看执行计划 :
9

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

4. 最佳实践

遇到这类问题,提供两个建议:

  1. ORDER BY 子句包含保证是唯一的列或列组合
  2. ORDER BY 子句的列或列组合可以利用INDEX进行排序(实际的执行计划必须是排序过的操作)

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Python包管理工具之pipenv
传统的成熟方案中,我们为了避免包版本的管理混乱,通常我们是依赖于当前的Python版本创建一个虚拟环境,并在虚拟环境中使用pip进行包管理。现在有一个好用的工具叫pipenv来帮助我们更方便的管理包。
12 0
python 包之 random 随机数库教程
带你走进随机数的美妙世界
50 0
jdk、jre、jvm的区别联系
 jdk包括以下三个东西: D:\Program Files\jdk1.7.0_21\bin\javac.exe和java.exe D:\Program Files\jdk1.7.0_21\jre  D:\Program Files\jdk1.7.0_21\jre\bin\client\jvm.dll Java类库概念:Java的应用程序接口以包的形式来组织,每个包提供了大量的相关类、接口和异常处理类,这些包的集合就是Java的类库。
1477 0
Maven自定义上传第三方包到3rd party(第三方无依赖jar和本地扩展类加入maven统一管理)
Maven自定义上传第三方包到3rd party(第三方无依赖jar和本地扩展类加入maven统一管理) 注:如果图片太小,则可以下载查看,CSDN,不提供点击放大! 1:用浏览器登陆nexus服务器(此处为本地): http://localhost:8081/nexus/ admin admin123 2:第三方依赖加入: Log in到nexus中,如下图: 3:手动设
4423 0
使用activeMQ实现jms
一:jms介绍         jms说白了就是java message service,是J2EE规范的一部分,跟jdbc差不多,sun只提供了接口,由各个厂商(provider)来进行具体的实现,然后使用者使用他们的jar包进行开发使用即可。
725 0
+关注
石沫
长期在电子商务行业从事SQL Server的设计,开发与维护,拥有10年的相关经验,擅长数据库的架构与设计,擅长数据库的性能优化,擅长数据库的自动化和智能化运维,从2014年开始, 在云计算领域坚持奋斗, 阿里云SQL Server系列产品的设计与规划者
13
文章
15
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载