LIMIT查询优化

LIMIT查询优化

如果你只需要结果集中指定数量的行，那么在查询中使用LIMIT子句，而不是匹配所有结果集再扔掉额外的数据。

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()获取检索的行数。

  • LIMIT 0能快速返回一个空的结果集，这可以用来检查一个查询是否有效。它也能用来获取结果列的类型，在使用了MySQL API的应用程序中使元数据信息有效。对于MySQL的客户端参数，可以使用–column-type-info来展示结果列类型。

    例如：

    $: mysql -uroot
 
mysql> select * from fulltext_test limit 0;
Empty set (0.00 sec)

$: mysql -uroot --column-type-info

mysql> select * from fulltext_test limit 0;
Field   1:  `id`
Catalog:    `def`
Database:   `test`
Table:      `fulltext_test`
Org_table:  `fulltext_test`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

  • 如果服务使用临时表解析查询，它使用LIMIT row_count子句来计算需要多少空间。

  • 如果索引没有被ORDER BY使用，但是LIMIT row_count依然存在，优化器可能会避免merge file并且使用内存中的filesort操作来排序行。

在ORDER BY列中有许多相同的值，MySQL服务会以任何顺序返回行，因为依赖主执行计划，可能返回不同的结果。换句话说，这些相同的行的排序是非确定的。

影响执行计划的一个因素是LIMIT，所以一个ORDERY BY查询是否带LIMIT可能返回不同的顺序。看下面的查询，它使用category列排序，但是对于id和reting列是不确定的：

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

（完）