[MySQL튜닝]힌트-해시 조인

SQL/MySQL

[MySQL튜닝]힌트-해시 조인

스윗보스 2023. 1. 2. 13:34

MySQL은 8 버젼부터 해시 조인을 지원하고 있습니다.

해시 조인을 유도할 수 있는 옵티마이져 힌트는 아래의 MySQL 레퍼런스를 통해 확인할 수 있습니다.

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

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

One means of control over optimizer strategies is to set the optimizer_switch system variable (see Section 8.9.2, “Switchable Optimizations”). Changes to this variable affect execution of all subsequent queries; to affect one query differently from an

dev.mysql.com

현재(2023년 1월) 기준으로 위의 레퍼런스를 살펴보면, 해시 조인과 관련된 힌트로, HASH_JOIN과 NO_HASH_JOIN이 있는 것을 알 수 있습니다. 하지만, Description을 보면 해당 힌트는 MySQL 8.0.18 에서만 사용할 수 있는 힌트입니다.

저는 현재 8.0.22 를 사용하고 있습니다. 이처럼 8.0.18 이후 버젼에서는 해시 조인을 컨트를하려면 BNL이나 NO_BNL이란 힌트를 사용해야 합니다.

위 레퍼런스에는, BNL 힌트의 설명으로 아래와 같이 정리되어 있습니다.

- Prior to MySQL 8.0.20: affects Block Nested-Loop join processing;

- MySQL 8.0.18 and later: also affects hash join optimization;

- MySQL 8.0.20 and later: affects hash join optimization only

BNL은 원래 Block Nested-Loop 를 뜻합니다. NL(Nested-Loops) 조인을 처리할 때, 조인 버퍼를 사용해 NL 조인 성능을 향상시키는 기능입니다. MySQL 8 이전 버젼은 해시 조인을 지원하지 않았기 때문에, BNL 기능으로 해시 조인을 어느 정도 대체했던거 같습니다.

어찌되었든, 8.0.2 부터는 BNL 힌트가 해시 조인 최적화에 영향을 준다니 혼란스럽습니다. 아마도 8.0.2부터는 본연의 BNL(Block Nested-Loops) 기능 자체는 없어진거 같습니다. 그냥 BNL 힌트를 제거하고 HASH_JOIN 힌트를 살려줬으면 혼란이 없지 않았을까 생각이 듭니다만, 제가 모르는 다양한 이유가 있겠죠.

그러면, BNL 힌트를 사용해 해시 조인을 한번 테스트해볼까 합니다. 생각보다? 아니, 생각만큼 힌트가 잘 안먹습니다.

먼저, 저희가 테스트할 테이블인 M_CUS와 T_ORD_BIG에는 어떤 인덱스가 있는지 먼저 살펴봅니다.

인덱스는 조인의 내부적인 처리 방법(NL, HASH, Sort Merge)에 영향을 끼치는 중요 요소입니다.

MySQL에서 인덱스는 'show index from 테이블명' 명령어를 통해 쉽게 확인할 수 있습니다.

현재는 아래와 같이 인덱스가 구성되어 있습니다.

- M_CUS: PRIMARY(CUS_ID)

- T_ORD_BIG: PRIMARY(ORD_SEQ), T_ORD_BIG_X1(ORD_DT)

- 테스트 데이터는 다음 글에서 구성할 수 있습니다.: https://sweetquant.tistory.com/346

위와 같이 인덱스가 구성된 상태에서 아래와 같이 SQL을 실행해 봅니다. 실행 계획을 보면, M_CUS와 T_ORD_BIG을 NL 방식으로 조인하고 있습니다. 조인 조건으로 사용된 CUS_ID 컬럼의 인덱스는 M_CUS에만 있으므로, M_CUS를 후행 집합으로 NL 처리하고 있습니다.

EXPLAIN ANALYZE
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>  (actual time=0.003..0.015 rows=72 loops=1)
     -> Aggregate using temporary table  (actual time=187.347..187.376 rows=72 loops=1)
         -> Nested loop inner join  (cost=31569.86 rows=39462) (actual time=1.043..126.440 rows=19800 loops=1)
             -> 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) (actual time=0.973..91.609 rows=19800 loops=1)
             -> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID)  (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=19800)

이제, NL이 아닌, 해시 조인으로 위 SQL이 처리되도록 하기 위해 아래와 같이 BNL 힌트를 적용합니다.

실행계획을 확인해보면, 힌트가 무시되고 NL 조인이 여전히 사용중인 것을 알 수 있습니다.

힌트를 잘 못 준건 아닐까란 생각에, /*+ BNL(T1) */ 이나, /*+ BNL(T1, T2) */, /*+ BNL(T2, T1) */ 등 다양하게 시도해봤으나 해시 조인이 발생하지는 않습니다.

아마도 MySQL의 옵티마이져가 생각했을때, 현재 SQL과 테이블, 인덱스 구성에서는 해시 조인이 비효율적이라고 판단한 것이겠죠.

