[MySQL] 지연된 조인(Delayed Join)

조인을 사용하는 쿼리에서 GROUP BY 또는 ORDER BY를 사용할 때 쿼리 성능을 높이는 방법 중 하나로 지연된 조인(Delayed Join)을 고려해볼 수 있다.

지연된 조인은 드라이빙 테이블만을 이용하여 임시 테이블을 먼저 만들고 임시 테이블을 드리븐 테이블과 조인하는 방식이다. 

• 드라이빙 테이블: 조인에서 제일 먼저 읽는 테이블
• 드리븐 테이블: 조인되는 테이블에서 드라이빙 테이블이 아닌 테이블

지연된 조인은 드라이빙 테이블의 레코드 건수를 줄이는 것이 핵심이다. 따라서 임시 테이블이 원래 테이블보다 레코드 건수가 줄어드는 연산이 쿼리에 포함된 경우 효과적이다. 레코드 건수가 줄어드는 연산의 예시로 GROUP BY, LIMIT, DISTINCT가 있다.

salaries 테이블과 employees 테이블을 조인할 때 GROUP BY, LIMIT, ORDER BY를 사용하는 쿼리를 예시로 살펴보자.

먼저 지연된 조인을 사용하지 않은 쿼리이다. salaries 테이블과 employees 테이블을 emp_no 칼럼으로 조인하고, salaries 테이블의 emp_no에 따라 GROUP BY를 수행하며, 각 emp_no의 salary 합계로 정렬하여 상위 10개 레코드를 조회한다. 

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;

위 쿼리의 실행 계획을 살펴보면 employees 테이블을 드라이빙 테이블로 선택하고 인덱스 레인지 스캔하여 e.emp_no between 10001 and 13000 조건을 만족하는 레코드 3000건을 읽고, 3000건의 레코드에 대해 각각 salaries 테이블을 탐색하여 조인 연산을 수행한다. 조인 결과로 28606건의 레코드를 가진 임시 테이블이 만들어지고 이 임시 테이블에 GROUP BY 처리를 통해 3000건으로 줄인 후 ORDER BY 처리를 통해 상위 10건을 최종적으로 반환한다.

| -> Limit: 10 row(s)  (actual time=59.1..59.1 rows=10 loops=1)
    -> Sort: sum(s.salary) DESC, limit input to 10 row(s) per chunk  (actual time=59.1..59.1 rows=10 loops=1)
        -> Table scan on <temporary>  (actual time=57.9..58.6 rows=3000 loops=1)
            -> Aggregate using temporary table  (actual time=57.9..57.9 rows=3000 loops=1)
                -> Nested loop inner join  (cost=4194 rows=28332) (actual time=0.086..20.5 rows=28606 loops=1)
                    -> Filter: (e.emp_no between 10001 and 13000)  (cost=604 rows=3000) (actual time=0.0605..2.68 rows=3000 loops=1)
                        -> Index range scan on e using PRIMARY over (10001 <= emp_no <= 13000)  (cost=604 rows=3000) (actual time=0.0555..2.25 rows=3000 loops=1)
                    -> Index lookup on s using PRIMARY (emp_no=e.emp_no)  (cost=0.253 rows=9.44) (actual time=0.0029..0.00493 rows=9.54 loops=3000)
 |

쿼리 실행시 0.07sec 소요되었다.

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  11486 | 1963-03-05 | Itzchak    | Ramaiah   | M      | 1985-05-31 |
|  12199 | 1962-11-04 | Huan       | Poupard   | F      | 1985-06-22 |
|  11402 | 1957-02-09 | Freyja     | Brizzi    | M      | 1985-03-10 |
|  10897 | 1958-05-27 | Arno       | Kumaresan | F      | 1986-04-09 |
|  12803 | 1952-05-12 | Gad        | Boissier  | M      | 1985-03-16 |
|  12703 | 1961-01-06 | Luigi      | Brookner  | F      | 1985-11-22 |
|  11537 | 1955-06-29 | DAIDA      | Pehl      | M      | 1986-07-06 |
|  11857 | 1955-12-03 | Divier     | Farrag    | F      | 1986-08-27 |
|  10548 | 1952-06-12 | Ramalingam | Gunderson | M      | 1986-05-04 |
|  12411 | 1952-05-31 | Shao       | Ibel      | M      | 1986-08-17 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.07 sec)

