by DBA_꿈꾸는개발자 [SQL Query] Mysql Query 튜닝 [2022.05.10 14:06:35]
안녕하세요. spring mybatis로 List 목록을 뿌리는 SQl문입니다. 현재 이 쿼리로 List 목록이 화면에 나타나는 시간이 12초 정도 걸립니다. 아직 SQL에 초보여서, 감이 없어서요, 혹시 이 쿼리를 보고 문제점이 있으시면 ㅠㅠ... 지적 부탁드리겠습니다.
SELECT COUNT(*) FROM T_Car_Repair AS CR LEFT JOIN T_Car_Repair_Info AS CRI ON CRI.repair_seq = CR.repair_seq LEFT JOIN T_Car AS C ON C.car_seq = CR.car_seq LEFT JOIN T_Area AS AR ON AR.area_seq = CR.area_seq LEFT JOIN T_Camp AS CP ON CP.camp_seq = CR.camp_seq LEFT JOIN T_Car_Equipment AS CE ON CE.car_equipment_seq = CRI.car_equipment_seq LEFT JOIN T_Equipment AS E ON E.equipment_seq = CE.equipment_seq LEFT JOIN T_Garage AS G ON G.garage_seq = CRI.schedule_garage_seq INNER JOIN TB_CODE AS C1 ON CRI.state = C1.code_val AND C1.group_cd = 'AST' INNER JOIN TB_CODE AS C3 ON CRI.repair_type = C3.code_val AND C3.group_cd = 'WTY' WHERE 1 = 1 AND (CR.is_del = 'N' OR CR.is_del IS NULL) AND CRI.state NOT IN ('AST00010', 'AST00020', 'AST00030', 'AST00040', 'AST00050') AND CR.repair_seq IN ( SELECT CRI.repair_seq FROM T_Car_Repair_Info AS CRI LEFT JOIN T_Car_Repair AS CR ON CR.repair_seq = CRI.repair_seq LEFT JOIN T_Car AS C ON C.car_seq = CR.car_seq LEFT JOIN T_Garage_Work AS GW ON GW.work_type = CRI.repair_type LEFT JOIN t_garage AS G ON GW.garage_seq = G.garage_seq WHERE 1=1 AND C.singulation_vendor_seq = 115 AND GW.work_type = 'WTY00030' #상태 : 업체A AND GW.garage_seq IN (SELECT garage_seq FROM T_Vendor_Garage WHERE vendor_seq = 115) AND (G.is_del = 'N' OR G.is_del IS NULL) UNION SELECT A.repair_seq FROM ( SELECT CRI.repair_seq, GW.garage_seq FROM T_Car_Repair_Info AS CRI LEFT JOIN t_car_equipment AS CE ON CE.car_equipment_seq = CRI.car_equipment_seq LEFT JOIN T_Garage_Work AS GW ON GW.equipment_seq = CE.equipment_seq WHERE 1=1 AND GW.work_type ='WTY00090' #상태 : 업체B AND GW.garage_seq IN (SELECT garage_seq FROM T_Vendor_Garage WHERE vendor_seq = 115) ) AS A, ( SELECT CR.repair_seq, G.garage_seq FROM T_Car_Repair AS CR LEFT JOIN T_Garage AS G ON G.camp_seq = CR.camp_seq WHERE 1=1 AND G.garage_seq IN (SELECT garage_seq FROM T_Vendor_Garage WHERE vendor_seq = 115) AND (G.is_del = 'N' OR G.is_del IS NULL) ) AS B WHERE 1=1 AND A.repair_seq = B.repair_seq AND A.garage_seq = B.garage_seq UNION SELECT A.repair_seq FROM ( SELECT CRI.repair_seq, GW.garage_seq FROM T_Car_Repair_Info AS CRI LEFT JOIN T_Garage_Work AS GW ON GW.work_type = CRI.repair_type WHERE 1=1 AND GW.work_type NOT IN ('WTY00030', 'WTY00090') #업체 A, 업체 B AND GW.garage_seq IN (SELECT garage_seq FROM T_Vendor_Garage WHERE vendor_seq = 115) ) AS A, ( SELECT CR.repair_seq, G.garage_seq FROM T_Car_Repair AS CR LEFT JOIN T_Garage AS G ON G.camp_seq = CR.camp_seq WHERE 1=1 AND G.garage_seq IN (SELECT garage_seq FROM T_Vendor_Garage WHERE vendor_seq = 115) AND (G.is_del = 'N' OR G.is_del IS NULL) ) AS B WHERE 1=1 AND A.repair_seq = B.repair_seq AND A.garage_seq = B.garage_seq )