4.13 优化分组

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

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

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

在MySQL中，GROUP BY用于排序，因此服务器也可以将ORDER BY 优化应用于分组。但是，不建议依赖隐式或显式GROUP BY排序。

松散索引扫描

处理的最有效方法GROUP BY是使用索引直接检索分组列。通过这种访问方法，MySQL使用键排序的某些索引类型的属性（例如BTREE）。使用此属性，可以在索引中使用查找组，而不必考虑索引中满足所有WHERE条件的所有键。此访问方法仅考虑索引中的一部分键，因此称为“ 松散索引扫描”。如果没有WHERE 子句，则“松散索引扫描”将读取与组数一样多的键，该数目可能比所有键的数目小得多。如果WHERE子句包含范围谓词，松散索引扫描”将查找满足范围条件的每个组的第一个键，并再次读取最小的可能值按键数。在以下情况下可以这样做：

查询是在单个表上。
该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)table上有一个索引 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()或之外，还有其他聚合函数 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 BYor DISTINCT子句。
先前描述的松散索引扫描限制仍然适用。

假设idx(c1,c2,c3)table上有一个索引 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子句定义的每个范围内的所有键，或者在没有范围条件的情况下扫描整个索引，因此称为“ 紧索引扫描”。使用紧密索引扫描，仅在找到所有满足范围条件的键之后才执行分组操作。

为了使该方法起作用，对于查询中所有引用键的各个部分之前或之间的部分，都必须有一个恒定的相等条件GROUP BY。来自等式条件的常数会填充搜索键中的所有“ 间隙 ”，以便可以形成索引的完整前缀。这些索引前缀然后可以用于索引查找。如果GROUP BY 结果需要排序，并且有可能形成作为索引前缀的搜索关键字，MySQL还避免了额外的排序操作，因为在有序索引中使用前缀搜索已经按顺序检索了所有关键字。

假设idx(c1,c2,c3)table上有一个索引 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;
```

Previoussql优化 Nextsql优化

Last updated 3 years ago