[Real MySQL 8.0] 쿼리 작성 및 최적화 - JOIN

JOIN이 어떻게 인덱스를 사용하는지에 대해 쿼리 패턴별로 알아보고 주의사항에 대해서 알아보고자 한다.

1. JOIN의 순서와 인덱스

인덱스 레인지 스캔은 인덱스를 탐색(Seek)하는 단계와 인덱스를 스캔(Scan)하는 과정으로 구분할 수 있다.
일반적으로 인덱스를 이용해서 쿼리하는 작업에서는 가져오는 레코드의 건수가 소량이기 때문에 인덱스 스캔 작업은 부하가 작지만 특정 인덱스 키를 찾는 인덱스 탐색 작업은 상대적으로 부하가 높다.

 

조인 작업에서 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업을 단 한 번만 수행하고, 그 이후부터는 스캔만 실행하면 된다.
하지만 드리븐 테이블에서는 인덱스 탐색 작업과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복한다.
드라이빙 테이블과 드리븐 테이블이 1:1로 조인되더라도 드리븐 테이블을 읽는 것이 훨씬 더 큰 부하를 차지한다.

 

따라서, 옵티마이저는 항상 드라이빙 테이블이 아니라 드리븐 테이블을 최적으로 읽을 수 있게 실행계획을 수립한다.

 

아래의 employees 테이블과 dept_emp 테이블을 조인하는 예시를 보자.

SELECT *
FROM A a, B b
WHERE a.emp_no=b.emp_no;

이 두테이블의 조인 쿼리에서 A 테이블의 emp_no 컬럼과 B 테이블의 emp_no 컬럼에 각각 인덱스가 있을 때와 없을 때 조인 순서가 어떻게 달라지는 한번 살펴보자.

 

• 1. 두 컬럼 모두 각각 인덱스가 있는 경우 
  • 각 두 테이블의 emp_no 컬럼에 모두 인덱스가 있는 경우에는 어느 테이블을 드라이빙 테이블으로 선택하든 인덱스를 이용해 드리븐 테이블의 검색 작업을 빠르게 처리 가능하다.
  • 옵티마이저는 통계 정보를 이용해 적절히 드라이빙 테이블을 선택한다. 각 테이블의 통계 정보에 있는 레코드 건수에 따라 둘 중에 하나가 드라이빙 테이블이 되는 것이다.
  • 보통 어느 쪽이 드라이빙 테이블이 되든 옵티마이저가 선택하는 방법이 최적일 때가 많다.
• 2. A.emp_no에만 인덱스가 있는 경우
  • 옵티마이저는 항상 B 테이블을 드라이빙 테이블로 선택하고, A 테이블을 드리븐 테이블로 선택한다.
  • 그 이유는, A.emp_no에만 인덱스가 있을 때 B 테이블이 드리븐 테이블로 선택된다면 employees 테이블의 레코드 건수만큼 B 테이블을 풀 스캔해야만 "a.emp_no=b.emp_no" 조건에 일치하는 레코드를 찾을 수 있다.
  • "e.emp_no=10001"과 같이 employees 테이블을 아주 효율적으로 검색할 수 있는 조건이 있더라도 옵티마이저는 B테이블을 드라이빙 테이블로 선택할 확률이 높다.
• 3. B.emp_no에만 인덱스가 있는 경우
  • 2번의 경우와 반대로 처리된다.
  • A 테이블의 반복된 풀 스캔을 피하기 위해 A테이블을 드라이빙 테이블, B 테이블을 드리븐 테이블로 선택한다.
• 4. 두 컬럼 모두 인덱스가 없는 경우
  • 1번의 경우와 마찬가지로 어느 테이블을 드라이빙 테이블로 선택하더라도 드리븐 테이블의 풀 스캔은 발생하기 때문에 옵티마이저가 적절히 드라이빙 테이블을 선택한다.
  • 단, 레코드 건수가 적은 테이블을 드라이빙 테이블로 선택하는 것이 훨씬 효율적이다.
  • 이렇듯 조인 조건을 빠르게 처리할 적절한 인덱스가 없는 경우 MySQL 8.0.18 버전부터는 해시 조인으로 처리한다.

 

2. JOIN 컬럼의 데이터 타입

이전 포스팅에서 WHERE 절에 사용되는 조건에서 비교 대상 컬럼과 표현식의 데이터 타입을 반드시 동일하게 사용해야 한다고 했었다.
JOIN의 조건에서 동일하게 적용된다. 조인 컬럼 간의 비교에서 각 컬럼의 데이터 타입이 일치하지 않으면 인덱스르 효율적으로 사용 불가하다.

 

만약 조인 조건의 양쪽 항의 데이터 타입이 다르다면 두 테이블 모두 풀 테이블 스캔으로 접근한다.
옵티마이저는 드리븐 테이블이 인덱스 레인지 스캔을 사용하지 못하고, 드리븐 테이블의 풀 테이블 스캔이 필요한 것을 알고 조금이라도 빨리 실행되도록 조인 버퍼를 활용한 해시 조인을 사용한다.

 

데이터 타입 불일치가 인덱스 사용에 문제를 일으키는 경우는 아래와 같다.

• CHAR 타입과 INT 타입의 비교와 같이 데이터 타입의 종류가 완전히 다른 경우
• 같은 CHAR 타입이더라도 문자 집합이나 콜레이션이 다른 경우
• 같은 INT 타입이더라도 부호(Sign)의 존재 여부가 다른 경우

