MySQL :: MySQL 8.3 Reference Manual :: 10.2.1.17 GROUP BY Optimization

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / GROUP BY Optimization

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 子句包含范围谓词（见 range 连接类型在第 10.8.1 节，“使用 EXPLAIN 优化查询”），松散索引扫描查找每个组的第一个键，该键满足范围条件，然后读取最小可能的键数。

查询是单表查询。
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() 函数的参数。
对于索引中的列，必须索引完整的列值，而不是前缀。例如，使用 c1 VARCHAR(20), INDEX (c1(10))，索引仅使用 c1 值的前缀，无法用于松散索引扫描。

如果Loose Index Scan适用于查询，则EXPLAIN输出将在Extra列中显示Using index for group-by。

假设表t1(c1,c2,c3,c4)上有索引idx(c1,c2,c3)。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()或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。

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

AVG(DISTINCT)、SUM(DISTINCT)和COUNT(DISTINCT)是支持的。AVG(DISTINCT)和SUM(DISTINCT)只有一个参数。COUNT(DISTINCT)可以有多个列参数。
查询中不能有GROUP BY或DISTINCT子句。
之前描述的Loose Index Scan限制仍然适用。

假设表t1(c1,c2,c3,c4)上有索引idx(c1,c2,c3)。Loose Index Scan访问方法可以用于以下查询：

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

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

紧索引扫描

紧索引扫描可能是完整索引扫描或范围索引扫描，具体取决于查询条件。

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

为了使该方法生效，需要满足以下条件：查询中必须对所有在GROUP BY键之前或之间的键部分存在常量相等条件。这些常量来自相等条件，填充了搜索键中的“间隙”，以便形成完整的索引前缀。然后可以使用这些索引前缀进行索引查找。如果GROUP BY结果需要排序，并且可以形成索引前缀，那么MySQL也可以避免额外的排序操作，因为在有序索引中搜索前缀已经检索了所有键的顺序。

假设表t1(c1,c2,c3,c4)上有索引idx(c1,c2,c3)。以下查询不适用于之前描述的Loose Index Scan访问方法，但仍然适用于紧索引扫描访问方法。

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;
```

PREV HOME UP NEXT