Real MySQL - BETWEEN문 튜닝하는 방법! (with MySQL Server 8.0)

BETWEEN

BETWEEN은 특정 범위를 스캔할 때 주로 사용한다. 개인적으로 IN절에서 여러 개의 값을 전부 명시하는 것보다 BETWEEN처럼 범위로 한 번에 당겨오는 것이 성능상 좋을 것이라고 생각했었다.

 

정말 그런지 살펴보자.

BETWEEN vs IN 절

dept_emp 테이블에 (dept_no, emp_no)로 구성된 PK가 있다고 가정하자. 아래의 SQL 문을 보자.

SELECT * FROM dept_emp WHERE dept_no BETWEEN 'd003' AND 'd005' AND emp_no=10001;

문제는 BETWEEN은 범위 안에 있는 모든 레코드를 읽는다는 점이다. 해당 조회는 먼저 BETWEEN으로 모든 레코드를 읽은 후에 emp_no로 조건을 비교한다. 즉, 많은 데이터가 메모리에 올라가게 된다.

 

가령, 아래의 두 쿼리를 비교해보자.

EXPLAIN SELECT * FROM dept_emp WHERE dept_no BETWEEN 'd003' AND 'd005' AND emp_no=10001;

EXPLAIN SELECT * FROM dept_emp WHERE dept_no IN ('d003', 'd004', 'd005') AND emp_no=10001;

BETWEEN을 사용한 쿼리의 결과(실행 계획)는 아래와 같다.

id	table	type	key	key_len	rows	Extra
1	dept_emp	range	PRIMARY	20	165571	Using where

반면, IN을 사용한 쿼리의 결과는 아래와 같다.

id	table	type	key	key_len	rows	Extra
1	dept_emp	range	PRIMARY	20	3	Using where

조회를 한 rows 수가 많이 차이가 나는 것을 알 수 있다. 이는 BETWEEN 이후에 나오는 조건이 조회 범위를 줄이는 역할을 하지 못하기 때문이다.

세미 조인 최적화

MySQL 8.0 부터는 IN (subquery) 형태로 작성하면 옵티마이저가 세미 조인 최적화를 이용해 더 빠른 쿼리로 변환해서 실행할 수 있다.

SELECT * FROM dept_emp
WHERE dept_no IN (
    SELECT dept_no
    FROM departments
    WHERE dept_no BETWEEN 'd003' AND d'005')
AND emp_no = 10001;

MySQL 8.0 부터는 세미 조인 최적화를 통해서 JOIN 쿼리로 아래와 같이 재작성해서 쿼리를 최적화한다.

SELECT *
FROM departments d
INNER JOIN dept_emp de
    ON d.dept_no = de.dept_no AND de.emp_no=10001
WHERE d.dept_no BETWEEN 'd003' AND 'd005';