寫分頁查詢介面，order by和limit混用的時候，出現了排序的混亂情況 在進行第N頁查詢時，出現與第一前面頁碼的數據一樣的記錄。 ...
踩坑描述:
寫分頁查詢介面，order by和limit混用的時候，出現了排序的混亂情況 在進行第N頁查詢時，出現與第一前面頁碼的數據一樣的記錄。
問題
在MySQL中分頁查詢，我們經常會用limit，如:limit(0,20)表示查詢第一頁的20條數據，limit(20,20)表示查詢第二頁的數據。業務上我們通常也會在分頁的時候加上排序 order by;
但是當limit和order by一起使用的時候，有可能會出現第N頁的數據，竟然和前面頁碼的數據有重覆
例如：
SELECT a,b FROM table WHERE c=1 ORDER BY d desc LIMIT 0,20
使用上述SQL查詢的時候，很有可能和LIMIT 20,20查出相同的某條數據。為瞭解決這個問題，我們在ORDER BY後面加上了ID（唯一索引頁可以）排序來進行規避，
如下：
SELECT a,b FROM table WHERE c=1 ORDER BY d desc，id desc LIMIT 0,20
理論上，MySQL的排序預設情況下是以主鍵ID作為排序條件的，也就是說，如果在條件d相等的情況下，主鍵id會作為預設的排序條件，不需要我們多此一舉加ID asc。但是事實就是，MySQL在order by和limit同時使用的情況下，出現了排序的混亂情況。
分析
在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版本之後才出現了這種情況。
(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時，僅把d值大的往前移動。但由於limit的因素，排序過程中只需要保留到20條記錄即可，d並不具備索引有序性，所以當第二頁數據要展示時，mysql見到哪一條就拿哪一條，因此，當排序值相同的時候，第一次排序是隨意排的，第二次再執行該sql的時候，其結果應該和第一次結果有可能一樣。
解決方法
1.儘量使用不重覆的值進行排序
如果在欄位添加上索引，就直接按照索引的有序性進行讀取並分頁（這個欄位如果有重覆值分頁會有可能出現重覆）。
可以最後加上ID排序，也不會影響業務
2.正確理解分頁
分頁是建立在排序的基礎上，進行了數量範圍分割。排序是資料庫提供的功能，而分頁卻是衍生出來的應用需求。 在MySQL和Oracle的官方文檔中提供了limit n和rownum < n的方法，但卻沒有明確的定義分頁這個概念。 還有重要的一點，雖然上面的解決方法可以緩解用戶的這個問題，但按照用戶的理解，依然還有問題：比如，這個表插入比較頻繁，用戶查詢的時候，在read-committed的隔離級別下，第一頁和第二頁仍然會有重合，這個可以使用ID來規避。 所以，分頁一直都有這個問題，不同場景對數據分頁都沒有非常高的準確性要求。
3.一些常見的資料庫排序問題
不加order by的時候的排序問題
用戶在使用Oracle或MySQL的時候，發現MySQL總是有序的，Oracle卻很混亂，這個主要是因為Oracle是堆表，MySQL是索引聚簇表的原因。所以沒有order by的時候，資料庫並不保證記錄返回的順序性，並且不保證每次返回都一致的。 分頁問題-分頁重覆的問題 如前面所描述的，分頁是在資料庫提供的排序功能的基礎上，衍生出來的應用需求，資料庫並不保證分頁的重覆問題。 NULL值和空串問題 不同的資料庫對於NULL值和空串的理解和處理是不一樣的，比如Oracle NULL和NULL值是無法比較的，既不是相等也不是不相等，是未知的。而對於空串，在插入的時候，MySQL是一個字元串長度為0的空串，而Oracle則直接進行NULL值處理。
深分頁問題
有些時候，我們需要偏移一定量數據之後，獲取某些數據，就很容易想到用limit，但是，如果偏移量很大時，就會發現SQL執行起來非常非常慢了，因為，偏移量會分頁讀取到buffpool中，數據量大，占用的buffpool空間就會大，而這個空間大小是配置的，一般不會很大；
其次需要從一開始就掃描數據，最後要捨棄前面大量數據，只保留需要的那幾條數據，而且過程還有可能需要回表操作，導致了慢sql。
對於這個問題的優化，建議寫一個過濾條件（比如：自增主鍵ID或有序的欄位），再與limit結合實現。
作者：京東零售 馬成龍
來源：京東雲開發者社區 轉載請註明來源
