如何解決MySQL order by limit語(yǔ)句的分頁(yè)數(shù)據(jù)重復(fù)問(wèn)題

如何解決MySQLorderbylimit語(yǔ)句的分頁(yè)數(shù)據(jù)重復(fù)問(wèn)題？0問(wèn)題描述在MySQL中我們通常會(huì)采用limit來(lái)進(jìn)行翻頁(yè)查詢，比如limit(0,10)表示列出第一頁(yè)的10條數(shù)據(jù)，limit(10,10)表示列出第二頁(yè)。但是，當(dāng)limit遇到orderby的時(shí)候，可能會(huì)出現(xiàn)翻到第二頁(yè)的時(shí)候，竟然又出現(xiàn)了第一頁(yè)的記錄。具體如下：SELECT`post_title`,`post_date`FROMpostWHERE`post_status`='publish'ORDERBYview_countdescLIMIT5,5

使用上述SQL查詢的時(shí)候，很有可能出現(xiàn)和LIMIT0,5相同的某條記錄。而如果使用如下方式，則不會(huì)出現(xiàn)重復(fù)的情況：SELECT*FROMpostWHEREpost_status='publish'ORDERBYview_countdescLIMIT5,5

但是，由于post表的字段很多，僅僅希望用這兩個(gè)字段，不想把post_content也查出來(lái)。為了解決這個(gè)情況，在ORDERBY后面使用了兩個(gè)排序條件來(lái)解決這個(gè)問(wèn)題，如下：SELECT`post_title`,`post_date`FROMpostWHERE`post_status`='publish'ORDERBYview_countdesc,IDascLIMIT5,5按理來(lái)說(shuō)，MySQL的排序默認(rèn)情況下是以主鍵ID作為排序條件的也就是說(shuō)，如果在view_count相等的情況下，主鍵ID作為默認(rèn)的排序條件，不需要我們多此一舉加IDasc。但是事實(shí)就是，MySQL再orderby和limit混用的時(shí)候，出現(xiàn)了排序的混亂情況。1分析問(wèn)題在MySQL5.6的版本上，優(yōu)化器在遇到orderbylimit語(yǔ)句的時(shí)候，做了一個(gè)優(yōu)化，即使用了priorityqueue。使用priorityqueue的目的，就是在不能使用索引有序性的時(shí)候，如果要排序，并且使用了limitn，那么只需要在排序的過(guò)程中，保留n條記錄即可這樣雖然不能解決所有記錄都需要排序的開(kāi)銷，但是只需要sortbuffer少量的內(nèi)存就可以完成排序。之所以MySQL5.6出現(xiàn)了第二頁(yè)數(shù)據(jù)重復(fù)的問(wèn)題，是因?yàn)閜riorityqueue使用了堆排序的排序方法，而堆排序是一個(gè)不穩(wěn)定的排序方法也就是相同的值可能排序出來(lái)的結(jié)果和讀出來(lái)的數(shù)據(jù)順序不一致。MySQL5.5沒(méi)有這個(gè)優(yōu)化，所以也就不會(huì)出現(xiàn)這個(gè)問(wèn)題。也就是說(shuō)，MySQL5.5是不存在本文提到的問(wèn)題的，5.6版本之后才出現(xiàn)了這種情況。再看下MySQL解釋sql語(yǔ)言時(shí)的執(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)

GROUPBY<group_by_list>

(8)

HAVING<having_condition>

(9)

ORDERBY<order_by_condition>

(10)

LIMIT<limit_number>執(zhí)行順序依次為

form…where…select…orderby…limit…由于上述priorityqueue的原因，在完成select之后，所有記錄是以堆排序的方法排列的搜索后端架構(gòu)師公眾號(hào)回復(fù)“架構(gòu)整潔”，送你一份驚喜禮包。在進(jìn)行orderby時(shí)，僅把view_count值大的往前移動(dòng)。但由于limit的因素，排序過(guò)程中只需要保留到5條記錄即可view_count并不具備索引有序性，所以當(dāng)?shù)诙?yè)數(shù)據(jù)要展示時(shí)，mysql見(jiàn)到哪一條就拿哪一條因此，當(dāng)排序值相同的時(shí)候，第一次排序是隨意排的，第二次再執(zhí)行該sql的時(shí)候，其結(jié)果應(yīng)該和第一次結(jié)果一樣。2解決方法（1）索引排序字段如果在字段添加上索引，就直接按照索引的有序性進(jìn)行讀取并分頁(yè)，從而可以規(guī)避遇到的這個(gè)問(wèn)題。（2）正確理解分頁(yè)分頁(yè)是建立在排序的基礎(chǔ)上，進(jìn)行了數(shù)量范圍分割。排序是數(shù)據(jù)庫(kù)提供的功能，而分頁(yè)卻是衍生的出來(lái)的應(yīng)用需求。在MySQL和Oracle的官方文檔中提供了limitn和rownum<n的方法，但卻沒(méi)有明確的定義分頁(yè)這個(gè)概念。還有重要的一點(diǎn)，雖然上面的解決方法可以緩解用戶的這個(gè)問(wèn)題，但按照用戶的理解，依然還有問(wèn)題比如，這個(gè)表插入比較頻繁，用戶查詢的時(shí)候，在read-committed的隔離級(jí)別下，第一頁(yè)和第二頁(yè)仍然會(huì)有重合。所以，分頁(yè)一直都有這個(gè)問(wèn)題，不同場(chǎng)景對(duì)數(shù)據(jù)分頁(yè)都沒(méi)有非常高的準(zhǔn)確性要求。（3）一些常見(jiàn)的數(shù)據(jù)庫(kù)排序問(wèn)題不加orderby的時(shí)候的排序問(wèn)題用戶在使用Oracle或MySQL的時(shí)候，發(fā)現(xiàn)MySQL總是有序的，Oracle卻很混亂，這個(gè)主要是因?yàn)镺racle是堆表，MySQL是索引聚簇表的原因。所以沒(méi)有orderby的時(shí)候，數(shù)據(jù)庫(kù)并不保證記錄返回的順序性，并且不保證每次返回都一致的。分頁(yè)問(wèn)題分頁(yè)重復(fù)的問(wèn)題如前面所描述的，分頁(yè)是在數(shù)據(jù)庫(kù)提供的排序功能的基礎(chǔ)上，衍生出來(lái)的應(yīng)用需求，數(shù)據(jù)庫(kù)并不保證分頁(yè)的重復(fù)問(wèn)題。NULL值和空串問(wèn)題不同