order by + limit 重复数据

本文解析了MySQL 5.6版本中优化ORDER BY LIMIT场景的Priority Queue(PQ)机制,当排序字段无索引且值不唯一时,如何利用堆排序提高性能。重点讨论了索引排序字段和加入唯一值的重要性,并引用官方文档作为参考。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

问题描述

最近线上出现了一个问题,在某个 SELECT … FROM table1 ORDER BY ××× LIMIT N 时,第一页和第二页出现了重复的数据。

结论

MySQL在5.6版本的时候对 ORDER BY LIMIT 做了个优化(排序字段无索引,且值不唯一)时,优化器遇到 ORDER BY LIMIT 语句的时候,使用了priority queue(优先队列),优先队列使用的堆排序,因为这种场景是用于求TOP N 问题,堆排快,但是堆排不稳定。

分析

在官方文档当中,有提到
在这里插入图片描述
官方文档当中

while (get_next_sortkey())
     {
       if (using priority queue)
         push sort key into queue
       else
       {
         if (no free space in sort_keys buffers)
         {
           sort sort_keys buffer;
           dump sorted sequence to 'tempfile';
           dump BUFFPEK describing sequence location into 'buffpek_pointers';
         }
         put sort key into 'sort_keys';
       }
     }
     if (sort_keys has some elements && dumped at least once)
       sort-dump-dump as above;
     else
       don't sort, leave sort_keys array to be sorted by caller.

描述了伪代码的优化。原因是啥呢,就是因为快。

PQ

priority queue 是一个堆,order by limit 本质上就是在求TOP N ,那只要求出最大N条数据/最小的N条数据就行。剩下的数据不需要再进行排序,这样就极大的加快了速度。而堆排序总所州市

解决办法

1.索引排序字段
在这里插入图片描述
2.在排序中加上唯一值,比如主键 id,这样由于 id 是唯一的,就能确保参与排序的 key 值不相同。

参考文章:

https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

### 使用 `ORDER BY` 和 `LIMIT` 进行分页时的数据重复解决方案 当使用 `ORDER BY` 结合 `LIMIT` 实现分页功能时,可能会遇到数据重复的问题。这通常发生在没有唯一键作为排序依据的情况下。 为了防止这种情况发生,在构建查询语句时应确保有一个稳定的排序条件来保证每次查询的结果一致性和唯一性。具体方法如下: #### 方法一:增加唯一字段到排序列表中 通过在原有的基础上加入可以区分每一条记录的唯一标识符(通常是主键),使得即使其他列相同也能依靠此额外添加的部分来进行最终判定从而避免重复项出现[^4]。 ```sql SELECT id, name FROM users WHERE status = 'active' ORDER BY created_at DESC, id ASC LIMIT 10 OFFSET 20; ``` 这里不仅按照创建时间降序排列还附加了一个升序排列的ID字段以确保稳定性。 #### 方法二:采用子查询方式获取下一页起始位置 另一种思路是从上一次请求返回的最后一项开始读取新一批次的内容而不是依赖于固定的偏移量(offset),这样做的好处是可以有效规避因删除操作而导致原有索引失效进而引发跳过或重叠等问题的发生。 ```sql WITH last_seen AS ( SELECT MAX(id) as max_id FROM articles WHERE category='tech' ), next_batch AS( SELECT a.* FROM articles a JOIN last_seen ls ON true WHERE a.category='tech' AND a.id > COALESCE(ls.max_id, 0) ORDER BY a.created_at DESC LIMIT 10 ) SELECT * FROM next_batch nb; ``` 上述例子展示了如何利用CTE(Common Table Expressions)结构先找到前一轮展示的最大id值再以此为基础向前推进一定数量的新纪录形成新的页面显示范围。 #### 方法三:使用窗口函数实现更高效的分页逻辑 对于大型表格而言,传统基于offset/limit模式效率较低且容易出现问题;此时推荐考虑引入window function特性如ROW_NUMBER(),它能够提供更加灵活精确控制的同时也提高了性能表现特别是在处理海量级联表关联场景下的优势尤为明显[^3]. ```sql WITH NumberedRows AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY publish_date DESC,id ASC) rn FROM posts p ) SELECT * FROM NumberedRows nr WHERE rn BETWEEN @startRow AND @endRow ; ``` 这段代码片段定义了一个带有行号属性的临时视图,并从中选取特定区间内的所有元素完成高效可靠的翻页动作而不会遗漏任何重要信息。
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值