우선 JOIN Query의 특성과 최적화에 대해 알아보자.
1. JOIN 쿼리의 순서와 인덱스
Index Range Scan은 Index를 탐색하는 단계(Index Seek)와 Index를 스캔하는 과정(Index Scan)으로 구분할 수 있다.
Index를 이용해서 쿼리하는 작업에서는 레코드의 건수가 소량이기 때문에 Index Scan 작업은 부하가 적다.
특정 Index 키를 찾는 Index Seek 작업은 Index Scan에 비해 부하가 크다.
JOIN 작업에서 Driving Table을 읽을 때, Index Seek 작업은 한 번만 수행하고, 그 이후에는 Index Scan만 실행하면 된다.
하지만 Driven Table에서는 Index Seek과 Index Scan을 Driving Table에서 읽은 레코드 건수만큼 반복한다.
Driving Table과 Driven Table이 1:1로 조인되더라도 Driven Table을 읽는 작업이 부하가 더 크다.
그래서 Optimizer는 항상 Driven Table을 최적으로 읽을 수 있도록 실행 계획을 수립한다.
다음과 같이 employees Table과 dept_emp Table을 JOIN 하는 쿼리를 살펴보자.
SELECT * FROM employess e, dept_emp de
WHERE e.emp_no = de.emp_no;employees 테이블의 emp_no 컬럼과 dept_emp 테이블의 emp_no 컬럼에 각각 Index가 있을 때와 없을 때,
JOIN 순서가 달라지는 양상을 한번 살펴보자.
-
두 컬럼 모두 Index가 있는 경우 : 어떤 테이블의 선택하든, Index를 이용해 Driven Table의 검색 작업을 빠르게 처리할 수 있다. Optimizer는 통계 정보를 이용해 최적의 Driving Table을 선택하게 된다.
-
employees.emp_no에만 Index가 있는 경우 : dept_emp 테이블이 Driven Table로 선택된다면, employees 테이블의 레코드 건수만큼 dept_emp 테이블을 Full Scan 해야만 "e.emp_no = de.emp_no" 조건에 일치하는 레코드를 찾을 수 있다.
그래서 Optimizer는 항상 dept_emp 테이블을 Driving Table로 선택하고, employees 테이블을 Driven Table로 선택한다.
이 때, "e.emp_no=10001"과 같이 employees 테이블을 효율적으로 접근할 수 있다고 해도, employees 테이블을 Driving Table로 선택하지 않을 가능성이 높다. -
dept_emp.emp_no에만 Index가 있는 경우: 이 경우, Optimizer가 Driving Table을 emp로, Driven Table을 dept_emp로 선택하여 실행 계획을 수립한다.
-
두 컬럼 모두 Index가 없는 경우 : 어떤 테이블을 Driving Table로 선택하더라도 Driven Table을 풀 스캔 해야 한다.
레코드 건수가 적은 테이블을 드라이빙 테이블로 선택하는 것이 효율적이다. 이렇게 조인 조건을 빠르게 처리할 인덱스가 없는 경우, MySQL 8.0.18 이전 버전까지는 블록 네스티드 루프 조인을 사용했다. 하지만 MySQL 8.0.19 버전부터는 블록 네스티드 루프 조인이 없어지고, 해시 조인이 도입되어 해시 조인으로 처리된다.
2. JOIN 컬럼의 데이터 타입
JOIN 컬럼 간의 비교에서 각 컬럼의 데이터 타입이 일치하지 않으면, 인덱스를 효율적으로 이용할 수 없다.
CREATE TABLE test1 (user_id INT, user_type INT, PRIMARY KEY(user_id));
CREATE TABLE test2 (user_type CHAR(1), type_desc VARCHAR(10), PRIMARY KEY(user_type));
EXPLAIN SELECT * FROM test1 t1, test2 t2
WHERE t1.user_type = t2.user_type;
# output
test2 테이블의 user_type 컬럼은 Primary Key이다.
SELECT 쿼리는 Primary Key를 이용한 Index Range Scan을 사용해서, JOIN이 처리될 것으로 예상할 수 있다.
하지만 이 쿼리는 두 테이블을 모두 풀 테이블 스캔으로 처리된다.
게다가 Driven Table의 Extra 컬럼에 "Using join buffer (hash join)"이 표시된 것으로 봐서, 조인 버퍼를 이용한 해시 조인이 실행된 것을 알 수 있다.
이 쿼리에서 test2 테이블의 user_type 컬럼을 CHAR(1)에서 INT로 변환해서 비교를 수행한다.
그로 인해 인덱스의 변형이 필요하므로, test2 테이블의 인덱스를 사용할 수 없게 된 것이다.
인덱스를 사용할 수 없으니, Driven Table을 풀 테이블 스캔해야 하지만 이를 최대한 빠르게 실행하기 위해 조인 버퍼를 이용한 해시 조인을 사용한다.
인덱스 사용에 영향을 미치는 데이터 타입 불일치는 CHAR 타입과 VARCHAR 타입, INT 타입과 BIGINT 타입, DATE 타입과 DATETIME 타입 사이에서는 발생하지 않는다.
주로 다음과 같은 데이터 타입에서 문제가 발생한다.
- CHAR 타입과 INT 타입의 비교와 같이 데이터 타입의 종류가 완전히 다른 경우
- 같은 CHAR 타입이더라도 문자 집합이나 콜레이션이 다른 경우
- 같은 INT 타입이더라도 부호의 존재 여부가 다른 경우
