这个问题的探讨来源于同事遇到的问题:页面上分页时数据重复了。这在之前是没有遇到过的,于是试着看找下原因。
说到排序,其实有这几种情况:
1.排序字段都没有值 这样排序没有意义
2.排序字段都有值,但都一样 这样排序也没有意义
3.排序字段都有值,值不唯一 较少的情况,也不是没有。
4.排序字段都有值,值唯一 正常情况,因为我们既然用那个字段排序,肯定是看到那个字段可以用来排序,值互不相同,有序可排。这种情况不会有问题。
那么就试着前三种情况看看MySQL的排序和通过MyBatis plus的结果
测试环境
数据库:MySQL 5.7
客户端:HeidiSQL
后端:Spring Boot
JPA框架:MyBatis Plus 3.1.0
第一种情况:排序字段都没有值
MySQL 5.7
再次查询,发现结果不一样
查第二页,发现”易文学”和第一页第二次查询结果重复
再查几次第二页,结果也变了
结论:使用一个没有值的字段排序,结果不稳定,分页大可能出现重复
通过Mybatis Plus,也就是走接口,同样的语句
第一页(截图都是第一、第二条)
发现和直接在数据库查询结果不一致
第二页
发现和第一页的”易金连”重复
结论:使用一个没有值的字段排序,分页大可能出现重复
第二种情况:排序字段都有值,但都一样 这样排序也没有意义。这次换salary排序
多查几次还是这样的结果
再查第二页
发现和第一页的”易细根”重复
结论:使用一个值都一样的字段排序,分页大可能出现重复
通过Mybatis Plus
语句一样
再次查询结果也不一样了
和直接在数据库查询的结果不一样
再查第二页
第三条和第一页的”易文学”重复
结论:使用一个固定值的字段排序,分页大可能出现重复
第三种情况:排序字段都有值,值不唯一。这次换create_by排序
MySQL 5.7
多查几次结果一样
再查第二页
没发现重复,再多查几次也没有重复。
结论:使用一个值不唯一的字段排序,分页一般不会出现重复
order by再加一个值不唯一的salary字段排序
发现第二页的”易文平”和第一页的重复
结论:使用一个值不唯一的字段排序,分页可能出现重复,如果再增加非唯一字段的排序,大可能会出现重复
通过Mybatis Plus
语句一样
第二页
和MySQL直接查询结果一样,没出现重复的。
出现这种情况,可以在排序字段加一个值唯一的字段作为排序。
由此联想到排序的问题,想到那些排序算法,于是查是查询相关原因,有人解释到:在MySQL 5.6及以后的版本上,优化器在遇到order by limit语句的时候,做了一个优化,即使用了priority queue。使用 priority queue 的目的,是在不能使用索引有序性的时候,如果要排序,并且使用了limitn,那么只需要在排序的过程中,保留n条记录即可,这样虽然不能解决所有记录都需要排序的开销,但是只需要 sort buffer少量的内存就可以完成排序。
之所以5.6出现了第二页数据重复的问题,是因为 priority queue使用了堆排序的排序方法,而堆排序是一个不稳定的排序方法,也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。5.5 没有这个优化,所以也就不会出现这个问题。
也就是说,mysql5.5是不存在本文提到的问题的,5.6版本之后才出现了这种情况。
原文链接:http://mysql.taobao.org/monthly/2015/06/04/
希望能对广大开发朋友提个醒,使用order by + limit排序时,应保证排序的字段必须有一个唯一值字段,假如这个字段可能会在后期的业务重复,应考虑用一个值唯一的字段做排序,如果无特殊要求,不用加order by字段,因为mysql默认用id排序。