Mysql分页重复

踩坑描述:

写分页查询接口，order by和limit混用的时候，出现了排序的混乱情况 在进行第N页查询时，出现与第一前面页码的数据一样的记录。

问题

在MySQL中分页查询，我们经常会用limit，如:limit(0,20)表示查询第一页的20条数据，limit(20,20)表示查询第二页的数据。业务上我们通常也会在分页的时候加上排序 order by;

但是当limit和order by一起使用的时候，有可能会出现第N页的数据，竟然和前面页码的数据有重复

例如：

ｓｅｌｅｃｔ a,b FROM table WHERE c=1 ORDER BY d desc LIMIT 0,20

使用上述SQL查询的时候，很有可能和LIMIT 20,20查出相同的某条数据。为了解决这个问题，我们在ORDER BY后面加上了ID（唯一索引页可以）排序来进行规避，

如下：

ｓｅｌｅｃｔ a,b FROM table WHERE c=1 ORDER BY d desc，id desc LIMIT 0,20

理论上，MySQL的排序默认情况下是以主键ID作为排序条件的，也就是说，如果在条件d相等的情况下，主键id会作为默认的排序条件，不需要我们多此一举加ID asc。但是事实就是，MySQL在order by和limit同时使用的情况下，出现了排序的混乱情况。

分析

在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) DISTINCT (3) FROM (4) JOIN (5) ON (6) WHERE (7) GROUP BY (8) HAVING (9) ORDER BY (10)LIMIT

执行顺序依次为 form… where… ｓｅｌｅｃｔ… order by… limit…，由于上述priority queue的原因，在完成ｓｅｌｅｃｔ之后，所有记录是以堆排序的方法排列的，在进行order by时，仅把d值大的往前移动。但由于limit的因素，排序过程中只需要保留到20条记录即可，d并不具备索引有序性，所以当第二页数据要展示时，mysql见到哪一条就拿哪一条，因此，当排序值相同的时候，第一次排序是随意排的，第二次再执行该sql的时候，其结果应该和第一次结果有可能一样。

解决方法1.尽量使用不重复的值进行排序

如果在字段添加上索引，就直接按照索引的有序性进行读取并分页（这个字段如果有重复值分页会有可能出现重复）。

可以最后加上ID排序，也不会影响业务

2.正确理解分页

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

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

不加order by的时候的排序问题 用户在使用Oracle或MySQL的时候，发现MySQL总是有序的，Oracle却很混乱，这个主要是因为Oracle是堆表，MySQL是索引聚簇表的原因。所以没有order by的时候，数据库并不保证记录返回的顺序性，并且不保证每次返回都一致的。 分页问题-分页重复的问题 如前面所描述的，分页是在数据库提供的排序功能的基础上，衍生出来的应用需求，数据库并不保证分页的重复问题。 NULL值和空串问题 不同的数据库对于NULL值和空串的理解和处理是不一样的，比如Oracle NULL和NULL值是无法比较的，既不是相等也不是不相等，是未知的。而对于空串，在插入的时候，MySQL是一个字符串长度为0的空串，而Oracle则直接进行NULL值处理。

深分页问题

有些时候，我们需要偏移一定量数据之后，获取某些数据，就很容易想到用limit，但是，如果偏移量很大时，就会发现SQL执行起来非常非常慢了，因为，偏移量会分页读取到buffpool中，数据量大，占用的buffpool空间就会大，而这个空间大小是配置的，一般不会很大；

其次需要从一开始就扫描数据，最后要舍弃前面大量数据，只保留需要的那几条数据，而且过程还有可能需要回表操作，导致了慢sql。

对于这个问题的优化，建议写一个过滤条件（比如：自增主键ID或有序的字段），再与limit结合实现。

作者：京东零售 马成龙

来源：京东云开发者社区 转载请注明来源