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) 组合唯一索引同时也是覆盖索引, 因为选择的列都在索引中,因此可以避免回表。 explain 结果,Extra 会出现 using index。
3. 由于 where 中 update_month 使用的是范围查询,会导致 group by site 无法走组合索引的 site , explain 结果,Extra 会出现 Using temporary; Using filesort 。如果不关心结果集的顺序,可以通过添加 order by null 取消按分组字段排序,避免 filesort 文件排序。
4. 由于上面使用了子查询,而子查询需要创建和填充临时表。当分组后的 site 不是很多时,没有问题,当 site 比较多时,需要实际测试下。
关于数据库B+Tree索引,可以参考我之前的文章 B树和B+树。
欢迎关注我的微信公众号[数学345]:长按"识别图中二维码";或打开微信扫一扫。