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

2021-02-14 java進階架構師

點擊上方「java進階架構師」,選擇右上角「置頂公眾號

20大進階架構專題每日送達

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


進入主題前先插一下,噹噹優惠碼福利來一波!噹噹全場自營圖書5折,用優惠碼:J2JYFK(長按複製),滿200(原價400)再減30,相當於170=400,四折!使用渠道:噹噹小程序或噹噹APP。使用時間:4/10-4/23。最近鬼吹燈之龍嶺迷窟正在熱播,作為原著黨,還是覺得看書更加刺激驚悚,喜歡驚悚的不要錯過。
1、問題描述

在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混用的時候,出現了排序的混亂情況。

2、分析問題

在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的時候,其結果應該和第一次結果一樣。

3、解決方法(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基礎、javaweb、mysql性能優化、JVM、鎖、百萬並發、消息隊列,高性能緩存、反射、Spring全家桶原理、微服務、Zookeeper、數據結構、限流熔斷降級等等。

獲取方式:點「在看」,V信關註上述單號並回復 【面試】即可領取,更多精彩陸續奉上。

相關焦點

  • MySQL中order by與limit不要一起用!
    後來百度了一下,如果 order by 的列有相同的值時,MySQL 會隨機選取這些行,為了保證每次都返回的順序一致可以額外增加一個排序欄位(比如:id),用兩個欄位來儘可能減少重複的概率。於是,改成 order by status,id:
  • MySQL性能優化
    但是,沒有冗餘的資料庫未必是最好的資料庫,有時為了提高運行效率,就必須降低範式標準,適當保留冗餘數據。具體做法是: 在概念數據模型設計時遵守第三範式,降低範式標準的工作放到物理數據模型設計時考慮。降低範式就是增加欄位,允許冗餘。
  • 14-excel使用分頁符分頁列印數據
    excel使用分頁符分頁列印數據以及調整文檔比例如果需要將工作表的數據強制分頁列印,可以通過插入分頁符來實現,分頁符將出現所選內容的左上方;默認情況下,excel按百分之百的列印比例將多頁內容列印在一頁或將不足一頁的內容列印滿一頁。
  • MySQL中按周統計數據
    環境準備準備建表語句使用中使用到的表結構如和建表語句如下:CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,`order_no測試使用到的初始化數據使用如下的SQL進行初始化:/*如下SQL執行多次即可產生多條測試數據*/insertinto test(order_no, create_timestamp)value(substr(rand(),3,18),date_add(date_add
  • delete後加 limit是個好習慣麼 !
    比如,在刪除執行中,第一條就命中了刪除行,如果 SQL 中有 limit 1;這時就 return 了,否則還會執行完全表掃描才 return。效率不言而喻。那麼,在日常執行 delete 時,我們是否需要養成加 limit 的習慣呢?是不是一個好習慣呢?在日常的 SQL 編寫中,你寫 delete 語句時是否用到過以下 SQL?
  • 電子數據取證之MySQL資料庫刪除數據的恢復指南
    MySQL 是最流行的關係型資料庫管理系統之一,在 WEB 應用方面,MySQL資料庫使用的比較多。公檢法部門在處理涉黃、詐騙的案件時,常常會遇到對涉案網站、論壇的伺服器進行取證,有時嫌疑人為了毀滅罪證,故意刪除數據,這就涉及到對MySQL資料庫進行數據恢復、固定和取證。
  • Mysql自增id用完了,到底會不會報錯?
    前些天有個粉絲在群裡發了一張圖片,成功引起了我的注意,圖片如下:大致內容是在mysql中,一個表的自增id用完了,繼續插入到底會不會報錯?說實話,工作這麼多年,我還真沒遇到這種問題,也沒太想過,我的第一反應應該是會報錯,否則mysql也不會有那麼多類型和長度的限制;但是同時我也很疑惑,說不定mysql有什麼保護機制,可以自動升級類型之類的,接下來我將帶領大家去一層層揭開這個疑點。我們可以想一下,資料庫為什麼需要主鍵?不需要主鍵行不行?
  • Mysql數據誤刪除快速回滾
    作者 | Video++極鏈科技OPSTeam整理 | 包包在資料庫操作中,難免會因為各種各樣的原因對數據造成損壞,這個時候就需要對資料庫快速恢復。傳統的方法會先恢復mysql備份,再去用mysqlbinlog抽取指定時間點的日誌,再恢復,這樣的操作比較耗時,容易出錯,那有沒有一種工具可以快速把誤刪除的操作SQL逆過來,然後重新插入誤刪除的數據呢?binlog2sql,就是一個很好的應用,它可以從MySQL binlog解析出SQL,根據不同選項,你可以得到原始SQL、回滾SQL、去除主鍵的INSERT SQL等。
  • Python連接MySQL資料庫方法介紹(超詳細!手把手項目案例操作)
    作者 | CDA數據分析師 來源 | CDA數據分析研究院本文涉及到的開發環境:作業系統 Windows 10資料庫 MySQL 8.0Python 3.7.2pip 19.0.3兩種方法進行資料庫的連接分別是PyMySQL和mysql.connector步驟:連接資料庫生成遊標對象執行SQL語句關閉遊標關閉連接PyMySQL
  • mysql指令、數據類型、表結構、約束學習記錄
    mysql常用口令--修改用戶密碼的命令mysqladmin -uroot -proot123 password mysql123--登錄mysql資料庫的命令mysql -uroot-proot123--顯示資料庫的命令show databases;--使用資料庫的命令use mysql;--顯示當前連接的資料庫select database
  • 技術分享 | MySQL:timestamp 時區轉換導致 CPU %sy 高的問題
    這個問題是一個朋友遇到的@風雲,並且這位朋友已經得出了近乎正確的判斷,下面進行一些描述。我們可以看到 40.4%sy 正是系統調用負載較高的表現,隨即朋友採集了 perf 如下:timestamp:佔用 4 字節,內部實現是新紀元時間(1970-01-01 00:00:00)以來的秒,那麼這種格式在展示給用戶的時候就需要做必要的時區轉換才能得到正確數據。下面我們通過訪問 ibd 文件來查看一下內部表示方法,使用到了我的兩個工具 innodb 和 bcview。詳細參考:https://www.jianshu.com/p/719f1bbb21e8。
  • 大量MySQL表導致服務變慢的問題
    切換之後,經過觀察,竟然沒有問題了。問題已經解決,那麼說明還是上面列出來的配置差別引起的問題。那麼解決之後,下面的工作就是重複一開始的工作,把 db1 下線,讓 db4 上線。此刻,之前的遷移工作已經完成,線上服務沒有問題。但……開發同學,能給我半個小時,讓我看看是哪個參數引起的麼? 得到的回答是:「迅速點,就這一次,給你 20 分鐘。」
  • MySQL基於MHA的FailOver過程
    當mha出現時,我們可以使用send_report以郵件報警的方式來獲得錯誤信息數據,方便了解資料庫狀態。(binlog_server)實時地接收主庫的數據,將數據保存到其他地區的機器上,可以理解為異地備份找到一臺額外機器,資料庫版本大於5.6,支持gtid並開啟在db03上配置no_master:表示不參與選主
  • 如何快速學習Mysql資料庫Select查詢?
    學習MySQL查詢及更新相關操作select - from -語句查詢調取表數據用法distinct 排除重複值用法having 分組過濾:用法:select【欄位】from【表名】where【條件】group by 【欄位】having【過濾條件】 ( 只能用於group by 之後,order by 之前,僅對分組使用。)
  • 寶馬limit燈什麼意思
    【太平洋汽車網】寶馬limit燈指的是汽車的限速。如果limit燈亮,說明汽車正在進行限速提示,可能是車主不小心按到了限速鍵,汽車就開始進行警報,這個功能在設置裡是可以關掉的,假如說汽車設置的限速為80km/h,當汽車的車速超過設定值時,limit燈就會報警。
  • MySQL怎麼刪除#sql開頭的臨時表
    例如,在對一張表(大表)添加索引時中途中斷、磁碟不足導致異常或正在添加索引時實例被kill等等情況所致。注意: 此類表空間文件不能直接rm -f的方式物理刪除,因為該信息記錄在ibdata的共享表空間裡,直接刪除後,後續實例重啟時會出現錯誤。3.
  • arma python 檢驗專題及常見問題 - CSDN
    k,autolag=AIC時會自動選擇滯後Number of Observations Used:樣本數量Critical Value(5%) : 顯著性水平為5%的臨界值。= st.arma_order_select_ic(ts_log_diff2,max_ar=5,max_ma=5,ic=['aic', 'bic', 'hqic'])order.bic_min_order'''我們常用的是AIC準則,AIC鼓勵數據擬合的優良性但是儘量避免出現過度擬合(Overfitting)的情況。