[MySQL] Join 속도 향상하려면 (MySQL Hint)

mysql 쿼리 튜닝은 다양한 방법으로 가능하다.

​

우선 쿼리의 성능을 측정하기 위해서는 실행계획을 보는것으로 부터 시작하는 것이 편하다.

 

A Table                B Table                       C Table (A와 B의 관계정보)

no / name            no / nickname              a_no | b_no

----------------------------------------------------------------------------

1 / kim                1 / cc                          1 / 1

2 / Lee                2 / dd                          1 /  2

3 / Park                                                 2  / 1

​

A, B, C 모두 Join 해서 데이터를 가져오고 싶을 경우 아무 생각없이 아래와 같이 join 한다면?

SELECT * FROM A LEFT JOIN C ON A.no = C.a_no;

A Table 에는 C에 없는 데이터들이 있을 수 있기 때문에 A Table 에 대해서 full scan 이 발생한다.

이걸 해결하려면 C Table 을 기준으로 Join 하면 된다.

SELECT * FROM C LEFT JOIN A ON C.a_no = A.no;

세개 Table 을 모두 Join 해 보면 이렇게 된다.

SELECT * FROM C LEFT JOIN A ON C.a_no = A.no LEFT JOIN B ON C.b_no = B.no;

그런데 실행계획을 다시 봐도 type 이 ALL 으로 나오는 경우가 있다.

All 이 아니더라도 full scan과 같이 A Table 의 모든 row 수 만큼 가져와서 join 하는 경우가 있다.

​

이건 옵티마이저가 알아서 Join 순서를 바꿔서 실행하기 때문이다.

Oracle의 hint 처럼 Join 의 순서를 명시하려면 STRAIGHT_JOIN 을 사용하면 된다.

SELECT STRAIGT_JOIN * FROM C LEFT JOIN A ON C.a_no = A.no LEFT JOIN B ON C.b_no = B.no;

STRAIGHT_JOIN 은 Join 과 동일한데 순서를 보장해서 왼쪽 테이블을 먼저 읽도록 강제하는 Join 이다.

STRAIGHT_JOIN 은 ANSI SQL 이 아니기 때문에 꼭 필요한 경우에만 사용하는 것이 좋다.

​

왼쪽 테이블에 비해 오른쪽 테이블의 데이터가 너무 많아 필터링 해야 하는데 정렬까지 필요할 경우

그리고 오른쪽 테이블의 많은 데이터 중 왼쪽 테이블과 Join 할때 필요 없는 데이터가 많은 경우

STRAIGHT_JOIN 을 이용하면 좋다. (반드시 사용 전후에 대한 실행계획을 비교해서 보는것이 좋다)

​

만약에 위와 같이 했음에도 실행계획을 봤더니 전체 Row 에 대해 가져와서 계산을 하고 있다면

Order by 없이 limit 을 사용하고 있지는 않은지,

Order by 대상 컬럼이 C 가 아닌 A, B Table 로 하고 있지 않은지를 봐야 한다.

​

참고)

MySQL 8.0 부터는 STRAIGHT_JOIN 을 포함한 여러 힌트를 보다 더 힌트스럽게 사용할 수 있는 옵티마이저 힌트가 추가 되었다.

- JOIN_FIXED_ORDER: STRAIGHT_JOIN 구문을 대체

- JOIN_ORDER: 가능하다면 나열된 Join 순서로 Join 할것을 권고

- JOIN_PREFIX: 처음의 Join 순서를 권고

- JOIN_SUFFIX: 마지막의 Join 순서를 권고

SELECT /*+ JOIN_FIXED_ORDER() */ * FROM C LEFT JOIN A ON C.a_no = A.no LEFT JOIN B ON C.b_no = B.no;

조금 더 우리가 잘 알고 있는 힌트 (Oracle hint)스럽게 사용할 수 있다.