[MySQL]MySQL 벼락치기(13) - 쿼리최적화(4)

강씨아저씨 2018. 12. 2. 17:14

이번 포스팅은 사내에서 MySQL 관련 내용 발표를 위해 Real MySQL(http://wikibook.co.kr/real-mysql/) 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 포스팅에서는 주로 InnoDB 스토리지 엔진을 기준으로 설명할 예정이다.

MySQL 시리즈의 마지막 포스팅이다.

이번에는 쿼리최적화에 대해서 알아볼 예정이고 오늘은 그중에서도 서브쿼리를 사용하려면 알아야 하는 정보에 대해서 알아볼 예정이다.

서브쿼리

쿼리를 작성할 때 서브 쿼리를 사용하면 단위 처리별로 쿼리를독립시킬 수 있다. 조인처럼 여러 테이블을 섞어두는 형태가 아니라서 쿼리의 가독성도 높아지고, 복잡한 쿼리도 손쉽게 작성할 수 있다. 서브쿼리는 외부 쿼리에서 정의된 컬럼을 참조하는지 여부에 따라 상관 서브쿼리와 독립 서브쿼리로 나눌 수 있다.

상관 서브쿼리
서브쿼리 외부에서 정의된 테이블의 컬럼을 참조해서 검색을 수행할 때 상관 서브쿼리라고 한다. 상관 서브쿼리는 독립적으로 실행되지 못하고 항상 외부 쿼리가 실행된 후 그 결과값이 전달돼야만 서브 쿼리가 실행될 수 있다.
```
SELECT *
FROM employees e
WHERE EXISTS
  ( SELECT 1 
    FROM dept_emp de
    WHERE de.emp_no = e.emp_no
      AND de.from_date BETWEEN '2001-01-01' AND '2011-12-30'
  )
```

독립 서브쿼리
외부쿼리의 컬럼을 사용하지 않고 서브쿼리에서 정의된 컬럼만 참조할 때 독립 서브쿼리라고 한다. 독립 서브쿼리는 외부 쿼리와 상관없이 항상 같은 결과를 반환하므로 외부 쿼리보다 먼저 실행되어 외부쿼리의 검색을 상수로 사용하는 것이 일반적이다.
```
SELECT e.dept_no, e.emp_no
FROM dept_emp e
WHERE d.emp_no = ( SELECT e.emp_no
                    FROM employees e
                    WHERE e.first_name = 'Georgi' AND e.last_name = 'Facello' LIMIT 1)
```

서브쿼리 제약사항

서브쿼리에는 다음과 같은 제약사항들이 존재한다.

서브 쿼리를 IN 연산자와 함께 사용할 때에는 효율적으로 처리되지 못한다.
IN 연산자 안에서 사용하는 서브 쿼리에는 LIMIT 를 사용할 수 없다.
FROM 절에 사용하는 서브 쿼리는 상관 서브쿼리 형태로 사용할 수 없다.
아래와 같은 쿼리는 오류 메시지를 반환한다.
```
SELECT * 
FROM departments d,
  ( SELECT * FROM dept_emp de WHERE de.dept_no = d.dept_no) x
WHERE d.dept_no = x.dept_no LIMIT 10
```

SELECT 절에 사용된 서브 쿼리

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

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

WHERE 절에 단순 비교를 이용해 서브 쿼리를 실행할 경우 다음과 같은 형식의 쿼리가 될 것이다.

 ... WHERE z1.code = ( SUBQUERY )

이를 실행계획으로 확인해보면 다음과 같이 적절히 최적화되어 실행된다는 사실을 알 수 있다.

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

다음 예제와 같이 쿼리의 WHERE 절에 IN 연산자를 상수와 함께 사용할 때는 동등 비교와 똑같이 처리되기 때문에 상당히 최적화돼서 실행된다.

SELECT * FROM zipcode WHERE code IN ('135962', '135965', '135967')

하지만 IN의 입력으로 상수가 아니라 서브 쿼리를 사용하면 처리 방식이 달라진다.

다음과 같은 쿼리를 보자.

SELECT * FROM zipcode AS z1 WHERE z1.code IN (SELECT z2.code FROM zipcode2 AS z2 WHERE z2.code = '135962');

MySQL 5.5 까지는 IN 절 내에 서브쿼리가 존재할 경우 IN(subquery) 부분을 EXISTS(subquery) 형태로 변환되어 실행되기 때문에 최적화 되지 못하고 외부 쿼리는 테이블 풀 스캔으로 동작하게 되었다. MySQL5.5 까지는 아래와 같은 쿼리로 변형되어 실행되었다.

SELECT * FROM zipcode AS z1 WHERE EXISTS ( SELECT 1 FROM zipcode2 AS z2 WHERE z2.code = '135962' AND z1.code = z2.code);

MySQL 5.6 이상부터는 IN (SUBQUERY) 를 이용할경우 MATERIALIZED라는 select_type 으로 적절히 최적화되어 실행되게 된다.

(https://dev.mysql.com/doc/refman/5.6/en/subquery-materialization.html)

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

서브 쿼리중에 주의해야하는 쿼리형태는 NOT IN( SUBQUERY ) 이다. 다음과 같은 쿼리를 보자.

SELECT * FROM zipcode AS z1 WHERE z1.code NOT IN (SELECT z2.code FROM zipcode2 AS z2 WHERE z2.code = '135962');

이때의 실행계획을 보면 항상 풀 테이블 스캔으로 처리되고 있다.

그렇다면 NOT IN 쿼리는 어떻게 해야 개선할 수 있을까?

책에서는 "NOT IN 쿼리는 LEFT OUTER JOIN 을 이용해서 ANTI-JOIN 을 이용하면 효율적인 쿼리로 개선할 수 있다" 고 한다.
(하지만 테스트를 해본 결과 큰 차이를 확인하지 못했다..)

책에서는 NOT IN(subquery) 형태의 쿼리를 조인으로 풀어서 작성하는 방식은 처리 대상의 레코드 건수가 많아질수록 서브쿼리보다 더 빠르게 처리된다고 한다. 하지만 반드시 직접 수행해서 쿼리의 성능을 비교한 후에 적용할 것을 권장한다.

오늘은 여기까지~

누군가에게 도움이 되었길 바라면서 오늘의 포스팅 끝~