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范围。查看其执行计划：

能看到其实该建的索引是基本上都建了，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：

所以现在的情况就很清晰了，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语句，执行计划如下：

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