[MySQL튜닝]힌트-조인순서

MySQL의 옵티마이져 힌트를 사용해 조인 순서를 조정하는 법을 살펴보겠습니다.

 

MySQL에 어떤 옵티마이져 힌트가 있는지는 아래 레퍼런스를 참고하면 알 수 있습니다.

* MySQL의 경우 버젼에 따라 작동하지 않는 힌트가 있으므로 주의가 필요합니다.

https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html

MySQL :: MySQL 8.0 Reference Manual :: 8.9.3 Optimizer Hints

 

아래와 같은 SQL을 실행해봅니다.

(아래 SQL을 실행하기 위해서는 https://sweetquant.tistory.com/346 글을 참고해 DB를 구성해야 합니다.)

EXPLAIN
SELECT  T1.CUS_ID
        ,MAX(T1.CUS_NM) CUS_NM
        ,SUM(T2.ORD_AMT) ORD_AMT
        ,COUNT(*) ORD_CNT
FROM    MYTUNDB.M_CUS T1
        INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE   T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND     T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;

 

결과를 보면 아래와 같습니다. T2(T_ORD_BIG)가 위에 있습니다. MySQL의 경우, 아래와 같은 결과에서는 위쪽에 있는 단계가 먼저 처리됩니다. 다시 말해 T2를 먼저 접근해 T1과 조인이 처리되고 있습니다.

 id   select_type   table   partitions   type     possible_keys   key            key_len   ref                 rows    filtered   Extra                                    
---- ------------- ------- ------------ -------- --------------- -------------- --------- ------------------- ------- ---------- ---------------------------------------- 
1    SIMPLE        T2      None         range    T_ORD_BIG_X1    T_ORD_BIG_X1   6         None                39462   100.0      Using index condition; Using temporary   
1    SIMPLE        T1      None         eq_ref   PRIMARY         PRIMARY        162       mytundb.T2.CUS_ID   1       100.0      None

위 SQL의 실행계획을 EXPLAIN FORMAT = TREE를 사용해 TREE 형태로 살펴보면 아래와 같습니다. T2를 T_ORD_BIG_X1 인덱스로 접근한 후, T1을 NL 조인 방식으로 처리하고 있는 것을 알 수 있습니다.

EXPLAIN FORMAT = TREE
SELECT  T1.CUS_ID
        ,MAX(T1.CUS_NM) CUS_NM
        ,SUM(T2.ORD_AMT) ORD_AMT
        ,COUNT(*) ORD_CNT
FROM    MYTUNDB.M_CUS T1
        INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE   T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND     T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;

-> Table scan on <temporary>
     -> Aggregate using temporary table
         -> Nested loop inner join  (cost=31569.86 rows=39462)
             -> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d'))))  (cost=17758.16 rows=39462)
             -> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID)  (cost=0.25 rows=1)

 

 

위 SQL의 조인 순서를 변경해봅니다. T1을 먼저 접근해 T2쪽으로 조인을 하도록 처리해보는 거죠.

아래와 같이 JOIN_ORDER라는 옵티마이져 힌트를 사용할 수 있습니다.

EXPLAIN
SELECT  /*+ JOIN_ORDER(T1, T2) */
        T1.CUS_ID
        ,MAX(T1.CUS_NM) CUS_NM
        ,SUM(T2.ORD_AMT) ORD_AMT
        ,COUNT(*) ORD_CNT
FROM    MYTUNDB.M_CUS T1
        INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE   T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND     T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;

아래와 같이 실행계획을 보면, T1부터 접근해서 조인이 처리되는 것을 알 수 있습니다. 힌트가 작동한거죠.

id   select_type   table   partitions   type    possible_keys   key            key_len   ref    rows    filtered   Extra                                                               
---- ------------- ------- ------------ ------- --------------- -------------- --------- ------ ------- ---------- ------------------------------------------------------------------- 
1    SIMPLE        T1      None         ALL     PRIMARY         None           None      None   90      100.0      Using temporary                                                     
1    SIMPLE        T2      None         range   T_ORD_BIG_X1    T_ORD_BIG_X1   6         None   39462   10.0       Using index condition; Using where; Using join buffer (hash join)

TREE 형태로 실행계획을 살펴보면 아래와 같습니다.

실행계획을 잘 살펴보면, T1을 먼저 처리하는 방식으로 조인 순서가 바뀌면서,  Inner hash join이 발생한 것을 알 수 있습니다. 바뀐 조인 순서에 따라 옵티마이져가 HASH 조인으로 변경을 한 것입니다.

EXPLAIN FORMAT = TREE
SELECT  /*+ JOIN_ORDER(T1, T2) */
        T1.CUS_ID
        ,MAX(T1.CUS_NM) CUS_NM
        ,SUM(T2.ORD_AMT) ORD_AMT
        ,COUNT(*) ORD_CNT
FROM    MYTUNDB.M_CUS T1
        INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE   T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND     T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;

-> Table scan on <temporary>
     -> Aggregate using temporary table
         -> Inner hash join (t2.CUS_ID = t1.CUS_ID)  (cost=1598243.65 rows=355158)
             -> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d'))))  (cost=13816.34 rows=39462)
             -> Hash
                 -> Table scan on T1  (cost=9.25 rows=90)

 

끝으로 주의할 점이 있습니다. 옵티마이져 힌트에서, JOIN_ORDER의 테이블을 정의할 때 반드시 콤마(,)로 구분을 해야 합니다. 아래와 같이 스페이스로만 두 테이블을 분리하면 힌트가 작동하지 않습니다.

EXPLAIN FORMAT = TREE
SELECT  /*+ JOIN_ORDER(T1 T2) */ # 잘못된 힌트, T2과 T2를 콤마(,)로 분리해야함!
        T1.CUS_ID
        ,MAX(T1.CUS_NM) CUS_NM
        ,SUM(T2.ORD_AMT) ORD_AMT
        ,COUNT(*) ORD_CNT
FROM    MYTUNDB.M_CUS T1
        INNER JOIN MYTUNDB.T_ORD_BIG T2 ON (T1.CUS_ID = T2.CUS_ID)
WHERE   T2.ORD_DT >= STR_TO_DATE('20170201','%Y%m%d')
AND     T2.ORD_DT < STR_TO_DATE('20170301','%Y%m%d')
GROUP BY T1.CUS_ID;

-> Table scan on <temporary>
     -> Aggregate using temporary table
         -> Nested loop inner join  (cost=31569.86 rows=39462)
             -> Index range scan on T2 using T_ORD_BIG_X1, with index condition: ((t2.ORD_DT >= <cache>(str_to_date('20170201','%Y%m%d'))) and (t2.ORD_DT < <cache>(str_to_date('20170301','%Y%m%d'))))  (cost=17758.16 rows=39462)
             -> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID)  (cost=0.25 rows=1)

 

오늘은 여기까지입니다.!