10.2.1.17 GROUP BY优化
满足GROUP BY子句最常见的方式是扫描整个表，并创建一个新的临时表，将每个分组的所有行连续存储在其中，然后使用这个临时表来确定分组并应用聚合函数（如果有的话）。在某些情况下，MySQL可以做得更好，通过使用索引访问来避免创建临时表。
使用索引进行GROUP BY的最重要前提条件是，所有GROUP BY列都引用同一索引中的属性，并且该索引按顺序存储其键（例如，BTREE索引是这样，而HASH索引则不是）。是否可以用索引访问替代临时表的使用，还取决于查询中使用了索引的哪些部分、为这些部分指定的条件以及所选的聚合函数。
通过索引访问执行GROUP BY查询有两种方式，以下部分将详细介绍。第一种方法是将分组操作与所有范围谓词（如果有的话）一起应用。第二种方法是先进行范围扫描，然后对生成的元组进行分组。
- 松散索引扫描
- 紧凑索引扫描
在某些条件下，即使没有GROUP BY，松散索引扫描也可以使用。请参见跳跃扫描范围访问方法。
松散索引扫描
处理GROUP BY最有效的方法是使用索引直接检索分组列。通过这种访问方法，MySQL利用了某些索引类型（例如BTREE）的键是有序的这一特性。这一特性使得在索引中查找分组时，无需考虑满足所有WHERE条件的所有键。这种访问方法仅考虑索引中的部分键，因此被称为松散索引扫描。当没有WHERE子句时，松散索引扫描读取的键数量与分组数量相同，这可能比所有键的数量少得多。如果WHERE子句包含范围谓词（请参见10.8.1节 “使用EXPLAIN优化查询” 中对range连接类型的讨论），松散索引扫描会查找满足范围条件的每个分组的第一个键，同样读取尽可能少的键。在以下条件下可以使用松散索引扫描：
- 查询针对单个表。
-
GROUP BY只指定构成索引最左前缀的列，不指定其他列。（如果查询使用的是DISTINCT子句而不是GROUP BY，那么所有不同的属性都必须引用构成索引最左前缀的列。）例如，如果表t1在(c1,c2,c3)上有一个索引，当查询为GROUP BY c1, c2时，松散索引扫描适用；当查询为GROUP BY c2, c3（列不是最左前缀）或GROUP BY c1, c2, c4（c4不在索引中）时，松散索引扫描不适用。 - 选择列表中（如果有的话）使用的唯一聚合函数是
MIN()和MAX()，并且它们都引用同一列。该列必须在索引中，且必须紧跟在GROUP BY中的列之后。 - 除
MIN()或MAX()函数的参数外，查询中引用的索引中GROUP BY之外的任何其他部分都必须是常量（即它们必须在与常量的等式中被引用）。 - 对于索引中的列，必须对完整的列值进行索引，而不只是前缀。例如，对于
c1 VARCHAR(20), INDEX (c1(10))，该索引仅使用c1值的前缀，不能用于松散索引扫描 。
如果松散索引扫描适用于某个查询，EXPLAIN输出的Extra列会显示Using index for group-by。
假设表t1(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;
由于以下原因，以下查询不能使用这种快速选择方法执行：
- 存在除
MIN()或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;
如果该查询包含WHERE c3 = const，则可以使用松散索引扫描。
除了已经支持的MIN()和MAX()引用之外，松散索引扫描访问方法还可以应用于选择列表中其他形式的聚合函数引用：
- 支持
AVG(DISTINCT)、SUM(DISTINCT)和COUNT(DISTINCT)。AVG(DISTINCT)和SUM(DISTINCT)接受单个参数，COUNT(DISTINCT)可以有多个列参数。 - 查询中不能有
GROUP BY或DISTINCT子句。 - 前面描述的松散索引扫描限制仍然适用。
假设表t1(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查询，仍然有可能避免创建临时表。如果WHERE子句中有范围条件，这种方法仅读取满足这些条件的键。否则，它会执行索引扫描。因为这种方法会读取WHERE子句定义的每个范围内的所有键，如果没有范围条件，则会扫描整个索引，所以被称为紧凑索引扫描。使用紧凑索引扫描时，只有在找到满足范围条件的所有键之后，才会执行分组操作。
要使这种方法生效，对于查询中引用GROUP BY键之前或之间部分的所有列，只要有常量等式条件就足够了。等式条件中的常量填补了搜索键中的任何“间隙”，以便能够形成索引的完整前缀。然后，这些索引前缀可用于索引查找。如果GROUP BY结果需要排序，并且能够形成作为索引前缀的搜索键，MySQL还可以避免额外的排序操作，因为在有序索引中使用前缀进行搜索已经按顺序检索了所有键。
假设表t1(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;
网友评论