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主鍵字段的排序），這個問題就能非常圓滿的解決了。