转自 http://www.ywnds.com/?p=8184
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规范，查询性能上也是最好的。
<参考>
扫一扫
398
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
