order by + limit分頁時數據重復問題及解決方法

Posted on by

問題描述：MYSQL version 5.6.8command 表結構

CREATE TABLE

command

(

ID INT NOT NULL,

NAME VARCHAR(16),

DESCRIPTION VARCHAR(32),

INDEX idx_command_id (ID)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8;

表數據

order by + limit分頁查詢

查詢第1頁

select * from command order by age limit 0,4;

查詢第2頁

select * from command order by age limit 4,4;

可以看到第2頁中查出瞭第1頁中存在的重復數據

原因分析：

查看以上語句的執行計劃

可以看到，order by limit時Mysql會進行優化，使用的是內存中的filesort文件排序，in memory filesort 使用的是優先級隊列（priority queue），優先級隊列使用的二叉堆；

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

因此，在limit n時，隻會堆排序前n個，且是不穩定排序，因此並不能保證字段值相同時的相對順序，因此分頁時可能造成重復；

MySQL 5.5 沒有這個優化，所以也就不會出現這個問題，5.6版本之後才出現瞭這種情況。

解決方案：

1. 新加一個排序字段，這個字段絕對有序，在第1個排序字段重復時， 使用第2個字段排序

2. 利用索引的有序性，如給id加上主鍵約束，排序字段添加索引

explain select id,age from command order by age limit 4,4

可以看到查詢走瞭索引，排序就穩定瞭，沒什麼問題

（3）一些常見的數據庫排序問題

不加order by的時候的排序問題
用戶在使用Oracle或MySQL的時候，發現MySQL總是有序的，Oracle卻很混亂，這個主要是因為Oracle是堆表，MySQL是索引聚簇表的原因。

所以沒有order by的時候，數據庫並不保證記錄返回的順序性，並且不保證每次返回都一致的。

分頁問題

分頁重復的問題

如前面所描述的，分頁是在數據庫提供的排序功能的基礎上，衍生出來的應用需求，數據庫並不保證分頁的重復問題。

到此這篇關於order by + limit分頁時數據重復的文章就介紹到這瞭,更多相關order by  limit分頁時數據重復內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：