2017-10-9 mysql limit和order by 引

1 问题描述

最简单的分页查询，第一页和第二页 出现重复数据。

SELECT item_id FROM category_item 
WHERE category_id = 12 
AND item_state = 20 
ORDER BY weight DESC LIMIT 0, 10

SELECT item_id FROM category_item 
WHERE category_id = 12 
AND item_state = 20 
ORDER BY weight DESC LIMIT 10, 10

2 修复方案

order by 增加自增字段 id 排序。

SELECT item_id FROM category_item 
WHERE category_id = 12 
AND item_state = 20 
ORDER BY weight DESC, id ASC 
LIMIT 0, 10

SELECT item_id FROM category_item 
WHERE category_id = 12 
AND item_state = 20 
ORDER BY weight DESC, id ASC 
LIMIT 10, 10

3 具体原因

引用文章描述：

在MySQL 5.6的版本上，优化器在遇到order by limit语句的时候，做了一个优化，即使用了priority queue。……

使用 priority queue 的目的，就是在不能使用索引有序性的时候，如果要排序，并且使用了limit
n，那么只需要在排序的过程中，保留n条记录即可，这样虽然不能解决所有记录都需要排序的开销，但是只需要 sort buffer
少量的内存就可以完成排序。

之所以5.6出现了第二页数据重复的问题，是因为 priority queue
使用了堆排序的排序方法，而堆排序是一个不稳定的排序方法，也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。

5.5 没有这个优化，所以也就不会出现这个问题。

也就是说，mysql5.5是不存在本文提到的问题的，5.6版本之后才出现了这种情况。

个人理解，MySQL 5.6版本新增的优化措施使得 非索引 排序的分页出现问题，解决方式两种，排序字段加索引；order by 增加额外排序，例如 id asc。

4 参考资料

1 segmentfault：mysql orderby limit 翻页数据重复的问题
2 淘宝-数据库内核月报：MySQL · 答疑解惑 · MySQL Sort 分页
3 阿里云论坛：发现超级大BUG，你遇到了吗？