SQL优化-子查询

最近发现线上系统有一个列表数据查询接口总是提示接口超时，经过排查发现前端请求接口超时时间设置的为5s，后端接口5s还未返回数据则造成了超时提示。
那么为什么5s都还没有返回数据呢，通过日志排查发现该接口的SQL语句查询花了近2s，典型的慢SQL呀(谁写的？出来挨打)，接下就是慢SQL分析、优化的旅程。

慢SQL语句

下面的SQL已经被我简化只留下了出问题的SQL，可以看到其实是1个子查询然后连接了两张表。

select expert.id as                                expert_id,
       (select count(app_scientific_achievement.id)
        from app_scientific_achievement
        where author_id = expert.id)               achievement_amount
from app_expert_user expert
         inner join app_user user on expert.id = user.target_id
         left join sys_basic_data basic on basic.id = expert.title_ids
where user.status < 2 or user.status > 2
limit 0,20

查询时间

通过Datagrip执行该SQL语句发现需要接近2秒的时间才能得到结果，这个时间已经算比较长的了（还是在数据只有几千条的情况下），在数据更多的情况下可能花费的时间更多。

执行计划

通过explain来分析SQL语句，可以看到整个SQL语句有三处全表扫描的地方，第一个为主表最后一个是字典表查询（这个不做分析）。
那么我们现在来分析这个两个个全表扫描是否可以进行优化？

1. user
   现在把涉及到user表的语句展示出来，可以看到以下语句是一个内联关系

   select * where app_expert_user expert
            inner join app_user user on expert.id = user.target_id

2. app_scientifc_achievement
   涉及这个表的语句是一个子查询，可以从SQL语句看到是一个聚合数据的子查询。如果 app_scientific_achievement 表的数据不断增加，这种全表扫描会随着数据量的增加而增加，那我们就需要分析 author_id 字段是否有创建索引的条件，根据自身业务场景可以做一个索引的建立

   select count(app_scientific_achievement.id) from app_scientific_achievement where author_id = expert.id

将 author_id 创建好索引后，我们再通过explain来分析一个刚刚那个SQL语句

可以看到之前那个子查询通过对 author_id 字段建立索引后已经不再进行全表扫描而是变成索引访问，现在我们再执行SQL查询，看看查询效率是否有所提升？

可以通过上图看见查询时间已经有接近2秒变成来接近0.9秒，说明我们对当前子查询创建索引优化是有效的。