MySQL优化之索引优化-子查询与GROUP BY优化

88 阅读2分钟

开启掘金成长之旅!这是我参与「掘金日新计划 · 12 月更文挑战」的第12天,点击查看活动详情

前言

上篇我们学习了MySQL中的数据库优化之索引优化-关联查询。有兴趣的小伙伴可以阅读(MySQL优化之索引优化-关联查询优化)。
下面我们继续学习MySQL中的数据库优化之索引优化-子查询与GROUP BY优化。

子查询优化

MySQL从4.1版本开始支持子查询,使用子查询可以进行SELECT语句的嵌套查询,即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询是MySQL的一项重要的功能。可以帮助我们通过一个SQL语句实现比较复杂的查询。但是子查询的执行效率不高。

原因:

  1. 执行子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表。这样会消耗过多的CPU和IO资源,产生大量的慢查询。
  2. 子查询的结果集存储的临时表,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响。
  3. 对于返回结果集比较大的子查询,其对查询性能的影响也就越大。

在MySQL中,可以使用连接JOIN查询来替代子查询。

连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用索引的话,性能会更好。

尽量不要使用NOT IN或者NOT EXISTS,用LEFT JOIN xxx ON XX WHERE xx IS NULL替代。

GROUP BY优化

  1. GROUP BY使用索引的原则几乎与order by一致,group by即使没有过滤条件使用到索引,也可以直接使用索引。
  2. GROUP BY先排序再分组,遵照索引建的最佳左前缀法则
  3. 当无法使用索引列,增大max_length_for_sort_data和sort_buffer_size参数的设置。
  4. where效率高于having,能写在where限定的条件就不要写在having中。
  5. 减少使用order by,能不排序就不排序,或将排序放到程序端去做。order by,GROUP BY,distinct这些语句较为耗费CPU,数据库的CPU资源是及其宝贵的。
  6. 包含了order by,GROUP BY,distinct这些查询的语句,where条件过滤出来的结果集请保持在1000行以内,否则SQL会很慢。

今天先学习到这里,明天继续。