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