본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
이번 포스팅에서는 JOIN이 어떻게 인덱스를 사용하는지에 대해 각 쿼리 패턴별로 자세히 살펴보자.
JOIN의 순서와 인덱스
인덱스 레인지 스캔으로 레코드를 읽는 순서를 정리해보자
1) 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 이 과정을 인덱스 탐색(Index seek)이라고 한다.
2) 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 죽 읽는다. 이 과정을 인덱스 스캔(Index scan)이라고 한다.
3) 2번에서 읽어들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.
인덱스 풀 스캔이나 테이블 풀 스캔은 인덱스 탐색(Index seek)과정이 거의 없지만 실제 인덱스나 테이블의 모든 레코드를 읽기 때문에 부하가 높다. 하지만 인덱스 레인지 스캔 작업에서는 가져오는 레코드의 건수가 소량이기 때문에 인덱스 스캔(Index scan) 과정은 부하가 작지만 특정 인텍스 키를 찾는 인덱스 탐색 과정이 상대적으로 부하가 높은 편이다.
조인 작업에서 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업을 단 한 번만 수행하고, 그 이후부터는 스캔만 실행하면 된다. 하지만 드리븐 테이블에서는 인덱스 탐색/스캔 작업을 드라이빙 테이블에서 읽은 레코드 건수만큼 반복한다. 드라이빙/드리븐 테이블이 1:!로 조인되더라도 드리븐 테이블을 읽는 것이 훨씬 큰 부하를 차지한다. 옵티마이저는 항상 드라이빙 테이블이 아닌 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 수립한다.
다음과 같이 employees 테이블과 dept_emp 테이블을 조인하는 쿼리로 이 내용을 한번 살펴보자.
SELECT *
FROM employees e, dept_emp de
WHERE e.emp_no=de.emp_no;
이 두 테이블의 조인 쿼리에서 employees 테이블의 emp_no 칼럼과 dept_emp 테이블의 emp_no 칼럼에 각각 인덱스가 있을 때와 없을 때 조인 순서가 어떻게 달라지는지 한번 살펴보자.
두 칼럼 모두 각각 인덱스가 있는 경우
어느 테이블을 드라이빙으로 선택하든 드리븐 테이블의 검색 작업을 빠르게 처리할 수 있다. 각 테이블의 통계 정보에 있는 레코드 건수에 따라 employees 가 드라이빙 테이블이 될 수도 있고, dept_emp 테이블이 드라이빙 테이블이 될 수도 있다. 보통의 경우 어느 쪽 테이블이 드라이빙 테이블이 되든 옵티마이저가 선택하는 방법이 최적일 때가 많다.
employees.emp_no에만 인덱스가 있는 경우
dept_emp 테이블이 드리븐 테이블로 선택된다면 employees 테이블의 레코드 건수만큼 dept_emp 테이블을 풀 스캔해야만 "e.emp_no = de.emp_no" 조건에 일치하는 레코드를 찾을 수 있다. 그래서 옵티마이저는 항상 dept_emp 테이블을 드라이빙 테이블로, employees 테이블을 드리븐 테이블로 선택하게 된다.
dept_emp.emp_no 에만 인덱스가 있는 경우
"employees.emp_no에만 인덱스가 있는 경우"와 반대로 처리된다. employees 테이블의 반복된 풀 스캔을 막기 위해 employees 테이블을 드라이빙 테이블로 선택하고 dept_emp 테이블을 드리븐 테이블로 조인을 수행하도록 실행 계획을 수립한다.
두 칼럼 모두 인덱스가 없는 경우
어느 테이블을 드라이빙으로 선택하더라도 드리븐 테이블의 풀 스캔은 발생하기 때문에 옵티마이저가 적절히 드라이빙 테이블을 선택한다. 레코드 건수가 적은 테이블을 드리븐 테이블로 선택하는 것이 훨씬 효율적이다. 드리븐 테이블을 읽을 때 조인 버퍼가 사용되기 때문에 실행계획에 "Using join buffer"가 표시된다.
결국 조인이 수행될때 양쪽 테이블의 칼럼에 모두 인덱스가 없을 때만 드리븐 테이블을 풀스캔한다. 나머지는 드라이빙 테이블은 풀 테이블 스캔을 사용할 수는 있어도 드리븐 테이블을 풀 테이블 스캔으로 접근하는 실행 계획은 옵티마이저가 거의 만들어내지 않는다.
JOIN 칼럼의 데이터 타입
WHERE 절에 사용하는 조건 중에서 비교 대상 칼럼과 표현식의 타입을 반드시 동일한하게 사용해야 하는 이유는 살펴보았다. 하지만 이것은 테이블의 조인을 위한 조인 조건에서도 동일하다. 조인 칼럼 간의 비교에서 각 칼럼의 데이터 타입이 일치하지 않으면 인덱스를 효율적으로 이용할 수 없다.
CREATE TABLE tb_test1 (user_id INT, user_type INT, PRIMARY KEY(user_id));
CREATE TABLE tb_test2 (user_type CHAR(1), type_desc VARCHAR(10), PRIMARY KEY(user_type));
SELECT *
FROM tb_test1 tb1, tb_test2 tb2
WHERE tb1.user_type=tb.2user_type;
tb_test2 테이블의 user_type 칼럼은 프라이머리 키다. 이 쿼리는 최소한 드리븐 테이블은 프라이머리 키를 이용한 인덱스 레인지 스캔을 사용해 조인이 처리될 것으로 예상할 수 있다. 하지만 아래의 표처럼 이 쿼리의 실행 계획은 두 테이블 모두 풀 테이블 스캔으로 접근되고 있다. Extra 컬럼에 "Using join buffer"가 표시된 것으로 봐서 조인 버퍼까지 사용됐다.
이 문제는 비교 조건에서 양쪽 항이 상수이든 테이블의 칼럼이든 관계없이 비교 조건에서 인덱스를 사용하려면 얀쪽 항의 데이터 타입을 일치시켜야 한다는 것은 마찬가지다. 위의 쿼리는 tb_test2 테이블의 user_type 칼럼은 CHAR(1)에서 INT로 변환해서 비교를 수행한다. 그로 인해 인덱스 변형이 발생하여 tb_test2 테이블의 인덱스를 제대로 사용할 수 없게 된다.
옵티마이저는 드리븐 테이블이 인덱스 레인지 스캔을 사용하지 못하고, 드리븐 테이블의 풀 테이블 스캔이 필요한 것을 알고 조금이라도 빨리 실행되도록 조인 버퍼를 사용하게 된 것이다.
인덱스 사용에 영향을 미치는 데이터 타입 불일치 비교 패턴의 대표적인 예는 아래와 같다.
- CHAR 타입과 INT타입의 비교와 같이 데이터 타입의 종류가 완전히 다른 경우
- 같은 CHAR 타입이더라도 문자집합이나 콜레이션이 다른 경우
- 같은 INT 타입이더라도 부호(Sign)가 있는지 여부가 다른 경우
