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主键字段的排序），这个问题就能非常圆满的解决了。