MySQL性能优化-group by语句优化

MySQL, 数据库 / 作者：

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 性能。

发表评论