神坑！MySQL中order by与limit不要一起用！

- 日理万妓 2023-01-06 05:36 117阅读 0赞

## 你知道的越多，不知道的就越多，业余的像一棵小草！ ##

你来，我们一起精进！你不来，我和你的竞争对手一起精进！

## 编辑：业余草 ##

## urlify.cn/2u2yEb ##

## 推荐：https://www.xttblog.com/?p=5144 ##

切记！MySQL 中 ORDER BY 与 LIMIT 不要一起用，有大坑......

现象与问题

ORDER BY 排序后，用 LIMIT 取前几条，发现返回的结果集的顺序与预期的不一样。

下面是我遇到的问题：

![format_png][]

![format_png 1][]

![format_png 2][]

![format_png 3][]

可以看到，带 LIMIT 与不带 LIMIT 的结果与我预期的不一样，而且“很不可思议”，真是百思不得其解。

后来百度了一下，如果 order by 的列有相同的值时，MySQL 会随机选取这些行，为了保证每次都返回的顺序一致可以额外增加一个排序字段（比如：id），用两个字段来尽可能减少重复的概率。

于是，改成 order by status，id：

![format_png 4][]

问题虽然是解决了，但还是看看官方文档上怎么说的吧！

LIMIT 查询优化

![format_png 5][]

*摘自“LIMIT 查询优化”*

如果你只需要结果集中的指定数量的行，那么请在查询中使用 LIMIT 子句，而不是抓取整个结果集并丢弃剩下那些你不要的数据。

MySQL 有时会优化一个包含 LIMIT 子句并且没有 HAVING 子句的查询：

**①**MySQL 通常更愿意执行全表扫描，但是如果你用 LIMIT 只查询几行记录的话，MySQL 在某些情况下可能会使用索引。

**②**如果你将 LIMIT row\_count 子句与 ORDER BY 子句组合在一起使用的话，MySQL 会在找到排序结果的第一个 row\_count 行后立即停止排序，而不是对整个结果进行排序。如果使用索引来完成排序，这将非常快。

如果必须执行文件排序，则在找到第一个 row\_count 行之前，选择所有与查询匹配但不包括 LIMIT 子句的行，并对其中大部分或所有行进行排序。

一旦找到第一个 row\_count 之后，MySQL 不会对结果集的任何剩余部分进行排序。

这种行为的一种表现形式是，一个 ORDER BY 查询带或者不带 LIMIT 可能返回行的顺序是不一样的。

**③**如果 LIMIT row\_count 与 DISTINCT 一起使用，一旦找到 row\_count 惟一的行，MySQL 就会停止。

**④**LIMIT 0 可以快速返回一个空的结果集，这是用来检测一个查询是否有效的一种很有用的方法。

**⑤**如果服务器使用临时表来解析查询，它将使用 LIMIT row\_count 子句来计算需要多少空间。

**⑥**如果 ORDER BY 不走索引，而且后面还带了 LIMIT 的话，那么优化器可能可以避免用一个合并文件，并使用内存中的 filesort 操作对内存中的行进行排序。

**⑦**如果 ORDER BY 列有多行具有相同的值，服务器可以自由地以任何顺序返回这些行，并且根据总体执行计划可能以不同的方式返回。换句话说，这些行的排序顺序对于无序列是不确定的。

影响执行计划的一个因素是 LIMIT，因此对于一个 ORDER BY 查询而言，带与不带 LIMIT 返回的行的顺序可能是不一样的。

看下面的例子：

![format_png 6][]

包含 LIMIT 可能会影响每一个 category 行的顺序。例如：

![format_png 7][]

如果你需要确保无论带不带 LIMIT 都要以相同的顺序返回，那么你可以在 ORDER BY 中包含附加列，以使顺序具有确定性。例如：

![format_png 8][]

小结

如果你只需要结果集中的某几行，那么建议使用 limit。这样这样的话可以避免抓取全部结果集，然后再丢弃那些你不要的行。

对于 order by 查询，带或者不带 limit 可能返回行的顺序是不一样的。

如果 limit row\_count 与 order by 一起使用，那么在找到第一个 row\_count 就停止排序，直接返回。

如果 order by 列有相同的值，那么 MySQL 可以自由地以任何顺序返回这些行。换言之，只要 order by 列的值不重复，就可以保证返回的顺序。

可以在 order by 子句中包含附加列，以使顺序具有确定性。

*参考文档：*

*  *https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html*
 *  *https://dev.mysql.com/doc/refman/5.7/en/*
 *  *https://dev.mysql.com/doc/*

![format_png 9][]

[format_png]: /images/20221119/def22c742ab649efa561982a4f2d9336.png
[format_png 1]: /images/20221119/d7173877b9a045208aef24c0bb81d5d6.png
[format_png 2]: /images/20221119/53d7a621f935420eb5302d4f46343cfa.png
[format_png 3]: /images/20221119/c2c97b3b343b436ca18b0a0acbd100e0.png
[format_png 4]: /images/20221119/05aecb1d695748d4a42fa1ace4d86fa2.png
[format_png 5]: /images/20221119/b2e06fd0d1b64fb3b0e9de44d2570f56.png
[format_png 6]: /images/20221119/028e77270457462893acaadd73e6fb5f.png
[format_png 7]: /images/20221119/fd339f0893734afc8ca32174575f3d4c.png
[format_png 8]: /images/20221119/367bf8a8ac7843bca08c00a3a877c18b.png
[format_png 9]: /images/20221119/86d06ff9d7014e5485efd3f3e9853046.png