다음으로 지연된 조인을 사용한 쿼리를 살펴보자.

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;

salaries 테이블에서 가능한 모든 처리(WHERE 조건, GROUP BY, ORDER BY, LIMIT)를 수행하여 임시 테이블에 저장하고 임시 테이블의 결과를 employees 테이블과 조인한다. 즉, 모든 처리를 salaries 테이블에서 먼저 수행하고 최종 10건만 employees 테이블과 조인한다.

| -> Nested loop inner join  (cost=12.4 rows=0) (actual time=19.5..19.6 rows=10 loops=1)
    -> Table scan on x  (cost=2.5..2.5 rows=0) (actual time=19.4..19.4 rows=10 loops=1)
        -> Materialize  (cost=0..0 rows=0) (actual time=19.4..19.4 rows=10 loops=1)
            -> Limit: 10 row(s)  (actual time=19.4..19.4 rows=10 loops=1)
                -> Sort: sum(s.salary) DESC, limit input to 10 row(s) per chunk  (actual time=19.4..19.4 rows=10 loops=1)
                    -> Stream results  (cost=17067 rows=56844) (actual time=0.134..18.6 rows=3000 loops=1)
                        -> Group aggregate: sum(s.salary)  (cost=17067 rows=56844) (actual time=0.129..17.9 rows=3000 loops=1)
                            -> Filter: (s.emp_no between 10001 and 13000)  (cost=11383 rows=56844) (actual time=0.0915..14.2 rows=28606 loops=1)
                                -> Index range scan on s using PRIMARY over (10001 <= emp_no <= 13000)  (cost=11383 rows=56844) (actual time=0.085..10.6 rows=28606 loops=1)
    -> Single-row index lookup on e using PRIMARY (emp_no=x.emp_no)  (cost=1 rows=1) (actual time=0.0173..0.0174 rows=1 loops=10)
 |

쿼리 실행 결과, 앞서 지연된 조인을 사용하지 않은 쿼리와 같은 결과를 반환하지만 쿼리 속도는 더 빠른 것을 확인할 수 있다.

+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  11486 | 1963-03-05 | Itzchak    | Ramaiah   | M      | 1985-05-31 |
|  12199 | 1962-11-04 | Huan       | Poupard   | F      | 1985-06-22 |
|  11402 | 1957-02-09 | Freyja     | Brizzi    | M      | 1985-03-10 |
|  10897 | 1958-05-27 | Arno       | Kumaresan | F      | 1986-04-09 |
|  12803 | 1952-05-12 | Gad        | Boissier  | M      | 1985-03-16 |
|  12703 | 1961-01-06 | Luigi      | Brookner  | F      | 1985-11-22 |
|  11537 | 1955-06-29 | DAIDA      | Pehl      | M      | 1986-07-06 |
|  11857 | 1955-12-03 | Divier     | Farrag    | F      | 1986-08-27 |
|  10548 | 1952-06-12 | Ramalingam | Gunderson | M      | 1986-05-04 |
|  12411 | 1952-05-31 | Shao       | Ibel      | M      | 1986-08-17 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.03 sec)

마무리하면,

지연된 조인은 GROUP BY, LIMIT처럼 원래 테이블보다 레코드 건수가 줄어드는 연산이 포함된 쿼리를 실행할 때 레코드 건수를 줄인 임시 테이블을 먼저 만들어 드라이빙 테이블로 사용함으로써 조인 횟수를 줄여 쿼리 속도를 개선할 수 있다.

또한 지연된 조인은 GROUP BY나 ORDER BY 처리가 필요한 레코드의 전체 크기를 줄이는 역할도 할 수 있다. 개선되기 전 쿼리는 salaries 테이블과 employees 테이블의 모든 칼럼을 임시 테이블에 저장하고 GROUP BY를 하지만, 지연된 조인으로 개선된 쿼리는 salaries 테이블의 칼럼만 임시 테이블에 저장하고 GROUP BY를 수행하면 되기 때문에 원래 쿼리보다 GROUP BY나 ORDER BY용 버퍼를 더 적게 필요로 한다.