记一次神奇的MySQL查询经历,group by慢查询优化
共 2065字,需浏览 5分钟
·
2021-08-23 12:25
你知道的越多,不知道的就越多,业余的像一棵小草!
你来,我们一起精进!你不来,我和你的竞争对手一起精进!
编辑:业余草
cnblogs.com/dijia478
推荐:https://www.xttblog.com/?p=5261
本文来源于群友的真实案例,对部分 SQL 略作脱敏修改,整理成本文,希望对大家有所帮助!
「一、问题背景」
现网出现慢查询,在 500 万数据量的情况下,单表查询速度在 30 多秒,需要对 sql 进行优化,sql 如下:
![](https://filescdn.proginn.com/56c45fc976bfa40a1984de3d93101045/8b255c4209814e171663ba080da2583a.webp)
我在测试环境构造了 500 万条数据,模拟了这个慢查询。
简单来说,就是查询一定条件下,都有哪些用户的。很简单的 sql,可以看到,查询耗时为 37 秒。
说一下 app_account 字段的分布情况,随机生成了 5000 个不同的随机数,然后分布到了这 500 万条数据里,平均来说,每个 app_account 都会有 1000 个是重复的值,种类共有 5000 个。
「二、看执行计划」
![](https://filescdn.proginn.com/de6691a4069e46d178fcfd645a60d27f/384456e2212b14e98a7f1bb75b66c4b4.webp)
可以看到,group by 字段上我是加了索引的,也用到了。
「三、优化」
说实话,我是不知道该怎么优化的,这玩意还能怎么优化啊!先说下,下面的思路都是没用的。
「思路一:」
后面应该加上 order by null;避免无用排序,但其实对结果耗时影响不大,还是很慢。
![](https://filescdn.proginn.com/521078be913b4a639f470dffe6d0e0cc/6add28165715e237f4ef1f394aaa9da6.webp)
「思路二:」
where 条件太复杂,没索引,导致查询慢,但我给 where 条件的所有字段加上了组合索引,也还是没用。
![](https://filescdn.proginn.com/df8c702977cb522b880b2fae9da89cf5/fcf31a96b4fceb8234cea7b8cd9d5b15.webp)
![](https://filescdn.proginn.com/98205a3ae7788cb7cb3e2155967341b7/8ee988304a203b835375ef5f1385b804.webp)
「思路三:」
既然 group by 慢,换 distinct 试试??(这里就是本篇博客里说的神奇的地方了)
![](https://filescdn.proginn.com/a55a857af24d50de7e4a701e3305c53d/edda6aad0e768443406add9ef297e9f9.webp)
卧槽???!!!这是什么情况,瞬间这么快了??!!!
虽然知道 group by 和 distinct 有很小的性能差距,但是真没想到,差距居然这么大!!!打破了认知,大发现啊!!
「四、你以为这就结束了吗」
我是真的希望就这么结束了,那这个问题就很简单的解决了,顺便还自以为是的发现了一个新知识。
但是!
这个 bug 转给测试后,测试一测,居然还是 30 多秒!?这是什么情况!!???
我当然是不信了,去测试电脑上执行 sql,还真是 30 多秒。。。
我又回我的电脑上,连接同一个数据库,一执行 sql,0.8 秒!?
什么情况,同一个库,同一个 sql,怎么在两台电脑执行的差距这么大!
后来直接在服务器上执行:
![](https://filescdn.proginn.com/af789d8fd8f7105e7c9fa0388cf3e319/ab5ce067ca37838e5a150e99d56f87f7.webp)
醉了,居然还是 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 分钟。。。):
![](https://filescdn.proginn.com/537fbc7ca152f053c2332fc1ee71d173/98aef42d08e0202a10aec0f3505c5cfe.webp)
「六、最终解决方案」
感谢群里的“言枫”大佬!
经过你的提醒,我确实发现,explain 执行计划里,索引好像并没有用到我创建的 idx_end_time。
然后果断在现网试了下,强制指定使用 idx_end_time 索引,结果只要 0.19 秒!
![](https://filescdn.proginn.com/dab18e800823b7247bfd7cf8a3b81802/9667934833d195bbd8638c15b4a4de57.webp)
至此问题解决,其实同事昨天也在怀疑,是不是这个表索引建的太多了,导致用的不对,原本用的是 idx_org_id 和 idx_mvno_id。
现在强制指定 idx_end_time 就 ok 了!
最后再对比下改前后的执行计划:
改之前(查询要 1 分钟左右):
![](https://filescdn.proginn.com/8ef5ae85b320a380c983b6e4c538b1e5/8c3c35b8c7ef5b20562e8d1bc779a559.webp)
改之后(查询只要几百毫秒):
![](https://filescdn.proginn.com/7fb0c9552b8a4945c2ce35a4e2d3981e/c4f7a218156973687b27f1646914c230.webp)