开发者社区> 石沫> 正文

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进行排序(实际的执行计划必须是排序过的操作)

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

相关文章
手动绑定SQLDataSource到GridView后分页的问题(转)
由于GridView的数据源是后台CS文件中代码绑定的。所以程序运行时,点击分页数后没有反应。解决办法如下: using System;using System.Data;using System.Configuration;using System.
735 0
查看SqlServer的内存使用情况
原文:查看SqlServer的内存使用情况       上一篇提到动态T-SQL会产生较多的执行计划,这些执行计划会占用多少内存呢?今天从徐海蔚的书中找到了答案。动态视图不仅可以查到执行计划的缓存,数据表的页面缓存也可以查到,将SQL整理一下,做个标记。
1247 0
SQLServer · 最佳实践 · 透明数据加密在SQLServer的应用
背景 作为云计算的服务提供者,我们在向用户提供优秀的服务能力时会遇到一个合规的问题。在数据库领域,数据是极其敏感和珍贵的,保护好数据,就如保护好企业的生命线。因此,需要采取一些预防措施来帮助保护数据库的安全,如设计一个安全系统、加密机密资产以及在数据库服务器的周围构建防火墙。但是,如果遇到物理介质
2116 0
NodeJs操作MongoDB之分页功能与常见问题
NodeJs操作MongoDB之分页功能与常见问题一,方法介绍 1,聚合操作之count count()方法可以查询统计符合条件的集合的总数 1 db.User.count() // 此方法等价于 db.User.find().count()在分布式集合中,会出现计算错误的情况,这个时候推荐使用aggregate; 2,find() 方法以非结构化的方式来显示所有文档。
1460 0
sqlserver 通用分页存储过程
来源:http://www.jb51.net/article/19936.htm CREATE PROCEDURE commonPagination @columns varchar(500), --要显示的列名,用逗号隔开 @tableName varchar(100), --要查询...
708 0
MSSQL-最佳实践-实例级别数据库上云RDS SQL Server
--- title: MSSQL-最佳实践-实例级别数据库上云RDS SQL Server author: 风移 --- # 摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、阿里云RDS SQL自动化迁移上云的一种
1539 0
+关注
石沫
长期在电子商务行业从事SQL Server的设计,开发与维护,拥有10年的相关经验,擅长数据库的架构与设计,擅长数据库的性能优化,擅长数据库的自动化和智能化运维,从2014年开始, 在云计算领域坚持奋斗, 阿里云SQL Server系列产品的设计与规划者
13
文章
15
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载