GROUP BY注意事项与优化

最近遇到了GROUP BY的问题，如下表结构与数据：

mysql> create table scores( 
    -> id int not null primary key auto_increment,
    -> student varchar(20),
    -> subject varchar(4), 
    -> score tinyint) charset utf8;
Query OK, 0 rows affected (0.35 sec)

mysql> insert scores values
    -> (null, 'wu', '语文', 88), 
    -> (null, 'luo', '语文',92), 
    -> (null, 'li', '语文', 100), 
    -> (null, 'wu', '数学', 99),
    -> (null, 'luo', '数学', 87),
    -> (null, 'li','数学', 92),
    -> (null, 'wu', '英语', 91),
    -> (null, 'luo', '英语',96),
    -> (null, 'li', '英语', 99);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

需求为：获取每科的最高分的学生姓名及分数。

第一感觉是这样写：

mysql> select student, subject, max(score) from scores group by subject;
+---------+---------+------------+
| student | subject | max(score) |
+---------+---------+------------+
| wu      | 数学    |         99 |
| wu      | 英语    |         99 |
| wu      | 语文    |        100 |
+---------+---------+------------+
3 rows in set (0.00 sec)

这里需要注意一下，上面的sql在MySQL5.6上执行是没问题的，如果是MySQL5.7，默认的模式为sql_mode = only_full_group_by，执行上面的语句会报错，为了测试可以将模式置空。

mysql> set sql_mode = '';
Query OK, 0 rows affected, 1 warning (0.02 sec)

再来看上面的执行结果，明显是有问题的，因为后两条数据都是不存在的。

第二感觉是这样写：

mysql> select student, scores.subject, scores.score from scores,
    -> (select max(score) score, subject from scores group by subject) as max_score
    -> where scores.score = max_score.score  and scores.subject = max_score.subject;
+---------+---------+-------+
| student | subject | score |
+---------+---------+-------+
| li      | 语文    |   100 |
| wu      | 数学    |    99 |
| li      | 英语    |    99 |
+---------+---------+-------+
3 rows in set (0.00 sec)

这个sql数据没有问题，可以满足需求。

第一个sql错误的原因是，根据录入数据的顺序，通过subject分组之后，得到的结果集中subject和MAX(score)是没有问题的，但是student是每个分组第一个录入的student，所以得到的每个分组的student都是’wu‘。

通过上面的分析，有了下面的sql：

mysql> select * from (select * from scores order by score desc) a group by a.subject;
+----+---------+---------+-------+
| id | student | subject | score |
+----+---------+---------+-------+
|  4 | wu      | 数学    |    99 |
|  7 | wu      | 英语    |    91 |
|  1 | wu      | 语文    |    88 |
+----+---------+---------+-------+
3 rows in set (0.00 sec)

可以看到这个结果也是错误的，亲测这样写在MySQL5.6.16版本是可用的，但是SQL99已经之后的标准就不允许这样写了（不绝对），同时MySQL5.7.5以及更高版本默认开启的ONLY_FULL_GROUP_BY也是决定了这种sql是不合法的。

下面是MySQL5.7手册上GROUP BY相关内容：

GROUP BY优化

实现GROUP BY最通常的方式就是扫描整个表同时创建一个临时表，每个组所有的行都是连续的（这块的意思应该指的是行被排序了），然后使用临时表去查找组同时应用聚合函数。在一些情况下，MySQL能通过使用索引来达到比创建临时表更好的效果（group by子句使用索引的时候）。

GROUP BY应用索引最重要的前提是里面所有的列来自于同一个索引，并且索引需要按顺序存储他的关键字（例如，BTREE索引，而不是HASH索引，关键字指的是应用在GROUP BY里面的索引数据）。是否能使用索引替代临时表也依赖于查询使用的索引的哪个部分，以及为这些部分指定条件和聚合函数。

MySQL有两种方式来执行GROUP BY，下面会详细介绍。第一种方法是分组与范围查找同时进行，第二种是先执行范围查找，然后再分组结果集。

在MySQL中，GROUP BY会进行排序，所以针对ORDER BY的优化也会同样可以应用于分组。但是，通过分组进行隐式的排序是不推荐的。

下面介绍GROUP BY的两种执行方式：

松散索引扫描

