查看“GROUP BY优化”的源代码


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

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

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

在某些条件下，也可以在没有GROUP BY的情况下使用松散索引扫描。

== 松散索引扫描 ==

处理GROUP BY的最高效方法是使用索引直接检索分组列。通过这种访问方法，MySQL利用了某些索引类型的特性，即键是有序的（例如BTREE）。这个特性使得可以在索引中使用查找组，而无需考虑满足所有WHERE条件的索引中的所有键。这种访问方法只考虑索引中的一小部分键，因此被称为松散索引扫描。当没有WHERE子句时，松散索引扫描读取的键的数量与组的数量相同，这可能比所有键的数量要小得多。如果WHERE子句包含范围谓词，则松散索引扫描会查找满足范围条件的每个组的第一个键，并再次读取尽可能少的键。这在以下条件下是可能的：

* 查询只涉及一个表。
* 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中的列之后。
* 查询中引用的索引除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)。可以使用松散索引扫描访问方法来处理以下查询：

<pre>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 &lt; const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 &gt; const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 &lt; const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;</pre>
以下查询由于以下原因无法使用这种快速选择方法执行：

<ul>
<li><p>存在除了MIN()或MAX()之外的聚合函数：</p>
<pre>  SELECT c1, SUM(c2) FROM t1 GROUP BY c1;</pre></li>
<li><p>GROUP BY子句中的列不形成索引的最左前缀：</p>
<pre>  SELECT c1, c2 FROM t1 GROUP BY c2, c3;</pre></li>
<li><p>查询引用了在GROUP BY部分之后的键的一部分，并且没有与常量的等式关系：</p>
<pre>  SELECT c1, c3 FROM t1 GROUP BY c1, c2;</pre>
<p>如果查询中包括WHERE c3 = const，就可以使用松散索引扫描。</p></li></ul>

除了已经支持的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)。可以使用松散索引扫描访问方法来处理以下查询：

<pre>SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;</pre>
== 紧密索引扫描 ==

紧密索引扫描可以是全索引扫描或范围索引扫描，具体取决于查询条件。

当不满足松散索引扫描的条件时，仍然可以避免为GROUP BY查询创建临时表。如果WHERE子句中存在范围条件，该方法只读取满足这些条件的键。否则，它执行索引扫描。因为该方法会读取由WHERE子句定义的每个范围中的所有键，或者如果没有范围条件则扫描整个索引，所以称为紧密索引扫描。通过紧密索引扫描，在找到满足范围条件的所有键之后才执行分组操作。

为了使该方法工作，只需要查询中所有引用到GROUP BY键的部分之前或之间的键的列都有一个常量等式条件即可。等式条件中的常量填补了搜索键中的任何“间隙”，使得可以形成索引的完整前缀。然后，这些索引前缀可以用于索引查找。如果GROUP BY结果需要排序，并且可以形成索引的前缀搜索键，MySQL还可以避免额外的排序操作，因为在有序索引中使用前缀搜索已经按顺序检索所有键。

假设在表t1(c1,c2,c3,c4)上存在索引idx(c1,c2,c3)。以下查询在前面描述的松散索引扫描访问方法下无法工作，但仍然可以使用紧密索引扫描访问方法。

<ul>
<li><p>GROUP BY中存在间隙，但它被条件c2 = 'a'覆盖：</p>
<pre>  SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;</pre></li>
<li><p>GROUP BY不以第一部分键开始，但有一个条件为该部分提供了常量：</p>
<pre>  SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;</pre></li></ul>