引言
此问题出现在一次写SQL练习时遇到的问题,遂在解决后分享出来,以帮助其他有同样问题的朋友.
问题复现与分析
首先我们有一张如下的表
表本身没什么好说的我们来看一条执行语句
explain select col1 from abc
where col1 in ('1','16','17')
group by col2\G;
这是一条效率很低的查询语句,其实预期的效果是在explain后在Extra字段中出现using temporary,因为我们知道select的实际执行顺序实在所有指令的最后面的,如图
但是上述语句在完成group by分组后其实是没有col2字段的,这意味着如果MySQL想要完成我们的指令就需要在内部生成带有col2字段的一张表,已进行group by,所以就会在Extra中出现using temporary字段,然而出现了错误,如下
(1055, "Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_mysql1.abc.col1' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")
大意就是seclect列表中的值没有出现在group by的列表中,你要生成的col1在功能上不依赖与group by后面的值,这与配置中的sql_mode=only_full_group_by是矛盾的.
解决方案
这其实就是我们前面说到的问题,MySQL为了不出现using temporary这个字段,直接就出了一个参数使这种低效的语句直接不通过语法检验.这个参数就是only_full_group_by
我们可以执行以下语句
select @@sql_mode
我们看到是确实存在的,其实我们也可以把这东西去掉,那样当然可以运行成功,MySQL既然加上了就说明这个参数确实能帮助我们提高查询的效率,没有必要去掉,那么前面的语句如何改成正确的呢?
explain select col1 from abc
where col1 in ('1','16','17')
group by col1 ,col2\G;
这样就OK了,我们看到相比与上一个语句其实就是在group by的时候加上select后面的项就好了,为了能够在select的时候不生成一张临时表,降低效率.而Extra字段中果然也没有了using temporary字段.
记得在刚开始学习group by的时候记了这么一句话
select后面跟的要和group by后面的项相同,剩下的只能跟聚合函数
这样看来确实是有一定道理的,因为select的执行是在所有语句的最后面,也即是生成了表以后我们选择一些进行展示,如果我们要展示的和group by后的没什么关系显然是不合理的,如果真的出现了这样的问题我想我们首先要做的也不是去改only_full_group_by参数,而是思考表哪里建的有问题.
结果
问题出现的原因就是select这个语句在MySQL看来是在group by之后执行的,MySQL为了优化SQL语句的效率而出了一个参数only_full_group_by,这个特性是在5.7以后的特性,所以大概出现这个问题的都是相对比较新的版本了.