SQL调优案例——多表查询(left jion)的优化

最近在工作的时候遇到一条慢查询,sql如下(根据业务仿写,非实际公司代码):

select t.id, t.task_name, t.task_status, t.remark
from task t
left join task_domain a on t.id = a.task_id
left join domain b on a.domain_id = b.id
left join category c on t.id = c.task_id
where t.deleted = 0
and (t.create_by = 1 or a.user_id = 1 or c.user_id = 1) 
group by t.id
order by t.update_at desc 
limit 20;

这几张表的数据都不大,均在10w以下,查询时间在800ms左右,远超公司允许的400ms范围。查看其执行计划:

54962aef2ad844e0810d23accdf03600.png

能看到其实该建的索引是基本上都建了,a,b,c三张表都是走了索引,只有t表是从索引里扫了全表,这很正常因为是left join。

然后t表里使用了filesort,没有用索引排序,因为排序用的update_at字段没有添加索引,但这个字段是需要经常更新的,不适合添加索引,所以我第一时间真觉得这个sql已经没有优化空间了,但是随后我注意到t表里还有一个using temporary,意为使用了临时表,一般来说使用临时表也有可能导致低效率,于是立马朝这个方向排查。

当然这里比较容易能判断出是group by语句导致使用了临时表,但因为这里的业务之前不是由我负责,一时半会没有理解这个group by语句的意图,等删掉该语句后发现,查询结果里t.id出现了大量重复,于是立马意识到是left join产生的重复,这个group by实际上是用来去重等同于distinct:

49500ed31a8d47dc85175ee80649158a.png

b5d8657ba4c74278aeb71c5b42a1db24.png

所以现在的情况就很清晰了,t表对于a、c两表来说,均是一对多的情况,我们最终的查询结果只需要保留t表的字段,而选择用left join连接实际将情况变成了多对多,使结果出现了大量重复,所以使用left join前一定要慎重,网上很多文章里提到用连接查询替代子查询会更好,其实不尽然,像这种一对多的情况子查询其实会更适合,将上述sql改为子查询:

select t.id, t.task_name, t.task_status, t.remark
from task t
where t.deleted = 0
and (t.create_by = 1 or t.id in (select task_id from domain b join task_domain a on b.user_id = 1 and a.domain_id = b.id) or t.id in (select task_id from category where user_id = 1))
order by t.update_at desc
limit 20;

这样得到的结果是没有重复的,可以去掉group by语句,执行计划如下:

1d23230a89ac4cbdb4651f07a4a34c91.png

可以看到临时表已经没有了,但是更重要的是查询出的结果要比left join连接查询的结果少上几倍甚至几十倍,优化后的查询时间在100ms以下,满足公司要求。

 

  • 3
    点赞
  • 7
    收藏
    觉得还不错? 一键收藏
  • 4
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值