MySQL 5.7默认ONLY_FULL_GROUP_BY语义介绍

ONLY_FULL_GROUP_BY是MySQL提供的一个sql_mode，通过这个sql_mode来提供SQL语句“分组求最值”合法性的检查，在MySQL的sql_mode为非ONLY_FULL_GROUP_BY语义时。一条select语句，MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式，但这个表达式的值可能在经过group by操作后变成了未知的，例如：

 

1 2 3 4 5 6 7

create table emp(id int primary key, ename varchar(20), sal decimal(10, 2), deptno int); insert into emp select 1001,'emp_1001',100.00,10; insert into emp select 1002,'emp_1002',200.00,10; insert into emp select 1003,'emp_1003',300.00,20; insert into emp select 1004,'emp_1004',400.00,20; insert into emp select 1005,'emp_1005',500.00,30; insert into emp select 1006,'emp_1006',600.00,30;

其中，empno是员工编号，ename是员工姓名，sal是工资，deptno是员工所在部门号。

业务的需求是，求出每个部门中工资最高的员工的相关信息。

在MySQL 5.6中，可能会看见这种写法：

 

1 2 3 4 5 6 7 8 9

mysql> select deptno,ename,max(sal) from emp group by deptno; +--------+----------+----------+ | deptno | ename    | max(sal) | +--------+----------+----------+ |     10 | emp_1001 |   200.00 | |     20 | emp_1003 |   400.00 | |     30 | emp_1005 |   600.00 | +--------+----------+----------+ 3 rows in set (0.00 sec)

实在不明白，这里的ename在业务层有何意义，毕竟，他并不是工资最高的那位员工。但仔细观察会发现，ename值也算有规律，总是分组后的第一条记录。由此，在5.6中，我们可以通过下面这个SQL来实现这个需求，算是一种投机方式。

 

1 2 3 4 5

SELECT     deptno,ename,sal FROM     ( SELECT * FROM emp ORDER BY sal DESC ) t GROUP BY deptno;

得到结果如下：

 

1 2 3 4 5 6 7 8

+--------+----------+--------+ | deptno | ename    | sal    | +--------+----------+--------+ |     10 | emp_1002 | 200.00 | |     20 | emp_1004 | 400.00 | |     30 | emp_1006 | 600.00 | +--------+----------+--------+ 3 rows in set (0.00 sec)

而对于语义限制都比较严谨的多家数据库，如SQLServer、Oracle、PostgreSql都不支持select target list中出现语义不明确的列，这样的语句在这些数据库中是会被报错的，这也是SQL92的标准。所以从MySQL 5.7版本开始修正了这个语义，就是我们所说的ONLY_FULL_GROUP_BY语义。

例如，查看MySQL 5.7默认的sql_mode如下：

 

1 2	mysql> select @@global.sql_mode; ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

所以，对于同样的SQL，我们在MySQL 5.7再跑一次就会报错了，刚才通过的查询语句被MySQL拒绝掉了！如下错误：

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't.ename' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在ONLY_FULL_GROUP_BY模式下，因为target list中的ename没有出现在聚集函数中，并且也没有出现在group by list中，所以MySQL给拒绝了。这也是MySQL 5.6升级到MySQL 5.7需要注意的地方。如果我们去掉ONLY_FULL_GROUP_BY模式，如下操作：

 

1	mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

现在，我们在去掉ONLY_FULL_GROUP_BY语义的MySQL 5.7上把刚才的查询再次执行，得到结果如下：

 

1 2 3 4 5 6 7 8

+--------+----------+--------+ | deptno | ename    | sal    | +--------+----------+--------+ |     10 | emp_1001 | 100.00 | |     20 | emp_1003 | 300.00 | |     30 | emp_1005 | 500.00 | +--------+----------+--------+ 3 rows in set (0.00 sec)

结果竟然跟5.6不一样。

实际上，在MySQL5.7中，对该SQL进行了改写，改写后的SQL可通过show warnings查看。

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> explain SELECT deptno,ename,sal FROM ( SELECT * FROM emp ORDER BY sal DESC ) t GROUP BY deptno; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ |  1 | SIMPLE      | emp   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec)   mysql> show warnings; +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message                                                                                                                                                                                       | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note  | 1003 | /* select#1 */ select `employees`.`emp`.`deptno` AS `deptno`,`employees`.`emp`.`ename` AS `ename`,`employees`.`emp`.`sal` AS `sal` from `employees`.`emp` group by `employees`.`emp`.`deptno` | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)

从改写后的SQL来看，其消除了子查询，导致结果未能实现预期效果。但可通过修改optimizer_switch来加以规避（setoptimizer_switch=”derived_merge=off”），derived_merge是MySQL 5.7引入的，其会试图将derived table（派生表，from后面的子查询）、视图引用、共用表表达式与外层表进行合并。

Tips：所以很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。但是这个问题也是需要注意的，不要以为这种投机的写法不会有人用。在stackoverflow中，该实现的点赞数就有116个，由此可见其受众之广。

所以ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。但是由于表达式的表现形式非常丰富，对于MySQL来说，很难精确的确定一些表达式的输出结果是明确的，比如：

 

1 2 3

mysql> select deptno from emp group by deptno+1; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.emp.deptno' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在上面的查询语句中，其实count的值也是能被唯一确定的，但是由于程序无法分析出这种复杂的关系，所以这条查询也被拒绝掉了。

