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

作为程序员,经常写 SQL 语句是正常不过了。然而,编写一些 SQL 语句,总会出现一些奇怪的问题。

问题

最近在项目中遇到一个很神奇的问题,MySQL 使用 order by 进行排序并进行分页的时候,会出现部分数据丢失和重复。具体看下面这三张图

图一

图二

图三

其中,

  • 第一张图查询所有数据,并按 sort 字段排序,
  • 第二张图,查询从第 1 条数据开始,查询 10 条数据,并按 sort 字段排序,
  • 第三张图,查询从第 11 条数据开始,查询 10 条数据,并按 sort 字段排序,

仔细看我用红色标记出来的,可以发现,分类11 的数据在分页后查询不出来,而分类18 则出现了两次。很明显的发现,当进行数据分页时,部分数据出现了丢失和重复。

分析原因

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

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

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

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

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

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

问题解决

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

  1. 方案一:降低 MySQL 版本为 5.5 或更低版本。此方案不推荐,数据库版本一般是指定的,降低数据库版本工作量较大。
  2. 方案二:在 order by 排序字段里,添加有索引的字段,比如主键ID。这样在排序时可以保证顺序稳定。

在图二、图三中,增加主键 category_id 字段排序后,就不会出现数据丢失和重复了。

image-20210117221819803

image-20210117221759168<

  • 4
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQLORDER BY语句用于对查询结果进行排序。当我们在分页查询中使用ORDER BY语句时,可能影响查询性能,原因如下: 1. 排序操作需要消耗额外的计算资源。ORDER BY语句对查询结果进行排序,这需要对每条记录进行比较和排序操作,消耗CPU和内存资源。 2. 排序操作可能需要使用临时表。如果查询结果集很大,MySQL可能使用临时表来存储中间结果,然后在临时表上进行排序操作。这增加磁盘IO操作和存储开销。 3. 排序字段的索引可能无效。如果排序的字段没有相应的索引,MySQL将无法高效地进行排序操作,而是执行全表扫描来排序结果。全表扫描导致查询性能下降。 为了优化分页查询中的排序操作,可以考虑以下方法: 1. 使用索引覆盖查询。如果排序字段有相应的索引,并且查询的字段只包含索引字段,MySQL可以直接使用索引进行排序,避免全表扫描和临时表的使用。 2. 限制查询结果集大小。如果只需要获取前几条记录,可以使用LIMIT子句限制结果集的大小,减少排序操作的开销。 3. 避免在分页查询中频繁改变排序字段。如果每次分页查询都使用不同的排序字段,MySQL无法充分利用缓存和索引,导致性能下降。最好在分页查询中保持相同的排序字段。 总而言之,分页查询中使用ORDER BY语句可能影响性能,但可以通过使用索引、限制结果集大小和保持相同的排序字段等方法进行优化。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值