关于MySQL limit+order by分页数据重复的问题

2020-06-12 11:06:39 MySQL

最近发现一个神奇的现象，就是limit跟order by同时使用的时候，居然不同分页出现了数据重复，原因竟然是MySQL的优化

问题以及解决方法

问题SQL:

select name, gender from student where cls = 1 order by score desc limit 0, 5;

解决方案，加上自增id排序。

select name, gender from student where cls = 1 order by score desc, id asc limit 0, 5;

原因分析

MySQL是在5.6后引入堆排序来优化limit子句，重要的是它的排序采用了堆排序，学过《数据结构与算法》的同学们应该知道，堆排序是个不稳定的排序算法，什么是不稳定呢？就是说，遇到相等的值，是不会保证前后顺序的，举个例子，假设有a, b, c, d 四个同学取得了同样的分，在输入算法的时候是abcd的顺序，输出可能就变了，就不是abcd的顺序了。

再来看MySQL的默认执行顺序：

(1)     SELECT 
(2)     DISTINCT <select_list>
(3)     FROM <left_table>
(4)     <join_type> JOIN <right_table>
(5)     ON <join_condition>
(6)     WHERE <where_condition>
(7)     GROUP BY <group_by_list>
(8)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

当前面的一系列筛选条件执行完之后，将符合条件的记录select出来，再执行 order by 操作，select出来的数据是按顺序来的，但是经过了堆排序，顺序就有可能发生变化。而如果你将Limit row_count与order by混用，mysql会找到排序的row_count行后立马返回，而不是排序整个查询结果再返回。

比如，查找第一页2个学生是ab，第二页2个学生是bc，或许真正的排序（或者说全部数据完全排序完成）是adbc，而出现这个问题的原因就是b和d的成绩是相同的。在第一页查询中，找到两条分数最大的两个记录ab就够了，就不在继续排序了，直接返回，而在第二页查询中需要将abcd排序后返回后两条记录，这个过程中堆排序就可能会改变相同分数的学生位置。

解决方案

下面是官方文档的一句话：

If an index is not used for ORDER BY but a LIMIT clause is also present, the optimizer may be able to avoid using a merge file and sort the rows in memory using an in-memory filesort operation. For details, see The In-Memory filesort Algorithm.

就是说在ORDER BY + LIMIT的查询语句中，如果ORDER BY不能使用索引的话，优化器可能会使用in-memory sort操作。也就出现了上述结果，而给出的解决方案也很简单，就是将主键(或者具有唯一性的字段)排序引入需要排序的业务字段后，就如同开头讲的：

select name, gender from student where cls = 1 order by score desc, id asc limit 0, 5;

在order by score desc，后面加id asc。

欢迎小伙伴们在下方评论区留言 ~ O(∩_∩)O
文章对我有帮助, 点此请博主吃包辣条 ~ O(∩_∩)O

关于MySQL limit+order by分页数据重复的问题

问题以及解决方法

原因分析

解决方案

猜你喜欢

评论

未登录

标签