If you need only a specified number of rows from a result set, use a LIMIT clause in the query, rather than fetching the whole result set and throwing away the extra data.如果只需要结果集中指定数量的行，请在查询中使用LIMIT子句，而不是获取整个结果集并丢弃额外的数据。

MySQL sometimes optimizes a query that has a LIMIT row_count clause and no HAVING clause:MySQL有时会优化具有LIMIT row_count子句并且无HAVING子句的查询：

If multiple rows have identical values in the ORDER BY columns, the server is free to return those rows in any order, and may do so differently depending on the overall execution plan. 如果多行在ORDER BY列中具有相同的值，则服务器可以自由地以任何顺序返回这些行，并且根据总体执行计划的不同，返回的顺序也可能不同。In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.换句话说，这些行的排序顺序相对于非排序列是不确定的。

One factor that affects the execution plan is LIMIT, so an ORDER BY query with and without LIMIT may return rows in different orders. 影响执行计划的一个因素是LIMIT，因此有LIMIT和无LIMIT的ORDER BY查询可能会以不同的顺序返回行。Consider this query, which is sorted by the category column but nondeterministic with respect to the id and rating columns:考虑此查询，该查询由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 |
+----+----------+--------+

Including LIMIT may affect order of rows within each category value. 包含LIMIT可能会影响每个category值中的行顺序。For example, this is a valid query result:例如，这是一个有效的查询结果：

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

In each case, the rows are sorted by the ORDER BY column, which is all that is required by the SQL standard.在每种情况下，行都是按照ORDER BY列排序的，这是SQL标准所要求的全部内容。

If it is important to ensure the same row order with and without LIMIT, include additional columns in the ORDER BY clause to make the order deterministic. 如果重要的是确保具有LIMIT和不具有LIMIT的行顺序相同，请在ORDER BY子句中包含其他列，以使顺序具有确定性。For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this:例如，如果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 |
+----+----------+--------+

For a query with an ORDER BY or GROUP BY and a LIMIT clause, the optimizer tries to choose an ordered index by default when it appears doing so would speed up query execution. 对于带有ORDER BY或GROUP BY和LIMIT子句的查询，优化器会在默认情况下尝试选择一个有序索引，因为这样做会加快查询的执行速度。Prior to MySQL 8.0.21, there was no way to override this behavior, even in cases where using some other optimization might be faster. 在MySQL 8.0.21之前，即使在使用其他优化可能更快的情况下，也无法覆盖此行为。Beginning with MySQL 8.0.21, it is possible to turn off this optimization by setting the optimizer_switch system variable's prefer_ordering_index flag to off.从MySQL 8.0.21开始，可以通过将optimizer_switch系统变量的prefer_ordering_index标志设置为off来关闭此优化。

Example: First we create and populate a table t as shown here:示例：首先，我们创建并填充一个表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]

Verify that the prefer_ordering_index flag is enabled:验证是否已启用prefer_ordering_index标志：

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

Since the following query has a LIMIT clause, we expect it to use an ordered index, if possible. 因为下面的查询有一个LIMIT子句，如果可能的话，我们希望它使用一个有序索引。In this case, as we can see from the EXPLAIN output, it uses the table's primary key.在本例中，正如我们从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

Now we disable the prefer_ordering_index flag, and re-run the same query; this time it uses the index i (which includes the id2 column used in the WHERE clause), and a filesort:现在我们禁用preference_ordering_index标志，并重新运行相同的查询；这次它使用索引i（包括WHERE子句中使用的id2列）和filesort：

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

See also Section 8.9.2, “Switchable Optimizations”.另请参阅第8.9.2节，“可切换优化”。