小心避坑：MySQL分頁時出現的資料重複問題

全文連結：  

0 問題描述

在MySQL中我們通常會採用limit來進行翻頁查詢，比如limit(0,10)表示列出第一頁的10條資料，limit(10,10)表示列出第二頁。但是，當limit遇到order by的時候，可能會出現翻到第二頁的時候，竟然又出現了第一頁的記錄。

具體如下：

SELECT

  
`post_title`,

  
`post_date`


FROM

  post


WHERE

  
`post_status` = 
'publish'


ORDER 
BY

  view_count 
desc


LIMIT

  
5, 
5

使用上述SQL查詢的時候，很有可能出現和LIMIT 0,5相同的某條記錄。而如果使用如下方式，則不會出現重複的情況：

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的排序預設情況下是以主鍵ID作為排序條件的，也就是說，如果在view_count相等的情況下，主鍵ID作為預設的排序條件，不需要我們多此一舉加ID asc。 但是事實就是，MySQL再order by和limit混用的時候，出現了排序的混亂情況。

1 分析問題

在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語言時的執行順序：

(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>

執行順序依次為 form… where… select… order by… limit…，由於上述priority queue的原因，在完成select之後，所有記錄是以堆排序的方法排列的，在進行order by時，僅把view_count值大的往前移動。

但由於limit的因素，排序過程中只需要保留到5條記錄即可，view_count並不具備索引有序性，所以當第二頁資料要展示時，mysql見到哪一條就拿哪一條，因此，當排序值相同的時候，第一次排序是隨意排的，第二次再執行該sql的時候，其結果應該和第一次結果一樣。

......

檢視全文請點選文章頂部連結