Skip to main content

.MySQL order by limit 分页数据重复或丢失问题说明

mysql 中当 order by 与limit 同时使用,数据可能出现重复或丢失问题

使用了 下面的 方法 发现也没什么问题

Db::name('users')
->where(['is_disabled'=>0])
->field('id,nickname,team_son+team_grandson as sort,team_son,team_grandson')
->order('sort desc')
->page($page, 20)
->select();

打印sql 语句 去运行 发现 第i 页 的数据 与 第 i+1 页 的 数据出现重复 并且 丢失了一些数据

SELECT `id`,`nickname`,team_son+team_grandson as sort,`team_son`,`team_grandson` FROM `table_name` WHERE
`is_disabled` = 0 ORDER BY `sort` DESC LIMIT 200,20

问题原因

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

如果order by的列有相同的值时, mysql会随机选取这些行,具体根据执行计划有所不同。

分析原因

在 MySQL 关系型数据库中,往往有多种排序算法。通过 MySQL 源码和官方文档介绍就可以知道,它的排名规则可以总结如下:

当 order by 没有索引排序时,会使用排序算法进行排序;

如果所有排序后的内容都可以放入内存,则只在内存中使用快速排序;

如果排序后的内容无法放入内存,则将排序后的内容批量放入文件中,然后对多个文件进行合并排序;

如果排序包含限制语句,则使用堆排序来优化排序过程。

根据上面的总结,当order by limit Paging 数据丢失和重复时。 order by 的 sort 字段不使用索引(一般情况下,Sorted 字段也不使用索引),如果使用了索引,则会进行索引排序。

因此可以得出结论,在上面的SQL语句使用了堆排序。因为 sort 字段没有索引,所以我没有使用索引排序;并使用限制。导致最终使用堆排序。

如果你知道算法,你应该知道堆排序是不稳定的。这种不稳定性,意味着经过多次排序后,数字的相对位置发生了变化。

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

堆排序的不稳定性导致了查询数据的重复或丢失

解决方法

在 order by 中的排序字段中,添加索引字段,例如主键 ID。这样排序才能稳定。

分页需要确定性的排序顺序:如果查询数据是排序分页的,如果排序字段没有使用索引,一定要加索引字段,比如主键ID,确保序列稳定。否则,查询数据会导致数据丢失和重复。

到 order by 子句是一个好的开始,这样我们就可以继续将此索引用于流水线 order by。如果这仍然不能产生确定性的排序顺序,只需添加任何唯一的列并相应地扩展索引。

若想 使用 order by 加 limit的 形式

1、 使用 索引列 进行排序

2、若不能使用索引字段的话,只能进行 双排序,也就是`ORDER BY sort DESC,id asc` 把索引字段id作为 附加条件 进行排序 就可以了
SELECT `id`,`nickname`,team_son+team_grandson as sort,`team_son`,`team_grandson` FROM `users` WHERE
`is_disabled` = 0 ORDER BY `sort` DESC,`id` ASC LIMIT 200,20