记一次sql优化，从40s到1s

写sql也快有3年了，总结过hive、mysql的sql优化，前几天遇到一个问题很快的解决了，纪录一下。

数据库为mysql，修改之前的sql也是我写的，出现问题是报表超时了无法加载，我手动执行了一下sql需要40s，如下：

select d.DEPT_NAME,CASE b.Corporation 
                WHEN 6 THEN
                'f' ELSE '其他' 
        END AS '公司'
        ,a.UserId
        ,a.Real_Name
        ,b.CityName
        ,sum(c.ActualPrice),sum(c.MissionValue)
from gxkf_ods_customeronlinedb.salemanager a
left join gxkf_ods_customeronlinedb.userbase b on a.UserId = b.UserId
left join gxkf_dm_customeronlinedb.APD_CITY_DEPTNAME d on b.CityName = d.CITY
inner join gxkf_ods_customeronlinedb.byboschedulemonthmissiondetail c on a.UserId = c.UserId 
where a.IsByBo = 1  and Left(c.PayDateMonth,4) =LEFT(replace('${queryDate}','-',''),4)
and LEFT(c.PayDateTime,10) <= LEFT('${queryDate}',10)
group by d.DEPT_NAME,2,a.UserId,a.Real_Name
ord