如何解決：MySQL 分頁時使用 limit-order by 會出現數據重複問題

0 問題描述

具體如下：

使用上述 SQL 查詢的時候，很有可能出現和 LIMIT 0,5 相同的某條記錄。而如果使用如下方式，則不會出現重複的情況：

 1SELECT
 2  *
 3FROM
 4  post
 5WHERE
 6  post_status = 'publish'
 7ORDER BY
 8  view_count desc
 9LIMIT
10  5, 5
11
12

但是，由於 post 表的字段很多，僅僅希望用這兩個字段，不想把 post_content 也查出來。爲了解決這個情況，在 ORDER BY 後面使用了兩個排序條件來解決這個問題，如下：

 1SELECT
 2  `post_title`,
 3  `post_date`
 4FROM
 5  post
 6WHERE
 7  `post_status` = 'publish'
 8ORDER BY
 9  view_count desc,
10  ID asc
11LIMIT
12  5, 5
13
14

按理來說，MySQL 的排序默認情況下是以主鍵 ID 作爲排序條件的，也就是說，如果在 view_count 相等的情況下，主鍵 ID 作爲默認的排序條件，不需要我們多此一舉加 ID asc。但是事實就是，MySQL 再 order by 和 limit 混用的時候，出現了排序的混亂情況。

1 分析問題

在 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 版本之後纔出現了這種情況。

再看下 MySQL 解釋 sql 語言時的執行順序：

 1(1)     SELECT 
 2(2)     DISTINCT <select_list>
 3(3)     FROM <left_table>
 4(4)     <join_type> JOIN <right_table>
 5(5)     ON <join_condition>
 6(6)     WHERE <where_condition>
 7(7)     GROUP BY <group_by_list>
 8(8)     HAVING <having_condition>
 9(9)     ORDER BY <order_by_condition>
10(10)    LIMIT <limit_number>
11
12

執行順序依次爲 form… where… select… order by… limit…，由於上述 priority queue 的原因，在完成 select 之後，所有記錄是以堆排序的方法排列的，在進行 order by 時，僅把 view_count 值大的往前移動。

但由於 limit 的因素，排序過程中只需要保留到 5 條記錄即可，view_count 並不具備索引有序性，所以當第二頁數據要展示時，mysql 見到哪一條就拿哪一條，因此，當排序值相同的時候，第一次排序是隨意排的，第二次再執行該 sql 的時候，其結果應該和第一次結果一樣。

2 解決方法

1. 索引排序字段

如果在字段添加上索引，就直接按照索引的有序性進行讀取並分頁，從而可以規避遇到的這個問題。

2. 正確理解分頁

分頁是建立在排序的基礎上，進行了數量範圍分割。排序是數據庫提供的功能，而分頁卻是衍生出來的應用需求。

在 MySQL 和 Oracle 的官方文檔中提供了 limit n 和 rownum < n 的方法，但卻沒有明確的定義分頁這個概念。

還有重要的一點，雖然上面的解決方法可以緩解用戶的這個問題，但按照用戶的理解，依然還有問題：比如，這個表插入比較頻繁，用戶查詢的時候，在 read-committed 的隔離級別下，第一頁和第二頁仍然會有重合。

所以，分頁一直都有這個問題，不同場景對數據分頁都沒有非常高的準確性要求。

3. 一些常見的數據庫排序問題

不加 order by 的時候的排序問題

用戶在使用 Oracle 或 MySQL 的時候，發現 MySQL 總是有序的，Oracle 卻很混亂，這個主要是因爲 Oracle 是堆表，MySQL 是索引聚簇表的原因。所以沒有 order by 的時候，數據庫並不保證記錄返回的順序性，並且不保證每次返回都一致的。

分頁問題 分頁重複的問題

如前面所描述的，分頁是在數據庫提供的排序功能的基礎上，衍生出來的應用需求，數據庫並不保證分頁的重複問題。

NULL 值和空串問題

不同的數據庫對於 NULL 值和空串的理解和處理是不一樣的，比如 Oracle NULL 和 NULL 值是無法比較的，既不是相等也不是不相等，是未知的。而對於空串，在插入的時候，MySQL 是一個字符串長度爲 0 的空串，而 Oracle 則直接進行 NULL 值處理。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/aYR54k22p9FxlI4Yi9Ojvw

猜你喜歡