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

最近发现一个神奇的现象，就是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

(3) FROM

(4) JOIN

(5) ON

(6) WHERE

(7) GROUP BY

(8) HAVING

(9) ORDER BY

(10) LIMIT

当前面的一系列筛选条件执行完之后，将符合条件的记录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。

viencoding.com版权所有，允许转载，但转载请注明出处和原文链接: https://viencoding.com/article/275

欢迎小伙伴们在下方评论区留言 ~ O(∩_∩)O