更多内容请点击MySQL8中文手册
8.2.1.17 GROUP BY 优化
通常用来满足GROUP BY分组的方式是扫描全表数据,创建临时表,将行数据按照分组字段连续存储在临时表中,然后使用这个临时表对同一分组的数据进行累加。在一些条件下,MySQL可以在这种方式上进行优化,使用索引进行分组,避免使用临时表。
要使用索引满足GROUP BY分组的重要的前提条件是所有GROUP BY的列都在同一个索引上,而且这个索引必须是有序的(比如BTREE索引,HASH索引则不行)。是否能用上索引进行分组,而不使用临时表,也取决于查询使用的哪部分索引,以及这部分索引上的查询条件,以及查询使用的聚合方法。
下面具体介绍两种使用索引满足GROUP BY分组的方式。第一种方式将分组操作与范围条件一起处理,第二种方式是先进行范围扫描,然后对结果进行分组。
- 松散索引扫描
- 紧密索引扫描
松散索引扫描在某些条件下,也可以用在没有GROUP BY的语句种。详见:Skip Scan Range Access Method.
松散索引扫描
进行GROUP BY最有效的方式是直接通过索引对指定列进行分组。这种方式,MySQL利用了某些类型的索引按照顺序存储的特性(比如BTREE)。基于这种特性,可以使用索引查找需要的分组,而不用考虑满足WHERE条件的所有key。这种方式,只使用了索引中key的一部分,所以叫做松散索引扫描。如果没有where条件,一次松散索引扫描会读取像分组数量一样多的key,可能会比所有key要少很多 。如果WHERE条件中有范围条件,松散索引扫描会查询每个组的第一个满足范围条件的可以,再读取最不可能的那个key。在下面的这些情况下是有可能会发生的:
-
查询在一张表上
-
GROUP BY的字段是索引的最左前缀,且不包含其他字段。例如,如果表t1有联合索引(c1,c2,c3),如果查询语句使用GROUP BY c1,c2,那这个查询会使用松散索引扫描。如果查询语句使用GROUP BY c2,c3或GROUP BY c1,c2,c4进行分组,那这个查询不会使用松散索引扫描。
-
查询聚合函数只使用了MIN()或MAX(),这些函数都针对同一列,这一列上必须有索引,而且紧跟在GROUP BY后面。
-
索引中除GROUP BY之外的其他字段,如果作为条件进行查询,必须是常量等值查询,或者使用MIN()、MAX()函数。
-
索引必须是全字段索引,不能是前缀索引,比如c1定义的是VARCHAR(20),而INDEX(c1(10)),这种情况下不能使用松散索引扫描
如果使用了松散索引扫描,EXPLAIN的Extra信息中会显示Using index for group-by。
假如表t1(