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

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

表數據

order by + limit分頁查詢

查詢第1頁

查詢第2頁

可以看到第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加上主鍵約束，排序字段添加索引

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

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

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

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

分頁問題

分頁重復的問題

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