02.15 小心避坑:MySQL分頁時使用 limit+order by 會出現數據重複問題


小心避坑:MySQL分頁時使用 limit+order by 會出現數據重複問題


0 問題描述

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

具體如下:

<code>SELECT
`post_title`,
`post_date`
FROM
post
WHERE
`post_status`='publish'
ORDERBY
view_countdesc
LIMIT
5,5/<code>

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

<code>SELECT
*
FROM
post
WHERE
post_status='publish'
ORDERBY
view_countdesc
LIMIT
5,5/<code>

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

<code>SELECT
`post_title`,
`post_date`
FROM
post
WHERE
`post_status`='publish'
ORDERBY
view_countdesc,
IDasc
LIMIT
5,5/<code>

按理來說,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語言時的執行順序:

<code>(1)SELECT
(2)DISTINCT<select>
(3)FROM<left>
(4)<join>JOIN<right>
(5)ON<join>
(6)WHERE<where>
(7)GROUPBY<group>
(8)HAVING<having>
(9)ORDERBY<order>
(10)LIMIT<limit>/<order>/<having>/<group>/<where>/<join>/<right>/<join>/<left>/<select>/<code>

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

Java知音,專注於Java實用文章推送,不容錯過!

來源:www.jianshu.com/p/544c319fd838


分享到:


相關文章: