[MsSQL] 쿼리튜닝 - Join Hint와 Leading Table

쿼리 튜닝

쿼리를 튜닝하다보면 여러 상황을 만나게 되는데, 그 중에 MsSQL쿼리의 Join에 대한 힌트들에 대해서 간단하게 소개해보도록하겠다.

 

사실 Oracle 쿼리튜닝 책만 읽어서 MsSQL에 대해서 자세하게 모르고 있었는데, 최근 사내에서 운영중인 MsSQL의 쿼리에 문제들이 있어 튜닝을 해야했었다.

 

팀장님이 잠시 자리를 비운사이 문제의 쿼리를 확인하고 조치하려했으나, 확인까지만하고 어떻게 조치해야하나 고민을 하며 자신있게 해결하지 못하였다.

 

팀장님이 돌아오신 후 Inner Loop Join으로 바꿔서 튜닝을 하라 라고 말씀하셨는데, 

정확히 힌트에 대해 알지 못하다보니 자신있게 한다고 말을 못 했던게 많은 아쉬움으로 남는다.

 

조금 다른이야기였지만, 아무튼 Join에 대한 힌트들에 대해서 소개해보도록 하겠다.

 

Join Hint

대체로 알고있는 조인은 Inner Join / Left Outer Join / Right Outer Join / Full Outer Join과 같은 논리적인 조인일 것이다.

 

이렇게 쿼리에 명시되는 논리적인 조인말고 해당 조인들이 실제로 물리적으로 동작 될 때,

어떠한 동작 방식을 가지는지에 따라 물리적인 조인 방식들이 있다.

 

물리적인 조인 알고리즘으로는 3가지를 대표적으로 꼽을 수 있다.

Nested Loop Join / Sort Merge Join / Hash Join 이러한 조인 알고리즘을 옵티마이저가 조인 할 때 필요에 따라 사용하게 된다.

각 조인 알고리즘을 힌트로 조절할 수 있는데 각 힌트에 대해서 알아보도록 하겠다.

 

물리적인 알고리즘에 대해서는 나중에 다른 포스팅에서 자세히 다루도록 하겠다.

 

Loop Join

Loop Join은 MsSQL에서 사용되는 힌트 옵션으로 Nested Loop Join을 사용하도록 유도하는 힌트이다.

쿼리힌트는 아래처럼 join 문에 포함하여 사용한다.

select *
  from TABLE_A a
 inner loop join TABLE_B b
    on a.id = b.id
    
select *
  from TABLE_A a
  left outer loop join TABLE_B b
    on a.id = b.id

 

TABLE_A에 데이터 100개 / TABLE_B에 데이터 50개를 넣고 간략히 테스트를 해보는데,

조인 힌트를 사용하지 않고 테스트 실행을 해볼 때는 아래와 같은 실행계획이 나온다.

TABLE_A부터 읽고 merge join으로 조인 알고리즘이 풀리는 것을 알 수 있다.

 

inner loop join 으로 힌트를 주며 사용하면 아래와같은 실행계획이 나온다.

조인의 알고리즘 방식을 loop join으로 바꾸는 것을 우리가 힌트를 주었으니 당연히 그렇게 바뀐다

하지만 테이블을 읽는 순서가 바뀌는데, loop join 힌트를 줄 경우 작은 데이터의 테이블을 먼저 선택하는 경향이 있다고한다.

leading table을 선택하는 쿼리 힌트가 없는 MsSQL 에서는 주요하게 사용 할 수 있는 조인 힌트라고 볼 수 있다.

 

작은 테이블을 선행으로 조인이 될 테이블을 하나씩 반복문 돌며 수행하기에 선행으로 읽는 테이블의 양이 무엇보다 중요하며, 적은 데이터를 조회 할 때 매우 유리하다.

 

Merge Join

Merge Join은 MsSQL에서 Sort Merge Join 알고리즘으로 유도하는 조인힌트이다.

쿼리힌트는 아래처럼 join 문에 포함하여 사용한다.

select *
  from TABLE_A a
 inner merge join TABLE_B b
    on a.id = b.id

select *
  from TABLE_A a
  left outer merge join TABLE_B b
    on a.id = b.id

 

merge join 쿼리힌트를 사용하여 해당 쿼리의 조인 알고리즘을 Sort Merge Join으로 풀어낸다.

각 테이블들을 정렬하고 각 정렬된 데이터 기반으로 조인을 한다. 

Join에 사용될 컬럼에 인덱스가 있을 경우 해당 인덱스를 활용하여 Sort 작업이 없어 효율이 좋으며 대용량 작업에 용이하다.

만약 인덱스가 없는 컬럼을 사용할 경우 해당 컬럼을 기준으로 다시 정렬해야하여 작업 효율이 좋지 못하다.

마찬가지로 merge join 을 사용할 경우 더 작은 테이블을 leading table로 선택하려는 경향이 강해진다고 한다.

인덱스가 없는 컬럼을 키로잡은경우 이렇게 Sort 비용이 발생한다.

 

Hash Join

Hash Join 힌트는 역시나 Hash Join을 유도하는 조인 힌트이다.

쿼리 힌트는 아래와 같이 join문에 사용한다.

select *
  from TABLE_A a
 inner hash join TABLE_B b
    on a.id = b.id
    
select *
  from TABLE_A a
  left outer hash join TABLE_B b
    on a.id = b.id

Hash Join으로 풀어낸 실행계획은 아래와 같다.

먼저 작은 테이블의 키 값을 해쉬화 메모리에 해쉬 테이블로 생성한다.

조인할 테이블의 키를 읽어가며 해쉬테이블과 매칭여부를 확인하여, 조인 동작을 진행한다.

 

Hash Join은 Merge Join보다 대용량 데이터 작업에 더욱 유리하지만, Hash Join을 하기위해서는 등차조건(=)의 비교로 조인 할때만 가능한 단점이 있다.

 

작은 테이블로 해쉬테이블을 만들기에 Hash Join 힌트 또한 leading table을 작은 테이블로 지정하려는 경향이 있다.

 

Leading Table

기본적으로 모든 조인은 작은 테이블을 먼저 읽고 그 결과값을 가지고 큰 테이블을 읽는게 성능상에 매우좋다.

그런데 가끔 옵티마이저가 잘못된 선택을하여 조인 테이블 순서를 바꾸어 주어야 할 때가 있는데, MsSQL에는 Join 순서를 명시하는 힌트가 따로없다.

 

그렇다면, 위에 Join Hint에서 설명했듯 Loop Join / Merge Join / Hash Join 등의 힌트를 통해 조인순서를 좀더 강력하게 어필 할 수 있다고 한다.

 

FORCE ORDER

 

MsSQL에는 명시적으로 조인 순서를 정하는 힌트는 없지만, 조인 순서에대해서 쿼리에 명시된 테이블 순서로 강제하는 옵션은 있다.

select *
  from TABLE_A a
 inner join TABLE_B b
    on a.id = b.id
 inner join TABLE_C c
    on a.id = c.id
 option (force order)

 

바로 option(force order) 구문인데, 해당 옵션을 통해 TABLE_A -> TABLE_B -> TABLE_C 순으로 읽게 조정 할 수 있다.

 

기존에 option이 없는 쿼리를 실행해보면 아래와 같은 실행계획을 받을 수 있다.

A와 C를 먼저 조인 한 후 B를 조인하는 구조이다.

 

강제로 force order 옵션을 준 결과 아래처럼 실행계획이 보이는 것을 알 수 있다.

 

옵션이 없을때와 다르게 A, B 테이블을 먼저 조인한 후 C를 조인하는 것을 알 수 있다.

 

COMMENT

 

요즘 많은것을 보고 배우기에 이론적인 것은 대체로 많이 공부하고 있다고 생각한다.

물론 많이 부족하지만... 이론적으로는 Oracle 이나 MsSQL 등 다 큰 틀은 같다는게 요즘 느껴진다. 자세히 모를뿐.

 

그리고 그 이론들이 실전에서 사용되는 것은 또 다른 내용인 것 같다. 

언젠가 나도 급한 상황에서 바로바로 판단이 되었으면 좋겠다.