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

version 8.4

8.0 current
5.7

8.0 Japanese

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

10.2.1.17 GROUP BY 优化

满足GROUP BY子句的最一般方法是扫描整个表，创建一个临时表，其中每个组中的所有行都是连续的，然后使用这个临时表来发现组和应用聚合函数（如果有）。在某些情况下，MySQL 可以做得更好，不需要创建临时表，而是使用索引访问。

使用索引访问GROUP BY的最重要前提是所有GROUP BY列都引用同一个索引的属性，并且该索引存储其键顺序（例如，BTREE 索引，但不是 HASH 索引）。是否可以将临时表替换为索引访问还取决于查询中使用的索引部分、这些部分的条件和选择的聚合函数。

有两种方式可以通过索引访问执行GROUP BY查询，详见以下各节。第一个方法同时应用分组操作和所有范围谓词（如果有）。第二个方法首先执行范围扫描，然后对结果进行分组。

Loose Index Scan 也可以在 absence of GROUP BY 下使用某些条件下。见Skip Scan Range Access Method

处理GROUP BY的最有效方法是当索引直接检索分组列时。使用这种访问方法，MySQL 使用某些索引类型的特性，即键值有序（例如，BTREE）。这种特性使得可以在索引中查找组群，而不需要考虑所有满足WHERE条件的键。这种访问方法只考虑索引中的少数键，因此称为Loose Index Scan。当没有WHERE子句时，Loose Index Scan 读取的键数量与组群数量相等，这可能是一个比所有键小得多的数字。如果WHERE子句包含范围谓词（请参阅range join type 在第10.8.1节，“使用 EXPLAIN 优化查询”中讨论），Loose Index Scan 将查找每个组群的第一键，使其满足范围条件，然后再读取最小可能数量的键。这是以下情况下可能的：

查询只涉及单个表。
只有在索引的左最前缀中形成的列名才能被GROUP BY语句使用。如果查询中有DISTINCT子句，而不是GROUP BY，那么所有distinct属性都指向索引的左最前缀中的列。例如，如果表t1在(c1,c2,c3)上有索引，那么 Loose Index Scan 可以应用于查询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值的前缀，因此不能用于Loose Index Scan。

如果 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 访问方法可以应用于 select 列表中的其他聚合函数引用，除了已经支持的 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)。 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 子句中存在范围条件，这种方法只读取满足这些条件的键。否则，它将执行索引扫描。由于这种方法在每个范围定义中读取所有键，或者如果没有范围条件，则扫描整个索引，因此称为 Tight Index Scan。使用 Tight Index Scan 时，分组操作仅在找到满足范围条件的所有键后执行。

为了使这种方法工作，只需确保查询中对 key 的部分具有常量等式条件，这些等式条件来自于 GROUP BY 关键字前或之间的列。这些等式条件填充索引搜索键中的““gaps””，从而使得可以形成完整的索引前缀，然后可以用于索引查找。如果 GROUP BY 结果需要排序，并且可以形成搜索键，这些搜索键是索引的前缀，MySQL 也避免了额外的排序操作，因为在有序索引中搜索前缀已经返回所有键的顺序。

假设表 t1( c1, c2, c3, c4 ) 上存在索引 idx(c1,c2,c3)。以下查询不能使用前述 Loose Index Scan 访问方法，但仍然可以使用 Tight 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