콜레이션이란? 
특정 문자 셋에 의해 DB에 저장된 값들을 비교 검색하거나 정렬 등의 작업을 위해 문자들을 서로 비교할때 사용하는 규칙들의 집함
ex) utf8_general_ci, latin1_general_ci

 

데이터 타입 불일치가 문제가 되지 않는 경우도 있다.
CHAR - VARCHAR , INT-BIGINT(SMALLINT), DATE-DATETIME 타입 사이에서는 타입 불일치 문제가 발생하지 않는다.

 

3. OUTER JOIN의 성능과 주의사항

INNER JOIN은 조인 대상 테이블에 모두 존재하는 레코드만 결과 집합으로 반환한다.
해당 INNER JOIN의 특성 때문에 OUTER JOIN으로만 조인을 실행하는 아래와 같은 쿼리도 많이 볼 수 있다.

SELECT *
FROM A a
	LEFT JOIN B b ON b.emp_no=a.emp_no
    	LEFT JOIN C c ON c.dept_no=b.dept_no AND C.dept_name='Dev';

 

해당 쿼리 아래 실행 계획을 보면 제일 먼저 A 테이블을 풀 스캔하면서 dept_emp 테이블과 departments 테이블을 드리븐 테이블로 사용한다는 것을 알 수 있다.

MySQL 옵티마이저는 절대 OUTER JOIN되는 테이블을 드라이빙 테이블로 선택하지 못하기 때문에 풀 스캔이 필요한 A 테이블을 드라이빙 테이블로 선택한다.
그 결과 쿼리의 성능이 떨어지게 된다.

 

만약, 이 쿼리에 INNER JOIN을 사용했다면 다음과 같이 C테이블에서 부서명이 'Dev'인 레코드 1건만 찾아서 조인을 실행하는 아래와 같은 실행 계획을 선택했을 것이다.

이렇듯 INNER JOIN으로 수행해도 되는 것을 OUTER JOIN으로 잘못 풀어냈을 때 옵티마이저가 조인 순서를 변경하면서 수행할 수 있는 최적화의 기회를 잃어버릴 수 있다. 따라서, 꼭 필요한 경우가 아니라면 INENR JOIN을 사용하는 것이 좋다.

OUTER 조인을 하면서 많이 하는 실수

SELECT *
FROM A a
	INNER JOIN B b ON b.emp_no=a.emp_no
WHERE b.dept_no='d001';

위 쿼리처럼 OUTER JOIN되는 테이블에 대한 조건을 WHERE 조건에 명시하는 실수를 하는 경우가 있다.

 

ON 절에 조인 조건은 명시했지만 OUTER JOIN 되는 테이블인 B의 'dept_no='d001'' 조건을 WHERE 절에 배치하는 것은 
MySQL 옵티마이저가 LEFT JOIN 을 아래와 같이 INNER조인으로 변환해서 실행하게 만들어버린다.

SELECT *
FROM A a
	INNER JOIN B b ON b.emp_no=a.emp_no
WHERE b.dept_no='d001';

정상적으로 쿼리를 실행시키고 싶다면 dept_no='d001' 조건을 LEFT JOIN의 ON절으로 옮겨야 한다.

 

여기서도 예외는 있다
예외적으로 OUTER JOIN으로 연결되는 테이블의 컬럼에 대한 조건을 WHERE 절에 사용해야 하는 경우가 있는데,
다음과 같이 ANTI-JOIN 효과를 기대하는 경우가 그러하다.

SELECT *
FROM A a
	LEFT JOIN B b ON b.emp_no=a.emp_no
WHERE b.emp_no IS NULL
LIMIT 10;

위 쿼리는 A의 레코드 중에서 B 레코드에 해당 되지 않는 레코드를 조회하는 쿼리인데, 이 경우에 WHERE 절에 B 테이블의 emp_no 컬럼이 NULL인 레코드들만 조회한다. 이런 형태의 요건이 아우터 테이블의 컬럼이 WHERE 절에 사용될 수 있는 유일한 경우이다.
이 경우를 제외하고는 WHERE 절에 OUTER JOIN 테이블의 컬럼 조건이 들어간다면 INNER JOIN으로 변경된다는 것을 이해하자.

 

4. JOIN과 외래키 (FOREIGN KEY)

DB에 외래키가 생성돼 있어야만 조인할 수 있는지 의문을 품을 수 있다.
결론은 외래키는 조인과 아무런 연관이 없다. 외래킬를 생성하는 주목적은 데이터의 무결성을 보장하기 위해서다.

 

외래키와 연관된 무결성을 참조 무결성이라고 표현한다. 
사원 테이블과 부서 테이블이 있을 때, 사원 테이블에는 사원의 부서 정보를 저장하는 컬럼이 있다.
이때 사원 테이블의 부서 코드는 반드시 부서 테이블에 존재하는 부서 정보만 사용해야 하는데 이게 참조 무결성이다.
참조 무결성이 깨지는 문제를 DBMS 차원에서 막기 위해 외래키를 생성하는 것이다.

 하지만 SQL로 테이블 간의 조인을 수행하는 것은 전혀 무관한 컬럼을 조인 조건으로 사용해도 문법적으로 문제가 되지 않는다.