MySQL中orderby和limit分页数据重复的问题

背景

有一段sql，根据状态值来排序，去掉limit一切正常，当加入分页查询时，第二页会出现第一个的结果

SELECT
    rl.id,
    rl.itemName,
    rl.menuCode,
    IFNULL( rf.state, '0' ) state 
FROM
    `runmattersinterior_catalog` rl
    LEFT JOIN `rummatters_frequently` rf ON rf.createrid = 'ADMIN'
    AND rl.menuCode = rf.menuCode 
WHERE
    1 = 1 
ORDER BY
    rf.state DESC
--      ,rf.itemName 
     limit 0,20

问题原因

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

使用 priority queue 的目的，就是在不能使用索引有序性的时候，如果要排序，并且使用了limit n，那么只需要在排序的过程中，保留n条记录即可

这样虽然不能解决所有记录都需要排序的开销，但是只需要 sort buffer 少量的内存就可以完成排序。

之所以MySQL 5.6出现了第二页数据重复的问题，是因为 priority queue 使用了堆排序的排序方法，而堆排序是一个不稳定的排序方法

也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。

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

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

解决方法

1. 索引排序字段

   可以直接给排序字段加上索引，这样就直接按照索引的有序性进行读取并分页，规避掉这个问题

2. 使用另一个唯一字段进行第二次排序

   可以使用另一个唯一的字段，进行排序，比如标题，id等这些不重复的字段