본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
지연된 조인(Delayed Join)
조인을 사용하는 쿼리에서 GROUP BY 또는 ORDER BY를 사용할 때 인덱스를 사용한다면 이미 최적으로 처리되고 있을 가능성이 높다. 하지만 그러지 못하다면 MySQL 서버는 우선 모든 조인을 실행하고 난 다음 GROUP BY나 ORDER BY를 처리할 것이다. 조인은 대체적으로 실행되면 될수록 결과 레코드 건수가 늘어난다. 그래서 조인의 결과를 GROUP BY 하거나 ORDER BY 하면 조인을 실행하기 전의 레코드를 GROUP BY나 ORDER BY를 수행하는 것보다 많은 레코드를 처리해야 한다.
지연된 조인이란 조인이 실행되기 이전에 GROUP BY나 ORDER BY를 처리하는 방식을 의미한다. 주로 지연된 조인은 LIMIT가 같이 사용된 쿼리에서 더 큰 효과를 얻을 수 있다.
인덱스를 사용하지 못하는 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;
위 쿼리의 실행계획은 다음과 같다.
1) employees 테이블을 드라이빙으로 선택해서 emp_no BETWEEN 10001 AND 13000 조건을 만족하는 레코드 2999건을 읽는다.
2) salaries 테이블과 조인하고, 조인을 수행한 횟수는 11,996번(2999 *4) 정도 발생한다.
3) 조인 결과 11,996건 레코드를 임시 테이블에 저장하고 GROUP BY 처리를 통해 3000건으로 줄인다.
4) ORDER BY 처리해서 상위 10건만 최종적으로 가져온다.
이제 지연된 조인으로 변경한 쿼리를 살펴보자.
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;
지연된 조인으로 변경한 쿼리 실행 계획은 다음과 같다.
1) FROM 절에 서브 쿼리가 사용되어 서브 쿼리 결과는 파생 테이블(세번째줄 DERIVED)로 처리됐다. 실행계획에서 FROM의 서브쿼리를 위해 57,790 레코드를 읽어야 한다고 나왔지만 28,606 건만 읽으면 되는 쿼리다.
2) salaries 테이블에서 28,606건의 레코드를 읽어 임시 테이블에 저장하고, GROUP BY 처리를 통해 3,000건으로 줄였다.
3) ORDER BY 처리해 상위 10건만 테이블<derived2>에 저장한다.
4) 최종적으로 임시 테이블의 10건을 읽어서 employees 테이블가 조인을 10번만 수행해서 결과를 반환한다.
지연된 조인은 임시 테이블<derived2>을 한 번 더 사용하기 때문에 느리다고 예상할수 있지만 임시 테이블에 저장할 레코드가 단 10건밖에 되지 않아 메모리를 이용해 빠르게 처리된다. 또한 지연된 조인으로 변경된 쿼리의 조인 횟수가 훨씬 적다.
웹 서비스에서 자주 사용되는 페이징 쿼리에서 지연된 조인을 적용하는 방법이다.
SELECT *
FROM dept_emp de, employees e
WHERE de.dept_no = 'd001' AND e.emp_no = de.emp_no
LIMIT 10;
위 쿼리는 마케팅 부서 (부서코드 d001)에서 일했떤 모든 사원의 정보를 조회하는 쿼리인데, 결과를 10건씩 잘라서 가져오기(페이징 처리) 위해서 LIMIT 10 조건이 같이 사용되었다. 위 쿼리에서 LIMIT10은 아무 문제가 없지만 두번째 이후 페이지에서는 아래와 같이 쿼리가 바뀌어 실행될 것이다.
SELECT *
FROM dept_emp de, employees e
WHERE de.dept_no = 'd001' AND e.emp_no = de.emp_no
LIMIT 100, 10;
이 쿼리는 dept_emp 테이블이 드라이빙 테이블이 되고, employees 테이블이 드리븐 테이블이 되어 조인이 실행될 것이다. 우선 dept_emp 테이블에서 de.dept_no='d001'인 레코드를 한 건씩 읽으면서 employees 테이블과 조인하면서 LIMIT 100, 10 조건이 만족될 때까지 조인을 수행하게 된다. 즉 dept_emp 테이블에서 110건을 읽어서 110번 employees 테이블고 자왼하게 되는 것이다.
최종적으로 사용자가 필요로 하는 데이터는 10건이므로 조인을 해서 가져왔던 앞쪽 100건의 데이터는 불필요하게 읽은 것이 된다. 이 쿼리를 지연된 조인으로 처리하면 필요한 10건만 employees테이블과 조인하게 만들수 있다.
우선 dept_emp 테이블에서 먼저 꼭 필요한 10개의 레코드만 조회하는 서브 쿼리(파생 테이블)를 만들고, 그 결과를 employees 테이블을 조인하면 된다.
SELECT *
FROM ( SELECT * FROM dept_emp WHERE dept_no='d001' LIMIT 100, 10 ) de,
employees e
WHERE e.emp_no = de.emp_no;
dept_emp 테이블에서 100번째부터 10개의 레코드만 가져오는 서브 쿼리로 FROM 절의 dept_emp 테이블을 대체했다. 이 서브 쿼리의 결과가 저장도니 임시 테이블의 레코드 10건과 employees 테이블을 조인했다. 즉, 꼭 필요한 레코드만 조인을 수행한 것이다. 이처럼 쿼리를 변경해 불필요한 조인 10번을 없앤 것이다.
일반적으로 지연된 조인으로 쿼리를 개선했을 때 FROM 절의 서브 쿼리 결과가 저장되는 임시 테이블이 드라이빙 테이블이 되어 나머지 테이블과 조인을 수행한다. 임시 테이블에 저장되는 레코드 건수가 작업량에 커다란 영향을 미치게 된다. 그래서 파생 테이블에 저장돼야 할 레코드의 건수가 적으면 적을수록 지연된 조인의 효과가 커진다. 따라서 쿼리에 GROUP BY, DISTINCT, LIMIT 절이 함께 사용된 쿼리에서 상당히 효과적이다.
하지만 모든 쿼리를 지연된 조인 형태로 개선할 수 있는 것은 아니다. OUTER JOIN과 INNER JOIN에 대해 다음과 같은 조건이 갖춰져야만 지연된 쿼리를 변경해서 사용할 수 있다.
- LEFT (OUTER) JOIN인 경우 드라이빙 테이블과 드리븐 테이블은 1:1 또는 M:1 관계여야 한다.
- INNER JOIN인 경우 드라이빙 테이블과 드리븐 테이블은 1:! 또는 M:1 관계임과 동시에 드라이빙 테이블에 있는 레코드는 드리븐 테이블에 모두 존재해야 한다.
