0 問題描述
在MySQL中我們通常會采用limit來進(jìn)行翻頁查詢颁独，比如limit(0,10)表示列出第一頁的10條數(shù)據(jù)奖磁，limit(10,10)表示列出第二頁渐裂。但是矮锈，當(dāng)limit遇到order by的時候霉翔，可能會出現(xiàn)翻到第二頁的時候，竟然又出現(xiàn)了第一頁的記錄苞笨。
具體如下：
SELECT `post_title`,`post_date` FROM post WHERE `post_status`='publish' ORDER BY view_count desc LIMIT 5,5
使用上述SQL查詢的時候债朵，很有可能出現(xiàn)和LIMIT 0,5相同的某條記錄。而如果使用如下方式瀑凝，則不會出現(xiàn)重復(fù)的情況：
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的排序默認(rèn)情況下是以主鍵ID作為排序條件的将塑，也就是說，如果在view_count相等的情況下蝌麸，主鍵ID作為默認(rèn)的排序條件点寥，不需要我們多此一舉加ID asc。但是事實就是来吩，MySQL再order by和limit混用的時候敢辩，出現(xiàn)了排序的混亂情況。
1 分析問題
在MySQL 5.6的版本上弟疆，優(yōu)化器在遇到order by limit語句的時候戚长，做了一個優(yōu)化，即使用了priority queue怠苔。
使用 priority queue 的目的同廉，就是在不能使用索引有序性的時候，如果要排序柑司，并且使用了limit n迫肖，那么只需要在排序的過程中，保留n條記錄即可攒驰，這樣雖然不能解決所有記錄都需要排序的開銷蟆湖，但是只需要 sort buffer 少量的內(nèi)存就可以完成排序。
之所以MySQL 5.6出現(xiàn)了第二頁數(shù)據(jù)重復(fù)的問題玻粪，是因為 priority queue 使用了堆排序的排序方法隅津，而堆排序是一個不穩(wěn)定的排序方法，也就是相同的值可能排序出來的結(jié)果和讀出來的數(shù)據(jù)順序不一致劲室。
MySQL 5.5 沒有這個優(yōu)化伦仍，所以也就不會出現(xiàn)這個問題。
也就是說痹籍，MySQL 5.5是不存在本文提到的問題的呢铆，5.6版本之后才出現(xiàn)了這種情況晦鞋。
再看下MySQL解釋sql語言時的執(zhí)行順序：
(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>
執(zhí)行順序依次為 form… where… select… order by… limit…蹲缠，由于上述priority queue的原因，在完成select之后悠垛，所有記錄是以堆排序的方法排列的线定，在進(jìn)行order by時，僅把view_count值大的往前移動确买。但由于limit的因素斤讥，排序過程中只需要保留到5條記錄即可，view_count并不具備索引有序性，所以當(dāng)?shù)诙摂?shù)據(jù)要展示時芭商，mysql見到哪一條就拿哪一條派草，因此，當(dāng)排序值相同的時候铛楣，第一次排序是隨意排的近迁，第二次再執(zhí)行該sql的時候，其結(jié)果應(yīng)該和第一次結(jié)果一樣簸州。
2 解決方法
-
索引排序字段
如果在字段添加上索引鉴竭，就直接按照索引的有序性進(jìn)行讀取并分頁，從而可以規(guī)避遇到的這個問題岸浑。
-
正確理解分頁
分頁是建立在排序的基礎(chǔ)上搏存，進(jìn)行了數(shù)量范圍分割。排序是數(shù)據(jù)庫提供的功能矢洲，而分頁卻是衍生的出來的應(yīng)用需求璧眠。
在MySQL和Oracle的官方文檔中提供了limit n和rownum < n的方法，但卻沒有明確的定義分頁這個概念读虏。還有重要的一點(diǎn)蛆橡，雖然上面的解決方法可以緩解用戶的這個問題，但按照用戶的理解掘譬，依然還有問題：比如泰演，這個表插入比較頻繁，用戶查詢的時候葱轩，在read-committed的隔離級別下睦焕，第一頁和第二頁仍然會有重合。
所以靴拱，分頁一直都有這個問題垃喊，不同場景對數(shù)據(jù)分頁都沒有非常高的準(zhǔn)確性要求。
-
一些常見的數(shù)據(jù)庫排序問題
不加order by的時候的排序問題
用戶在使用Oracle或MySQL的時候袜炕，發(fā)現(xiàn)MySQL總是有序的本谜，Oracle卻很混亂，這個主要是因為Oracle是堆表偎窘，MySQL是索引聚簇表的原因乌助。所以沒有order by的時候，數(shù)據(jù)庫并不保證記錄返回的順序性陌知，并且不保證每次返回都一致的他托。
分頁問題 分頁重復(fù)的問題
如前面所描述的，分頁是在數(shù)據(jù)庫提供的排序功能的基礎(chǔ)上仆葡，衍生出來的應(yīng)用需求赏参，數(shù)據(jù)庫并不保證分頁的重復(fù)問題。
NULL值和空串問題
不同的數(shù)據(jù)庫對于NULL值和空串的理解和處理是不一樣的，比如Oracle NULL和NULL值是無法比較的把篓，既不是相等也不是不相等纫溃，是未知的。而對于空串韧掩，在插入的時候皇耗，MySQL是一個字符串長度為0的空串，而Oracle則直接進(jìn)行NULL值處理揍很。
