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

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

时间:2023-03-25 22:32:20浏览次数:45  
标签:sort MySQL 使用 索引 limit team 排序 order

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

标签:sort,MySQL,使用,索引,limit,team,排序,order
From: https://blog.51cto.com/sdwml/6149663

相关文章

  • mysql公共字段填充
    在实体类的属性上打@TableField注解,并在写明何时自动填充。 按照框架要求编写元数据对象处理器,在此类中统一为公共字段赋值,此类需要实现MetaObjectHandler接口1/**......
  • mysql
    数据库简述StructureQueryLanguage(结构化查询语言)简称SQL登录数据库与用户操作cmd打开数据库 添加用户 删除用户 查看所有用户......
  • Centos8安装MySQL
    受苦于博客总是过于老旧,安装时总会出现版本包已经删除的情况而且步骤总是各式各样难以理解,无奈之下还是官网靠谱,于是这篇博客以官网方法为基准,方便以后安装的时候可以直接......
  • mysql数据库备份与恢复
    环境:CentOS7.9mysql-5.71.数据库准备,建表createdatabaseschool;CREATETABLEstudent(idINT(10)NOTNULLUNIQUEPRIMARYKEY,nameVARCHAR(20)NOTNULL,se......
  • mysql手动实现窗口函数的方法
    背景:窗口函数是一种强大的SQL函数,它允许在查询中对一组行进行计算,而不需要将它们分组或汇总。然而,如果使用的MySQL版本不支持窗口函数,我们仍然可以通过一些技巧手动实现它......
  • golang 实现的零依赖、高性能、并发 mysqldump 工具。
    mysqldumpgolang中实现的零依赖、高性能、并发mysqldump工具。项目地址:https://github.com/dengjiawen8955/mysqldump/blob/master/README-zh.md文章地址:https:/......
  • Linux报错:audit: backlog limit exceeded(审计:超出积压限制)
    Linux报错:audit:backloglimitexceeded(审计:超出积压限制)系统版本:CentOSLinuxrelease7.6.1810(Core)问题现象:一次巡检中发现业务系统打不开,对应的Linux服务器ssh连......
  • 在windows系统中设置MySQL数据库
    MySQL搭建效果图step1:下载安装包https://downloads.mysql.com/archives/community/step2:解压后即完成安装step3:创建my.ini配置文件(注意路径)[mysqld]......
  • mysql语句
    数据库拼接字符串updatefriendscirclesetreply=CONCAT(reply,"3333")wherename="22";//求和如果为空,默认给0selectIFNULL(sum(amount_incurred),0)from......
  • mysql怎么删除重复数据并且保留其中一条
    现在有张加班表,一个人同一天只允许申请一次加班,就是加班日期和userid相同的数据应该只有一条,但是现在由于之前没有做限制,导致很多数据重复怎么通过sql删掉重复数据思路:......