如何解决MySQL order by limit语句的分页数据重复问题？

SELECT post_title,post_date FROM post WHERE post_status=‘publish’ ORDER BY view_count desc,ID asc LIMIT 5,5

按理来说，MySQL的排序默认情况下是以主键ID作为排序条件的

也就是说，如果在view_count相等的情况下，主键ID作为默认的排序条件，不需要我们多此一举加ID asc。

但是事实就是，MySQL再order by和limit混用的时候，出现了排序的混乱情况。

1 分析问题

==========

在MySQL 5.6的版本上，优化器在遇到order by limit语句的时候，做了一个优化，即使用了priority queue。

使用 priority queue 的目的，就是在不能使用索引有序性的时候，如果要排序，并且使用了limit n，那么只需要在排序的过程中，保留n条记录即可

这样虽然不能解决所有记录都需要排序的开销，但是只需要 sort buffer 少量的内存就可以完成排序。

之所以MySQL 5.6出现了第二页数据重复的问题，是因为 priority queue 使用了堆排序的排序方法，而堆排序是一个不稳定的排序方法

也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。

MySQL 5.5 没有这个优化，所以也就不会出现这个问题。

也就是说，MySQL 5.5是不存在本文提到的问题的，5.6版本之后才出现了这种情况。

再看下MySQL解释sql语言时的执行顺序：

(1)     SELECT

(2)     DISTINCT <select_list>

(3)     FROM <left_table>

(4)     <join_type> JOIN <right_table>

(5)     ON <join_condition>

(6)     WHERE <where_condition>

(7)     GROUP BY <group_by_list>

(8)     HAVING <having_condition>

(9)     ORDER BY <order_by_condition>

(10)    LIMIT <limit_number>

执行顺序依次为 form… where… select… order by… limit…

由于上述priority queue的原因，在完成select之后，所有记录是以堆排序的方法排列的

在进行order by时，仅把view_count值大的往前移动。但由于limit的因素，排序过程中只需要保留到5条记录即可

view_count并不具备索引有序性，所以当第二页数据要展示时，mysql见到哪一条就拿哪一条

因此，当排序值相同的时候，第一次排序是随意排的，第二次再执行该sql的时候，其结果应该和第一次结果一样。

2 解决方法

======

（1）索引排序字段

如果在字段添加上索引，就直接按照索引的有序性进行读取并分页，从而可以规避遇到的这个问题。

（2）正确理解分页

分页是建立在排序的基础上，进行了数量范围分割。排序是数据库提供的功能，而分页却是衍生的出来的应用需求。

在MySQL和Oracle的官方文档中提供了limit n和rownum < n的方法，但却没有明确的定义分页这个概念。

还有重要的一点，虽然上面的解决方法可以缓解用户的这个问题，但按照用户的理解，依然还有问题

比如，这个表插入比较频繁，用户查询的时候，在read-committed的隔离级别下，第一页和第二页仍然会有重合。

所以，分页一直都有这个问题，不同场景对数据分页都没有非常高的准确性要求。

（3）一些常见的数据库排序问题

不加order by的时候的排序问题

用户在使用Oracle或MySQL的时候，发现MySQL总是有序的，Oracle却很混乱，这个主要是因为Oracle是堆表，MySQL是索引聚簇表的原因。

所以没有order by的时候，数据库并不保证记录返回的顺序性，并且不保证每次返回都一致的。

分页问题 分页重复的问题

如前面所描述的，分页是在数据库提供的排序功能的基础上，衍生出来的应用需求，数据库并不保证分页的重复问题。

NULL值和空串问题

不同的数据库对于NULL值和空串的理解和处理是不一样的

比如Oracle NULL和NULL值是无法比较的，既不是相等也不是不相等，是未知的。

而对于空串，在插入的时候，MySQL是一个字符串长度为0的空串，而Oracle则直接进行NULL值处理。

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后：学习总结——MyBtis知识脑图（纯手绘xmind文档）

学完之后，若是想验收效果如何，其实最好的方法就是可自己去总结一下。比如我就会在学习完一个东西之后自己去手绘一份xmind文件的知识梳理大纲脑图，这样也可方便后续的复习，且都是自己的理解，相信随便瞟几眼就能迅速过完整个知识，脑补回来。下方即为我手绘的MyBtis知识脑图，由于是xmind文件，不好上传，所以小编将其以图片形式导出来传在此处，细节方面不是特别清晰。但可给感兴趣的朋友提供完整的MyBtis知识脑图原件（包括上方的面试解析xmind文档）

除此之外，前文所提及的Alibaba珍藏版mybatis手写文档以及一本小小的MyBatis源码分析文档——《MyBatis源码分析》等等相关的学习笔记文档，也皆可分享给认可的朋友！

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
文所提及的Alibaba珍藏版mybatis手写文档以及一本小小的MyBatis源码分析文档——《MyBatis源码分析》等等相关的学习笔记文档，也皆可分享给认可的朋友！

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！