MySQL性能优化group by语句优化

2023年 8月 15日 102.4k 0

MySQL在处理group by语句时,最常规的方式是扫描整个表,然后创建一个临时表,使用临时表存储分组和聚合函数的值。但是在一些特别的场景下,通过索引可以避免创建临时表,以获取更好的性能。

group by 子句使用索引的先决条件是group by的字段必须都在同一个索引里,这样才能使用索引已经排好序的特性。group by 走索引,通常有两种索引扫描方式。

  • 松散索引扫描,Loose Index Scan
  • 紧凑索引扫描,Tight Index Scan

一、松散索引扫描

最有效的处理group by的方式就是通过索引能够直接获取到所有分组字段,这种方式能够利用索引本身有序的特性,另外通过where条件,可以不用扫描索引中的所有key,只需要读取与分组数量相同的key数量,这个数量显然大多数场景下会比索引中所有key的数量要小很多。尤其对于范围分组查询,松散索引扫描从每个分组的第一个key开始查找,而不是从所有key的最小值那个开始查找。

松散索引扫描需要满足一定的条件:

  1. 查询只能涉及一张表
  2. group by字段只能是一个索引的最左前缀,且不能有其他的字段。比如一个表有一个联合索引(c1,c2,c3),group by c1,c2 可以使用松散索引扫描,而group by c2,c3 或者 group by c1,c2,c4 则不能使用松散索引扫描
  3. 聚合函数只能使用min或者max,如果这两个函数同时使用的话,只能在同一个字段上使用,这个字段也必须在索引中。看一个示例,如下:
    select c1,c2,min(c3),max(c3) from t1 group by c1,c2;
    联合索引:(c1,c2,c3)
  4. 联合索引中的字段,除了group by 以外的,必须为常量。示例如下:
    SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;
    联合索引:(c1,c2,c3)
  5. 联合索引中的字段必须是整个字段值被索引,而不能是前缀,比如(c1(10),c2,c3)

如果group by 可以走松散索引扫描,那么执行计划中的Extra字段将会显示为:Using index for group-by。

二、紧凑索引扫描

紧凑索引扫描会根据条件,对索引进行一次全索引扫描或者一个范围的索引扫描。相对于松散索引扫描只需要扫描部分满足条件的key,紧凑索引扫描需要扫描全部索引或者整个范围索引里面的key。

紧凑索引扫描示例:

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

联合索引:idx(c1,c2,c3)

三、优化建议:

group by 尽量通过走索引而减少临时表、排序、全表扫描。尽可能创造走松散索引扫描的条件,其次是紧凑索引扫描,通过已有索引,提高group by 性能。

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论