mysql select语句优化之GROUP BY Optimization

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

使用GROUP BY索引的最重要前提条件是，所有GROUP BY列引用的属性来自同一索引，并且这个索引按顺序存储keys （例如，BTREE索引而不是HASH索引）。是否可以通过索引访问来替换临时表的使用还取决于查询中使用了索引的哪些部分，为这些部分指定的条件以及所选聚合函数。

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

• Loose Index Scan

• Tight Index Scan

Loose Index Scan

处理GROUP BY的最有效方法是使用索引直接检索分组的columns。使用这种访??问方法，MySQL使用keys排序的一些索引类型（例如，BTREE）的属性。该属性支持在索引中使用查找groups，而不必考虑索引中满足所有WHERE条件的所有keys。此访问方法仅考虑索引中的部分keys，因此称为松散索引扫描。当没有WHERE子句时，松散索引扫描会读取与groups数量相同的keys，这可能比所有密钥的数量少得多。如果WHERE子句包含范围谓词（请参见 Section 8.8.1, “Optimizing Queries with EXPLAIN”中对 range join type的讨论），宽松索引扫描会查找满足范围条件的每组中第一个key，并再次读取尽可能少的keys。在以下情况下可以这样做：

• The query is over a single table.

• The GROUP BY names only columns that form a leftmost prefix of the index and no other columns. (If, instead of GROUP BY, the query has a DISTINCTclause, all distinct attributes refer to columns that form a leftmost prefix of the index.) For example, if a table t1 has an index on (c1,c2,c3), loose index scan is applicable if the query has GROUP BY c1, c2,. It is not applicable if the query has GROUP BY c2, c3 (the columns are not a leftmost prefix) or GROUP BY c1, c2, c4 (c4 is not in the index).

• The only aggregate functions used in the select list (if any) are MIN() and MAX(), and all of them refer to the same column. The column must be in the index and must immediately follow the columns in the GROUP BY.

• Any other parts of the index than those from the GROUP BY referenced in the query must be constants (that is, they must be referenced in equalities with constants), except for the argument of MIN() or MAX() functions.

• For columns in the index, full column values must be indexed, not just a prefix. For example, with c1 VARCHAR(20), INDEX (c1(10)), the index cannot be used for loose index scan.

如果松散索引扫描适用于查询，则EXPLAIN输出会在Extra列中显示 Using index for group-by。

假设表t1（c1，c2，c3，c4）上存在索引idx（c1，c2，c3）。松散索引扫描访问方法可用于以下查询：

出于以下原因，以下select查询无法使用松散索引扫描：

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

• AVG(DISTINCT), SUM(DISTINCT), and COUNT(DISTINCT) are supported. AVG(DISTINCT) and SUM(DISTINCT) take a single argument. COUNT(DISTINCT)can have more than one column argument.

• There must be no GROUP BY or DISTINCT clause in the query.

• The loose scan limitations described earlier still apply.

假设表t1（c1，c2，c3，c4）上存在索引idx（c1，c2，c3）。松散索引扫描访问方法可用于以下查询：

Tight Index Scan

严格的索引扫描可能是全索引扫描或范围索引扫描，具体取决于查询条件。

当没有满足松散索引扫描的条件时，仍可以避免为GROUP BY查询创建临时表。如果WHERE子句中有范围条件，则此方法只读取满足这些条件的keys。否则，它会执行索引扫描。因为此方法读取WHERE子句定义的每个范围内的所有keys，或者在没有范围条件时扫描整个索引，因此我们称其为严格的索引扫描。使用严格索引扫描时，只有在找到满足范围条件的所有keys后才执行分组操作。

为了使这种方法起作用，查询的列里面，和GROUP BY key之前或之间的key part对应的列都有一个常量条件就足够了。来自相等条件的常量填充搜索关键字中的任何“间隙”，以便可以形成索引的完整前缀。这些索引前缀可以用于索引查找。如果我们需要对GROUP BY结果进行排序，并且可以形成作为索引前缀的搜索关键字，那么MySQL也会避免额外的排序操作，因为在有序索引中使用前缀进行搜索已经按顺序检索所有关键字。

假设表t1（c1，c2，c3，c4）上存在索引idx（c1，c2，c3）。以下查询不适用于前面描述的宽松索引扫描访问方法，但仍然适用于严格的索引扫描访问方法。