最近上线的一个统计月活用户的需求,看起来一个定时任务+几句sql统计就一步到位结束了。近几日发现生产数据没我的月活数据,于是就排查起来。
问题知道,那下一步肯定就是去排查了。
看了下生产的表,乍一看数据都挺正常的。然后去筛一下自己的记录,发现近几日都没有我的月活。于是上CAT
服务监控系统去看下有没有相关的日志,发现有一条SQL报了超时错误。
因为这种超时错误是在深度翻页时发生,找到合适的参数执行SQL复现问题。测试的时候没有这么多的数据,所以这个问题没有暴露出来。在日志中找到发生错误时的参数,复现了问题。
先来看下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
。这里 通过主键索引找到行记录,然后分组聚合,通过临时文件排序。
这里还是一知半解,先记录下。有大佬了解的也可以说说[手动狗头]