小心避坑:MySQL分頁時出現的資料重複問題

語言: CN / TW / HK

點選進入“PHP開源社群”    

免費獲取進階面試、文件、影片資源

0 問題描述

在MySQL中我們通常會採用limit來進行翻頁查詢,比如limit(0,10)表示列出第一頁的10條資料,limit(10,10)表示列出第二頁。但是,當limit遇到order by的時候,可能會出現翻到第二頁的時候,竟然又出現了第一頁的記錄。

具體如下:

SELECT
`post_title`,
`post_date`
FROM
post
WHERE
`post_status` = 'publish'
ORDER BY
view_count desc
LIMIT
5, 5

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

SELECT
*
FROM
post
WHERE
post_status = 'publish'
ORDER BY
view_count desc
LIMIT
5, 5

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

SELECT
`post_title`,
`post_date`
FROM
post
WHERE
`post_status` = 'publish'
ORDER BY
view_count desc,
ID asc
LIMIT
5, 5

按理來說,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)     SELECT 
(2) DISTINCT <select_list>
(3) FROM <left_table>
(4) <join_type> JOIN <right_table>
(5) ON <join_condition>
(6) WHERE <where_condition>
(7) GROUP BY <group_by_list>
(8) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

執行順序依次為 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值處理。

原文連結:https://www.jianshu.com/p/544c319fd838

作者:猿碼道 

END

PHP開源社群

掃描關注  進入”PHP資料“

免費獲取進階

面試、文件、影片資源