MySQL :: MySQL 8.4 Reference Manual :: 10.2.1.19 LIMIT Query Optimization

Documentation Home

version 8.4

8.0 current
5.7

8.0 Japanese

MySQL 8.4 Reference Manual / ... / LIMIT Query Optimization

10.2.1.19 LIMIT 查询优化

如果您需要从结果集中获取指定的行数，可以使用LIMIT子句，而不是将整个结果集fetch到内存中并丢弃多余的数据。

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

如果您使用LIMIT选择少量行，MySQL在某些情况下会使用索引，而不是执行全表扫描。
如果您将LIMIT row_count与ORDER BY组合，MySQL会在找到第一个row_count行的排序结果时停止排序，而不是对整个结果进行排序。如果使用索引进行排序，这将非常快。如果需要执行文件排序，则所有匹配查询（不包括LIMIT子句）都将被选择，并且大多数或所有这些行将被排序，然后找到第一个row_count行。然后，MySQL不会对结果集的其余部分进行排序。

这种行为的一个表现形式是，具有ORDER BY子句且无LIMIT子句的查询可能返回不同的行顺序，如后续部分所述。
如果您将LIMIT row_count与DISTINCT组合，MySQL会在找到第一个row_count唯一行时停止。
在某些情况下，一个GROUP BY可以通过顺序读取索引（或对索引进行排序），然后直到索引值变化时计算摘要。在这种情况下，LIMIT row_count不会计算任何不必要的GROUP BY值。
一旦MySQL将所需的行发送给客户端，它将中止查询，除非您使用了SQL_ CALC_FOUND_ROWS。在这种情况下，可以使用SELECT FOUND_ROWS()来检索行数。请参阅第14.15节，“信息函数”。
LIMIT 0快速返回一个空集。这可以用于检查查询的有效性，也可以在使用MySQL API的应用程序中用来获取结果列的类型。使用mysql客户端程序，可以使用--column-type-info选项来显示结果列类型。
如果服务器使用临时表来解决查询，它将使用LIMIT row_count子句来计算所需的空间。
如果索引不用于ORDER BY但同时存在LIMIT子句，优化器可能能够避免使用合并文件，并使用内存中的filesort操作对行进行排序。

如果多行在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节，“可切换优化”。

PREV HOME UP NEXT