从根上理解order by limit分页数据重复问题

JAVA herman 256浏览
公告:“业余草”微信公众号提供免费CSDN下载服务(只下Java资源),关注业余草微信公众号,添加作者微信:codedq,发送下载链接帮助你免费下载!
本博客日IP超过2000,PV 3000 左右,急需赞助商。
极客时间所有课程通过我的二维码购买后返现24元微信红包,请加博主新的微信号:codedq,之前的微信号好友位已满,备注:返现
饿了么大量招人,我内推!Java 方向!薪资不设上限,工作年龄不限!工作地点限魔都,可电话面试!简历,发我微信:codedq
所有面试题(java、前端、数据库、springboot等)一网打尽,请关注文末小程序
视频教程免费领

这个问题相信不少人都遇到过,但很少有人追根溯源。今天我抽出一点时间,来讨论讨论 order by + limit 在什么情况下会出现分页数据重复。

昨天在“CTO”群提问了之后,不少大神都回答的相当正确。我这里结合一下他们的回答,并补充一些内容分享给大家!

排序离不开算法,在关系型数据库中,往往会存在多种排序算法。通过 MySQL 的源码和官方文档介绍可以得知,它的排序规律可以总结如下:

  1. 当 order by 不能使用索引进行排序时,将使用排序算法进行排序;
  2. 若排序内容能全部放入内存,则仅在内存中使用快速排序;
  3. 若排序内容不能全部放入内存,则分批次将排好序的内容放入文件,然后将多个文件进行归并排序;
  4. 若排序中包含 limit 语句,则使用堆排序优化排序过程。

其他如:PG,MariaDB,AliSQL,SQL Server 等排序算法方面差别不大。

根据上面的总结,当你的 order by limit 分页出现数据重复。比如,一个用户表,当使用 limit 5 后出现一个张三。再使用 limit 5,10 的时候,张三又出现了。注意,这两个张三是同一个人,id 是相同的。在这种情况下,你的 order by 肯定是没有使用索引的。因为使用了索引,就会进行索引排序。

order by limit 分页出现数据重复

根据官方文档显示,以及我上面的总结,可以得出。上面的 SQL 使用了堆排序。因为,category 没索引,所以没走索引排序;其二我们使用了 limit,所以最终使用了堆排序。而了解算法的朋友都知道,堆排序是不稳定的。

比如,我们现在有下面一列数。

堆排序

为了,看清两个 2 的区别,我分别给它们多标了一个数字。

那么使用堆排序的结果,可能会存在下面的结果。

堆排序结果不稳定

这种不稳定性,指的就是多次排序后,各个数的相对位置发生了变化。

除了堆排序,不稳定的排序还有下面这些排序算法。

不稳定的排序算法

那么如何解决 order by limit 分页数据重复问题呢?方法有多种,我这里列举最常用的两种方法。

第一种就是,在排序中加上唯一值,比如主键 id,这样由于 id 是唯一的,就能确保参与排序的 key 值不相同。

解决 order by limit 分页数据重复问题

第二种就是避免使用堆排序,让 order by 根据索引来排序。说白了,就是 order by 后面的字段要有索引。

以上,知道的越多,不知道的就越多,业余的就像一棵小草一样!

参考资料:

业余草公众号

最后,欢迎关注我的个人微信公众号:业余草(yyucao)!可加作者微信号1:xmtxtt(5000人已满),微信号2:xttblog(5000人已满),微信号3:codedq(超3800)。备注:“1”,添加博主微信拉你进微信群。备注错误不会同意好友申请。再次感谢您的关注!后续有精彩内容会第一时间发给您!原创文章投稿请发送至532009913@qq.com邮箱。商务合作也可添加作者微信进行联系!

本文原文出处:业余草: » 从根上理解order by limit分页数据重复问题