文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

mysql 优化慢复杂sql (多个left join 数量过大 order by 巨慢)

2023-09-13 08:53

关注

懒得看过程的话这里直接总结一下最后的解决方法:
如果不能直接减少主表的数据(小表驱动大表),就想办法把多个left join合成一个子查询,速度是否变快,没有的话再在子查询底下加一个having条件(having什么不重要,结果不会错就行)

项目场景:因为一些迫不得已的原因(产品一定要)导致一个分页查询数据的sql非常复杂,查询效率巨巨巨慢(从来没查到过结果,最长等了2分钟)


涉及项目,就不贴真实代码了,大概结构是

select p.id,p.name,ps2.sortfrom table1 pleft join table2 pson p.name = ps.nameand ps.region = 1left join table2 ps2on ps.name = ps2.nameand ps2.region = 1and ...where ...order by ps2.sort asc,p.sale desc,p.time desclimit 0,10

table1表有1w+的数据量,table2表有八百万数据,每个region大概有1w+数据。


left join数据量太大,笛卡尔积相当于1w✖️1w✖️1w,也就是做了1w✖️1w✖️1w的关联。最后的order by由于数据量过大,要反复回表查做排序,导致查询速度及其慢。
(不加order by时10秒能查出来,虽然也还是慢但是至少能查出来,但是order by 不能不要)


经过一系列百度,作出以下几种解决尝试。

方案一:按需给涉及的两个表加了索引,explain走了索引,但仍然因为order by 无法查出结果。(删除order by仍需要10s+)

方案二:根据对mysql join的理解,按理说减少笛卡尔积应该能大幅度提升速度,于是猜想把sql的两次left join 变成一次,是不是就能解决问题?对sql进行分析,能把后两次left join抽成一个子查询,即如下结构

select p.id,p.name,pss.sortfrom table1 pleft join (select name,sortfrom table2 psleft join table2 ps2on ps.name = ps2.nameand ...where ...) psson p.name = pss.namewhere ...order by pss.sort asc,p.sale desc,p.time desclimit 0,10

但是仍然查询很慢也仍然无法查出结果,explain的结果与未作修改的explain结果没有差别,也就是说笛卡尔积仍然是1w✖️1w✖️1w,而我预想的结果应当是1w✖️1w。

方案三:百思不得其解时,我随手在方案二升级的子查询里加了一个having sort = 1,速度陡然变快,与方案二的sql仅一个having之差,速度却提升了数十倍,原本2min都查不出来的sql,现在3秒内能查出,explain结果也有所不同,多了一行table为 < dervied2>的结果,看样子是实现了1w✖️1w的效果。


猜想:

方案二的子查询应该是被mysql自动优化成直接的left join关联,所以explain结果才会没有差别,而在子查询中加了having之后mysql无法自动优化成直接的left join,就沿用了sql的调用顺序,所以explain才会多一个子查询的行,也就实现了我想要的效果,即从1w✖️1w✖️1w ->1w✖️1w,速度也就得到了很大的提升。
没有谷歌出来实锤,但是根据explain的结果应该是这样没错。

讲道理应该从更根本的表结构的源头上解决问题,或者考虑是不是应该用es,但是有些东西身不由己,代码和人总要有一个能跑,哎。

来源地址:https://blog.csdn.net/qq_40835969/article/details/128239398

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