如上，我们在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，如下：