먼저 저는 입사 4개월 차 개발자 신입입니다. 질문 수준이 조금 낮을 수 있다는 점 참고 바랍니다.
성능 개선을 위해 3일간 많은 노력을 했지만 성과가 잘 나오지 않아 도움 요청 드리게 됐네요
SELECT TT.ADDR_BASIC , TT.ADDR_DETAIL , TT.ADDR_INFO_SEQ , SUM(TT.BOX_COUNT) AS BOX_COUNT , @rownum AS ORDER_COUNT , MAX(TT.ORDER_BECON_TYPE) AS ORDER_BECON_TYPE , MAX(TT.PREMIUM_YN) AS PREMIUM_YN , MIN(TT.INSPECT_STATUS) AS INSPECT_STATUS , MAX(TT.ROUTING_YN) AS ROUTING_YN , MAX(TT.MANAGER_PICK_UP_YN) AS MANAGER_PICK_UP_YN , TT.DELIVERY_GROUP_LABEL FROM ( SELECT B.ADDR_BASIC , B.ADDR_DETAIL , B.ADDR_INFO_SEQ , IFNULL(C.BOX_COUNT, 1) BOX_COUNT , @rownum:=@rownum+1 AS SHIPPING_RN , A.ORDER_BECON_TYPE , B.PREMIUM_YN , A.INSPECT_STATUS , CASE WHEN D.ROUTING_CNT > 0 THEN 1 ELSE 0 END AS ROUTING_YN , CASE WHEN D.MANAGER_PICK_UP_CNT > 0 THEN 1 ELSE 0 END AS MANAGER_PICK_UP_YN , A.DELIVERY_GROUP_LABEL FROM DELIVERY_REPORT_TB A INNER JOIN ORDER_DELIVERY_INFO_TB B ON A.ORDER_DELIVERY_INFO_SEQ = B.ORDER_DELIVERY_INFO_SEQ LEFT OUTER JOIN ORDER_LIST_TB C ON B.ORDER_DELIVERY_INFO_SEQ = C.ORDER_DELIVERY_INFO_SEQ AND C.ORDER_TYPE = 1 INNER JOIN ( SELECT A.ORDER_LIST_SEQ , COUNT(D.MANAGER_PICK_UP_STATUS) AS MANAGER_PICK_UP_CNT , COUNT(CASE WHEN (A.ORDER_BECON_TYPE = 2 AND A.D_RNK != 999) THEN 1 ELSE NULL END) AS ROUTING_CNT FROM DELIVERY_REPORT_TB A INNER JOIN ORDER_DELIVERY_INFO_TB B ON A.ORDER_DELIVERY_INFO_SEQ = B.ORDER_DELIVERY_INFO_SEQ LEFT OUTER JOIN BARCODE_TB D ON A.ORDER_LIST_SEQ = D.ORDER_LIST_SEQ WHERE A.ORDER_DATE_SEQ = '1234' AND A.USE_YN = 1 AND B.PROGRESS_LEVEL <> 2 GROUP BY A.ORDER_LIST_SEQ ) D ON A.ORDER_LIST_SEQ = D.ORDER_LIST_SEQ , (SELECT @ROWNUM:=0) AS RN WHERE A.ORDER_DATE_SEQ = '1234' AND B.ADDR_INFO_SEQ = '123456' AND A.USE_YN = 1 AND B.PROGRESS_LEVEL <> 2 AND A.DELIVERY_GROUP_LABEL = '12A34' ) TT GROUP BY TT.ADDR_BASIC , TT.ADDR_DETAIL, TT.ADDR_INFO_SEQ ORDER BY ORDER_BECON_TYPE DESC;
위 쿼리를 보시면 성능 개선 할 부분이 많을 것 같다는 추측으로 개선하겠다고 마음 먹었지만 3일간 별다른 성과를 보지 못했네요..
평균적으로 1000ms가 약간 넘으며 트래픽이 점점 많이 지고 있으며 시간이 지날수록 개선이 필요하다고 판단하고 있습니다
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | <derived2> | ALL | NULL | NULL | NULL | NULL | 520 | Using temporary; Using filesort |
2 | DERIVED | <derived4> | system | NULL | NULL | NULL | NULL | 1 | |
2 | DERIVED | A | ref | IX_DELIVERY_REPORT_TB_1,IX_DELIVERY_REPORT_TB_2,IX_DELIVERY_REPORT_TB_4,IX_DELIVERY_REPORT_TB_6,IX_DELIVERY_REPORT_TB_8,IX_DELIVERY_REPORT_TB_9,IX_DELIVERY_REPORT_TB_10,IX_USE_YN,IX_DELIVERY_REPORT_TB_20 | IX_DELIVERY_REPORT_TB_6 | 68 | const,const | 52 | Using index condition; Using where |
2 | DERIVED | B | ref | PRIMARY,IX_ORDER_DELIVERY_INFO_TB,IX_ORDER_DELIVERY_INFO_TB_3 | PRIMARY | 4 | timftms.A.ORDER_DELIVERY_INFO_SEQ | 1 | Using where |
2 | DERIVED | C | ref | IX_ORDER_LIST_TB_6 | IX_ORDER_LIST_TB_6 | 4 | timftms.A.ORDER_DELIVERY_INFO_SEQ | 1 | Using where |
2 | DERIVED | <derived3> | ref | key0 | key0 | 5 | timftms.A.ORDER_LIST_SEQ | 10 | |
4 | DERIVED | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
3 | DERIVED | A | index_merge | IX_DELIVERY_REPORT_TB_1,IX_DELIVERY_REPORT_TB_6,IX_DELIVERY_REPORT_TB_9,IX_USE_YN,IX_DELIVERY_REPORT_TB_20 | IX_DELIVERY_REPORT_TB_1,IX_USE_YN | 5,5 | NULL | 16826 | Using intersect(IX_DELIVERY_REPORT_TB_1,IX_USE_YN); Using where; Using temporary; Using filesort |
3 | DERIVED | D | ref | IX_BARCODE_TB_1 | IX_BARCODE_TB_1 | 4 | timftms.A.ORDER_LIST_SEQ | 1 | Using where |
3 | DERIVED | B | ref | PRIMARY,IX_ORDER_DELIVERY_INFO_TB,IX_ORDER_DELIVERY_INFO_TB_3 | PRIMARY | 4 | timftms.A.ORDER_DELIVERY_INFO_SEQ | 1 | Using where |
쿼리 플랜을 조회 해봐도 인덱스도 잘 타고 있습니다.
select 순서를 바꿔보기도 하고 join 순서를 바꾸기도 해보고 다 해봤지만 답이 안나오네요
이런 쿼리의 성능 개선을 위해 어떤식으로 접근해야 할 지 궁금합니다. (조회 순서 변경은 해봤습니다)
먼저 답변 감사드립니다!! 제가 댓글을 늦게 확인해서 죄송합니다ㅠㅠ
답변 주신 내용에 다시 답변을 드리자면,
1. IFNULL에서 0이 아닌 1을 사용 하는 것은 잘못된 것으로 보여집니다. 데이터를 조회했을 때 0을 사용 하는 것이 논리적으로 더 맞는데 왜 이렇게 해놨는지 저도 의문입니다... 한번 더 다른 이유가 있는지 파악해보고 책임 개발자에게 물어보겠습니다.
2. addr_info_seq, delivery_group_label 조건은 서브쿼리에 들어가는게 맞습니다... 이 부분에서 조회하는 rows가 많이 줄었습니다.. 감사합니다ㅠ
3. @rownum을 두 번이나 구한 이유는 단순히 별칭으로 구분을 하기 위해서입니다. 하지만 쿼리가 클린하지 못한 것 같아 tt.shipping_rn as order_count로 수정했습니다.
4. MYSQL 버전은 클라이언트가 8.0대 인 최신 버전이고 서버는 5.5.64-MariaDB를 사용하고 있습니다. 분석함수에 대해서는 자세히 모르겠습니다..
1. @rownum AS order_count -- 최종순번 N = 전체 건수
2. tt.shipping_rn AS order_count -- 순번(1, 2, 3, ... N)
3. COUNT(tt.shipping_rn) AS order_count -- 그룹별 건수
3가지 모두 의미가 다릅니다.
3번의 의미라면? 그냥 COUNT(*) 하면 되는 것이지.
shipping_rn 이나 @rownum 을 사용할 이유가 없습니다.
1번의 의미라면? SUM(COUNT(*)) OVER()
2번의 의미라면? ROW_NUMBER() OVER(ORDER BY ?)
처럼 분석함수 사용이 가능합니다.
실제 order_count 라는 항목이 프로그램에서 사용이 되는 것인지?
만약 사용이 되고 있다면? 어떤 의미로 사용되는 것인지?
추가적으로
DBA 분들은 쿼리플랜을 보고 잘못된 쿼리를 어떤식으로 찾아내는지 궁금합니다.
또 뭔가 잘못된 것 같은 쿼리를 찾아 냈는데요.. 이것도 개선하고자 하는데 어떤식으로 접근해서 풀어내야 하는지 배우고 싶습니다.
질문 퀄리티가 너무 저질이라 이 질문에는 답변 따로 안주셔도 이해 하겠습니다;;(답답한맘에)
SELECT W.AREA_GROUP_LABEL , W.STANDARD_CNT , IFNULL(X.POINT_CNT , 0) AS POINT_CNT ,CASE WHEN STANDARD_CNT < IFNULL(X.POINT_CNT , 0) THEN CONCAT('<font color="red" >', IFNULL(X.POINT_CNT , 0) , '</font>') WHEN STANDARD_CNT > IFNULL(X.POINT_CNT , 0) THEN CONCAT('<font color="blue" >', IFNULL(X.POINT_CNT , 0) , '</font>') WHEN STANDARD_CNT = IFNULL(X.POINT_CNT , 0) THEN IFNULL(X.POINT_CNT , 0) END AS POINT_CNT_FONT , IFNULL(Y.SHIPPING_COUNT, 0) AS SHIPPING_CNT , IFNULL(Y.ORDER_COUNT , 0) AS ORDER_CNT , IFNULL(Y.BACK_COUNT , 0) AS BACK_CNT , IFNULL(Y.BOX_COUNT , 0) AS BOX_CNT , IFNULL(Y.ORDER_P_COUNT , 0) AS ORDER_P_CNT , IFNULL(Y.BOX_P_COUNT , 0) AS BOX_P_CNT , W.JO_LABEL FROM (SELECT AREA_GROUP_LABEL, STANDARD_CNT,JO_LABEL, NUM1, NUM2 FROM AREA_BASIC_INFO_TB WHERE USE_YN = 1 ) W LEFT OUTER JOIN (SELECT DELIVERY_GROUP_LABEL , COUNT(DELIVERY_GROUP_LABEL) AS POINT_CNT FROM( SELECT DELIVERY_GROUP_LABEL FROM DELIVERY_REPORT_TB A INNER JOIN ORDER_DELIVERY_INFO_TB B ON A.ORDER_DELIVERY_INFO_SEQ = B.ORDER_DELIVERY_INFO_SEQ INNER JOIN ORDER_LIST_TB C ON A.ORDER_LIST_SEQ = C.ORDER_LIST_SEQ AND C.PROGRESS_LEVEL <>2 WHERE A.ORDER_DATE_SEQ = 608 AND A.USE_YN = 1 GROUP BY DELIVERY_GROUP_LABEL, B.ADDR_BASIC, B.ADDR_DETAIL ) TT GROUP BY DELIVERY_GROUP_LABEL )X ON W.AREA_GROUP_LABEL = X.DELIVERY_GROUP_LABEL LEFT OUTER JOIN (SELECT DELIVERY_GROUP_LABEL AS SHIPPING_GROUP_LABEL , SUM(IF(A.INSPECT_STATUS >= 6, 1, 0)) AS SHIPPING_COUNT , COUNT( IF( B.ORDER_TYPE = 1 and B.PREMIUM_YN = 0, DELIVERY_GROUP_LABEL, null)) AS ORDER_COUNT , SUM(IF( B.ORDER_TYPE = 1 and B.PREMIUM_YN = 0, B.BOX_COUNT, 0)) AS BOX_COUNT , COUNT( IF( B.ORDER_TYPE = 1 and B.PREMIUM_YN = 1, DELIVERY_GROUP_LABEL, null)) AS ORDER_P_COUNT , SUM( IF( B.ORDER_TYPE = 1 and B.PREMIUM_YN = 1, B.BOX_COUNT, 0)) AS BOX_P_COUNT , COUNT(IF( B.ORDER_TYPE = 2 , DELIVERY_GROUP_LABEL, null)) AS BACK_COUNT FROM DELIVERY_REPORT_TB A INNER JOIN ORDER_LIST_TB B ON A.ORDER_LIST_SEQ = B.ORDER_LIST_SEQ WHERE A.ORDER_DATE_SEQ = 608 AND B.PROGRESS_LEVEL NOT IN (2) AND A.USE_YN = 1 GROUP BY DELIVERY_GROUP_LABEL )Y ON W.AREA_GROUP_LABEL = Y.SHIPPING_GROUP_LABEL ORDER BY W.JO_LABEL, W.NUM1, W.NUM2
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | PRIMARY | AREA_BASIC _INFO_TB |
ALL | NULL | NULL | NULL | NULL | 474 | Using where; Using filesort |
1 | PRIMARY | <derived3> | ref | key0 | key0 | 63 | AREA_BASIC_INFO_TB .AREA_GROUP_LABEL |
27 | Using where |
1 | PRIMARY | <derived5> | ref | key0 | key0 | 63 | AREA_BASIC_INFO_TB .AREA_GROUP_LABEL |
27 | Using where |
5 | DERIVED | A | index _merge |
IX_DELIVERY_REPORT_TB_1,IX_DELIVERY_REPORT_TB_2, IX_DELIVERY_REPORT_TB_6,IX_DELIVERY_REPORT_TB_8, IX_DELIVERY_REPORT_TB_10,IX_USE_YN, IX_DELIVERY_REPORT_TB_20 |
IX_DELIVERY_REPORT_TB_1, IX_USE_YN |
5,5 | NULL | 17120 | Using intersect(IX_DELIVERY_REPORT_TB_1,IX_USE_YN); Using where; Using temporary; Using filesort |
5 | DERIVED | B | ref | IX_ORDER_LIST_TB,IX_ORDER_LIST_TB_8, IX_ORDER_LIST_TB_9 |
IX_ORDER_LIST_TB | 4 | A.ORDER_LIST_SEQ | 1 | Using where |
3 | DERIVED | <derived4> | ALL | NULL | NULL | NULL | NULL | 12840 | Using temporary; Using filesort |
4 | DERIVED | A | index _merge |
IX_DELIVERY_REPORT_TB_1,IX_DELIVERY_REPORT_TB_2, IX_DELIVERY_REPORT_TB_6,IX_DELIVERY_REPORT_TB_8, IX_DELIVERY_REPORT_TB_9,IX_DELIVERY_REPORT_TB_10, IX_USE_YN,IX_DELIVERY_REPORT_TB_20 |
IX_DELIVERY_REPORT_TB_1, IX_USE_YN |
5,5 | NULL | 17120 | Using intersect(IX_DELIVERY_REPORT_TB_1,IX_USE_YN); Using where; Using temporary; Using filesort |
4 | DERIVED | B | ref | PRIMARY,IX_ORDER_DELIVERY_INFO_TB, IX_ORDER_DELIVERY_INFO_TB_3 |
PRIMARY | 4 | A.ORDER_DELIVERY _INFO_SEQ |
1 | |
4 | DERIVED | C | ref | IX_ORDER_LIST_TB,IX_ORDER_LIST_TB_8, IX_ORDER_LIST_TB_9 |
IX_ORDER_LIST_TB | 4 | A.ORDER_LIST_SEQ | 1 | Using where |
제가 접근했던 내용은 이렇습니다.
현재 3,4,5번째 select가 인덱스를 타지 않아,
where절에 추가로 인덱스를 탈 수 있을만한 컬럼을 추가하려 했지만 조건에 맞는 컬럼이 존재 하지 않았습니다.
그룹핑을 할 때 꼭 필요한 데이터와 필요 없는 데이터들을 생각해서 조건을 추가하려고 했습니다.
결국 이래저래 시도해보았지만 결과는 도출하지 못했습니다...
답답하네요ㅠ