Mysql sql优化（一）

喜欢ヅ旅行 2021-09-25 14:36 376阅读 0赞

业务场景1：

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

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUVE6MzA4MzE1NTkwOA_size_20_color_FFFFFF_t_70_g_se_x_16][]

原始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还是需要如何创建合理的索引

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAUVE6MzA4MzE1NTkwOA_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20210923/1f296325406a43caa559b636ae2dde68.png