Skip to main content

19.LIMIT 查询优化

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

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

  • 如果仅使用 LIMIT 选择几行，MySQL 在某些情况下会使用索引，而通常情况下它更愿意进行全表扫描。
  • 如果将 LIMIT row_count 与 ORDER BY 结合使用，MySQL 在找到排序结果的前 row_count 行后立即停止排序，而不是对整个结果进行排序。 如果通过使用索引来完成排序，则速度非常快。 如果必须进行文件排序，则在找到第一个 row_count 之前，将选择与不带 LIMIT 子句的查询匹配的所有行，并对其中大部分或全部进行排序。 找到初始行后，MySQL 不会对结果集的任何剩余部分进行排序。
  • 此行为的一种表现是，带有和不带有 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 的应用程序中的结果列的类型，该 API 使结果集元数据可用。 在mysql客户端程序中，可以使用--column-type-info选项来显示结果列类型。
  • 如果服务器使用临时表来解析查询，它将使用 LIMIT row_count 子句来计算需要多少空间。
  • 如果 ORDER BY 未使用索引，但也存在 LIMIT 子句，则优化器可能能够避免使用合并文件并使用内存中文件排序操作对内存中的行进行排序。

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

影响执行计划的因素之一是 LIMIT，因此带有和不带有 LIMIT 的 ORDER BY 查询可能会以不同的顺序返回行。 考虑这个查询，它按类别列排序，但相对于 id 和 rating 列是不确定的：

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 可能会影响每个类别值内的行顺序。 例如，这是一个有效的查询结果：

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 顺序显示：

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

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:

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]

验证 Preferred_ordering_index 标志是否已启用：

SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

由于以下查询具有 LIMIT 子句，因此我们希望它尽可能使用有序索引。 在这种情况下，正如我们从 EXPLAIN 输出中看到的，它使用表的主键。

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 列）和文件排序：

SET optimizer_switch = "prefer_ordering_index=off";

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