20.MySQL优化LIMIT Query Optimization

介绍

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

MySQL可以优化包含 LIMIT row_count 但没有HAVING的查询

• 假如使用LIMIT扫描少数行，MySQL在某些情况下使用索引，通常情况下它更喜欢进行全表扫描。
• 假如LIMIT与ORDER BY进行组合，MySQL会在找到排序结果的第一行后立即停止排序 ，而不是对整个结果进行排序。如果使用索引完成排序，则速度非常快。如果必须进行一个filesort ，选择与查询匹配的所有行不做LIMIT限制，并在找到第一个之前对其中的大部分或全部进行排序 。在找到初始行之后，MySQL不会对结果集的任何剩余部分进行排序。
• 假如LIMIT row_count与DISTINCT组合，MySQL当查询到了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，则优化器可能能够避免使用合并文件并使用内存中filesort操作对内存中的行进行排序 。

当ORDER BY多个列时，则服务器可以按任何顺序自由返回这些行，并且可能会根据整体执行计划的不同而不同。换句话说，这些行的排序顺序相对于无序列是不确定的。

影响执行计划的一个因素是 LIMIT，因此ORDER BY 带有和不带的查询LIMIT可能会返回不同顺序的行。考虑这个查询，它按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 |
+----+----------+--------+

如果确保使用和不使用相同的行顺序很重要，请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 |
+----+----------+--------+