Documentation Home
MySQL 5.6 参考手册

8.2.1.14 GROUP BY优化

满足GROUP BY 子句的最一般方法是扫描整个表并创建一个新的临时表，其中每个组的所有行都是连续的，然后使用此临时表来发现组并应用聚合函数（如果有）。在某些情况下，MySQL 能够做得比这更好，并通过使用索引访问避免创建临时表。

使用索引的最重要的先决条件 GROUP BY是所有GROUP BY列都引用来自同一索引的属性，并且索引按顺序存储其键（例如，对于BTREE索引，但不是 HASH索引）。临时表的使用是否可以被索引访问取代还取决于查询中使用了索引的哪些部分、为这些部分指定的条件以及所选的聚合函数。

有两种方法可以GROUP BY 通过索引访问执行查询，如以下部分所述。第一种方法将分组操作与所有范围谓词（如果有）一起应用。第二种方法首先执行范围扫描，然后对生成的元组进行分组。

在 MySQL 中，GROUP BY用于排序，因此服务器也可以ORDER BY 对分组进行优化。但是，不推荐依赖隐式 GROUP BY排序。请参阅 第 8.2.1.13 节，“ORDER BY 优化”。

松散索引扫描

最有效的处理方式GROUP BY是使用索引直接检索分组列。使用这种访​​问方法，MySQL 使用某些索引类型的属性，这些索引类型的键是有序的（例如，BTREE）。此属性允许在索引中使用查找组，而无需考虑索引中满足所有 WHERE条件的所有键。这种访问方法只考虑索引中的一小部分键，因此称为松散索引扫描。当没有WHERE 子句时，松散索引扫描读取与组数一样多的键，这可能比所有键的数量小得多。如果WHERE子句包含范围谓词（请参阅 第 8.8.1 节“使用 EXPLAIN 优化查询”range中对连接类型 的讨论），松散索引扫描查找满足范围条件的每个组的第一个键，并再次读取可能的最小值键的数量。这在以下条件下是可能的：

  • 查询是针对单个表的。

  • GROUP BY仅命名构成索引最左边前缀的列，不命名其他列 。（如果不是GROUP BY，查询有一个DISTINCT子句，所有不同的属性都引用构成索引最左边前缀的列。）例如，如果表 t1有一个索引 (c1,c2,c3)，如果查询有 ，松散索引扫描适用GROUP BY c1, c2。如果查询具有 GROUP BY c2, c3（列不是最左边的前缀）或GROUP BY c1, c2, c4（c4不在索引中），则不适用。

  • 选择列表中唯一使用的聚合函数（如果有的话）是MIN()和 MAX()，它们都引用同一列。该列必须在索引中，并且必须紧跟在 GROUP BY.

  • 除了查询中引用的索引部分之外，索引的任何其他部分都 GROUP BY必须是常量（即，它们必须与常量等同地引用）， MIN()或 MAX()函数的参数除外。

  • 对于索引中的列，必须索引完整的列值，而不仅仅是前缀。例如，对于 c1 VARCHAR(20), INDEX (c1(10))，索引仅使用值的前缀，c1不能用于松散索引扫描。

如果松散索引扫描适用于查询，则 EXPLAIN输出显示 Using index for group-by在 Extra列中。

假设 idx(c1,c2,c3)表 上有一个索引t1(c1,c2,c3,c4)。松散索引扫描访问方法可用于以下查询： 

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;

    如果查询包含，则可以使用松散索引扫描。 WHERE c3 = const

Loose Index Scan 访问方法可以应用于选择列表中的其他形式的聚合函数引用，除了已经支持 的MIN()和 引用：MAX()

假设 idx(c1,c2,c3)表 上有一个索引t1(c1,c2,c3,c4)。松散索引扫描访问方法可用于以下查询： 

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;
紧索引扫描

紧密索引扫描可以是全索引扫描，也可以是范围索引扫描，这取决于查询条件。

当不满足松散索引扫描的条件时，仍然可以避免为GROUP BY查询创建临时表。如果子句中有范围条件，则WHERE此方法只读取满足这些条件的键。否则，它执行索引扫描。因为这种方法读取 WHERE子句定义的每个范围内的所有键，或者如果没有范围条件则扫描整个索引，所以它被称为 Tight Index Scan。使用 Tight Index Scan 时，只有在找到所有满足范围条件的键后，才会执行分组操作。

为使此方法起作用，查询中引用键的部分之前或之间的键部分的所有列都存在恒定的相等条件就足够了GROUP BY。来自相等条件的常量填充搜索键中的任何“空白” ，以便可以形成索引的完整前缀。然后，这些索引前缀可用于索引查找。如果GROUP BY结果需要排序，并且可以形成作为索引前缀的搜索键，MySQL 还避免了额外的排序操作，因为在有序索引中使用前缀搜索已经按顺序检索了所有键。

假设 idx(c1,c2,c3)表 上有一个索引t1(c1,c2,c3,c4)。以下查询不适用于前面描述的松散索引扫描访问方法，但仍适用于紧密索引扫描访问方法。

  • 中存在间隙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;