sql分页遍历出现重复数据原因与解决方案

1. 问题描述

有同时反馈,直接通过如下的sql进行分页查询,分页会出现重复数据,于是乎我专门查了相关了资料,整理了一下。

-- 根据sort字段对dbname进行排序,每五百条数据一页
SELECT * FROM  (  
	SELECT A.*, ROWNUM RN  FROM ( 
		select * from dbname
		where createtime between '20211212' and '20211213') A  
	WHERE ROWNUM <=7000 )  
WHERE RN >6500

2. 问题分析

可能的问题原因

2.1 Oracle 的 order by 是稳定排序么?

根据oracle官方文档:ORDER BY clauses,里边有针对排序是否稳定做了说明。

EQL保证语句的结果在查询中是稳定的。这意味着:

  • 如果没有执行更新,则即使没有指定ORDER BY子句,或者ORDER BY句中指定的顺序有联系,同一语句也会在重复查询时以相同的顺序返回结果。
  • 如果执行了更新,那么只有明确影响订单的更改才会影响订单;订单不会受到其他影响。订单可能会受到更改的影响,例如删除或插入有助于返回页面上或之前结果的记录,或修改用于分组或订购的值。

例如,在没有ORDER BY子句的语句中,使用PAGE(0, 10)然后是PAGE(10, 10)然后是PAGE(20, 10)查询,在没有更新的情况下,从同一任意但稳定的结果返回连续的10条记录。

对于带有更新的示例,在带有ORDER BY Num PAGE(3, 4)的语句中,初始查询返回记录{5、6、7、8}。然后,更新插入带有4的记录(在指定页之前),删除带有6的记录(在指定页上),并插入带有9的记录(在指定页之后)。更新后,同一查询的结果将为{4、5、7、8}。这是因为:

  • 插入4将所有后续结果向下移动一个。抵消3条记录包括新记录。
  • 删除6个班次会将所有后续结果增加一个。
  • 插入9不影响此结果之前或包含的任何记录。

从官方文档的描述来看,只要加上order by,那么在没有影响到该查询条件的更新或者写入操作,则排序是不受影响的,是稳定的。官方文档的描述比较符合我的预期,因为我觉得要是我去实现,我就会使用稳定排序的算法去实现,而不是非稳定算法。

我看网络上充斥着这片文章:Oracle——分页查询出现重复数据问题的分析与解决,该文章提到一个观点需要唯一索引才能够保证分页排序不会重复。我觉得看法太浅了,相当于提出了解决方案,但是不知道为什么能够解决没有了解,另外就是文章感觉个人主观猜想太强了,理论没有依据来源的感觉,可信度就感觉比较低。根据官方文档的说明,实际只要加上排序即可保证分页遍历是不会出现重复数据的。

3. 解决方法

3.1 通过排序分页

我想这是最高效的写法,只要在createtime 字段加上索引,则查询和排序都会利用到该索引。

-- 根据sort字段对dbname进行排序,每五百条数据一页
SELECT * FROM  (  
	SELECT A.*, ROWNUM RN  FROM ( 
		select * from dbname
		where createtime between '20211212' and '20211213'
		order by createtime) A  
	WHERE ROWNUM <=7000 )  
WHERE RN >6500

posted on 2021-12-30 21:59  xiaoheike  阅读(1006)  评论(0编辑  收藏  举报

导航