mysql5.7以上的group by 内部排序

sueong

已于 2023-01-01 17:06:49 修改

阅读量462

点赞数

分类专栏：开发笔记文章标签： mysql 数据库

于 2023-01-01 17:05:15 首次发布

本文链接：https://blog.csdn.net/sueong/article/details/128466641

版权

开发笔记专栏收录该内容

24 篇文章 1 订阅

订阅专栏

文章探讨了在MySQL中，当使用groupby操作时遇到的优化器问题，特别是在5.7版本中。由于优化器可能会对ORDERBY进行外部排序，导致无法获取每个分组下最近的时间记录。作者提出了通过添加DISTINCT和LIMIT来规避这个问题，但因不确定数据量而不适用。最后，文章提供了一个解决方案，即在子查询中使用DISTINCTID来确保获取每个分组的最新记录，同时不影响SUM操作。

摘要由CSDN通过智能技术生成

需求:

https://zhuanlan.zhihu.com/p/414683659
与这个需求类似

找出每个分组下最近一条时间的记录,同时还要进行sum操作,但是group by 只会匹配每个分组下的第一条数据(如果id自增的话,就是每个分组下最小的id),
所以想到的子查询建个子表先排序再连起来,结果发现 5.7版本的mysql的优化器会优化order by到外部排序,5.6的版本似乎可行,
然后查了一些看到了解决方法是,加limit,但是我无法确定数据量的大小,加limit显然不可行,
因为mysql优化器特定情况下会合并派生表
而limit distinct group by等可以关闭这个特性,
所以可以在子表中加distinct id,关闭这个特效,因为id肯定不会重复,所以确保了子表数据的完整性,也不会耽误我之后的sum操作

官方文档如下,

https://dev.mysql.com/doc/refman/5.7/en/derived-table-optimization.html

sql如下,

SELECT
  y.id,
  account,
  sum(mark_end_time - mark_start_time) as work_time
from
   as y
  right join ( -- 右连接, 因为在子表里用where筛选
    SELECT
      -- 加distinc,消除mysql优化特性质
      distinct id
    FROM
      `batch_tasks_apply_detail`
    WHERE
      project_id = 'abc'
      AND (
        mark_end_time > 11111111
        and mark_end_time < 2222222      )
      AND status = 2
      AND apply_type = 1
    order BY
      updated_at desc
  ) as u on u.id = y.id
group by
  y.account