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 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;
运行的结果是这样的:
在SQL SERVER 2012上是这样的:
USE AdventureWorks2012 SELECT SalesOrderID, OrderDate,DueDate FROM Sales.SalesOrderHeader ORDER BY OrderDate OFFSET 49 ROWS FETCH NEXT 11 ROWS ONLY
运行的结果是这样的:
好,我们来看看,因为表数据少,我们看看整个表的数据库 ,从50到60条数据的值
SELECT SalesOrderID, OrderDate,DueDate FROM Sales.SalesOrderHeader
这里可以看出来, 业务想要的数据和ROW_NUMBER分页产生的数据是一致的,也就是正确的数据。但是与OFFSET来分页的做对比,就出现问题了。
我们可以看到OrderDate = 2005-07-03 00:00:00.000的有5条数据,取了前3天,后2条丢弃了,正常是取后3条。这个地方发生了什么? 我们可以看看实际执行计划。
ROW_NUMBER:
这个地方的物理操作和逻辑操作都是Sort。
而用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的算法本省就是这样,这个留在后面再调查一下。
而实际上,微软的帮助文档说得很清楚,要实现结果的唯一性或者持久一致性,必须满足下列条件:
查询使用的基础数据不能发生变化 ORDER BY 子句包含保证是唯一的列或列组合嗯,确实这个说法没错,如果更将主键列加入ORDER BY ,确实可以解决:
USE AdventureWorks2012 SELECT SalesOrderID, OrderDate,DueDate FROM Sales.SalesOrderHeader ORDER BY SalesOrderID,OrderDate OFFSET 49 ROWS FETCH NEXT 11 ROWS ONLY
这时候,实际的执行计划其实已经发生改变:
SORT操作不再需要,因为通过cluster index san取出来的数据已经是排序过了(ORDERED为1)。
我们还可以看到,OrderDate上是没有INDEX的,如果我们加上IDNEX,会是怎么样呢?
CREATE INDEX IX_SalesOrderHeader_OrderDate ON Sales.SalesOrderHeader(OrderDate) WITH(ONLINE=ON)
然后,我们再看看执行计划 :
很明显,也是没有问题的。
4. 最佳实践遇到这类问题,提供两个建议:
ORDER BY 子句包含保证是唯一的列或列组合 ORDER BY 子句的列或列组合可以利用INDEX进行排序(实际的执行计划必须是排序过的操作)[ SQL Server ] 计算N个月前,N个月后的方法 #1. 计算N个月前的日期 #2. 计算N个月后的日期 #3. 月末最后一天的情况(计算从日数多的月到日数较少的月) #4. 月末最后一天的情况(计算从日数少的月到日数较多的月)
【SQL Server】数据库开发指南(三)面向数据分析的 T-SQL 编程技巧与实践 T-SQL 指的是 Transact-SQL,是一种针对 Microsoft SQL Server 数据库系统的 SQL 方言。T-SQL 扩展了标准 SQL 语言,提供了更多的功能和特性,包括事务处理、错误处理、游标处理、动态 SQL、存储过程、触发器、用户定义函数等等。
石沫01 长期在电子商务行业从事SQL Server的设计,开发与维护,拥有10年的相关经验,擅长数据库的架构与设计,擅长数据库的性能优化,擅长数据库的自动化和智能化运维,从2014年开始, 在云计算领域坚持奋斗, 阿里云SQL Server系列产品的设计与规划者
相关文章
- SQLServer 错误 18452 用户 “%.*ls” 登录失败。 该登录名为 SQL Server 登录名,不能与 Windows 身份验证一起使用。%.*ls 故障 处理 修复 支持远程
- 使用SQL Server实现行转列(sqlserver行转列)
- SQL Server数据库备份技术简介(sqlserver备份数据库)
- SQLServer 简化版:轻松管理你的数据库(sqlserver简化版)
- SQL Server重复更新数据的防范方案(sqlserver防重复)
- SQL Server中表的状态:洞察与分析(sqlserver表状态)
- SQL Server获得锁的技巧分享(sqlserver获得锁)
- SQL Server自规约:获取更高数据安全性(sqlserver自约束)
- 自增SQL Server中ID自增的应用(sqlserver的id)
- 掌握SQL Server,激发新技术能力(sqlserver演讲)
- 复制解决SQLServer异地复制的最佳实践(sqlserver异地)
- SQL Server:高效学习和应用(sqlserver好书)
- 模式SQL Server 构建基于单例模式的高效解决方案(sqlserver 单例)
- SQLServer添加主键约束的技巧(sqlserver加主键)
- 优化SQL Server写入效率,助力系统加速(sqlserver写入慢)
- SQL Server实现行计数的方法(sqlserver行计数)