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，你遇到了吗？
网友评论