mysql在使用order by +limit分页时遇到的数据重复及丢失问题

苦命的码仔

已于 2024-09-07 17:56:27 修改

阅读量1k

点赞数 12

文章标签： mysql 数据库排序算法

于 2024-09-07 17:03:39 首次发布

本文链接：https://blog.csdn.net/ygd185/article/details/141996955

版权

最近自己在做项目组遇到了order by+limit分页时遇到的数据重复的问题，自己在网上查了一些资料：

问题现象：order by+limit会出现数据丢失及分页重复问题

直接上图：

图0（表结构）：

图1：

图2：

图2

从图中可以看出，id=30的数据在第1页，和第3，最后1页重复了2次

通过查看执行计划，发现排序方式是文件排序，

图3：

问题原因

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

如果order by的列有大量重复的值的时候， mysql会随机选取这些行，具体根据执行计划有所不同。

很显然，这是由于order by 排序导致的，那么mysql order by排序底层是采用什么机制呢？

分析原因：

order by排序

MySQL支持二种方式的排序，Index和FileSort。

Index排序：索引排序，即我们通常为了查询建立的索引，也就是说，Mysql能为排序和查询使用相同的索引；
FileSort：文件排序，当不能使用Index排序时，使用文件排序。FileSort排序有两种方法，堆排序和快速排序。

Index排序使用场景

where + order by 场景
如果order by的字段加了索引，则使用索引排序；
只有order by场景
order by中字段满足组合索引字段排序，则使用组合使用，
order by id（主键）
不管有没有where条件，或者where 字段有没有索引，只要order by id，都会使用Index 排序，而不会使用FileSort

其余情况下，使用FileSort排序，可以通过EXPLAIN中的Extra来判断使用哪种排序方式。

从图3也可以看出直接使用Order by limit确实会使用FileSort。

FileSort排序使用场景

在不能使用Index索引排序的时候，使用FileSort排序，FileSort排序有两种方式：

堆排序：在排序量不大的情况下，使用堆排序
快速排序：在需要大量排序的情况下，使用快排

从执行计划中只能看出来是使用Index排序，还是FileSort排序。而使用堆排序还是快排，是Mysql根据待排序数据量的大小进行切换具体根据函数check_if_pq_applicable进行判定的。

有一个简单的判断，如果使用的是order by limit n ，且在数据量不大的情况下（数据可以在内存中加载），使用的是堆排序；
而当n到了一个数量级的时候会切换成快排，具体使用那种算法是优化器通过函数check_if_pq_applicable进行判定的。
同时，如果没有limit时，就算数据量小，使用的也是快排（这个结论是从下面的参考文章中得出的）。

在大量排序的情况下快速排序是有优势的，而堆排序使用优先队列只完成少量的排序是有优势的，因为它根本不需要排序完成只排序你需要的数据量就可以了，MySQL认为快速排序的速度是堆排序的3倍。

问题释疑

从导致问题的sql，我们可以判断使用的是FileSort排序中的堆排序。那么为啥堆排序会出问题呢？

因为快速排序和堆排序是不稳定的排序算法，也就是对于重复值是不能保证顺序的。而直接利用索引的话其返回数据是稳定的，因为索引的B+树叶子结点的顺序（就是聚簇索引的顺序）是唯一且一定的。
因此在这种不稳定的算法情况下上面的查询出现了不一样的结果，归根结底就是使用索引避免排序和堆排序对于重复值的处理上是不同的。

order by limit排序时，根据取值大小创建相应容量的堆，即
order by limit 0,10；——创建一个10容量的堆，然后取前10
order by limit 10,10 ——创建一个20容量的堆，然后取10到20

那现在开始给org_num加索引，使用where条件

图4：