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的時候，可能會出現翻到第二頁的時候，竟然又出現了第一頁的記錄。
具體如下：
<code>SELECT
`post_title`,
`post_date`
FROM
post
WHERE
`post_status` = 'publish'
ORDER BY
view_count desc
LIMIT
5, 5
/<code>
使用上述SQL查詢的時候，很有可能出現和LIMIT 0,5相同的某條記錄。而如果使用如下方式，則不會出現重複的情況：
<code>SELECT
*
FROM
post
WHERE
post_status = 'publish'
ORDER BY
view_count desc
LIMIT
5, 5
/<code>
但是，由於post表的字段很多，僅僅希望用這兩個字段，不想把post_content也查出來。為了解決這個情況，在ORDER BY後面使用了兩個排序條件來解決這個問題，如下：
<code>SELECT
`post_title`,
`post_date`
FROM
post
WHERE
`post_status` = 'publish'
ORDER BY
view_count desc,
ID asc
LIMIT
5, 5
/<code>
按理來說，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語言時的執行順序：
<code>(1) SELECT
(2) DISTINCT
(3) FROM
(4) JOIN
(5) ON
(6) WHERE
(7) GROUP BY
(8) HAVING
(9) ORDER BY
(10) LIMIT
/<code>
執行順序依次為 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的時候，數據庫並不保證記錄返回的順序性，並且不保證每次返回都一致的。
分頁問題 分頁重複的問題
如前面所描述的，分頁是在數據庫提供的排序功能的基礎上，衍生出來的應用需求，數據庫並不保證分頁的重複問題。
值和空串問題
不同的數據庫對於值和空串的理解和處理是不一樣的，比如Oracle 和值是無法比較的，既不是相等也不是不相等，是未知的。而對於空串，在插入的時候，MySQL是一個字符串長度為0的空串，而Oracle則直接進行值處理。
之前，給大家發過三份Java面試寶典，這次新增了一份，目前總共是四份面試寶典，相信在跳槽前一個月按照面試寶典準備準備，基本沒大問題。
《java面試寶典5.0》(初中級)
《350道Java面試題：整理自100+公司》（中高級）
《資深java面試寶典-視頻版》（資深）
《Java[BAT]面試必備》（資深）
分別適用於初中級，中高級，資深級工程師的面試複習。
內容包含java基礎、javaweb、mysql性能優化、JVM、鎖、百萬併發、消息隊列，高性能緩存、反射、Spring全家桶原理、微服務、Zookeeper、數據結構、限流熔斷降級等等。
看到這裡，證明有所收穫
