踩坑描述:

写分页查询接口，只取需要的字段进行查询,order by走的其他字段排序。
在进行第二页查询时，出现与第一页的数据一样的记录。

实例:

SELECT
  `id`,
  `name`
FROM
  test_table
WHERE
  `status` = 1
ORDER BY
  c_time desc
LIMIT
  1, 5

若使用上述sql查询,很有可能会出现与 LIMIT 0,5 相同的记录。
若使用如下方式，则恢复正常的情况：

SELECT
  *
FROM
  test_table
WHERE
  `status` = 1
ORDER BY
  c_time desc
LIMIT
  1, 5

业务场景需要，我们不可能把test_table表的所有的字段都取出来。
而是希望拿出我们需要用到的字段。
为了解决上述的情况，可以使用以下的手段：

1.在ORDER BY使用索引或主键排序

SELECT
  *
FROM
  test_table
WHERE
  `status` = 1
ORDER BY
  id asc,
LIMIT
  1, 5

2.在ORDER BY使用两个排序条件解决

SELECT
  *
FROM
  test_table
WHERE
  `status` = 1
ORDER BY
c_time desc
  id asc,
LIMIT
  1, 5

由上可知，mysql的排序默认走的是id主键作为排序条件。则在c_time相等的情况下，主键id就作为默认的排序条件，就不需要多此一举加上id asc。

但是事实上，mysql在和order by和limit混用的时候，出现了排序的混乱情况。

1.问题分析

查阅相关资料了解到，mysq5.6的版本上，优化器在遇到order by limit语句的时候，做了一个优化，即使用了priority queue。

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

所以mysql5.6以上版本出现了第二页数据重复的问题，就是因为priority queue 使用了堆排序的排序方法，而堆排序 是一个不稳定的排序方法，也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。
（mysql5.5没有这个优化，所有不会有这个问题）

再来看下mysql解释sql语句时的执行语句:

(1)     SELECT 
(2)     DISTINCT <select_list>
(3)     FROM <left_table>
(4)     <join_type> JOIN <right_table>
(5)     ON <join_condition>
(6)     WHERE <where_condition>
(7)     GROUP BY <group_by_list>
(8)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

执行顺序为:

 from / where / select / order by / limit 

由于priority queue的原因，在完成select之后，所有记录以堆排序的方法排列的。
在进行order by时，仅把c_time值大的行往前移动。

但是由于limit的因素，排序过程中只需要保留到5条记录即可。c_time并不具备索引有序性。所以当第二页数据展示的时候，mysql见到哪一条就拿哪一条，因此，当排序值相同的时候，第一次排序是随意排序的，第二次再执行该sql的时候，其结果应该和第一次的结果一样。

2.解决方法

1.索引排序字段
如果在字段添加上索引，就直接按照索引的有序性进行读取并分页，从而规避此类问题。

2.正确理解分页
分页是建立在排序的基础上，进行了数量范围的分割。
排序是数据库提供的功能，而分页是咱们衍生的应用需求。
在MySQL和Oracle的官方文档中提供了limit n和rownum < n的方法，但却没有明确的定义分页这个概念。
还有重要的一点，虽然上面的的解决方法可以缓解问题，但是依然还会有问题：比如，这个表插入频繁，用户在查询的时候，在read-committted的隔离级别下，第一页和第二页依然会有重合。
所以，分页一直都有这个问题，不同场景对数据分页都没有非常高的准确性要求。

3.一些常见的数据库排序问题

不加order by时候的排序问题
用户在使用mysql或者使用oracle的时候，会发现mysql总是有序的，oracle却很混乱，这是因为oracle是堆表，而mysql是索引聚簇表。 所在没有order by的时候，数据库并不保证记录返回的顺序性，并且不保证每次返回都一致。

分页问题 分页重复的问题
如前面所描述的，分页是在数据库提供的排序功能的基础上，衍生出来的应用需求，数据库并不保证分页的重复问题。

NULL值和空串问题
不同的数据对于null和空串的理解和处理是不一样的，比如oracle null和null值是无法比较的，既不是相等也是不相等，是未知的。而对于空串，在插入的时候，MySQL是一个字符串长度为0的空串，而Oracle则直接进行NULL值处理。

本文章为记录整理,感谢博主分享
www.jianshu.com/p/544c319fd838