查看原文
其他

记一次详细的的SQL查询经历,group by慢查询优化

dijia478 数据和云 2024-03-03

一、问题背景



现网出现慢查询,在500万数量级的情况下,单表查询速度在30多秒,需要对sql进行优化,sql如下:



这里测试环境构造了500万条数据,模拟了这个慢查询。

简单来说,就是查询一定条件下,都有哪些用户的。很简单的sql,可以看到,查询耗时为37秒。


说一下app_account字段的分布情况,随机生成了5000个不同的随机数,然后分布到了这500万条数据里,平均来说,每个app_account都会有1000个是重复的值,种类共有5000个。


二、看执行计划




可以看到,group by字段上是加了索引的,也用到了。


三、优化



优化思路:


思路一:


后面应该加上 order by null;避免无用排序,但其实对结果耗时影响不大,还是很慢。



思路二:


where条件太复杂,没索引,导致查询慢,但给where条件的所有字段加上了组合索引,没起作用。


思路三:


既然group by慢,换distinct试试



瞬间就加快了。


虽然知道group by和distinct有很小的性能差距,但是没想到,差距居然这么大。


四、你以为这就结束了吗



这个bug转给测试后,测试一测,居然还是30多秒。再测试电脑上执行sql,依旧是30多秒。


又回本人的电脑上,连接同一个数据库,一执行sql,0.8秒。


同一个库,同一个sql,怎么在两台电脑执行的差距这么大。


后来直接在服务器上执行:



还是30多秒。那看来就是本人的电脑问题。


后来又实验多个同事的电脑,最后得出的结论是:是因为之前用的SQLyog。


最后发现,只有用sqlyog执行这个“优化后”的sql会是0.8秒,在navcat和服务器上直接执行,都是30多秒。


那就是sqlyog的问题了,现在也不清楚sqlyog是不是做什么优化了,这个慢查询的问题还在解决中(问题可能是出在mysql自身的参数上)。


这里只是记录下这个问题,sqlyog执行sql速度,和服务器执行sql速度,在有的sql中差异巨大,并不可靠


五、后续(还未解决)



感谢大家在评论里出谋划策,本人来回复下问题进展:


1.所谓的sqlyog查询快,命令行查询慢的现象,已经找到原因了。是因为sqlyog会在查询语句后默认加上limit 1000,所以导致很快。这个问题不再纠结。


2.已经试验过的方法(都没有用):


①给app_account字段加索引。

②给sql语句后面加order by null。

③调整where条件里字段的查询顺序,有索引的放前面。

④给所有where条件的字段加组合索引。

⑤用子查询的方式,先查where条件里的内容,再去重。

 

测试环境和现网环境数据还是有点不一样的,这里贴一张现网执行sql的图(1分钟):



六、最终解决方案



经过网友的提醒,发现explain执行计划里,索引好像并没有用到创建的idx_end_time。


然后果断在现网试了下,强制指定使用idx_end_time索引,结果只要0.19秒。


 

至此问题解决,其实同事昨天也在怀疑,是不是这个表索引建的太多了,导致用的不对,原本用的是idx_org_id和idx_mvno_id。


现在强制指定idx_end_time就ok了!


最后再对比下改前后的执行计划:


改之前(查询要1分钟左右):

 

改之后(查询只要几百毫秒):


出处:https://www.cnblogs.com/dijia478/p/11550902.html(复制到浏览器中打开)




数据和云

ID:OraNews

如有收获,请划至底部,点击“在看”,谢谢!


资源下载

关注公众号:数据和云(OraNews)回复关键字获取

help,30万+下载的完整菜单栏

2019DTCC,数据库大会PPT

2018DTCC , 数据库大会PPT

2018DTC,2018 DTC 大会 PPT

ENMOBK《Oracle性能优化与诊断案例》

DBALIFE,“DBA 的一天”海报

DBA04,DBA 手记4 电子书

122ARCH,Oracle 12.2体系结构图

2018OOW,Oracle OpenWorld 资料

产品推荐

云和恩墨BethuneX  企业版,集监控、巡检、安全于一身,你的专属数据库实时监控和智能巡检平台,漂亮的不像实力派,你值得拥有!



云和恩墨zData一体机现已发布超融合版本和精简版,支持各种简化场景部署,零数据丢失备份一体机ZDBM也已发布,欢迎关注。


云和恩墨大讲堂 | 一个分享交流的地方

长按,识别二维码,加入万人交流社群


请备注:云和恩墨大讲堂

继续滑动看下一个

记一次详细的的SQL查询经历,group by慢查询优化

向上滑动看下一个

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存