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. 问题分析

可能的问题原因

• 数据库本身有重复数据：经排查，表结构有唯一索引，不存在重复数据
• 分页数据有交集：根据这个问题：What is the default order of records for a SELECT statement in MySQL?，里边的高赞回答可知，sql-92标准中指明如果没有排序，那么返回数据的顺序将由数据库实现决定。

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