20大進(jìn)階架構(gòu)專題每日送達(dá)

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

進(jìn)入主題前先插一下，當(dāng)當(dāng)優(yōu)惠碼福利來一波！當(dāng)當(dāng)全場(chǎng)自營(yíng)圖書5折，用優(yōu)惠碼：J2JYFK(長(zhǎng)按復(fù)制)，滿200(原價(jià)400)再減30，相當(dāng)于170=400，四折！使用渠道：當(dāng)當(dāng)小程序或當(dāng)當(dāng)APP。使用時(shí)間：4/10-4/23。最近鬼吹燈之龍嶺迷窟正在熱播，作為原著黨，還是覺得看書更加刺激驚悚，喜歡驚悚的不要錯(cuò)過。

1、問題描述

在MySQL中我們通常會(huì)采用limit來進(jìn)行翻頁(yè)查詢，比如limit(0,10)表示列出第一頁(yè)的10條數(shù)據(jù)，limit(10,10)表示列出第二頁(yè)。但是，當(dāng)limit遇到order by的時(shí)候，可能會(huì)出現(xiàn)翻到第二頁(yè)的時(shí)候，竟然又出現(xiàn)了第一頁(yè)的記錄。

具體如下：

SELECT
`post_title`,
`post_date`
FROM
post
WHERE
`post_status` = 'publish'
ORDER BY
view_count desc
LIMIT
5, 5

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

SELECT
*
FROM
post
WHERE
post_status = 'publish'
ORDER BY
view_count desc
LIMIT
5, 5

但是，由于post表的字段很多，僅僅希望用這兩個(gè)字段，不想把post_content也查出來。為了解決這個(gè)情況，在ORDER BY后面使用了兩個(gè)排序條件來解決這個(gè)問題，如下：

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。但是事實(shí)就是，MySQL再order by和limit混用的時(shí)候，出現(xiàn)了排序的混亂情況。

2、分析問題

在MySQL 5.6的版本上，優(yōu)化器在遇到order by limit語句的時(shí)候，做了一個(gè)優(yōu)化，即 使用了priority queue。

使用 priority queue 的目的，就是在不能使用索引有序性的時(shí)候，如果要排序，并且使用了limit n，那么只需要在排序的過程中，保留n條記錄即可，這樣雖然不能解決所有記錄都需要排序的開銷，但是只需要 sort buffer 少量的內(nèi)存就可以完成排序。

之所以MySQL 5.6出現(xiàn)了第二頁(yè)數(shù)據(jù)重復(fù)的問題，是因?yàn)?priority queue 使用了堆排序的排序方法，而堆排序是一個(gè)不穩(wěn)定的排序方法，也就是相同的值可能排序出來的結(jié)果和讀出來的數(shù)據(jù)順序不一致。

MySQL 5.5 沒有這個(gè)優(yōu)化，所以也就不會(huì)出現(xiàn)這個(gè)問題。

也就是說，MySQL 5.5是不存在本文提到的問題的，5.6版本之后才出現(xiàn)了這種情況。

再看下MySQL解釋sql語言時(shí)的執(zhí)行順序：

(1) SELECT
(2) DISTINCT
(3) FROM
(4) JOIN
(5) ON
(6) WHERE
(7) GROUP BY
(8) HAVING
(9) ORDER BY
(10) LIMIT

執(zhí)行順序依次為 form… where… select… order by… limit…，由于上述priority queue的原因，在完成select之后，所有記錄是以堆排序的方法排列的，在進(jìn)行order by時(shí)，僅把view_count值大的往前移動(dòng)。

但由于limit的因素，排序過程中只需要保留到5條記錄即可，view_count并不具備索引有序性，所以當(dāng)?shù)诙?yè)數(shù)據(jù)要展示時(shí)，mysql見到哪一條就拿哪一條，因此，當(dāng)排序值相同的時(shí)候，第一次排序是隨意排的，第二次再執(zhí)行該sql的時(shí)候，其結(jié)果應(yīng)該和第一次結(jié)果一樣。

3、解決方法

(1) 索引排序字段

如果在字段添加上索引，就直接按照索引的有序性進(jìn)行讀取并分頁(yè)，從而可以規(guī)避遇到的這個(gè)問題。

(2) 正確理解分頁(yè)

分頁(yè)是建立在排序的基礎(chǔ)上，進(jìn)行了數(shù)量范圍分割。排序是數(shù)據(jù)庫(kù)提供的功能，而分頁(yè)卻是衍生出來的應(yīng)用需求。

在MySQL和Oracle的官方文檔中提供了limit n和rownum < n的方法，但卻沒有明確的定義分頁(yè)這個(gè)概念。

還有重要的一點(diǎn)，雖然上面的解決方法可以緩解用戶的這個(gè)問題，但按照用戶的理解，依然還有問題：比如，這個(gè)表插入比較頻繁，用戶查詢的時(shí)候，在read-committed的隔離級(jí)別下，第一頁(yè)和第二頁(yè)仍然會(huì)有重合。

所以，分頁(yè)一直都有這個(gè)問題，不同場(chǎng)景對(duì)數(shù)據(jù)分頁(yè)都沒有非常高的準(zhǔn)確性要求。

(3) 一些常見的數(shù)據(jù)庫(kù)排序問題

不加order by的時(shí)候的排序問題

用戶在使用Oracle或MySQL的時(shí)候，發(fā)現(xiàn)MySQL總是有序的，Oracle卻很混亂，這個(gè)主要是因?yàn)镺racle是堆表，MySQL是索引聚簇表的原因。所以沒有order by的時(shí)候，數(shù)據(jù)庫(kù)并不保證記錄返回的順序性，并且不保證每次返回都一致的。

分頁(yè)問題 分頁(yè)重復(fù)的問題

如前面所描述的，分頁(yè)是在數(shù)據(jù)庫(kù)提供的排序功能的基礎(chǔ)上，衍生出來的應(yīng)用需求，數(shù)據(jù)庫(kù)并不保證分頁(yè)的重復(fù)問題。

值和空串問題

不同的數(shù)據(jù)庫(kù)對(duì)于值和空串的理解和處理是不一樣的，比如Oracle 和值是無法比較的，既不是相等也不是不相等，是未知的。而對(duì)于空串，在插入的時(shí)候，MySQL是一個(gè)字符串長(zhǎng)度為0的空串，而Oracle則直接進(jìn)行值處理。

之前，給大家發(fā)過三份Java面試寶典，這次新增了一份，目前總共是四份面試寶典，相信在跳槽前一個(gè)月按照面試寶典準(zhǔn)備準(zhǔn)備，基本沒大問題。

《java面試寶典5.0》(初中級(jí))

《350道Java面試題：整理自100+公司》(中高級(jí))

《資深java面試寶典-視頻版》(資深)

《Java[BAT]面試必備》(資深)

分別適用于初中級(jí)，中高級(jí)，資深級(jí)工程師的面試復(fù)習(xí)。

內(nèi)容包含java基礎(chǔ)、javaweb、mysql性能優(yōu)化、JVM、鎖、百萬并發(fā)、消息隊(duì)列，高性能緩存、反射、Spring全家桶原理、微服務(wù)、Zookeeper、數(shù)據(jù)結(jié)構(gòu)、限流熔斷降級(jí)等等。

看到這里，證明有所收獲

總結(jié)

以上是生活随笔為你收集整理的layui分页limit不显示_小心避坑：MySQL分页时使用 limit+order by 会出现数据重复问题...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。