서브쿼리 튜닝

서브쿼리 전반

서브쿼리의 종류

서브쿼리는 두 가지 종류가 있다.

• 상관서브쿼리 : 서브 쿼리가 독립적으로 실행되지 못하고 외부 쿼리 결과를 기다려야 하는 경우
• 독립서브쿼리 : 서브 쿼리가 독립적으로 실행될 수 있는 경우.. 하지만 MySQL에서는 독립 서브쿼리라 하더라도 효율적으로 처리되지 못할 때가 많다.

서브쿼리의 제약 사항

• 서브 쿼리를 IN 연산자와 함께 사용할 때에는 효율적으로 처리되지 못한다.
• IN 연산자 안에서 사용되는 서브 쿼리에는 ORDER BY와 LIMIT을 동시에 사용할 수 없다.

서브쿼리가 사용되는 곳

SELECT 절에 사용된 서브쿼리

SELECT 절에 사용된 서브쿼리는 내부적으로 임시 테이블을 만든다거나 쿼리를 비효율적으로 실행하도록 만들지는 않기 때문에 서브 쿼리가 적절한 인덱스를 사용할 수 있다면 크게 주의할 사항은 없다.

가끔 조인으로 처리해도 되는 쿼리를 SELECT 절의 서브쿼리를 이용해 작성할 때도 있다. 하지만 서브 쿼리로 실행될 때보다 조인으로 처리할 때가 훨씬 빠르기 때문에 가능하다면 조인으로 쿼리를 작성하는 것이 좋다.

WHERE 절에 단순 비교를 위해 사용된 서브쿼리

독립 서브 쿼리일 때, 서브쿼리를 먼저 실행한 후 상수로 변환하고, 그 조건을 범위 제한 조건으로 사용하는 것이 일반적인 RDBMS의 처리 방식이다. 하지만 MySQL은 서브 쿼리를 매번 실행해서 서브 쿼리가 포함된 조건이 참인지 비교한다. (5.1버전 기준) MySQL 5.5에서는 일반적인 RDBMS 방식과 동일하게 서브쿼리를 상수로 변환해 처리하게 된다.

WHERE 절에 IN과 함께 사용된 서브 쿼리

SELECT * FROM dept_emp de
WHERE de.dept_no IN
(SELECT d.dept_no FROM departments d WHERE d.dept_name = 'Finance');

이런 쿼리는 옵티마이저에 의해 아래와 같이 바뀌게 된다.

SELECT * FROM dept_emp de
WHERE EXISTS (
    SELECT 1 FROM departments d WHERE d.dept_name = 'Finance' AND d.dept_no = de.dept_no );

상관 서브 쿼리로 변경되었기 때문에 풀 테이블 스캔이 계속 발생할 수 밖에 없는 것이다.

MySQL 5.0, 5.1 그리고 5.5버전까지 모든 버전에서 IN 비교 + 서브쿼리 작업은 최적화되지 못했다. (최신 버전은 확인이 필요하다) 현재의 MySQL에서 IN 형태의 비교 + 서브쿼리는 다른 형태로 쿼리를 변경해서 사용하는 것이 좋다.

5.6 버전은 갓-jojoldu님의 블로그에서 확인해볼 수 있다.

https://jojoldu.tistory.com/520

MySQL where in (서브쿼리) vs 조인 조회 성능 비교 (5.5 vs 5.6)

 

바깥 쪽 테이블(dept_emp)과 서브 쿼리 테이블(departments)의 관계가 1:1이거나 M:1 인경우

조인으로 풀어 쉽게 성능을 개선할 수 있다.

SELECT de.*
FROM dept_emp de
INNER JOIN departments d ON d.dept_name = 'Finance' AND d.dept_no = de.dept_no;

바깥 쪽 테이블(dept_emp)과 서브 쿼리 테이블(departments)의 관계가 1:M인 경우

GROUP BY를 활용 (중복제거) 하거나 원본 쿼리에서 서브 쿼리를 분리시켜 2개의 쿼리를 실행할 수 있다.

SELECT de.*
FROM dept_emp de
INNER JOIN departments d ON d.dept_name = 'Finance' AND d.dept_no = de.dept_no;
GROUP BY de.dept_no;

WHERE 절에 NOT IN과 사용된 서브 쿼리

LEFT JOIN으로 개선하는 것이 좋다.

SELECT de.*
FROM dept_emp de
LEFT JOIN departments d ON d.dept_name = 'Finance' AND d.dept_no = de.dept_no;
WHERE d.dept_no IS NULL:

FROM 절에 사용된 서브 쿼리

저자가 쿼리를 튜닝할 때는 가장 먼저 FROM 절의 서브 쿼리를 조인 쿼리로 바꾼다. FROM 절에 사용된 서브 쿼리는 항상 임시 테이블을 사용하므로 제대로 최적화되지 못하고 비효율적일 때가 많으며, 더구나 불필요하게 사용된 경우가 많기 때문이다.