8.2.1.17 LIMIT 查询优化

8.2.1.17 LIMIT Query Optimization 8.2.1.17 LIMIT 查询优化

原文：https://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

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 在找到排序结果的第一行 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 行计数不会计算任何不必要的 groupby 值。
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。在这种情况下，可以使用 selectfound _ rows ()检索行数。见第12.16节“信息功能”。
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 使结果集元数据可用。使用 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 子句，优化器可以避免使用合并文件，并使用内存中的文件分类操作对内存中的行进行排序。

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. In other words, the sort order of those rows is nondeterministic with respect to the nonordered columns.

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

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 的 orderby 查询可能会以不同的顺序返回行。考虑一下这个查询，它是按类别列排序的，但是对于 id 和等级列不确定:

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. For example, this is a valid query result:

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

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.

在每种情况下，行都按 orderby 列进行排序，这是 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 顺序出现:

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

对于具有 ORDER BY 或 GROUP BY 和 LIMIT 子句的查询，优化器在查询出现时尝试默认选择有序索引，这样做可以加快查询执行。在 MySQL 5.7.33之前，没有办法覆盖这种行为，即使在使用其他优化可能更快的情况下。从 MySQL 5.7.33开始，可以通过将优化器 _ switch system 变量的 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. In this case, as we can see from the EXPLAIN output, it uses the table's primary key.

因为下面的查询有一个 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

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 标志，并重新运行相同的查询; 这次它使用了 index i (包括 WHERE 子句中使用的 id2列)和一个 fileport:

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

参见8.9.2节，“可切换优化”。

LIMIT 查询优化总结

limit 作用是从结果集中获取指定数量的行

优化limit

1. 使用索引进行排序，理由索引的有序性

2. 基于游标的分页

LIMIT EXPLAIN error

原文： https://www.percona.com/blog/2006/07/24/mysql-explain-limits-and-errors/

posted @ 2022-03-10 10:26 yuing_cc 阅读(133) 评论(0) 收藏举报

刷新页面返回顶部

yuing_cc

8.2.1.17 LIMIT 查询优化

8.2.1.17 LIMIT Query Optimization 8.2.1.17 LIMIT 查询优化

LIMIT 查询优化 总结

LIMIT EXPLAIN error

公告

LIMIT 查询优化总结