[RealMySQL] 서브쿼리

상관 서브 쿼리(Correlated subquery)

• 서브쿼리 외부에서 정의된 테이블 칼럼을 참조해서 수행하는 쿼리
• 상관 서브 쿼리는 독립적으로 실행되지 못하고, 항상 외부 쿼리가 실행된 후 서브쿼리가 실행된다.
• 일반적으로 상관 서브 쿼리를 포함하는 비교 조건은 범위 제한 조건이 아니라 체크 조건으로 사용된다

SELECT *
FROM employees e
WHERE EXIST
	( SELECT 1
    FROM dept_emp de
   	WHERE de.emp_no = e.emp_no # 서브쿼리 외부 테이블 참조
    	AND de.from_date BETWEEN '2000-01-01' AND '2011-12-30'
  )

독립 서브 쿼리(Self-Contained subquery)

• 외부 쿼리의 칼럼을 사용하지 않고, 서브 쿼리에서 정의된 칼럼만 참조
• 외부의 쿼리와 상관없이 항상 같은 결과를 반환함으로 외부 쿼리보다 먼저 실행되어 외부 쿼리 검색을 위한 상수로 사용되는 것이 일반적

SELECT *
FROM dept_emp de
WHERE de.emp_no = (
	SELECT e.emp_no
  FROM employees e
  WHERE e.first_name = 'Georgi' AND e.last_name = 'Facello' LIMIT 1
)

서브 쿼리의 제약사항

• FROM절에는 상관 서브 쿼리 형태로 사용할 수 없다. 사용할 경우 Unknown column 에러 발생
• 서브 쿼리를 이용해 하나의 테이블에 대해 읽고 쓰기를 동시에 할 수 없다.

SELCET 절에 사용된 서브 쿼리

• SELECT 절에 사용된 서브 쿼리는 내부적으로 임시 테이블을 만든다거나 쿼리를 비효율적으로 실행하지 않기때문에 적절한 인덱스를 사용할 수 있다면 크게 주의할 사항은 없다.
• 일반적으로 SELECT 절에 서브 쿼리는 항상 칼럼과 레코드가 하나인 결과를 반환해야한다. (스칼라 서브 쿼리)
• 조인으로 처리가 가능하뎌면 조인으로 쿼리를 작성하는 것이 성능 이점이 있다.

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

SELECT *
FROM dept_emp de
WHERE de.emp_no = 
	(
    SELECT e.emp_no
    FROM employees e
    WHERE e.first_name = 'Georgi' AND e.last_name = 'Facello' LIMIT 1
  )

MySQL 5.5 에서 위 쿼리의 실행 계획은 다음과 같다.

첫 번째 라인에서 메인쿼리의 인덱스가 제대로 동작했음을 확인할 수 있다. 이는 두 번째 라인의 서브 쿼리가 먼저 실행되어서 그 결과를 외부 쿼리 비교 조건의 입력으로 전달했음을 의미한다. 그러나 5.1 버전에서는 메인 쿼리는 인덱스를 활용하지 못하고, type 이 ALL 잡혀 풀 테이블 스캔을 하게 된다.

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

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

책에서는 MySQL 5.5 버전을 기준으로 설명하고 있다. 5.5에서는 서브 쿼리 최적화가 거의 되어 있지 않아 위 쿼리 예제에서 서브쿼리의 select_type 이 DEPENDENT SUBQUERY 로 잡히게 된다. 옵티마지어에 의해 IN 쿼리 부분이 EXISTS 형태로 변환되어 아래의 쿼리로 실행된다.

SELECT * 
FROM dept_emp de
WHERE EXISTS
	(SELECT 1 FROM departments d WHERE d.dept_name='Finance' AND d.dept_no=de.dept_no); # 상관 쿼리로 변경됨

서브쿼리가 상관 서브 쿼리로 변경됐기 때문에 외부 쿼리는 풀 테이블 스캔을 사용할 수 밖에 없다.

