JOIN 절에서 INDEX 사용 시 유의할 점

인덱스 레인지 스캔은 인덱스를 탐색하는(Index Seek) 단계와 인덱스를 스캔하는(Index Scan) 과정으로 구분할 수 있다. 탐색 단계는 B-Tree를 통해 특정 인덱스 혹은 범위를 탐색하는 작업이고, 스캔 단계는 리프노드를 순차적으로 읽는 작업이다. 일반적으로 인덱스를 통해 가져오는 레코드는 소량이다. 따라서 스캔 작업 보다는 탐색 작업이 상대적으로 부하가 크다.

 

☁️ 드라이빙 테이블과 드리븐 테이블

두 개 이상의 테이블을 JOIN하는 과정에서 드라이빙 테이블과 드리븐 테이블이 구분된다. 보통 드라이빙 테이블을 읽을 때는 인덱스 탐색 작업을 한 번만 수행한 뒤 스캔작업만 수행하면 되지만 드리븐 테이블은 탐색과 스캔 작업을 드라이빙 테이블에서 읽은 레코드 수만큼 진행한다.따라서 드라이빙 테이블과 드리븐 테이블의 잘 선택해야 효율적인 쿼리를 작성할 수 있다.

 

아래의 쿼리에서 드라이빙 테이블은 a_tb, 드리븐 테이블은 b_tb이다.

SELECT * 
FROM a_tb a
INNER JOIN b_tb b ON a.id = b.id;

혹은

SELECT * 
FROM a_tb a, b_tb b
WHERE a.id = b.id;

각 테이블의 id 값에 인덱스가 걸려있다고 가정했을 때는 어떤 테이블이 드라이빙 테이블이 되든 효과으로 인덱스를 사용할 수 있다. 이는 옵티마이저가 선택한다.

 

a.id에만 인덱스가 걸려있는 경우에는 b_tb를 드라이빙 테이블로 선택할 가능성이 높다. a.id의 레코드 건수만큼 b_tb를 풀 스캔하는 것은 비효율적이기 때문이다. 반대도 동일하다.

 

두 테이블의 id값 모두 인덱스가 걸려있지 않는다면, 두 테이블 모두 풀 스캔으로 처리해야 한다. 이러한 경우 MySQL 8.0.18 이전 버전까지는 블록 네스티드 루프 조인 이후 부터는 해시 조인을 통해 처리한다.

 

WHERE, GROUP BY, ORDER BY에서 인덱스를 사용할 때의 주의할 점인 타입을 일치시켜라 라는 말은 조인에서도 동일하게 적용된다. 옵티마이저는 문자열 → 숫자로 변경하는 작업을 우선적으로 처리한다는 점을 잊지 말자.

 

타입 불일치 문제는 아래의 상황에서 발생한다.

• CHAR 타입과 INT 타입의 비교와 같이 타입의 종류가 완전히 다른 경우
• 같은 CHAR 타입에서 문자 집합이나 콜레이션이 다른 경우
• 같은 INT 타입에서 부호의 존재 여부가 다른 경우 (signed, unsigned)

 

☁️ 지연된 조인 (Delayed Join)

지연된 조인이란 조인이 실행되기 이전에 GROUP BY 혹은 ORDER BY 절을 처리하는 방식을 의미한다. 보통 조인의 결과는 조인하기 전의 결과보다 수가 많기에 조인 후 GROUP BY, ORDER BY를 처리하는 작업보다는 조인 전의 드리븐 테이블에서 이를 처리하는 것이 좋다.

select e.*
from salaries s, employees e
where e.emp_no=s.emp_no
    and s.emp_no between 10001 and 13000
group by s.emp_no
order by sum(s.salary) desc
limit 10;

+----+-------------+-------+------------+-------+-------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys     | key     | key_len | ref                | rows | filtered | Extra                                        |
+----+-------------+-------+------------+-------+-------------------+---------+---------+--------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | e     | NULL       | range | PRIMARY           | PRIMARY | 4       | NULL               | 3000 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | s     | NULL       | ref   | PRIMARY,ix_salary | PRIMARY | 4       | employees.e.emp_no |    9 |   100.00 | NULL                                         |
+----+-------------+-------+------------+-------+-------------------+---------+---------+--------------------+------+----------+----------------------------------------------+

위의 코드는 지연된 조인이 적용되지 않은 조인 쿼리이다. 실행 계획을 보면 e.emp_no를 프라이머리 키를 통해 10001 ~ 13000 레인지 스캔한다. 다음으로 해당 레코드를 기반으로 salaries의 s.emp_no와 비교하여 조인한 뒤, 해당 결과를 기반으로 그룹핑, 정렬을 수행한다.

 

아래는 지연된 조인을 사용한 쿼리이다.

select e.*
from (
    select s.emp_no
    from salaries s
    where s.emp_no between 10001 and 13000
    group by s.emp_no
    order by sum(s.salary) desc
    limit 10
) x, employees e
where e.emp_no=x.emp_no;

+----+-------------+------------+------------+--------+-------------------+---------+---------+----------+-------+----------+----------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys     | key     | key_len | ref      | rows  | filtered | Extra                                        |
+----+-------------+------------+------------+--------+-------------------+---------+---------+----------+-------+----------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL              | NULL    | NULL    | NULL     |    10 |   100.00 | NULL                                         |
|  1 | PRIMARY     | e          | NULL       | eq_ref | PRIMARY           | PRIMARY | 4       | x.emp_no |     1 |   100.00 | NULL                                         |
|  2 | DERIVED     | s          | NULL       | range  | PRIMARY,ix_salary | PRIMARY | 4       | NULL     | 56844 |   100.00 | Using where; Using temporary; Using filesort |
+----+-------------+------------+------------+--------+-------------------+---------+---------+----------+-------+----------+----------------------------------------------+

쿼리를 살펴보면 s 테이블에서 프라이머리 키를 통해 emp_no가 10001 ~ 13000인 레코드를 탐색하고 그룹핑과 정렬을 모두 수행한 뒤 임시 테이블을 만든다. 임시 테이블과 e 테이블과 조인한다. 임시테이블에는 10건의 레코드만 저장되기에 성능에 큰 영향을 끼치지 않는다.

이렇게 지연된 조인과 그렇지 않은 조인의 성능 차이는 로컬에서 아래와 같이 0.049초와 0.022초로 2배 이상의 차이를 보여준다.

 

 

☁️ 결론

드라이빙 테이블과 드리븐 테이블의 인덱스를 판단하여 타입을 일치시켜 인덱스를 효율적으로 사용할 수 있는 쿼리를 작성하자. 또한 지연된 조인을 통해 성능을 향상시킬 수 있다면 적극적으로 활용하자.