Mysql sql优化（一）

业务场景1：

排查生产日志，发现执行该sql需要16s

 原始sql:

SELECT 
            dance_.company,
            dance_.depart_name,
            fr.workTime attendanceDate,
            user_.nick_name,
               user_.record_no number,
          
            group_concat(DATE_FORMAT(leave_start_time,'%m月%d日 %H:%i'),';',DATE_FORMAT(leave_end_time,'%H:%i') order by leave_start_time asc) timeDetail,
            sum(leave_.leave_hour) totalTimes
        FROM
            oct_hr_user_attendance_leave leave_
        JOIN oct_hr_user_attendance dance_ ON dance_.id = leave_.user_attendance_id
        join sys_user user_ on user_.id=dance_.user_id
        LEFT JOIN (select group_concat(work_time,';',offwork_time)
        workTime,frequency_id from oct_hr_frequency_details fd join
        oct_hr_frequency f on f.id=fd.frequency_id group by fd.frequency_id )
        fr on fr.frequency_id=dance_.frequency_id
        where dance_.attendance_date between '2021-07-21 00:00:00' and '2021-08-20 00:00:00' and leave_.leave_type='0'
        AND (dance_.depart_id in
                ("402881935e19455f015e1c22f39b00b4","402881935e19455f015e1c23e05d00b6","402881935e19455f015e1c462d22013a","40288193692876ee01692dc31d53209e","402881935e19455f015e1c45baf70138","402881935e19455f015e1c45003f0136","297e402a7aecc5e0017af0178d151d96","402881935e19455f015e1c2646be00c0","4028819373ae2a6b0173b361e7a314a0","402881935e19455f015e1c26c18f00c4","402881935e19455f015e1c3814cf0107","402881935e19455f015e1c3ee3a90120","402881935e19455f015e1c435f1b0132","40288193692876ee01692dba55e82061","40288193692876ee01692dc778ef20fe","402881936f5b78bb016f6a0ac8ca1e57","40288193764c2aa20176603b90db4eab","40288193764c2aa20176603c720b4eb1","40288193764c2aa20176603d5ffc4ebf","402881935e19455f015e1c2862fc00c9","297e402a776ba27901778ac64d3071aa","297e402a776ba27901778aceba377213","4028819373be203d0173c81163a0310c","4028819373be203d0173c812f99f3129","402881935e19455f015e1c2ac96700d6","402881935e19455f015e1c2b7e4700d9","402881935e19455f015e1c2c23a200db","402881935e19455f015e1c2cb2ed00df","402881935e19455f015e1c2fe41600e8","402881935e19455f015e1c29264400ce","402881935e19455f015e1c2e35bc00e4","402881935e19455f015e1c2edd9c00e6","4028819375a104930175a5a06c0c05d9","4028819375a104930175a5a160c105df","4028819375a104930175a5a274c305e1","4028819375a104930175a5a398ff05e3","402881935e19455f015e1c3f5f6a0122","402881935e19455f015e1c3fe1410124","402881935e19455f015e1c40e4340129","402881935e19455f015e1c414084012b","402881935e19455f015e1c41ccdc012d","40288193692876ee01692dc04b28208b","40288193692876ee01692dc1c3f8209a","402881935e19455f015e1c24fbd500bc","402881935e19455f015e1c244af900b9","402881936f1d36b6016f466cc7c804aa","297e402a781023d6017819d84fa330ec","297e402a781023d6017819d98a663108","297e402a781023d6017819d39d78306f","297e402a781023d6017819d8c81e30f2","297e402a781023d6017819ce27042eac","297e402a781023d6017819f565c43279","402881935e19455f015e1c31328400ea","402881935e19455f015e1c31b2cd00ec","402881935e19455f015e1c3286d200f0","402881935e19455f015e1c32f95f00f2","4028819366ed91dd0166f61940fd0d55","402881935e19455f015e1c3b059b0111","402881935e19455f015e1c3a0153010e","4028819365ec54900165f508fc7e11c5","402881935e19455f015e1c3c636d0117","402881935e19455f015e1c3be46e0114","4028819375a104930175a5a53d1805e7","402881935e19455f015e1c3cdb4c0119","40288193692876ee01692dc6826020f6","402881935e19455f015e1c42ac06012f","40288193737607bd0173a2ea869f2530","4028819375a104930175abb17dd3192b","402881936e645508016e7e08c5732e21","402881936e645508016e7e0a09012e2d","4028819372fa893b0173040fcd99258f","4028819360721ffa01607bc359761688","4028819360721ffa01607bc556ff1691","4028819360721ffa01607bc5c2731693","4028819360721ffa01607bc630381696","402881935e19455f015e1c258ca500be"
                ))
        group by dance_.user_id ORDER BY dance_.depart_id DESC;

问题分析：

刚开始认为是使用了in关键字导致查询变慢了，查询资料发现in最终是走索引了

解决方案：

再oct_hr_user_attendance_leave请假明细表中创建了索引列  user_attendance_id 和leave_type

最终执行sql只需要0.2s

总结：

优化sql还是需要如何创建合理的索引