LIMIT查询优化
LIMIT查询优化
吴泽辉 发表于6个月前
LIMIT查询优化
  • 发表于 6个月前
  • 阅读 20
  • 收藏 0
  • 点赞 0
  • 评论 0

新睿云服务器60天免费使用,快来体验!>>>   

开始

如果你只需要结果集中指定数量的行,那么在查询中使用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 |
+----+----------+--------+
  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 3
博文 17
码字总数 20782
×
吴泽辉
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: