网站首页 » MySQL » MySQL 使用 order by limit 分页排序会导致数据丢失和重复

MySQL 使用 order by limit 分页排序会导致数据丢失和重复

July 30, 2021 MySQL

在 MySQL 关系型数据库中,往往会存在多种排序算法。

通过 MySQL 的源码和官方文档介绍可以得知,它的排序规律可以总结如下:

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

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

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

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

当 order by limit 分页出现数据丢失和重复。而 order by 的 sort 字段没有使用索引(正常情况下,排序的字段也不会使用索引),如果使用了索引,则会进行索引排序。

因此可以得出,上面的图二和图三的 SQL 语句使用了堆排序。因为 sort 字段没有索引,所以没走索引排序;并且使用了 limit。导致最终使用了堆排序。

如果了解算法的你,应该知道堆排序是不稳定的。这种不稳定性,指的就是多次排序后,各个数的相对位置发生了变化。

但是,不是所有的 MySQL 版本都是这样。从 MySQL 5.6 版本开始,优化器在使用 order by limit 时,做了上面的优化,导致排序字段没有使用索引时,使用堆排序。

问题解决

通过上面的分析,有两种解决方案可以解决此问题。

方案一:降低 MySQL 版本为 5.5 或更低版本。此方案不推荐,数据库版本一般是指定的,降低数据库版本工作量较大。

方案二:在 order by 排序字段里,添加有索引的字段,比如主键ID。这样在排序时可以保证顺序稳定。