MySQL order by, limit 同时使用问题

MySQL 文档原文

建议去阅读一下 MySQL 文档

问题

如果 order by 排序属性的值不唯一, order by 与 limit 同时使用可能会出现查询结果不同的现象.

例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

原因

文档原文如下:

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.

如果你将 LIMIT row_count 子句与 ORDER BY 子句组合在一起使用的话, MySQL 会找到排序结果的第一个 row_count 后立即停止排序, 而不是对整个结果进行排序. 如果使用索引来完成排序, 这将非常快. 如果必须执行文件排序, 则在找到第一个 row_count 行之前, 选择所有与查询匹配但不包括 LIMIT 子句的行, 并对其中大部分或所有行进行排序. 一旦找到第一个 row_count 之后, MySQL 不会对结果集的任何剩余部分进行排序

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

解决方法

在 order by 子句中包含其他列以使顺序具有确定性.

例如: 假如 id 的值是唯一的, 则可以通过下面的方法解决此问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+