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数据完全一致 

 

 # 解决方法

1.  给需要排序的字段添加索引（索引本身是有序的，添加索引会按照索引的顺序进行排序返回）
2.  在排序字段后面再添加一个唯一值的字段排序，比如id（保证参与排序的值不一样就行）

 

# 分析问题

• 在MySQL 5.6的版本上，优化器在遇到order by limit语句的时候，做了一个优化，即使用了priority queue。
• 使用 priority queue 的目的，就是在不能使用索引有序性的时候，如果要排序，并且使用了limit n，那么只需要在排序的过程中，保留n条记录即可，这样虽然不能解决所有记录都需要排序的开销，但是只需要 sort buffer 少量的内存就可以完成排序。
• 之所以MySQL 5.6出现了第二页数据重复的问题，是因为 priority queue 使用了堆排序的排序方法，而堆排序是一个不稳定的排序方法，也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。

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

 

# 问题产生原因

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

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

1. 若排序内容能全部放入内存，则仅在内存中使用快速排序；
2. 若排序内容不能全部放入内存，则分批次将排好序的内容放入文件，然后将多个文件进行归并排序
3. 若排序中包含limit语句，则使用堆排序优化排序过程

    根据我们当前遇到的问题，很明显是采用了堆排序的排序方法（对于堆排序不了解的可以先去看看这个文章了解）

　基于给定无序数组创造大根堆，6₁、6₂、6₃、模拟值一致的情况

61

1

5

3

62

2

4

63

　

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

　大根堆(1)的最后三位分别是[6₂、6₃、6₁ ]

　大根堆(2)的最后三位分别是[6₁、6₂、6₃ ]

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

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

　

 

　参考链接

　浅谈堆排序

　MySQL学习——排序算法

　☆MySQL order by limit 分页数据重复问题

　MySQL limit查询优化