东莞市盛裕绒艺玩具有限公司

东莞市盛裕绒艺玩具有限公司

biwin必赢在线官方客户端

18511750219
联系方式
全国服务热线: 18511750219

咨询热线:15822710732
联系人:李莎
地址:中国宁夏贺兰县贺兰县银河西街(和平商贸对面)

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

来源:biwin必赢在线官方客户端   发布时间:2019-06-24   点击量:78

 # 问题背景

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 使用了堆排序的排序方法,而堆排序是一个不稳定的排序方法,也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。

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

 

# 问题产生原因

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

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

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

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

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

61153622463

 

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

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

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

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

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

 

 

 参考链接

 浅谈堆排序

 MySQL学习——排序算法

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

 MySQL limit查询优化

  

 

相关产品

COPYRIGHTS©2017 biwin必赢在线官方客户端 ALL RIGHTS RESERVED 备案号:78