数据库order by排序不唯一，导致分页出现重复数据问题

AC_great 2017-07-09 01:55:30

前些天出现了一个非常有意思的问题，sql简单化出来以后是下面这个样

select * from(  

SELECT tmp_page.*,  

    rownum row_id FROM (    

SELECT * FROM T_BAP_MEC_TZERO_STL ORDER BY dt_ute  desc nulls last  

)tmp_page  WHERE rownum<10  

  

)where row_id>0

解决的方案出来了，就是order by 后面加索引，但是我想知道其具体的原因，大佬们求帮助

...全文

3691 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

JavaTestFast 2019-08-16

打赏
举报

我们再看下mysql解释sql语言时的执行顺序： (7) SELECT (8) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) HAVING <having_condition> (9) ORDER BY <order_by_condition> (10) LIMIT <limit_number> 在我们本文的案例sql中，执行顺序依次为form… where… select… order by… limit… 由于上述priority queue的原因，在完成select之后，所有记录是以堆排序的方法排列的，在进行order by时，仅把view_count值大的往前移动。但由于limit的因素，排序过程中只需要保留到5条记录即可，view_count并不具备索引有序性，所以当第二页数据要展示时，mysql见到哪一条就拿哪一条，因此，当排序值相同的时候，第一次排序是随意排的，第二次再执行该sql的时候，其结果应该和第一次结果一样。 https://www.cnblogs.com/wuwenshuai/p/7158389.html

小灰狼W 2017-07-12

打赏
举报

这个本质是因为执行计划不同的分页，如果执行计划不一样，是可能重复的解决方法，除了排序字段中加入唯一的字段以外，也可以用rowid伪列

AC_great 2017-07-12

打赏
举报

引用 6 楼 wmxcn2000 的回复:

[quote=引用 4 楼 AC_great的回复:][quote=引用 2 楼 wmxcn2000 的回复:] 数据库order by排序不唯一，的确会导致最终的数据排序是不是稳定的，表现出来的结果，就是分页时会重复显示；建议在 order by 最后，增加 PK 列或 ROWID；

感谢你的回答，可是我想我需要更深层次的理解，这个层次的我已经了解了[/quote] 增加索引，会提高查询语句的性能，并不能解决数据重复的问题。另外，你提到的更深层次，指的是什么？[/quote] 你说的很有道理，我刚尝试了一下，单单是增加索引缺失不能解决问题，但是增加唯一性索引就行了，pk列或者rowid也能确保唯一，结论是只有能保证order by排序唯一，才会导致结果不重复另外，我说的更深的层次大概是指order by 如果后面接的列不唯一，为什么就会导致数据重复，毕竟如果不分页的情况下，你怎么点数据记录都不会变。谢谢你前面的回答

ochinchin 2017-07-12

打赏
举报

这个问题还是蛮有意思，题主还是蛮有钻研精神的。手工点个赞。 9i 的时候是会自动根据rowid排序的，接着说下oracle10g的。摘一个网上其他人的回答：“我们对于翻页等逻辑必须默认加上order by排序，而且order by的字段如果有重复值，必须指定第二排序字段，如果第二排序字段还有重复值，那必须指定更多的字段，直到所有的排序字段能够指定唯一顺序”。贴个大师的话：According to Tom Kyte: "Unless and until you add "order by" to a query, you cannot say ANYTHING about the order of the rows returned. Well, short of 'you cannot rely on the order of the rows being returned'." 贴个我们DBA专家的话：根据相同值来进行排序，就跟你没加ORDER BY 一样的，是随机给你的。这个题应该跟执行计划没有太直接的关系，主要是oracle对于orderby的处理机制的实现。

AC_great 2017-07-11