phplimitorderby技巧_MySQL中Order by 结合 Limit 运用时的潜在陷阱

文章目录 [+]

如上，我们在MySQL 中常用 order by 来进行排序，再结合利用 limit 来实现数据的分页获取，然而这种写法却隐蔽着较深的利用陷阱——在排序字段有数据重复的情形下，可能涌现排序结果与预期不一致的问题，即分页查询数据时，不同分页之间存在相同的数据。

接下来我们详细描述一下这个陷阱及办理方案。

phplimitorderby技巧_MySQL中Order by 结合 Limit 运用时的潜在陷阱

问题复盘

我们通过三步重现一下这个问题：

phplimitorderby技巧_MySQL中Order by 结合 Limit 运用时的潜在陷阱

（图片来自网络侵删）

根据age升序取前20条（ORDER BY + LIMIT 20）根据age升序取前10 条，第一页（ORDER BY + LIMIT 10）根据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，如下：