摘要

能把復(fù)雜的知識(shí)講的簡(jiǎn)單很重要

在學(xué)習(xí)的過(guò)程中我們看過(guò)很多資料、視頻、文檔等，因?yàn)楝F(xiàn)在資料視頻都較多所以往往一個(gè)知識(shí)點(diǎn)會(huì)有多種多樣的視頻形式講解。除了推廣營(yíng)銷(xiāo)以外，確實(shí)有很多人的視頻講解非常優(yōu)秀，例如李永樂(lè)老師的短視頻課，可以在一個(gè)黑板上把那么復(fù)雜的知識(shí)，講解的那么容易理解，那么透徹。而我們學(xué)習(xí)編程的人也是，不只是要學(xué)會(huì)把知識(shí)點(diǎn)講明白，也要寫(xiě)明白。

問(wèn)題描述

在 MySQL 中我們通常會(huì)采用 limit 來(lái)進(jìn)行翻頁(yè)查詢(xún)，比如 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查詢(xún)的時(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也查出來(lái)。為了解決這個(gè)情況，在 ORDER BY 后面使用了兩個(gè)排序條件來(lái)解決這個(gè)問(wèn)題，如下：

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

按理來(lái)說(shuō)， MySQL 的排序默認(rèn)情況下是以主鍵ID作為排序條件的，也就是說(shuō)，如果在 view_count 相等的情況下，主鍵ID作為默認(rèn)的排序條件，不需要我們多此一舉加 ID asc。但是事實(shí)就是，MySQL 在 order by 和 limit 混用的時(shí)候，出現(xiàn)了排序的混亂情況。

分析問(wèn)題

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

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

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

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

也就是說(shuō)，MySQL 5.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)     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 時(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é)果一樣。

解決問(wèn)題

(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 的官方文檔中提供了 limit n 和 rownum < n 的方法，但卻沒(méi)有明確的定義分頁(yè)這個(gè)概念。

還有重要的一點(diǎn)，雖然上面的解決方法可以緩解用戶(hù)的這個(gè)問(wèn)題，但按照用戶(hù)的理解，依然還有問(wèn)題：比如，這個(gè)表插入比較頻繁，用戶(hù)查詢(xún)的時(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)題

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

用戶(hù)在使用 Oracle 或 MySQL 的時(shí)候，發(fā)現(xiàn) MySQL 總是有序的， Oracle 卻很混亂，這個(gè)主要是因?yàn)?Oracle 是堆表， MySQL 是索引聚簇表的原因。所以沒(méi)有 order by 的時(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)題

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

到此這篇關(guān)于MySql分頁(yè)時(shí)使用limit+order by會(huì)出現(xiàn)數(shù)據(jù)重復(fù)問(wèn)題解決的文章就介紹到這了,更多相關(guān)MySql limit+order by分頁(yè)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論