8.2.1.19 LIMIT 查询优化_MySQL 8.0 参考手册

MySQL 8.0 参考手册 / 第8章优化 / 8.2 优化SQL语句 / 8.2.1 优化 SELECT 语句 / 8.2.1.19 LIMIT 查询优化

8.2.1.19 LIMIT 查询优化

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

MySQL 有时会优化有子句和无子句的查询： LIMIT row_countHAVING

如果您使用只选择几行 LIMIT，MySQL 在某些情况下会使用索引，而通常情况下它更愿意进行全表扫描。
如果结合使用，MySQL 会在找到排序结果的第一行后立即停止排序，而不是对整个结果进行排序。如果使用索引进行排序，速度会非常快。如果必须进行文件排序，则选择所有与不带子句的查询匹配的行，并且在找到第一行之前对其中的大部分或全部进行排序。找到初始行后，MySQL 不会对结果集中的任何剩余部分进行排序。 LIMIT row_countORDER BYrow_countLIMITrow_count
这种行为的一种表现是， ORDER BY有和没有的查询 LIMIT可能会以不同的顺序返回行，如本节后面所述。
如果与结合使用，MySQL 会在找到唯一行后立即停止。LIMIT row_countDISTINCTrow_count
在某些情况下，GROUP BY可以通过按顺序读取索引（或对索引进行排序）来解决问题，然后计算摘要直到索引值发生变化。在这种情况下，不计算任何不必要的值。 LIMIT row_countGROUP BY
一旦 MySQL 向客户端发送了所需数量的行，它就会中止查询，除非您使用 SQL_CALC_FOUND_ROWS. 在这种情况下，可以使用检索行数SELECT FOUND_ROWS()。请参阅第 12.16 节，“信息功能”。
LIMIT 0快速返回一个空集。这对于检查查询的有效性很有用。它还可以用于在使用使结果集元数据可用的 MySQL API 的应用程序中获取结果列的类型。配合 mysql客户端程序，可以使用 --column-type-info选项显示结果列类型。
如果服务器使用临时表来解析查询，它会使用该子句来计算需要多少空间。 LIMIT row_count
如果索引不用于ORDER BY 但LIMIT也存在子句，优化器可以避免使用合并文件并使用内存中 filesort操作对内存中的行进行排序。

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

影响执行计划的一个因素是 LIMIT，因此ORDER BY 有和没有的查询LIMIT可能会以不同的顺序返回行。考虑这个查询，它按列排序但关于和列是category不确定的： idrating

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 BYor GROUP BY和LIMIT 子句的查询，优化器会在默认情况下尝试选择有序索引，这样做会加快查询执行速度。在 MySQL 8.0.21 之前，没有办法覆盖此行为，即使在使用其他一些优化可能更快的情况下也是如此。optimizer_switch从 MySQL 8.0.21 开始，可以通过将系统变量的 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（包括子句id2中使用的列 WHERE）和文件排序：

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 节，“可切换优化”。