Documentation Home
MySQL 5.7 参考手册

8.2.1.15 GROUP BY优化

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

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

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

在 MySQL 中，GROUP BY用于排序，因此服务器也可以ORDER BY 对分组进行优化。但是，不推荐依赖隐式或显式GROUP BY排序。请参阅第 8.2.1.14 节，“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;
紧索引扫描

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

When the conditions for a Loose Index Scan are not met, it still may be possible to avoid creation of temporary tables for GROUP BY queries. If there are range conditions in the WHERE clause, this method reads only the keys that satisfy these conditions. Otherwise, it performs an index scan. Because this method reads all keys in each range defined by the WHERE clause, or scans the whole index if there are no range conditions, it is called a Tight Index Scan. With a Tight Index Scan, the grouping operation is performed only after all keys that satisfy the range conditions have been found.

For this method to work, it is sufficient that there be a constant equality condition for all columns in a query referring to parts of the key coming before or in between parts of the GROUP BY key. The constants from the equality conditions fill in any “gaps” in the search keys so that it is possible to form complete prefixes of the index. These index prefixes then can be used for index lookups. If the GROUP BY result requires sorting, and it is possible to form search keys that are prefixes of the index, MySQL also avoids extra sorting operations because searching with prefixes in an ordered index already retrieves all the keys in order.

假设 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;