• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

MySQL 5.7默认ONLY_FULL_GROUP_BY语义介绍

MySQL 5.7 彭东稳 7年前 (2017-02-14) 68049次浏览 已收录 6个评论

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操作后变成了未知的,例如:

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

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

在MySQL 5.6中,可能会看见这种写法:

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

得到结果如下:

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

例如,查看MySQL 5.7默认的sql_mode如下:

所以,对于同样的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模式,如下操作:

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

结果竟然跟5.6不一样。

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

从改写后的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来说,很难精确的确定一些表达式的输出结果是明确的,比如:

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

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

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

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

再看下面这条语句:

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中有哪些实现方式。

方法一:

方法二:

性能呢?

方法一执行计划:

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

方法二执行计划:

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

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

<参考>

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


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (45)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!

(6)个小伙伴在吐槽
  1. 这个不知道是开启好!还是关闭的好
    Tinywan2018-07-27 08:54 Windows 10 | Chrome 67.0.3396.99
  2. 感觉有无ONLY_FULL_GROUP_BY得情况跟我这正好相反
    2018-02-26 18:36 Windows 7 | Chrome 55.0.2883.87