8.2.1.15 GROUP BY 最佳化
满足GROUP BY子句的最通用方法是扫描整个 table 并创建一个新的临时 table，其中每个组中的所有行都是连续的，然后使用该临时 table 发现组并应用聚合函数(如果有)。在某些情况下，MySQL 可以做得更好，并且可以避免使用索引访问来创建临时 table。
为GROUP BY使用索引的最重要前提是所有GROUP BY列都引用同一索引的属性，并且索引按 Sequences 存储其键(例如，对于BTREE索引，但对于HASH索引，则为真) 。临时 table 的使用是否可以由索引访问代替，还取决于查询中使用索引的哪些部分，为这些部分指定的条件以及所选的聚合函数。
有两种通过索引访问执行GROUP BY查询的方法，如以下各节所述。第一种方法将分组操作与所有范围谓词(如果有)一起应用。第二种方法首先执行范围扫描，然后对所得的 Tuples 进行分组。
在 MySQL 中，使用GROUP BY进行排序，因此服务器也可以将ORDER BY优化应用于分组。但是，不建议依赖隐式或显式GROUP BY排序。参见第 8.2.1.14 节“按优化排序”。
索引扫描松散
处理GROUP BY的最有效方法是使用索引直接检索分组列。通过这种访问方法，MySQL 使用键排序的某些索引类型的属性(例如BTREE)。使用此属性，可以在索引中使用查找组，而不必考虑索引中满足所有WHERE条件的所有键。此访问方法仅考虑索引中的一部分键，因此称为“松散索引扫描”。如果没有WHERE子句，则“松散索引扫描”将读取与组数一样多的键，该数目可能比所有键的数目小得多。如果WHERE子句包含范围谓词(请参见第 8.8.1 节“使用 EXPLAIN 优化查询”中有关range连接类型的讨论)，则“松散索引扫描”将查找满足范围条件的每个组的第一个键，并再次读取尽可能少的键数。在以下情况下可以这样做：
-
查询是在单个 table 上。
-
GROUP BY仅命名构成索引最左前缀的列，不命名其他列。 (如果查询具有DISTINCT子句，而不是GROUP BY，则所有不同的属性都引用构成索引最左前缀的列.)例如，如果 tablet1在(c1,c2,c3)上具有索引，则松散索引扫描适用于查询具有GROUP BY c1, c2。如果查询具有GROUP BY c2, c3(列不是最左边的前缀)或GROUP BY c1, c2, c4(c4不在索引中)，则此方法不适用。 -
选择列 table 中使用的唯一聚合函数(如果有)是MIN()和MAX()，它们全部引用同一列。该列必须在索引中，并且必须紧接着
GROUP BY中的列。 -
除了MIN()或MAX()函数的参数外，除查询中所引用的
GROUP BY之外的其他任何索引部分都必须是常量(即，必须与常量相等地引用)。 -
对于索引中的列，必须索引完整的列值，而不仅仅是索引。例如，对于
c1 VARCHAR(20), INDEX (c1(10))，索引仅使用c1值的前缀，不能用于宽松索引扫描。
如果“宽松索引扫描”适用于查询，则EXPLAIN输出在Extra列中显示Using index for group-by。
假设在 tablet1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3)。松散索引扫描访问方法可用于以下查询：
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;
由于给出的原因，无法使用此快速选择方法执行以下查询：
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;
如果查询包含WHERE c3 = const，则可以使用松散索引扫描。
除了已经支持的MIN()和MAX()引用之外，“松散索引扫描”访问方法还可以应用于选择列 table 中的其他形式的聚合函数引用：
-
支持AVG(DISTINCT)，SUM(DISTINCT)和COUNT(DISTINCT)。 AVG(DISTINCT)和SUM(DISTINCT)采用一个参数。 COUNT(DISTINCT)可以有多个列参数。
-
查询中不得包含
GROUP BY或DISTINCT子句。 -
先前描述的松散索引扫描限制仍然适用。
假设在 tablet1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3)。松散索引扫描访问方法可用于以下查询：
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;
紧密索引扫描
紧密索引扫描可以是全索引扫描，也可以是范围索引扫描，具体取决于查询条件。
当不满足宽松索引扫描的条件时，仍然有可能避免为GROUP BY查询创建临时 table。如果WHERE子句中有范围条件，则此方法仅读取满足这些条件的键。否则，它将执行索引扫描。由于此方法读取WHERE子句定义的每个范围内的所有键，或者在没有范围条件的情况下扫描整个索引，因此称为“紧索引扫描”。对于紧密索引扫描，仅在找到所有满足范围条件的键之后才执行分组操作。
为了使此方法起作用，对于查询中所有引用GROUP BY键的各个部分之前或之间的键的部分，都必须有一个恒定的相等条件。来自相等条件的常量将填充搜索键中的所有“间隙”，以便可以形成索引的完整前缀。这些索引前缀然后可以用于索引查找。如果GROUP BY结果需要排序，并且有可能形成作为索引前缀的搜索关键字，则 MySQL 也避免了额外的排序操作，因为在有序索引中使用前缀进行搜索已经按 Sequences 检索了所有关键字。
假设在 tablet1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3)。以下查询不适用于前面所述的“松散索引扫描”访问方法，但仍适用于“紧索引扫描”访问方法。
GROUP BY中有一个缝隙，但已被条件c2 = 'a'覆盖：
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
GROUP BY并非以键的第一部分开头，但是存在为该部分提供常量的条件：
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;