GROUP BY 分组中找最值问题和性能优化

需求：查找 update_month 在指定范围，并且按 site 分组，返回分组中 update_month 最大的即最接近指定的时间的记录。site_test 这张表 (update_month, site) 已经建立了唯一索引。

select id, site, max(update_month) update_month, name     
from site_test 
where update_month <= '2022-12'
group by site;

上面的 sql 查询在 Mysql 5.6 版本中测试没有问题。 但是在 MySQL 5.7 版本中却报下面的 only_full_group_by 错误。

错误 [1055] [42000]: Expression #1 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'test.site_test.id' 
which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by

这其实是因为 MySQL 认为 id, name 这2个列不依赖于(not functionally dependent on ) GROUP BY 从句中的 site 列。即 id, name 不能被 site 唯一决定，换句话说，在一个分组中可能有多行数据，MySQL 无法决定使用哪一行的数据填充 id, name。

不要以为 MySQL 会依赖于 max(update_month) 选择的行，这里只是一个简单的例子，考虑下其他情况，就知道这在逻辑上完全行不通。比如，可能有多行 update_month 同时是最大值，更复杂的情况是，用户可能增加其它列的聚合函数 MAX(other_cloumn) ，都会导致分组内有多行的问题。

你可能会提出使用 order by update_month desc 进行降序排序，但是 order by 是对选择的结果集进行排序，而不是分组内进行排序。

简单的关闭 MySQL ONLY_FULL_GROUP_BY 选项重启MySQL 数据库，或者使用 ANY_VALUE() 函数(如下)，虽然可以避免报错，但是却掩盖了问题：MySQL 会在分组中任意选择一行数据进行填充。 不建议关闭 ONLY_FULL_GROUP_BY 选项。

select ANY_VALUE(id), site, max(update_month) update_month, ANY_VALUE(name)     
from site_test 
where update_month <= '2022-12'
group by site;

注: 在某些情况下，如果我们知道在分组结果集中必定是唯一的，那么使用 ANY_VALUE() 也是一种折中方案，添加注释说明唯一以便后续阅读不困惑即可。

我的做法如下，嵌套的 select 语句只选择 site 和 max(update_month) ，然后再和 site_test 表 inner join 。

select c.id, c.site, c.update_month, c.name
from(
select site, max(update_month) update_month 
from site_test  
where update_month <= '2022-12' 
group by site) AS c1
inner join site_test c 
on c.site = c1.site and c.update_month = c1.update_month;

由于 (update_month, site) 是唯一的，因此必定不会有重复的记录。

我们继续讨论下面的几个性能问题：

1. 对于组合索引，根据最左前缀原则，顺序显然应当是 (update_month, site)。
2. 对于嵌套的 select 语句， (update_month, site) 组合唯一索引同时也是覆盖索引， 因为选择的列都在索引中，因此可以避免回表。
3. 由于 where 中 update_month 使用的是范围查询，会导致 group by site 无法走组合索引的 site ， explain 会出现 Using temporary; Using filesort 。如果不关心结果集的顺序，可以通过添加 order by null 取消按分组字段排序，避免 filesort 文件排序。
4. 由于上面使用了子查询，而子查询需要创建和填充临时表。当分组后的 site 不是很多时，没有问题，当 site 比较多时，需要实际测试下。

原文链接

---

欢迎关注我的微信公众号[数学345]：长按"识别图中二维码"；或打开微信扫一扫。