전체 아티클 목록으로
Database • 2026-11-21 • 약 5분 분량

MySQL - query-optimization(4)

상관/독립 서브쿼리의 개념과 제약사항, WHERE 절에서의 서브쿼리 처리, MySQL 5.5 vs 5.6 Materialization 최적화 및 NOT IN의 ANTI-JOIN 튜닝 완벽 정리.

#MySQL #Query Optimization #InnoDB #Subquery #Index
[광고 영역] AdSense Top Unit Slot

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 옵티마이저의 특성상 서브쿼리 작성 시 다음 제약사항들을 반드시 염두에 두어야 한다:

  1. IN (SUBQUERY)의 비효율: 서브쿼리를 IN 연산자와 함께 사용할 때는 버전 및 형태에 따라 비효율적으로 풀 스캔이 발생할 수 있다.
  2. IN 서브쿼리 내부 LIMIT 불가: IN 연산자 내부 서브쿼리에서는 LIMIT 구문을 지원하지 않는다.
  3. 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 ...);

WHERE 절 단순 비교 서브쿼리 실행계획

실행 계획을 확인해 보면, 서브쿼리가 먼저 상수로 깔끔하게 계산되어 외부 쿼리가 인덱스를 타고 적절히 최적화되어 실행된다.


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
);

MySQL 5.5 IN 서브쿼리 실행계획 (DEPENDENT SUBQUERY)

  • 결과적으로 외부 쿼리가 인덱스를 사용하지 못하고 풀 테이블 스캔(Full Table Scan)을 수행하여 심각한 성능 저하가 발생했다.

② MySQL 5.6 이후: Subquery Materialization (구체화) 최적화

MySQL 5.6부터는 MATERIALIZED라는 최적화 방식이 도입되었다. 서브쿼리의 결과를 메모리 임시 테이블(임시 인덱스 포함)에 먼저 구체화해 두고, 외부 테이블과 조인하는 방식으로 동작하여 인덱스를 정상적으로 활용한다.

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'
);

NOT IN 서브쿼리 실행계획 (풀 테이블 스캔 발생)

  • 실행 계획을 확인해 보면 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;

LEFT OUTER JOIN ANTI-JOIN 실행계획

  • 원리: 좌측 테이블 전체를 읽으면서 우측 테이블과 조인한 뒤, 우측 테이블에 매칭되지 않은(IS NULL) 레코드만 남기는 방식이다.
  • 특히 처리 대상 레코드 건수가 많아질수록 서브쿼리보다 조인 기반의 ANTI-JOIN이 훨씬 더 압도적인 속도를 낸다.

💡 실무 팁: 데이터 규모와 인덱스 구성에 따라 성능 차이가 다를 수 있으므로, 반드시 운영 환경과 유사한 대용량 데이터에서 EXPLAIN 실행 계획과 처리 시간을 직접 비교 검증한 후 적용하는 것이 바람직하다.


마치며

총 4편에 걸쳐 MySQL 쿼리 최적화의 핵심 원리(타입 일치, NOW vs SYSDATE, GROUP BY/ORDER BY 복합 인덱스, JOIN 및 서브쿼리 튜닝)를 정리했다.

  • 서브쿼리는 필요한 경우에 유용하지만, 조인으로 대체 가능하거나 IN/NOT IN 연산자와 결합될 때는 항상 실행 계획을 점검해야 한다.
  • MySQL 버전별 옵티마이저 발전(Materialization 등)을 이해하고, 상황에 맞는 ANTI-JOIN 및 지연된 조인 기법을 적재적소에 활용하자.
[광고 영역] AdSense Bottom Unit Slot