如果您只需要结果集中的指定行数，请在查询中使用 LIMIT 子句，而不是获取整个结果集并丢弃多余的数据。

MySQL 有时会优化具有 LIMIT row_count 子句且没有 HAVING 子句的查询。

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

影响执行计划的一个因素是 LIMIT，因此带有和不带有 LIMIT 的 ORDER BY 查询可能会以不同的顺序返回行。考虑以下查询，它按 category 列排序，但对于 id 和 rating 列是非确定性的。

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 |
+----+----------+--------+

包含 LIMIT 可能会影响每个 category 值的行顺序。例如，这是一个有效的查询结果。

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 |
+----+----------+--------+

在每种情况下，行都按 ORDER BY 列排序，这正是 SQL 标准所要求的。

如果需要确保使用和不使用 LIMIT 时行顺序一致，请在 ORDER BY 子句中添加额外的列，使排序结果确定性。例如，如果 id 值是唯一的，可以通过以下方式排序，使给定 category 值的行按照 id 顺序排列

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 |
+----+----------+--------+

对于带有 ORDER BY 或 GROUP BY 以及 LIMIT 子句的查询，优化器默认情况下会尝试选择一个有序索引，如果这样做可以加速查询执行。在某些情况下，使用其他优化可能会更快，可以通过将 optimizer_switch 系统变量的 prefer_ordering_index 标志设置为 off 来关闭此优化。

示例: 首先，我们创建一个表 t 并按如下所示填充数据

# Create and populate a table t:

mysql> CREATE TABLE t (
    ->     id1 BIGINT NOT NULL,
    ->     id2 BIGINT NOT NULL,
    ->     c1 VARCHAR(50) NOT NULL,
    ->     c2 VARCHAR(50) NOT NULL,
    ->  PRIMARY KEY (id1),
    ->  INDEX i (id2, c1)
    -> );

# [Insert some rows into table t - not shown]

验证 prefer_ordering_index 标志是否已启用

mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

由于以下查询包含 LIMIT 子句，我们期望它使用有序索引（如果可能）。在这种情况下，正如我们从 EXPLAIN 输出中看到的那样，它使用了表的主键。

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: i
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 70.00
        Extra: Using where

现在我们禁用 prefer_ordering_index 标志，并重新运行相同的查询；这次它使用了索引 i（它包含 WHERE 子句中使用的 id2 列），并进行文件排序。

mysql> SET optimizer_switch = "prefer_ordering_index=off";

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 8
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index condition; Using filesort

另请参阅 第 10.9.2 节，“可切换优化”。