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

本文涉及的产品
云原生数据库 PolarDB 分布式版,标准版 2核8GB
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介:

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进行排序(实际的执行计划必须是排序过的操作)
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
2月前
|
SQL 开发框架 .NET
ASP.NET连接SQL数据库:详细步骤与最佳实践指南ali01n.xinmi1009fan.com
随着Web开发技术的不断进步,ASP.NET已成为一种非常流行的Web应用程序开发框架。在ASP.NET项目中,我们经常需要与数据库进行交互,特别是SQL数据库。本文将详细介绍如何在ASP.NET项目中连接SQL数据库,并提供最佳实践指南以确保开发过程的稳定性和效率。一、准备工作在开始之前,请确保您
204 3
|
21天前
|
SQL PHP 数据库
解决高版本laravel/framework中SQLServer2008分页报错问题
【11月更文挑战第6天】在高版本的 `laravel/framework` 中使用 SQL Server 2008 进行数据库操作时,可能会出现分页报错。这是由于 `laravel` 的分页机制与 SQL Server 2008 的某些特性不兼容所致。解决方法包括:1. 升级数据库版本;2. 自定义分页查询语句;3. 使用兼容包或插件;4. 修改 `laravel` 的分页逻辑。
|
2月前
|
SQL 存储 数据库
SQL语句给予用户权限:技巧、方法与最佳实践
在数据库管理中,为用户分配适当的权限是确保数据安全性和操作效率的关键步骤
|
2月前
|
SQL 数据管理 数据库
文章初学者指南:SQL新建数据库详细步骤与最佳实践
引言:在当今数字化的世界,数据库管理已经成为信息技术领域中不可或缺的一部分。作为广泛使用的数据库管理系统,SQL已经成为数据管理和信息检索的标准语言。本文将详细介绍如何使用SQL新建数据库,包括准备工作、具体步骤和最佳实践,帮助初学者快速上手。一、准备工作在开始新建数据库之前,你需要做好以下准备工作
134 3
|
2月前
|
SQL 数据库
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)之解决方案
执行 Transact-SQL 语句或批处理时发生了异常。 (Microsoft.SqlServer.ConnectionInfo)之解决方案
284 0
|
4月前
|
Java 开发者 前端开发
Struts 2、Spring MVC、Play Framework 上演巅峰之战,Web 开发的未来何去何从?
【8月更文挑战第31天】在Web应用开发中,Struts 2框架因强大功能和灵活配置备受青睐,但开发者常遇配置错误、类型转换失败、标签属性设置不当及异常处理等问题。本文通过实例解析常见难题与解决方案,如配置文件中遗漏`result`元素致页面跳转失败、日期格式不匹配需自定义转换器、`<s:checkbox>`标签缺少`label`属性致显示不全及Action中未捕获异常影响用户体验等,助您有效应对挑战。
94 0
|
4月前
|
Java 应用服务中间件 Maven
从零到英雄:一步步构建你的首个 JSF 应用程序,揭开 JavaServer Faces 的神秘面纱
【8月更文挑战第31天】JavaServer Faces (JSF) 是一种强大的 Java EE 标准,用于构建企业级 Web 应用。它提供了丰富的组件库和声明式页面描述语言 Facelets,便于开发者快速开发功能完善且易于维护的 Web 应用。本文将指导你从零开始构建一个简单的 JSF 应用,包括环境搭建、依赖配置、Managed Bean 编写及 Facelets 页面设计。
100 0
|
4月前
|
前端开发 开发者
Vaadin Grid的秘密武器:打造超凡脱俗的数据展示体验!
【8月更文挑战第31天】赵萌是一位热爱UI设计的前端开发工程师。在公司内部项目中,她面临大量用户数据展示的挑战,并选择了功能强大的Vaadin Grid来解决。她在技术博客上分享了这一过程,介绍了Vaadin Grid的基本概念及其丰富的内置功能。通过自定义列和模板,赵萌展示了如何实现复杂的数据展示。
45 0
|
4月前
|
SQL 开发框架 .NET
深入解析Entity Framework Core中的自定义SQL查询与Raw SQL技巧:从基础到高级应用的全面指南,附带示例代码与最佳实践建议
【8月更文挑战第31天】本文详细介绍了如何在 Entity Framework Core (EF Core) 中使用自定义 SQL 查询与 Raw SQL。首先,通过创建基于 EF Core 的项目并配置数据库上下文,定义领域模型。然后,使用 `FromSqlRaw` 和 `FromSqlInterpolated` 方法执行自定义 SQL 查询。此外,还展示了如何使用 Raw SQL 进行数据更新和删除操作。最后,通过结合 LINQ 和 Raw SQL 构建动态 SQL 语句,处理复杂查询场景。本文提供了具体代码示例,帮助读者理解和应用这些技术,提升数据访问层的效率和灵活性。
223 0
|
4月前
|
SQL 关系型数据库 MySQL
【超全整理】SQL日期与时间函数大汇总会:MySQL与SQL Server双轨对比教学,助你轻松搞定时间数据处理难题!
【8月更文挑战第31天】本文介绍了在不同SQL数据库系统(如MySQL、SQL Server、Oracle)中常用的日期与时间函数,包括DATE、NOW()、EXTRACT()、DATE_ADD()、TIMESTAMPDIFF()及日期格式化等,并提供了具体示例。通过对比这些函数在各系统中的使用方法,帮助开发者更高效地处理日期时间数据,满足多种应用场景需求。
463 0

相关产品

  • 云数据库 RDS SQL Server 版