MySQL分页排序时数据重复问题

问题版本

MySQL 5.6

问题复现

首先，创建一张表，记录的是用户信息，id是主键，其他为业务字段。

CREATE TABLE `account_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `seq` bigint(20) unsigned DEFAULT NULL COMMENT '序号',
  `userId` varchar(64) NOT NULL COMMENT '用户ID',
  `amount` decimal(10,3) unsigned NOT NULL COMMENT '余额',
  PRIMARY KEY (`id`),
  KEY `I_PIN` (`userId`),
  KEY `I_SEQ` (`seq`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

插入测试数据如下，

• id、seq是数值类型保持自增的差异性
• userId是字符类型保持差异性
• amount是数值类型保持数据一致性。
  
  执行SQL查询第1页数据SELECT * FROM account_info ORDER BY amount DESC LIMIT 0,5，结果如下：
  
  执行SQL查询第2页数据SELECT * FROM account_info ORDER BY amount DESC LIMIT 5,5，结果如下：
  
  综上可以看到，id为2、3、4、5的数据在第1、2页均出现了，这便是我们所说的MySQL在排序+分页过程中可能出现的数据重复问题。

问题分析

排序算法优化

在MySQL 5.6的版本上，优化器在遇到order by x limit m,n语句的时使用priority queue进行了优化。

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

堆排序不稳定性

之所以MySQL 5.6出现了第二页数据重复的问题，是因为priority queue使用了堆排序的排序方法，而堆排序是一个不稳定的排序方法，也就是相同的值可能排序出来的结果和读出来的数据顺序不一致。

排序场景

不使用order by条件的排序

SELECT * FROM account_info LIMIT 0,5

不使用order by条件时，默认使用主键进行排序，因此查询分页也是具有有序性，不存在数据重复的问题

使用order by条件分页的排序

使用有序性字段

使用唯一索引、不重复数据字段排序，不会出现分页重复数据情况

SELECT * FROM account_info ORDER BY userId LIMIT 0,5

使用非有序性字段

使用重复数据字段排序，会出现分页重复数据情况

SELECT * FROM account_info ORDER BY amount LIMIT 0,5

总结

汇总分页情况下的排序条件如下：

分页排序字段	排序字段是否数据唯一	排序字段是否有序	分页重复数据
主键	是	是	否
唯一索引	是	否	否
普通字段	否	是	是
普通字段	否	否	是
普通字段	是	是	否
普通字段	是	否	否

可以得到结论是，分页重复数据是否出现与排序字段数据唯一性有关，与排序字段是否有序无关，换句话说，只要排序字段的数据能够保证唯一性（如主键、唯一索引、不重复的普通字段），那么分页就不会存在重复数据，否则会有可能出现重复数据在不同分页中。

解决方法

使用或结合数据唯一的字段进行排序

SELECT * FROM account_info order by id LIMIT 0,5
SELECT * FROM account_info order by amount,id LIMIT 0,5

结合使用数据唯一的字段，将原本不唯一的排序条件变成组合唯一的排序条件，因此可以解决分页数据重复的问题

参考

MySQL分页时使用 limit+order by 会出现数据重复问题