mysql 在 orderby 和 limit 混合使用时重复数据问题

# 问题背景
select * from table_1 order by field_1,field_2 limit 0,10;
select * from table_1 order by field_1,field_2 limit 10,10;

这样两条分页 sql 在查询数据时有两条数据既出现在第一次查询的结果集中, 又出现在第二次查询的结果集当中, 导致了分页结果的不准确.

经分析两条数据的 field_1,field_2 数据完全一致

# 解决方法

给需要排序的字段添加索引(索引本身是有序的, 添加索引会按照索引的顺序进行排序返回)

在排序字段后面再添加一个唯一值的字段排序, 比如 id(保证参与排序的值不一样就行)

# 分析问题

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

使用 priority queue 的目的, 就是在不能使用索引有序性的时候, 如果要排序, 并且使用了 limit n, 那么只需要在排序的过程中, 保留 n 条记录即可, 这样虽然不能解决所有记录都需要排序的开销, 但是只需要 sort buffer 少量的内存就可以完成排序.

之所以 MySQL 5.6 出现了第二页数据重复的问题, 是因为 priority queue 使用了堆排序的排序方法 https://www.jianshu.com/p/938789fde325 , 而堆排序是一个不稳定的排序方法, 也就是相同的值可能排序出来的结果和读出来的数据顺序不一致.

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

# 问题产生原因

当在 select 语句中使用 order by 语句时, MySQL 首先会使用索引来避免执行排序算法; 在不能使用索引的情况下, 可能使用 快速排序, 归并排序 及 堆排序进行排序.

当 order by 不能使用索引进行排序时, 将使用排序算法进行排序:

若排序内容能全部放入内存, 则仅在内存中使用快速排序;

若排序内容不能全部放入内存, 则分批次将排好序的内容放入文件, 然后将多个文件进行归并排序

若排序中包含 limit 语句, 则使用堆排序优化排序过程

根据我们当前遇到的问题, 很明显是采用了堆排序的排序方法 https://www.jianshu.com/p/938789fde325 (对于堆排序不了解的可以先去看看这个文章了解)

基于给定无序数组创造大根堆, 61,62,63, 模拟值一致的情况

以上两个大根堆都是根据此无序数组生成的合法大根堆, 在进行前三次首尾置换的排序后

大根堆 (1) 的最后三位分别是[62,63,61 ]

大根堆 (2) 的最后三位分别是[61,62,63 ]

假设我们取降序的后两位, 每次查询 2 条数据, 那么两次查询可以很明显的看到 61,62,63 可能既出现在第一页, 也出现在第二页, 即导致了前文所说的问题

结论: 每次生成大根堆的不一样会导致查询结果的不一致

来源: https://www.cnblogs.com/Smile-Jie/p/9431705.html