이를 개선하기 위해서는 외부 테이블의 관계에 따라 개선할 수 있다.

• 메인 테이블과 서브 쿼리 테이블의 관계과 1:1이거나 M:1 인경우
  • 조인으로 풀어서 작성
• 메인 테이블과 서브쿼리 테이블 관계가 1:M인 경우
  • 조인으로 풀 경우, 카타시안 곱으로 인해 최종 결과의 건수가 달라질 수 있다. 때문에 그룹핑 또는 distint를 통해 중복을 제거해야한다.
  • 두개의 쿼리로 풀어서 실행

반면에 MySQL5.6 에서는 서브쿼리에 대한 최적화가 많이 이루어졌는데, 5.6에서는 서브쿼리를 조인으로 풀어서 실행한다.

MySQL5.6 에서 실행계획은 다음과 같다

실행계획의 id가 같으므로, 조인이 이루어졌음을 알 수 있다.

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

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

NOT IN (subsquery) 형태의 쿼리 역시 NOT EXISTS 형태로 변환해서 실행한다.

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

--> 옵티마이저가 변환한 쿼리
SELECT * 
FROM dept_emp de
WHERE NOT EXISTS
	(SELECT 1 FROM departments d WHERE d.dept_name='Finance' AND d.dept_no=de.dept_no); 

여기서 복잡한 문제는 de.dept_no 가 NULL 이 될 수 있다면 위와 같이 NOT EXISTS 형태로 변환할 수 없게 되고 두 가지 중 어떤 경우인지를 비교하는 작업을 수행하게 된다.

• 서브 쿼리가 결과 레코드를 한 건이라도 가진다면 -> NULL IN (레코드를 가지는 결과) => NULL
• 서브 쿼리가 결과 레코드를 한 건도 가지지 않는다면 -> NULL IN (빈 결과) => FALSE

결국 NOT IN (subquery) 형태의 최적화는 왼쪽의 값이 NULL 인지 아닌지에 따라 결정된다. NULL인 경우에는 최적화를 수행하지 못하고, NOT IN 연산자의 오른쪽에 위치한 서브 쿼리가 결과를 한 건이라도 가지는지 판단해야 한다. 이때 서브 쿼리를 실행할 때는 절대 인덱스를 사용할 수 없게 된다. 이는 옵티마이저가 NOT IN (subquery)를 최적화하기 위해 trigcond 라는 선택적인 최적화 방법을 사용하기 때문이다.

즉 서브쿼리가 풀 테이블 스캔을 통해 일치하는 레코드 한 건을 가져오는 방식으로 처리되므로 일치하는 레코드가 희박할수록 성능이 더 느려진다.

옵티마이저가 NULL에 대한 고려 없이 최적화 할 수 있으려면 가급적 칼럼을 NOT NULL로 정의하거나 IS NOT NULL 옵션을 통해 옵티마이저에게 알려주는 방법도 있다

NOT IN (subquery) 은 LEFT JOIN으로 아래와 같이 개선 할 수도 있다.

SELECT de.*
FROM dept_emp de
	LEFT JOIN derpartments d ON d.dept_name='Finace' AND d.dept_no=de.dept_no
WHERE d.dept_no is NULL;

JOIN ~ ON은 조인할 테이블의 레코드를 필터하는 역할을 한다.

위 쿼리에서는 derpartments 테이블에서 dept_name='Finace' 레코드들만 조인 대상이 된다.

FROM 절에 사용된 서브쿼리

• FROM 절에 사용된 서브 쿼리는 항상 임시테이블을 사용하므로 제대로 최적화되지 못하고 비효율적일 때가 많다.
• FROM 절에 사용된 서브 쿼리가 만들어 내는 데이터가 클 경우 메모리가 아닌 디스크에 임시 테이블을 만들게 되고 그로 인해 디스크의 읽고 쓰기 작업이 병목 지점이 될 수 있다.
• SHOW STATUS LIKE 'Created_tmp%'; 을 통해 임시 테이블 생성 횟수를 확인 할 수 있다.