因此，如果查询语句中的target list，having condition或者order by list里引用了的表达式不是聚集函数，但是和group by list中的表达式严格匹配，该语句也是合法的（deptno+1和deptno+1是严格匹配的，deptno+1和deptno+2在MySQL认为是不严格匹配的， deptno+1和1+deptno也是不严格匹配的）。

如下严格匹配模式下，查询是没有问题的。

 

1 2 3 4 5 6 7 8 9

mysql> select deptno+1 from emp group by deptno+1; +----------+ | deptno+1 | +----------+ |       11 | |       21 | |       31 | +----------+ 3 rows in set (0.00 sec)

这条语句target list中的deptno+1和group by中的deptno+1是严格匹配的，所以MySQL认为target list中的deptno+1是语义明确的，因此该语句可以通过。

再看下面这条语句：

 

1 2 3 4 5 6 7 8 9

mysql> select deptno+1 as a from emp group by a order by deptno+1 desc; +------+ | a    | +------+ |   31 | |   21 | |   11 | +------+ 3 rows in set (0.00 sec)

MySQL允许target list中对于非聚集函数的alias column被group by、having condition以及order by语句引用，从上面两条语句可以看出，group by和order by中引用了alias column，并且其等价于基础列语义。

总结一下：MySQL对于ONLY_FULL_GROUP_BY语义的判断规则是，如果group by list中的表达式是basic column，那么target list中允许出现表达式是group by list中basic column或者alias column的组合结果，如果group by list中的表达式是复杂表达式（非basic column或者alias column），那么要求target list中的表达式必须能够严格和group by list中的表达式进行匹配，否者这条查询会被认为不合法。

其实分组求最值是一个很普遍的需求。下面具体来看看，MySQL中有哪些实现方式。

方法一：

 

1 2 3 4 5 6 7 8 9 10

SELECT     e.deptno,     ename,     sal FROM     emp e,     ( SELECT deptno, max( sal ) maxsal FROM emp GROUP BY deptno ) t WHERE     e.deptno = t.deptno     AND e.sal = t.maxsal;

方法二：

 

1 2 3 4 5 6 7 8 9 10

SELECT     a.deptno,     a.ename,     a.sal FROM     emp a     LEFT JOIN emp b ON a.deptno = b.deptno     AND a.sal < b.sal WHERE     b.sal IS NULL;

性能呢？

方法一执行计划：

 

1 2 3 4 5 6 7 8 9

mysql> desc SELECT e.deptno,ename,sal FROM emp e, ( SELECT deptno, max( sal ) maxsal FROM emp GROUP BY deptno ) t WHERE e.deptno = t.deptno AND e.sal = t.maxsal; +----+-------------+------------+------------+-------+----------------+----------------+---------+-------------------+------+----------+----------------------------------------+ | id | select_type | table      | partitions | type  | possible_keys  | key            | key_len | ref               | rows | filtered | Extra                                  | +----+-------------+------------+------------+-------+----------------+----------------+---------+-------------------+------+----------+----------------------------------------+ |  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL           | NULL           | NULL    | NULL              |    4 |   100.00 | Using where                            | |  1 | PRIMARY     | e          | NULL       | ref   | idx_deptno_sal | idx_deptno_sal | 11      | t.deptno,t.maxsal |    1 |   100.00 | Using join buffer (Batched Key Access) | |  2 | DERIVED     | emp        | NULL       | range | idx_deptno_sal | idx_deptno_sal | 5       | NULL              |    4 |   100.00 | Using index for group-by               | +----+-------------+------------+------------+-------+----------------+----------------+---------+-------------------+------+----------+----------------------------------------+ 3 rows in set, 1 warning (0.00 sec)

在有复合索引（deptno, sal）的情况下，结果瞬间就能出来应该。根据执行计划，先将group by的结果放到临时表中，然后再将该临时表作为驱动表，来和emp表进行关联查询。驱动表小（只有3条记录），关联列又有索引，无怪乎，结果能秒出。

方法二执行计划：

 

1 2 3 4 5 6 7 8

mysql> explain SELECT a.deptno,a.ename,a.sal FROM emp a LEFT JOIN emp b ON a.deptno = b.deptno AND a.sal < b.sal WHERE b.sal IS NULL; +----+-------------+-------+------------+------+----------------+----------------+---------+--------------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                | rows | filtered | Extra                    | +----+-------------+-------+------------+------+----------------+----------------+---------+--------------------+------+----------+--------------------------+ |  1 | SIMPLE      | a     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL               |    6 |   100.00 | NULL                     | |  1 | SIMPLE      | b     | NULL       | ref  | idx_deptno_sal | idx_deptno_sal | 5       | employees.a.deptno |    2 |    16.67 | Using where; Using index | +----+-------------+-------+------------+------+----------------+----------------+---------+--------------------+------+----------+--------------------------+ 2 rows in set, 1 warning (0.00 sec)

两表关联查询。其犯了SQL优化中的两个大忌。驱动表对于优化器来说，没办法自由选择，只能使用a表，所以a表如果越大就越慢。被驱动表虽然也有索引，但从执行计划上看，其只使用了复合索引（deptno,sal）中的deptno，如果deptno选择性太低，则越慢。

所以，对于分组求最值的需求，建议使用方法一，其不仅符合SQL规范，查询性能上也是最好的。

<参考>

https://mp.weixin.qq.com/s/u9Twv24IKxfHVyj62B4VtQ

 

http://www.ywnds.com/?p=8184