为什么MYSQL分页时使用limit+ order by会出现数据重复问题

问题描述： MYSQL采用limit进行翻页查询时，搭配order by ，在翻到第二页的时候可能会出现第一页的数据, 示例sql如下：

select a,b from c where d = 'xxx' order by e desc limit 5,5

使用上述sql查询的时候，可能出现和limit 0,5 相同的某条记录，但是使用select * 时又不会出现重复的情况

显然用select * 对于表字段多的时候不可取，所以解决这个情况用修改后的sql:

select a,b from c where d = 'xxx' order by e desc, ID asc limit 5,5 // 排序的时候用两个字段

问题原因：

MYSQL5.6版本，优化器在遇到order by limit 语句的时候，做了一个优化，使用了priority queue

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

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

注：MYSQL5.5不存在这个问题

mysql解释sql语言的执行顺序：

select

DISTINCT<select_list>

FROM<left_table>

JOIN<right_table>

on <join_condition>

where <where_condition>

group by <group_by_list>

having <having_condition>

order by <order_by_condition>

limit<limit_number>

问题解决办法：

办法1，如开头的一种方式，排序的时候，把ID显式的加上

办法2, 在字段上添加索引，直接按照索引的有序性进行读取并分页

另：分页是建立在排序的基础上，进行数量范围分割。排序是数据库提供的功能，分页是业务衍生出来的应用需求。

巴特西