Mysql踩坑——Group by+Order by带来的问题
2021/4/30 19:27:11
本文主要是介绍Mysql踩坑——Group by+Order by带来的问题,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
Mysql踩坑——Group by
- 问题背景
最近上线的一个统计月活用户的需求,看起来一个定时任务+几句sql统计就一步到位结束了。近几日发现生产数据没我的月活数据,于是就排查起来。
问题知道,那下一步肯定就是去排查了。
- 排查思路
- 先确定是不是任务挂了导致数据没跑出来
看了下生产的表,乍一看数据都挺正常的。然后去筛一下自己的记录,发现近几日都没有我的月活。于是上CAT
服务监控系统去看下有没有相关的日志,发现有一条SQL报了超时错误。
- 知道错误在哪,然后正常的就是复现问题了
因为这种超时错误是在深度翻页时发生,找到合适的参数执行SQL复现问题。测试的时候没有这么多的数据,所以这个问题没有暴露出来。在日志中找到发生错误时的参数,复现了问题。
- 问题也复现了,现在只剩下fix了
- 优化(修复)
先来看下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
。这里 通过主键索引找到行记录,然后分组聚合,通过临时文件排序。
这里还是一知半解,先记录下。有大佬了解的也可以说说[手动狗头]
这篇关于Mysql踩坑——Group by+Order by带来的问题的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-04-26MySQL查出时间比实际晚8小时的解决方案
- 2024-04-01JPA不识别MySQL的枚举类型
- 2024-03-30mysql数据库表卡死解决方法
- 2024-03-15MySQL多数据源笔记5-ShardingJDBC实战
- 2024-03-11natural join mysql
- 2024-03-11关于VS2017,VS2015 中利用 EF使用Mysql 不显示数据源问题解决方案
- 2024-02-26mysql 阿里云xb后缀备份文件恢复-icode9专业技术文章分享
- 2024-02-22docker mysql 5.7
- 2024-02-18从 20 多套 MySQL 到 1 套 TiDB丨骏伯网络综合运营管理平台应用实践
- 2024-02-07mysql 外键索引入门介绍,为什么工作中很少有人使用?