MySQL - query-optimization(4)¶
이번 포스팅은 사내 MySQL 관련 내용 발표를 위해 Real MySQL 서적을 기반으로 학습하고 이해한 내용을 정리하는 포스팅이다. 주로 InnoDB 스토리지 엔진을 기준으로 설명하며, 이번 글은 MySQL 쿼리 최적화 시리즈의 마지막 편이다.
이번에는 쿼리 최적화 중에서도 서브쿼리(Subquery)를 작성할 때 성능을 위해 반드시 알아두어야 할 최적화 원칙과 주의점을 다룬다.
1. 서브쿼리의 종류: 상관 서브쿼리 vs 독립 서브쿼리¶
쿼리를 작성할 때 서브쿼리를 사용하면 복잡한 로직을 단위 처리별로 독립시킬 수 있다. 조인처럼 여러 테이블을 섞어두지 않아 쿼리의 가독성이 높아지고 관리가 용이해진다.
서브쿼리는 외부 쿼리(Outer Query)의 컬럼을 참조하는지 여부에 따라 크게 두 가지로 나뉜다.
1.1 상관 서브쿼리 (Correlated Subquery)¶
서브쿼리 외부에서 정의된 테이블의 컬럼을 참조하여 검색을 수행하는 서브쿼리다.
상관 서브쿼리는 단독으로 실행될 수 없으며, 항상 외부 쿼리가 먼저 실행되어 가져온 레코드의 값이 전달되어야만 서브쿼리가 실행된다.
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'
);
1.2 독립 서브쿼리 (Non-correlated Subquery)¶
외부 쿼리의 컬럼을 전혀 사용하지 않고, 서브쿼리 자체에서 정의된 컬럼만 참조하는 서브쿼리다.
외부 쿼리와 상관없이 항상 고정된 결과를 반환하므로, 옵티마이저에 의해 외부 쿼리보다 먼저 한 번만 실행되어 상수로 캐싱/대체된 후 검색에 사용되는 것이 일반적이다.
SELECT e.dept_no, e.emp_no
FROM dept_emp e
WHERE e.emp_no = (
SELECT emp_no
FROM employees
WHERE first_name = 'Georgi' AND last_name = 'Facello'
LIMIT 1
);
2. 서브쿼리의 주요 제약사항¶
MySQL 옵티마이저의 특성상 서브쿼리 작성 시 다음 제약사항들을 반드시 염두에 두어야 한다:
IN (SUBQUERY)의 비효율: 서브쿼리를IN연산자와 함께 사용할 때는 버전 및 형태에 따라 비효율적으로 풀 스캔이 발생할 수 있다.IN서브쿼리 내부LIMIT불가:IN연산자 내부 서브쿼리에서는LIMIT구문을 지원하지 않는다.FROM절 서브쿼리의 상관 서브쿼리 불가:FROM절에 위치하는 인라인 뷰(파생 테이블)는 외부 테이블의 컬럼을 참조하는 상관 서브쿼리 형태로 작성할 수 없다.
-- ❌ 에러 발생: FROM 절 파생 테이블에서 외부 d.dept_no 참조 불가
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;
3. 위치별 서브쿼리 처리와 최적화¶
3.1 SELECT 절에 사용된 서브쿼리¶
SELECT 절에 사용되는 스칼라 서브쿼리는 내부적으로 임시 테이블을 만들지 않는다. 서브쿼리가 적절히 인덱스를 탈 수 있다면 특별히 큰 성능 저하 없이 효율적으로 동작한다.
3.2 WHERE 절에 단순 비교로 사용된 서브쿼리¶
단순 비교 연산자(=, >, < 등)와 함께 사용되는 서브쿼리는 다음과 같은 형태다:
SELECT * FROM zipcode z1 WHERE z1.code = (SELECT ...);
실행 계획을 확인해 보면, 서브쿼리가 먼저 상수로 깔끔하게 계산되어 외부 쿼리가 인덱스를 타고 적절히 최적화되어 실행된다.
3.3 WHERE 절에 IN과 함께 사용된 서브쿼리 (MySQL 5.5 vs 5.6+)¶
IN ('A', 'B', 'C')처럼 상수 목록을 사용할 때는 동등 비교(=) 목록으로 변환되므로 매우 빠르게 실행된다.
하지만 IN (SELECT ...) 형태의 서브쿼리를 사용하면 버전에 따라 동작 방식이 완전히 달라진다.
SELECT *
FROM zipcode AS z1
WHERE z1.code IN (
SELECT z2.code
FROM zipcode2 AS z2
WHERE z2.code = '135962'
);
① MySQL 5.5 이전: 비효율적인 EXISTS 변환¶
MySQL 5.5까지는 IN (subquery)를 내부적으로 EXISTS (subquery) 상관 서브쿼리 형태로 변환하여 실행했다:
SELECT *
FROM zipcode AS z1
WHERE EXISTS (
SELECT 1
FROM zipcode2 AS z2
WHERE z2.code = '135962' AND z1.code = z2.code
);
- 결과적으로 외부 쿼리가 인덱스를 사용하지 못하고 풀 테이블 스캔(Full Table Scan)을 수행하여 심각한 성능 저하가 발생했다.
② MySQL 5.6 이후: Subquery Materialization (구체화) 최적화¶
MySQL 5.6부터는 MATERIALIZED라는 최적화 방식이 도입되었다. 서브쿼리의 결과를 메모리 임시 테이블(임시 인덱스 포함)에 먼저 구체화해 두고, 외부 테이블과 조인하는 방식으로 동작하여 인덱스를 정상적으로 활용한다.
4. 주의해야 할 NOT IN(SUBQUERY)와 ANTI-JOIN 개선¶
서브쿼리 패턴 중 가장 주의해야 할 형태는 NOT IN (SUBQUERY)이다.
SELECT *
FROM zipcode AS z1
WHERE z1.code NOT IN (
SELECT z2.code
FROM zipcode2 AS z2
WHERE z2.code = '135962'
);
- 실행 계획을 확인해 보면
z1테이블이 항상 풀 테이블 스캔으로 처리된다. NOT IN은NULL값의 존재 가능성으로 인해 옵티마이저가 인덱스를 활용한 최적화를 적용하기 매우 까다롭기 때문이다.
튜닝 해결책: LEFT OUTER JOIN을 활용한 ANTI-JOIN¶
NOT IN (subquery) 쿼리는 LEFT OUTER JOIN과 IS NULL을 결합한 ANTI-JOIN 기법으로 재작성하여 최적화할 수 있다:
EXPLAIN SELECT *
FROM zipcode AS z1
LEFT JOIN zipcode2 AS z2
ON z1.code = z2.code AND z2.code = '135962'
WHERE z2.code IS NULL;
- 원리: 좌측 테이블 전체를 읽으면서 우측 테이블과 조인한 뒤, 우측 테이블에 매칭되지 않은(
IS NULL) 레코드만 남기는 방식이다. - 특히 처리 대상 레코드 건수가 많아질수록 서브쿼리보다 조인 기반의 ANTI-JOIN이 훨씬 더 압도적인 속도를 낸다.
💡 실무 팁: 데이터 규모와 인덱스 구성에 따라 성능 차이가 다를 수 있으므로, 반드시 운영 환경과 유사한 대용량 데이터에서
EXPLAIN실행 계획과 처리 시간을 직접 비교 검증한 후 적용하는 것이 바람직하다.
마치며¶
총 4편에 걸쳐 MySQL 쿼리 최적화의 핵심 원리(타입 일치, NOW vs SYSDATE, GROUP BY/ORDER BY 복합 인덱스, JOIN 및 서브쿼리 튜닝)를 정리했다.
- 서브쿼리는 필요한 경우에 유용하지만, 조인으로 대체 가능하거나
IN/NOT IN연산자와 결합될 때는 항상 실행 계획을 점검해야 한다. - MySQL 버전별 옵티마이저 발전(Materialization 등)을 이해하고, 상황에 맞는 ANTI-JOIN 및 지연된 조인 기법을 적재적소에 활용하자.