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 |
+----+----------+--------+
(完)
- 本文作者:吴泽辉
- 本文链接:https://mutex.top/posts/12c6d1a8/
- 发表日期:2017年7月25日
- 版权声明:本文章为原创,采用《知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议》进行许可