mysql的groupby优化 mysql分组优化

目录

• 第八章 优化（十八）—— 分组优化

• 8.2 优化SQL语句

• 8.2.1 优化 SELECT 语句

• 8.2.1.17 GROUP BY （分组）优化

• 松散索引扫描
• 紧密索引扫描

第八章 优化（十八）—— 分组优化

8.2 优化SQL语句

8.2.1 优化 SELECT 语句

8.2.1.17 GROUP BY （分组）优化

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

GROUP BY能使用索引的最重要的先决条件是：所有GROUP BY中的列都引用自同一索引的属性（译者：这里的属性是指复合索引键的有次序的各部分），并且该索引是按顺序存储键的（例如，对于BTREE索引是这样的，而对于哈希索引则不是）。是否可以用索引访问代替临时表的使用还取决于在查询中使用了索引的哪些部分、对这些部分指定了什么条件以及所选的聚合函数是什么。

通过索引访问，执行GROUP BY查询的方法有两种，详细内容将在以下章节中介绍。第一种方法是与所有范围谓词（如果有的话）一起进行分组操作。第二种方法是先执行范围扫描，然后对结果元组结果集进行分组。

• 松散索引扫描（Loose Index Scan）
• 紧密索引扫描（Tight Index Scan）

在某些情况下，在没有GROUP BY的情况下也可以使用松散索引扫描方法。请参阅跳跃扫描范围访问方法。

松散索引扫描

处理GROUP BY最有效的方法是使用索引直接检索需要分组的列。要使用这种访问方法，MySQL会使用一些按键顺序排序的索引类型的属性（例如BTREE索引类型）。此属性允许在一个索引中进行组的查找，而不必考虑该索引中能满足所有WHERE条件的所有键。由于这种访问方法只需考虑索引中的部分键，因此称为松散索引扫描。当没有WHERE子句时，松散索引扫描读取的键数量与组数量一样多，通常，组的数量可能比所有键数量要少得多。如果WHERE子句包含范围谓词（请参阅第8.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不在索引中），则松散索引扫描不适用。
• 在SELECT列表中所使用的聚合函数（如果有的话）只能是MIN()和MAX()，并且它们都指向同一列。并且该列必须在索引中，并且必须紧跟在GROUP BY中的列后。
• 查询中所引用的，除了GROUP BY指定的索引部分外，索引的任何其他部分必须是常量（也就是说，它们必须在等于常量中被引用），MIN()或MAX()函数参数是例外。
• 对于索引中的列，必须对全部列值创建索引，而不仅仅是列的前缀。例如，对于c1 VARCHAR(20)， INDEX (c1(10))，该索引只使用c1值的前缀，因此不适用松散索引扫描。

如果一个查询适用松散索引扫描，那么EXPLAIN输出将在Extra列中显示Using Index for group-by（分组中使用了索引）。

假设在表t1(c1, c2, c3, c4)上有一个索引idx(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 key部分之后的部分键，但是该部分键没有使用等于常量的条件：

SELECT c1, c3 FROM t1 GROUP BY c1, c2;

如果该查询包含c3 = const（const表示常量），则可以使用松散索引扫描。

除了已经支持的MIN()和MAX()引用之外，松散索引扫描访问方法还可以应用于SELECT列表中的其他形式的聚合函数引用：

• 支持AVG(DISTINCT)、SUM(DISTINCT)和COUNT(DISTINCT)。其中AVG(DISTINCT)和SUM(DISTINCT)只能有一个列参数。而COUNT(DISTINCT)可以有多个列参数。
• 查询中不能有GROUP BY或DISTINCT子句。
• 前面描述的松散索引扫描限制仍然适用。

假设表t1(c1, c2, c3, c4)上有一个索引idx(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 key部分之前，或位于GROUP BY key部分之间。相等条件中的常量会填充搜索键中的任何“缺口”，从而可以形成索引的完整前缀。然后，这些索引前缀可用于索引查找。如果GROUP BY结果需要排序，并且可能已形成了作为索引前缀的搜索键，那么，MySQL也可避免额外的排序操作，因为在有序索引中使用前缀搜索时，已经按顺序检索了所有键。

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