JOIN 쿼리 튜닝

조인과 인덱스 레인지 스캔

조인이 들어간 쿼리 튜닝을 하기 전에, 인덱스 레인지 스캔으로 레코드를 읽는 작업을 다시 한 번 생각해 보아야 한다.

1. 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 이 과정을 인덱스 탐색이라고 한다.
2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 쭉~ 읽는다. 이 과정을 인덱스 스캔이라 한다.
3. 2번에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

일반적으로 인덱스 풀 스캔이나 테이블 풀 스캔 작업은 인덱스 탐색 과정이 거의 없지만 실제 인덱스나 테이블의 모든 레코드를 읽기 때문에 부하가 높다. 하지만 인덱스 레인지 스캔 작업에서는 가져오는 레코드의 건수가 소량이기 때문에 인덱스 스캔 과정은 부하가 작지만 특정 인덱스 키를 찾는 인덱스 탐색과정이 상대적으로 부하가 높은 편이다.

조인 작업에서 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업을 단 한 번만 수행하고, 드리븐 테이블에서는 인덱스 탐색 작업과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복한다 때문에 옵티마이저는 항상 드라이빙 테이블이 아니라 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립한다.

예를 들어 다음 쿼리를 보자.

SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no = de.emp_no;

employees 테이블과 dept_emp 테이블의 emp_no 칼럼에 인덱스가 어떻게 있냐에 따라 쿼리 수행 절차가 달라진다.

• 두 테이블 모두 index 존재 : 통계 정보에 따라 두 테이블 모두 드라이빙 테이블이 될 수 있다.
• employees에만 index 존재 : 인덱스가 존재하는 테이블을 반복적으로 읽는 것이 효과적이므로 인덱스가 없는 dept_emp 테이블을 드라이빙 테이블로 선택하고, employees 테이블을 드리븐 테이블로 선택한다.
• dept_emp에만 index 존재 : 반대로 dept_emp 테이블을 드리븐 테이블로, employees 테이블을 드라이빙 테이블로 선택한다.
• 모두 인덱스가 없는 경우 : 통계 정보에 따라 두 테이블 모두 드라이빙 테이블이 될 수 있으며, 어느쪽이든 드리븐 테이블을 읽어올때 풀 테이블 스캔이 걸린다. 때문에 조인 버퍼를 사용할 가능성이 높다.

결론적으로, 조인이 수행될 때 조인되는 양쪽 테이블의 칼럼에 모두 인덱스가 없을 때만 드리븐 테이블을 풀 스캔 한다.

JOIN 주의 사항

데이터 타입이 같은지 확인을 잘 해야 한다. 같은 CHAR 타입이더라도 문자집합이나 콜레이션이 다른 경우 문제가 될 수 있다.

가능하다면 DB 모델에 대한 표준화 규칙을 수립하고, 규칙을 기반으로 설계를 진행한다면 이런 문제를 최소화할 수 있을 것이다. 조인이 수행되는 칼럼드리끼리는 데이터 타입을 일치시키기 위해 최종 점검을 하는 것이 좋다.

OUTER JOIN 주의사항

OUTER로 조인되는 테이블의 칼럼에 대한 조건은 모두 ON 절에 명시해야 한다.

LEFT JOIN이 사용된 쿼리는 WHERE 절의 조건 때문에 MySQL 옵티마이저가 LEFT JOIN을 다음 쿼리와 같이 INNER JOIN으로 변환해 버린다.

# As-Is
SELECT * FROM employees e
  LEFT JOIN dept_manager mgr ON mgr.emp_no = e.emp_no
WHERE mgr.dept_no = 'd001';

# To-Be
SELECT * FROM employees e
    INNER JOIN dept_manager mgr ON mgr.emp_no = em.emp_no
WHERE mgr.dept_no = 'd001';

생각해보면 당연한데, LEFT JOIN 절이 걸린 다음 WHERE 조건 절을 체크하기 때문에 dept_manager가 없는 경우는 dept_amanger 테이블의 dept_no 칼럼이 존재하지 않아 체크할 수가 없다. 그래서 mgr.dept_no가 d001인 dept_manager만 있는 경우 조인하는 것이 아니라, 일단 조인하고 dept_no를 체크하기 때문에 그냥 inner join으로 바껴 버린다.

LEFT JOIN을 쓰는 경우 어떤 것을 의도했는지 꼭 확인해야 할 필요가 있다.

ANTI JOIN

두 개의 테이블에서 한쪽 테이블에는 있지만 다른 한쪽 테이블에는 없는 레코드를 검색할때 사용할 수 있다.

# t1에는 있지만 t2에 없는 레코드를 찾을 수 있다.
SELECT t1.id
FROM test1 t1
    LEFT JOIN test2 t2 ON t1.id=t2.id
WHERE t2.id IS NULL;

이 때 test1 : test2 관계가 1:1이거나 M:1일때만 사용할 수 있다.

JOIN과 FK

FK는 조인과 아무런 연관이 없다. FK를 생성하는 주 목적은 데이터의 무결성을 보장하기 위해서이다.

지연된 조인

조인이 실행되기 이전에 GROUP BY, ORDER BY를 처리하는 방식

조인을 사용해서 데이터를 조회하는 쿼리에 GROUP BY 또는 ORDER BY를 사용할 때 각 처리 방법에서 인덱스를 사용한다면 이미 최적으로 처리되고 있을 가능성이 높다. 하지만 그렇지 못한다면 MySQL 서버는 조인이 모든 조인을 실행하고 난 다음 GROUP BY나 ORDER BY를 실행하게 된다.

파생 테이블에 저장돼야 할 레코드의 건수가 적으면 적을수록 지연된 조인의 효과가 커진다. 따라서 쿼리에 GROUP BY나 DISTINCT 등과 LIMIT 절이 함께 사용된 쿼리에서 상당히 효과적이다.