数据库查询优化

来源：互联网发布：云网络编辑：程序博客网时间：2024/06/06 00:25

数据库查询优化，尽量使用JOIN，代替子查询

原来的SQL:SELECT bu.realname,bu.username,de.departname from t_s_base_user bu
LEFT JOIN t_s_user_org uo on uo.user_id=bu.ID
left JOIN t_s_depart de on de.ID= uo.org_id
WHERE not exists (select r.responsibleid from t_family_info fi
LEFT JOIN t_responsible r on r.infoid=fi.id
WHERE fi.id in (select f.infoid from t_follow f where f.addtime>='2016-09-01') and r.responsibleid=bu.ID)
and bu.ID in (select responsibleid from t_responsible)

优化后的SQL:

select * from (SELECT bu.ID,bu.realname,bu.username,de.departname from t_s_base_user bu
LEFT JOIN t_s_user_org uo on uo.user_id=bu.ID
left JOIN t_s_depart de on de.ID= uo.org_id
INNER join t_responsible r ON r.responsibleid=bu.id GROUP BY bu.ID) as temp
LEFT JOIN (select r.responsibleid from t_family_info fi
LEFT JOIN t_responsible r on r.infoid=fi.id
WHERE fi.id in (select f.infoid from t_follow f where f.addtime>='2016-09-01')) as temp2 ON
temp.ID=temp2.responsibleid WHERE temp2.responsibleid IS NULL

0 0