Mysql踩坑——Group by

1. 问题背景

最近上线的一个统计月活用户的需求，看起来一个定时任务+几句sql统计就一步到位结束了。近几日发现生产数据没我的月活数据，于是就排查起来。

问题知道，那下一步肯定就是去排查了。

2. 排查思路

• 先确定是不是任务挂了导致数据没跑出来

看了下生产的表，乍一看数据都挺正常的。然后去筛一下自己的记录，发现近几日都没有我的月活。于是上CAT服务监控系统去看下有没有相关的日志，发现有一条SQL报了超时错误。

• 知道错误在哪，然后正常的就是复现问题了

因为这种超时错误是在深度翻页时发生，找到合适的参数执行SQL复现问题。测试的时候没有这么多的数据，所以这个问题没有暴露出来。在日志中找到发生错误时的参数，复现了问题。

• 问题也复现了，现在只剩下fix了

3. 优化（修复）

先来看下SQL

				SELECT
        user_id ,count(*) as `count`
        FROM
        A
        WHERE
        <![CDATA[ id >= #{minId} and id <= #{maxId} ]]>
        GROUP BY
        user_id
        ORDER BY
        user_id
        LIMIT #{offSet},#{pageSize}

索引建立情况：id -> 主键

user_id -> 普通索引

有内行的老哥肯定发现了，为啥GROUP BY了又ORDER BY。因为当初认为 GROUP BY后，数据在分页的时候可能会乱序。好家伙，直接跟他跟上一个ORDER by强制排序。页数不多的时候速度还行，所以也没太关注。因为在user_id上有索引，页数变大的时候索引扫描的行数很多（接近亿级别了）。明明已经限制了统计区间，结果却不是我所想的那样。

于是开始一顿操作，先试试把ORDER by去掉来看看，结果快的起飞，相同条件下，只需要扫瞄数百万行，而且实际生效的索引是主键索引。看了几眼数据和自己强制排序的数据一模一样，看起来顺序没有变化。于是开始求证，MySQL官方给出的说明是，GROUP BY 是有默认隐式排序的，这不就是我的需求。。。但是MySQL不建议使用隐式排序，在8.0版本中会被剔除。也可以显示指定 ASC、DESC

所以最后就去掉了强制排序，本次优化（修复）完成

		SELECT
        user_id ,count(*) as `count`
        FROM
        A
        WHERE
        <![CDATA[ id >= #{minId} and id <= #{maxId} ]]>
        GROUP BY
        user_id
        LIMIT #{offSet},#{pageSize}

• 以下属于个人猜想，请教下大家

为什么强制排序后，深度翻页会带来这么多的性能损耗？

使用ORDER BY user_id，执行计划显示确实走了user_id这个索引，Extra:Using where; Using index。但性能很差，扫表数量巨大。是因为这里强制使用了排序，MySQL要在user_id这个索引树上找到对应ID区间的行记录，然后在进行Group by分组聚合，最后在排序输出？

不使用ORDER BY user_id，执行计划显示使用主键索引，Extra:Using where; Using temporary; Using filesort。这里 通过主键索引找到行记录，然后分组聚合，通过临时文件排序。

这里还是一知半解，先记录下。有大佬了解的也可以说说[手动狗头]