EXPLAIN ANALYZE
SELECT  /*+ BNL(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>  (actual time=0.002..0.014 rows=72 loops=1)
     -> Aggregate using temporary table  (actual time=120.311..120.340 rows=72 loops=1)
         -> Nested loop inner join  (cost=31569.86 rows=39462) (actual time=0.240..80.108 rows=19800 loops=1)
             -> 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) (actual time=0.223..58.354 rows=19800 loops=1)
             -> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID)  (cost=0.25 rows=1) (actual time=0.000..0.001 rows=1 loops=19800)

자, 그러면, 해시 조인을 발생시키기 위햇 JOIN_ORDER 힌트를 적용해보면 어떨까요?

T1를 먼저 접근하도록 JOIN_ORDER(T1, T2)를 적용해봅입니다.

이 경우, T2(T_ORD_BIG)쪽에는 CUS_ID에 대한 인덱스가 없으므로 해시 조인이 발생할 가능성이 높습니다. (FULL SCAN 방식으로 NL을 처리할 수는 없으니까요.)

BNL 힌트 없이, JOIN_ORDER 만으로 조인 순서를 변경하자 해시 조인이 사용된 것을 알 수 있습니다.

EXPLAIN ANALYZE
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>  (actual time=0.002..0.014 rows=72 loops=1)
     -> Aggregate using temporary table  (actual time=106.843..106.871 rows=72 loops=1)
         -> Inner hash join (t2.CUS_ID = t1.CUS_ID)  (cost=1598243.65 rows=355158) (actual time=0.386..67.943 rows=19800 loops=1)
             -> 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) (actual time=0.250..55.218 rows=19800 loops=1)
             -> Hash
                 -> Table scan on T1  (cost=9.25 rows=90) (actual time=0.058..0.081 rows=90 loops=1)

이번에는 조인 순서를 T2->T1으로 변경하고 BNL 힌트까지 추가해봅니다. 혹시나 JOIN_ORDER와 BNL을 같이 써야만 해시 조인이 사용되는지 의심되서 입니다.

하지만, 다시 NL 조인이 사용되는 것을 볼 수 있습니다. 아무래도 M_CUS의 PRIMARY 인덱스 사용의 우선 순위가 높은거 아닌가 싶습니다.

EXPLAIN ANALYZE
SELECT  /*+ JOIN_ORDER(T2, T1) BNL(T2, T1) */
        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>  (actual time=0.002..0.011 rows=72 loops=1)
     -> Aggregate using temporary table  (actual time=93.163..93.184 rows=72 loops=1)
         -> Nested loop inner join  (cost=31569.86 rows=39462) (actual time=0.236..62.395 rows=19800 loops=1)
             -> 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) (actual time=0.219..44.639 rows=19800 loops=1)
             -> Single-row index lookup on T1 using PRIMARY (CUS_ID=t2.CUS_ID)  (cost=0.25 rows=1) (actual time=0.000..0.000 rows=1 loops=19800)

끝으로, 실제 성능과 상관없이, 죽었다 깨어나도 T2->T1 순서로 조인 순서를 유지하면서, 해시 조인을 만들어 보고 싶다면, 조인 조건의 강제 변화를 고려할 수 있습니다.

아래와 같이 T1.CUS_ID를 TRIM 처리해서 인덱스를 사용못하도록 합니다. 그리고 JOIN_ORDER 힌트만 사용해서 T2 먼저 접근하도록 합니다. BNL 힌트를 주지 않았지만, 해시 조인이 발생한 것을 알 수 있습니다.

(아래와 같이 조인 조건절을 일부러 변형해 해시 조인을 유도하는 것은 절대 좋은 방법이 아니죠.)

EXPLAIN ANALYZE
SELECT  /*+ JOIN_ORDER(T2, T1) */
        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 (TRIM(T1.CUS_ID) =  T2.CUS_ID) -- > T1.CUS_ID를 일부러 TRIM 처리
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>  (actual time=0.002..0.024 rows=72 loops=1)
     -> Aggregate using temporary table  (actual time=280.777..280.815 rows=72 loops=1)
         -> Inner hash join (trim(t1.CUS_ID) = t2.CUS_ID)  (cost=372923.03 rows=3551580) (actual time=141.649..239.391 rows=19800 loops=1)
             -> Table scan on T1  (cost=0.00 rows=90) (actual time=0.078..0.119 rows=90 loops=1)
             -> Hash
                 -> 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) (actual time=0.243..48.248 rows=19800 loops=1)

지금까지 살펴본 바로는, 해시 조인을 사용하는 힌트인 BNL이 그렇게 잘 작동하는거 같지는 않습니다. 조인을 처리할 수 있는 막강한 PRIMARY 인덱스가 있다면, 아마도 해시 조인보다 NL 조인을 선호하는 것은 아닌가 생각이 듭니다.

BNL 힌트는 잘 작동하지 않지만, NO_BNL 힌트는 또 잘 먹는거 같드라고요.

아래와 같이 해시 조인이 자동으로 작동했던, JOIN_ORDER(T1, T2)를 적용한 후에 NO_BNL까지 힌트를 걸어주면 NL 방식으로 조인이 처리되어 매우 느린 것을 알 수 있습니다.

EXPLAIN ANALYZE
SELECT  /*+ JOIN_ORDER(T1, T2) NO_BNL(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;

-> Group aggregate: max(t1.CUS_NM), sum(t2.ORD_AMT), count(0)  (actual time=187.159..6557.740 rows=72 loops=1)
     -> Nested loop inner join  (cost=1598243.65 rows=355158) (actual time=16.554..6530.982 rows=19800 loops=1)
         -> Index scan on T1 using PRIMARY  (cost=9.25 rows=90) (actual time=0.060..0.328 rows=90 loops=1)
         -> Filter: (t2.CUS_ID = t1.CUS_ID)  (cost=13816.34 rows=3946) (actual time=24.775..72.503 rows=220 loops=90)
             -> 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) (actual time=0.290..61.939 rows=19800 loops=90)

본 글에서는 BNL을 이용해 해시 조인이 발생한 케이스는 나오지 않았으나, 인덱스의 구성, 쿼리 조건의 변경에 따라 BNL 힌트가 작동이 될 수도 있다고 생각합니다. 어쨋든, 8.0.2 부터는 BNL 힌트를 사용해 해시 조인을 유도할 수 있다는 점과, 힌트를 준다고 해도, 무조건 해시 조인이 발생하지 않는다는 점을 알고 있으면 될거 같습니다.

오늘은 여기까지입니다. 감사합니다.