MySQL-5.7-8.2.1.17 LIMIT Query Optimization

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 you select only a few rows with LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.

• 如果使用LIMIT只选择几行，MySQL在某些情况下会使用索引，而通常情况下它更喜欢执行全表扫描。

• 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.

• 如果你把LIMIT row_count和ORDER BY结合起来，MySQL一旦找到排序结果的第一个row_count行，就会停止排序，而不是对整个结果进行排序。如果排序是通过使用索引来完成的，这是非常快的。如果必须执行filesort，那么在找到第一个row_count之前，将选择所有与没有LIMIT子句的查询匹配的行，并对其中大部分或全部行进行排序。在找到初始行之后，MySQL不会对结果集的任何剩余行进行排序。

  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的ORDER BY查询可能以不同的顺序返回行，本节后面将对此进行描述。

• If you combine LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

• 如果你把LIMIT row_count和DISTINCT结合起来，MySQL一旦发现row_count唯一的行就会停止。

• In some cases, a GROUP BY can be resolved by reading the index in order (or doing a sort on the index), then calculating summaries until the index value changes. In this case, LIMIT row_count does not calculate any unnecessary GROUP BY values.

• 在某些情况下，可以通过按顺序读取索引(或对索引进行排序)来解析GROUP BY，然后计算摘要，直到索引值发生变化。在这种情况下，LIMIT row_count不会计算任何不必要的GROUP BY值。

• As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using SQL_CALC_FOUND_ROWS. In that case, the number of rows can be retrieved with SELECT FOUND_ROWS(). See Section 12.16, “Information Functions”.

• MySQL一旦向客户端发送了所需的行数，它就会终止查询，除非您正在使用SQL_CALC_FOUND_ROWS。在这种情况下，可以使用SELECT FOUND_ROWS()检索行数。

• LIMIT 0 quickly returns an empty set. This can be useful for checking the validity of a query. It can also be employed to obtain the types of the result columns within applications that use a MySQL API that makes result set metadata available. With the mysql client program, you can use the --column-type-info option to display result column types.

• LIMIT 0很快返回一个空集。这对于检查查询的有效性非常有用。它还可以用于在使用MySQL API的应用程序中获取结果列的类型，该API使结果集元数据可用。在mysql客户端程序中，可以使用——column-type-info选项来显示结果列类型。

• If the server uses temporary tables to resolve a query, it uses the LIMIT row_count clause to calculate how much space is required.

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

• 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.

• 如果ORDER BY没有使用索引，但也存在LIMIT子句，优化器可能能够避免使用合并文件，并使用内存中的filesort 操作对内存中的行进行排序。

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. Consider this query, which is sorted by the category column but nondeterministic with respect to the id and rating columns:

影响执行计划的一个因素是LIMIT，因此带有或不带有LIMIT的ORDER BY查询可能以不同的顺序返回行。考虑下面这个查询，它是按类别列排序的，但对id和评级列不确定:

Including LIMIT may affect order of rows within each category value. For example, this is a valid query result:

包括LIMIT可能会影响每个类别值中的行顺序。例如，这是一个有效的查询结果:

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. For example, if id values are unique, you can make rows for a given category value appear in id order by sorting like this:

 如果重要的是确保具有或不具有LIMIT的行顺序相同，则在order BY子句中包括额外的列，以使顺序具有确定性。例如，如果id值是唯一的，你可以让一个给定类别值的行按照id顺序出现，排序如下:

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 5.7.33, there was no way to override this behavior, even in cases where using some other optimization might be faster.

在MySQL 5.7.33之前，没有办法覆盖这个行为，即使在使用一些其他优化可能更快的情况下。

Beginning with MySQL 5.7.33, it is possible to turn off this optimization by setting the optimizer_switch system variable's prefer_ordering_index flag to off. 

从MySQL 5.7.33开始，可以通过将optimizer_switch系统变量的prefer_ordering_index设置为off来关闭此优化

Example: First we create and populate a table t as shown here:

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

Verify that the prefer_ordering_index flag is enabled: 

验证prefer_ordering_index标志是否启用:

 Since the following query has a LIMIT clause, we expect it to use an ordered index, if possible. In this case, as we can see from the EXPLAIN output, it uses the table's primary key.

由于下面的查询有一个LIMIT子句，我们希望它使用有序索引(如果可能的话)。在本例中，正如我们从EXPLAIN输出中看到的，它使用表的主键。

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: 

现在我们禁用prefer_ordering_index标志，并重新运行相同的查询;这次它使用索引i(包括WHERE子句中使用的id2列)和一个文件排序:

See also Section 8.9.2, “Switchable Optimizations”.