和前面写的一样，执行GROUP BY最有效的方式就是使用索引直接检索分组信息。对于这种访问方式，MySQL需要使用比如像BTREE索引那样的关键字排序特性。这种特性也会应用于查找索引中的分组而不用考虑索引中的所有的关键字满足所有的WHERE条件。这种仅考虑索引中一部分关键字的访问方式被叫做松散索引扫描。如果没有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不是索引）。

  • 在select列表中使用的聚合函数只能有MAX()和MIN()，并且他俩的参数为同一个列。这列还必须在索引中同时紧跟在GROUP BY 里面列的后面（通过下面的例子可以看出，GROUP BY c1, c2， SELECT的数据为MAX(c3)，c3是跟在c1, c2后面的索引，MAX(c1)或MAX(c2)没有意义）。

  • 除了被GROUP BY引用的索引的其他部分，在查询中都必须是常量（这里的意思应该是查询的列如果不在GROUP BY中，那么这个列的值是不准确的，稍后有例子说明这个），除了MIN()或MAX()的参数。

  • 对于在索引中的列，整个列的值都必须被索引，不能是前缀索引。例如，c1 varchar(20) index(c1(10))，这样的索引不会用在松散索引扫描中。

如果松散索引扫描应用于一个查询，那么EXPLAIN输出的Extra列显示Using index for group-by（这个并没有复现）。

假设有一个表t1(c1, c2, c3, c4)，索引是index(c1, c2, c3)，下面的查询能使用松散索引扫描：

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, c3 FROM t1 GROUP BY c2, c3;
  • 查询应用了除了GROUP BY引用的列之外的键，并且这一列没有等于常量的条件：
    SELECT c1, c3 FROM c1 GROUP BY c1, c2;

当上面的查询包含WHERE c3 = const，松散索引扫描就被用上了。（ 前面的sql获取的c3列是不准确的，ubuntu上apt安装的MySQL5.7.20默认的sql模式（sql_mode = only_full_group_by）下，这个sql会报错了）

除了已经支持的MAX()，MIN()，松散索引扫描访问方式可以应用于其他形式的聚合函数：

  • AVG(DISTINCT)，SUM(DISTINCT)，COUNT(DISTINCT)都是支持的。AVG和SUM只能用一个参数，COUNT可以有多个参数。

  • 查询中不能有GROUP BY或DISTINCT子句。

  • 之前描述的松散索引扫描的限制依然适用。

假设一个表为t1(c1, c2, c3, c4)，索引为index(c1, c2, c3），松散索引扫描能被应用在下面的查询中：

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

紧密索引扫描

紧密索引扫描可能是全索引扫描也可能是范围查找，取决于查询条件。

当条件不能满足松散索引扫描的时候，MySQL依然会尝试避免为GROUP BY查询创建临时表。如果WHERE子句中有范围条件，这种方式仅读取满足条件的关键字，否则进行索引扫描。因为这种方式读取定义在WHERE子句中所有的关键字，或者在没有范围条件的时候扫描整个索引，所以被叫做紧密索引扫描。对于这种方式，分组操作仅在满足范围条件的所有的关键字被发现之后执行。

为了让这种方式工作，使GROUP BY之前或者之间的所有的键都有一个等值条件就可以了（其实就是说GROUP BY里面的索引如果不能满足索引前缀，那么就必须有相应的常量加入来满足索引前缀）。来自等值条件的常量填空“空白”，目的是形成一个完整的索引前缀，这些索引前缀可以用来索引查找。如果我们需要排序GROUP BY结果，并且可能形成索引前缀的搜索关键字，MySQL也避免额外的排序操作，因为使用搜索前缀在已经排序的索引上进行搜索已经获取了排好序的关键字。

假设一个表t1(c1, c2, c3, c4)索引为index(c1, c2, c3)。接下来的查询不会应用之前说的松散索引扫描，但是会应用紧密索引扫描的方式。

  • 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;

总结

其实无论是松散索引扫描还是紧密索引扫描，目的都是防止建立临时表和文件排序。而两者的区别就在于GROUP BY子句是否应用了索引，如果GROUP BY子句中的列是索引，或者是索引前缀，那么就是松散索引扫描；而紧密索引扫描产生的原因就是GROUP BY子句里面的列已经不能满足松散索引扫描的条件（GROUP BY中的列不是索引前缀），所以这时候MySQL的补救措施就是如果索引前缀或整个索引分布在WHERE常量条件中和GROUP BY子句中，也就是说这两部分构成了索引前缀或整个索引，那么就用紧密索引扫描。上面两种方式的例子很好的说明了这一点。

如果GROUP BY列都不是索引，优化也无从谈起。

update 2019.03.28

因为GROUP BY在没有使用到索引的时候会使用临时表和文件排序（自动排序），在对GROUP BY的结果顺序没有要求的时候可以在sql后面添加如下语句，禁用文件排序：

ORDER BY NULL

（完）