如上，我們在MySQL 中常用 order by 來進行排序，再結合使用 limit 來實現數據的分頁獲取，然而這種寫法卻隱藏著較深的使用陷阱——在排序字段有數據重複的情況下，可能出現排序結果與預期不一致的問題，即分頁查詢數據時，不同分頁之間存在相同的數據。

接下來我們詳細描述一下這個陷阱及解決方案。

---

問題覆盤

我們通過三步重現一下這個問題：

1. 根據age升序取前20條（ORDER BY + LIMIT 20）
2. 根據age升序取前10 條，第一頁（ORDER BY + LIMIT 10）
3. 根據age升序取前10~20條，第二頁（ORDER BY + LIMIT 10，20）

首先，使用 order by 對age進行排序，通過 limit 限制返回前20條記錄，SQL如下。

查詢結果如下：

---

然後，同樣使用 order by 對age進行排序，通過 limit 限制返回前10條記錄，作為第一頁數據，SQL如下：

查詢結果如下，我們上一步比較一下，發現前10條記錄竟然存在不一樣的地方，如下綠框中數據，在上一步的limit20結果中並不屬於前10條。

---

最後，同樣使用 order by 對 age 進行排序，通過 limit 限制返回前第11~20條記錄，作為第二頁數據，SQL如下：

查詢結果如下，我們也同樣發現了存在於 limit 10 裡面的記錄，如黃色框。

分析上面的數據，出現重複的數據項存在一個比較明顯的特徵，那就是他們的排序字段（age）值相同。也就是說，order by查詢與limit 只保證排序字段不同的其結果集時是絕對有序的，排序值相同的結果不保證其順序的一致性，MySQL是會隨機的順序返回查詢結果的

，具體依賴對應的執行計劃。

---

求證MySQL參考手冊

在 MySQL5.7參考手冊的 LIMIT Query Optimization 章節中提到：

If you combine LIMIT row_count with ORDER BY, MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without the LIMIT clause are selected, and most or all of them are sorted, before the first row_count are found. After the initial rows have been found, MySQL does not sort any remainder of the result set.

One manifestation of this behavior is that an ORDER BY query with and without LIMIT may return rows in different order, as described later in this section.

若將 order by 與 limit row_count 一起使用，MySQL會在找排序結果中的第一個“第N條記錄” 時就停止，而不是對整個結果進行排序。如果使用索引進行排序，那麼這個操作就會非常快。如果必須要通過文件排序，所有符合查詢條件的記錄都將被選中，並且所有的數據都將被排序，直到第一個 “第N條記錄” 被找到。在第一條的數據都被找到之後，MySQL不會繼續把結果中剩餘的數據進行排序。

這種實現行為的表現之一就是order by查詢在配合 limit 使用 和不配合limit使用的情況下的返回記錄，排序情況是不同的。

---

在 LIMIT Query Optimization 章節中，同時也提到：

If an index is not used for ORDER BY but a LIMIT clause is also present, the optimizer may be able to avoid using a merge file and sort the rows in memory using an in-memory filesort operation. For details, see The In-Memory filesort Algorithm.

如果 order by 不使用索引，同時還存在 limit 子句，優化器可能會使用 filesort 操作對內存中的行進行排序（in-memory sort）。

在 LIMIT Query Optimization章節最後，提供了一個例子，與我們遇到的問題一樣。此外，給出解決方案——在order by中指定一個二級排序字段，這個字段需唯一，這樣就保證了整個排序結果的有序性，如下：

---

解決方案

正如 MySQL 參考手冊中提到的，在order by 指定的排序字段後，增加加一個二級排序字段，這個字段需要絕對有序，這樣就保證了整個排序結果的有序性，接下來我們改寫下之前的SQL，如下：

---