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

问题描述

在MySQL中我们通常会采用limit来进行翻页查询，比如limit(0,10)表示列出第一页的10条数据，limit(10,10)表示列出第二页。但是，当limit遇到order by的时候，可能会出现翻到第二页的时候，竟然又出现了第一页的记录。

问题本源

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

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

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

问题复现

这里创建了一个 glon 表，字段有自增 id, 姓名 name, 年龄 age, 及用户注册时间 create_time。这个问题较难复现，要不是碰上了，还不知道居然有这种莫名的问题。

1.根据用户注册时间 create_time 来排序：

root@localhost [glon_ho]>select * from glon ORDER BY create_time limit 0, 4;
+----+--------------+---------------------+------+
| id | name         | create_time         | age  |
+----+--------------+---------------------+------+
|  1 | Eason Chan   | 2017-05-02 08:10:10 |   19 |
|  8 | 刘三姐       | 2017-05-02 14:00:00 |   19 |
|  6 | Andy Lau     | 2017-05-02 14:00:00 |   50 |
|  4 | Jacky Cheung | 2017-05-02 14:00:00 |   22 |
+----+--------------+---------------------+------+
4 rows in set (0.00 sec)root@localhost [glon_ho]>select * from glon ORDER BY create_time limit 4, 4;
+----+-----------+---------------------+------+
| id | name      | create_time         | age  |
+----+-----------+---------------------+------+
|  7 | 至尊宝    | 2017-05-02 14:00:00 |   20 |
|  8 | 刘三姐    | 2017-05-02 14:00:00 |   19 |
|  2 | Glon Ho   | 2017-05-03 12:10:10 |   18 |
|  3 | 赵敏      | 2017-05-03 14:10:10 |   17 |
+----+-----------+---------------------+------+
4 rows in set (0.00 sec)

可以看到两次查询结果中都出现了 id 为 8 的刘三姐，从上面初始化数据来看，总共有 8 条数据，现在不但分页出现重复数据，还丢了一条！

问题确实重现了，不过先不急，我们再来试多几组其他的排序方式。

2.create_time 和 age 组合排序：

root@localhost [glon_ho]>select * from glon ORDER BY create_time,age limit 0, 4;
+----+------------+---------------------+------+
| id | name       | create_time         | age  |
+----+------------+---------------------+------+
|  1 | Eason Chan | 2017-05-02 08:10:10 |   19 |
|  5 | 周芷若     | 2017-05-02 14:00:00 |   16 |
|  8 | 刘三姐     | 2017-05-02 14:00:00 |   19 |
|  7 | 至尊宝     | 2017-05-02 14:00:00 |   20 |
+----+------------+---------------------+------+
4 rows in set (0.00 sec)root@localhost [glon_ho]>select * from glon ORDER BY create_time,age limit 4, 4;
+----+--------------+---------------------+------+
| id | name         | create_time         | age  |
+----+--------------+---------------------+------+
|  4 | Jacky Cheung | 2017-05-02 14:00:00 |   22 |
|  6 | Andy Lau     | 2017-05-02 14:00:00 |   50 |
|  2 | Glon Ho      | 2017-05-03 12:10:10 |   18 |
|  3 | 赵敏         | 2017-05-03 14:10:10 |   17 |
+----+--------------+---------------------+------+
4 rows in set (0.00 sec)

3.create_time 和 id 组合排序：

root@localhost [glon_ho]>select * from glon ORDER BY create_time,id limit 0, 4;
+----+--------------+---------------------+------+
| id | name         | create_time         | age  |
+----+--------------+---------------------+------+
|  1 | Eason Chan   | 2017-05-02 08:10:10 |   19 |
|  4 | Jacky Cheung | 2017-05-02 14:00:00 |   22 |
|  5 | 周芷若       | 2017-05-02 14:00:00 |   16 |
|  6 | Andy Lau     | 2017-05-02 14:00:00 |   50 |
+----+--------------+---------------------+------+
4 rows in set (0.00 sec)root@localhost [glon_ho]>select * from glon ORDER BY create_time,id limit 4, 4;
+----+-----------+---------------------+------+
| id | name      | create_time         | age  |
+----+-----------+---------------------+------+
|  7 | 至尊宝    | 2017-05-02 14:00:00 |   20 |
|  8 | 刘三姐    | 2017-05-02 14:00:00 |   19 |
|  2 | Glon Ho   | 2017-05-03 12:10:10 |   18 |
|  3 | 赵敏      | 2017-05-03 14:10:10 |   17 |
+----+-----------+---------------------+------+
4 rows in set (0.00 sec)

4.主键 id 排序：

root@localhost [glon_ho]>select * from glon ORDER BY id limit 0, 4;
+----+--------------+---------------------+------+
| id | name         | create_time         | age  |
+----+--------------+---------------------+------+
|  1 | Eason Chan   | 2017-05-02 08:10:10 |   19 |
|  2 | Glon Ho      | 2017-05-03 12:10:10 |   18 |
|  3 | 赵敏         | 2017-05-03 14:10:10 |   17 |
|  4 | Jacky Cheung | 2017-05-02 14:00:00 |   22 |
+----+--------------+---------------------+------+
4 rows in set (0.00 sec)root@localhost [glon_ho]>select * from glon ORDER BY id limit 4, 4;
+----+-----------+---------------------+------+
| id | name      | create_time         | age  |
+----+-----------+---------------------+------+
|  5 | 周芷若    | 2017-05-02 14:00:00 |   16 |
|  6 | Andy Lau  | 2017-05-02 14:00:00 |   50 |
|  7 | 至尊宝    | 2017-05-02 14:00:00 |   20 |
|  8 | 刘三姐    | 2017-05-02 14:00:00 |   19 |
+----+-----------+---------------------+------+
4 rows in set (0.00 sec)

总结

MySQL 使用 limit 进行分页时，可能会出现重复数据，通过加入 order by 子句可以解决，但是需要注意的是，如果排序字段有相同值的情况下，由于排序字段数据重复,可能会导致每次查询排序后结果顺序不同，分页还是会出现重复数据，这时可以加入第二个排序字段，提高排序的唯一性，最好保证排序的字段在表中的值是唯一的，这样就可以少写一个排序字段，增加查询效率，因为 order by 后面有多个排序字段时，无法用到索引。

当分页重复发生了，最简单的方法就是在排序字段（如 create time）上加索引，如果还是无法解决，在 order by 上明示 primary key（即在order by中增加id主键字段的排序），这个问题就能非常圆满的解决了。