8.2.1.16 GROUP BY Optimization

8.2.1.16 GROUP BY Optimization

最大的方式来满足一个GROUP BY 子句是来scan 整个表,创建一个新的临时表,

所有的记录从每个组是连续的, 然后使用临时表来发现组和应用聚合函数(如果有的话)，

在一些情况下, MySQL 是能够做的更好,避免创建临时表通过使用索引访问。

最重要的前提用于使用索引用于GROUP BY 是,所有的GROUP BY 子句关联关联相同索引的属性,

索引存储它的keys 按顺序(举个例子, 这里有一个BTREE index 不是一个HASH index）。

是否使用 临时表可以通过索引访问替换 依赖于 索引那部分被使用,

条件指定的那些部分, 和选择的聚合函数。

这里有两种方式来执行一个GROUP BY 查询通过索引访问, 细节在下面章节描述。

在第一种方法, grouping 操作是应用和所有的范围谓词一起(如果有的话).

第2个方法首先执行一个range scan，然后汇总结果tuples。

在MySQL，GROUP BY 是用于排序, 因此server 可能应用ORDER BY 优化分组。

8.2.1.16.1 Loose Index Scan

最有效的方式处理GROUP BY 是放一个索引用于直接检索 grouping columns.

在这种访问方法里,MySQL 使用一些索引类型的属性 keys 是排序的(比如,BTREE).

这个属性让 lookup groups 使用在一个index 不比考虑所有的keys 在index，

满足WHERE 子句的,这个访问方法被认为只是一个 keys 在索引里的一小部分,

因此被称为是一个loose index scan.

当没有一个WHERE 子句, 一个loose index scan 读取读取尽可能多的keys，

这个可能比全部的keys小的多。 如果WHERE 子句包含范围谓词

一个 loose index scan 查找每个group的第一个key满足范围条件的,

并在此读取最少可能的键值数。 有下面的条件是可能的:

1.查询通过单个表

GROUP BY 命令只有列,形成一个 索引的最左前缀,没有其他列。

（如果, 代替GROUP BY,查询有一个DISTINCT 子句,所有的单个属性关联列形成一个最左前缀在索引上)

比如,一个表t1有一个index on(c1,c2,c3)，loose index scan 是和使用的

如果查询有GRAOUP BY c1,c2。它是不适用的如果查询有GROUP BY c2,c3

(列不是最左前缀) 或者 group by c1,c2,c4(c4不是在索引里)

唯一的聚合函数使用在select 列表(如果有的话) 是MIN()和MAX(),

他们都指向一个相同的列, 列必须在所以里, 列必须在GROUP BY 里。

索引的其他部分比从GROUP BY关联的在查询部分必须是常数(也就是说,它们必须是等值关联)

除了MIN() 或者MAX()函数:

对于索引中的列, 全部的列值必须被索引,不只是一个前缀。

比如,with c1 VARCHAR(20), INDEX (c1(10)), index不能用于loose index scan.

如果 loose index scan 是适用于查询的,EXPLAIN 输出适用index 用于group-by 在额外的列里。

假设有一个index idx(c1,c2,c3) 在table t1(c1,c2,c3,c4).

loose index scan 访问方法可以用于下面查询:

SELECT c1, c2 FROM t1 GROUP BY c1, c2;

SELECT DISTINCT c1, c2 FROM t1;

SELECT c1, MIN(c2) FROM t1 GROUP BY c1;

SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;

SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

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

下面的查询不能被执行使用这个快速查询方法, 给出理由如下:

有聚合函数除了 MIN() or MAX():

SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

列在GROUP BY 子句,不是形成一个最左边浅醉的索引:

SELECT c1, c2 FROM t1 GROUP BY c2, c3;

查看指向一个索引的部分,在GROUP BY 部分后面,没有一个等于常值

SELECT c1, c3 FROM t1 GROUP BY c1, c2;
Were the query to include WHERE c3 = const, loose index scan could be used.