LIMIT 查询优化

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

MySQL 有时会对具有 LIMIT row_count 子句且没有 HAVING 子句的查询进行优化：

如果您使用 LIMIT 仅选择少量行，则在某些情况下，MySQL 会在通常情况下更喜欢进行全表扫描时使用索引。
如果您将 LIMIT row_count 与 ORDER BY 结合使用，MySQL 会在找到排序结果的前 row_count 行后停止排序，而不是对整个结果进行排序。如果使用索引进行排序，速度非常快。如果必须进行文件排序，则会选择与不带 LIMIT 子句的查询匹配的所有行，并对其中的大部分或全部行进行排序，然后找到前 row_count 行。在找到初始行之后，MySQL 不会对结果集的其余部分进行排序。

这种行为的一种表现是，在没有 LIMIT 的 ORDER BY 查询和有 LIMIT 的 ORDER BY 查询可能以不同的顺序返回行。
如果您将 LIMIT row_count 与 DISTINCT 结合使用，MySQL 会在找到 row_count 个唯一行后立即停止。
在某些情况下，GROUP BY 可以通过顺序读取索引（或对索引进行排序）来解决，然后在索引值更改之前计算摘要。在这种情况下，LIMIT row_count 不会计算任何不必要的 GROUP BY 值。
一旦 MySQL 向客户端发送了所需数量的行，除非您使用了 SQL_CALC_FOUND_ROWS，否则它将终止查询。在这种情况下，可以使用 SELECT FOUND_ROWS() 检索行数。
LIMIT 0 快速返回一个空集。这对于检查查询的有效性很有用。它还可用于在使用使结果集元数据可用的 MySQL API 的应用程序内获取结果列的类型。使用 mysql 客户端程序，您可以使用 –column-type-info 选项显示结果列类型。
如果服务器使用临时表来解析查询，则它使用 LIMIT row_count 子句来计算所需的空间量。
如果未对 ORDER BY 使用索引，但也存在 LIMIT 子句，优化器可能能够避免使用合并文件，并使用内存中的文件排序操作在内存中对行进行排序。

如果多个行在 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值是唯一的，可以通过像这样排序，使得具有相同类别值的行按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子句的查询，在查询执行速度可以加快的情况下，优化器会默认尝试选择一个有序索引。在MySQL 8.0.21之前，没有办法覆盖这种行为，即使在其他优化可能更快的情况下也是如此。从MySQL 8.0.21开始，可以通过将optimizer_switch系统变量的prefer_ordering_index标志设置为off来关闭此优化。

示例

1. 首先，我们按照以下所示创建和填充一个名为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，并执行了排序操作。

 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

LIMIT 查询优化

导航菜单

搜索