본문 바로가기
카테고리 없음

MySQL 서브쿼리 최적화 전략 (IN, EXISTS, JOIN 변환 등)

by lilililililiiii 2025. 5. 4.

SQL에서 서브쿼리(Subquery)는 쿼리 내부에 또 다른 쿼리를 포함시키는 방식으로, 복잡한 조건이나 필터링 로직을 간결하게 표현할 수 있게 해주는 강력하고 유연한 기능입니다. SELECT 절, FROM 절, WHERE 절 등 다양한 위치에서 활용되어 데이터 조회 및 가공의 표현력을 높여주지만, 그 편리함 이면에는 성능 저하라는 잠재적 위험이 도사리고 있습니다. 특히 과거 MySQL 버전에서는 서브쿼리 처리 방식의 비효율성으로 인해 성능 문제의 주범으로 지목되는 경우가 많았습니다. 최신 버전에서는 서브쿼리 구체화(Materialization), 세미 조인(Semi-Join) 등 다양한 자동 최적화 기법이 도입되어 상당 부분 개선되었지만, 여전히 서브쿼리의 종류(스칼라, 인라인 뷰, 중첩)와 사용 방식, 특히 외부 쿼리에 의존하는 상관 서브쿼리(Correlated Subquery) 등에 따라 예기치 못한 성능 문제를 야기할 수 있습니다. 따라서 본 글에서는 다양한 서브쿼리의 유형과 각각이 가질 수 있는 잠재적 문제점을 살펴보고, MySQL 옵티마이저의 자동 최적화 원리를 이해하며, 나아가 성능 개선이 필요할 때 적용할 수 있는 핵심 수동 최적화 전략, 특히 서브쿼리를 JOIN 구문으로 변환하는 구체적인 방법과 고려사항에 대해 심도 있게 논의하고자 합니다.

 

서브쿼리의 종류 및 문제점

서브쿼리는 사용되는 위치에 따라 크게 세 가지로 나눌 수 있습니다.

1. 스칼라 서브쿼리 (Scalar Subquery): SELECT 절에서 사용되며, 단일 값(하나의 행, 하나의 컬럼)을 반환해야 합니다.

SELECT
    e.employee_id,
    e.first_name,
    (SELECT MAX(salary) FROM salaries s WHERE s.emp_no = e.emp_no) AS max_salary
FROM employees e;
  • 문제점: 바깥쪽 쿼리(Outer Query)의 각 행마다 서브쿼리가 반복 실행될 수 있어(Dependent Subquery), 행 수가 많으면 성능이 크게 저하될 수 있습니다.

2. 인라인 뷰 (Inline View): FROM 절에서 사용되며, 테이블처럼 취급됩니다. 서브쿼리 결과가 임시 테이블(Derived Table)로 생성될 수 있습니다. 

SELECT e.first_name, d.dept_name
FROM employees e
JOIN (SELECT emp_no, dept_no FROM dept_emp WHERE to_date = '9999-01-01') AS current_dept
  ON e.emp_no = current_dept.emp_no
JOIN departments d ON current_dept.dept_no = d.dept_no;
  • 문제점: 서브쿼리 결과가 크거나, 서브쿼리 자체를 최적화하기 어려운 경우 성능 문제가 발생할 수 있습니다. 때로는 불필요한 임시 테이블 생성 비용이 발생합니다.

3. 중첩 서브쿼리 (Nested Subquery): WHERE 절이나 HAVING 절에서 사용되며, 주로 IN, NOT IN, EXISTS, NOT EXISTS, 비교 연산자(=, >, <, 등)와 함께 사용됩니다.

-- IN 사용 예시
SELECT first_name
FROM employees
WHERE emp_no IN (SELECT emp_no FROM salaries WHERE salary > 150000);

-- EXISTS 사용 예시
SELECT d.dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM dept_manager dm WHERE dm.dept_no = d.dept_no AND dm.to_date = '9999-01-01');
  • 문제점
    • IN 서브쿼리는 과거 버전에서 매우 비효율적으로 처리되는 경우가 많았습니다. (현재는 많이 개선됨)
    • NOT IN 이나 NOT EXISTS는 최적화가 더 까다로울 수 있습니다.
    • 서브쿼리가 바깥쪽 쿼리의 컬럼을 참조하는 상관 서브쿼리(Correlated Subquery)는 바깥쪽 행마다 반복 실행되어 성능 저하를 유발할 수 있습니다. (EXPLAIN 결과 select_type이 DEPENDENT SUBQUERY로 표시됨)

서브쿼리 최적화 기법

MySQL 옵티마이저는 다양한 기법을 사용하여 서브쿼리를 자동으로 최적화하려고 시도합니다.

  • 서브쿼리 구체화 (Subquery Materialization): FROM 절이나 WHERE 절의 IN 서브쿼리 결과를 내부 임시 테이블로 미리 만들어두고 사용하는 방식입니다. 이를 통해 서브쿼리가 반복 실행되는 것을 방지합니다. (EXPLAIN 결과 select_type이 MATERIALIZED 또는 table이 <subqueryN>으로 표시됨)
  • IN-to-EXISTS 변환: IN 서브쿼리를 EXISTS 형태로 변환하여 최적화합니다.
  • 세미 조인 (Semi-Join): IN 또는 EXISTS 서브쿼리를 조인(Join) 형태로 변환하여 처리하는 기법입니다. 중복을 제거하고 효율적으로 일치하는 행을 찾습니다. 다양한 세미 조인 전략(Duplicate Weedout, FirstMatch, LooseScan 등)이 사용됩니다.
  • 서브쿼리 풀링 아웃 (Subquery Pullout): 서브쿼리를 바깥쪽 쿼리와 조인하는 형태로 변환합니다.
  • 조건 푸시다운 (Condition Pushdown): 서브쿼리 내부로 외부 조건을 밀어 넣어 서브쿼리 결과 크기를 줄입니다.

수동 최적화 전략은? JOIN으로 변환하기

옵티마이저가 항상 최적의 실행 계획을 찾는 것은 아니므로, 때로는 개발자가 직접 서브쿼리를 더 효율적인 형태로 변경해야 합니다. 가장 일반적인 방법은 서브쿼리를 JOIN으로 변환하는 것입니다.

1. IN 서브쿼리를 INNER JOIN으로 변환

-- 원본 쿼리 (IN 사용)
SELECT first_name
FROM employees
WHERE emp_no IN (SELECT emp_no FROM salaries WHERE salary > 150000);

-- JOIN으로 변환
SELECT DISTINCT e.first_name -- 중복 제거 필요 시 DISTINCT 사용
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE s.salary > 150000;
  • 장점: 옵티마이저가 조인 최적화(인덱스 활용, 조인 순서 변경 등)를 더 효과적으로 수행할 수 있습니다.
  • 주의: 서브쿼리 결과에 중복이 있을 경우 JOIN 결과도 중복될 수 있으므로, 필요에 따라 DISTINCT를 사용해야 합니다.

2. EXISTS 서브쿼리를 LEFT JOIN 또는 INNER JOIN으로 변환

-- 원본 쿼리 (EXISTS 사용)
SELECT d.dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM dept_manager dm WHERE dm.dept_no = d.dept_no AND dm.to_date = '9999-01-01');

-- INNER JOIN으로 변환 (현재 매니저가 있는 부서만 조회)
SELECT DISTINCT d.dept_name
FROM departments d
INNER JOIN dept_manager dm ON d.dept_no = dm.dept_no
WHERE dm.to_date = '9999-01-01';

 

스칼라 서브쿼리를 LEFT JOIN으로 변환

-- 원본 쿼리 (스칼라 서브쿼리)
SELECT
    e.employee_id,
    e.first_name,
    (SELECT MAX(salary) FROM salaries s WHERE s.emp_no = e.emp_no) AS max_salary
FROM employees e;

-- LEFT JOIN으로 변환
SELECT
    e.employee_id,
    e.first_name,
    max_s.max_salary
FROM employees e
LEFT JOIN (SELECT emp_no, MAX(salary) AS max_salary FROM salaries GROUP BY emp_no) max_s
  ON e.emp_no = max_s.emp_no;
  • 장점: 반복 실행을 피하고 조인 최적화를 활용할 수 있습니다.

NOT IN 또는 NOT EXISTS를 LEFT JOIN ... IS NULL로 변환

-- 원본 쿼리 (NOT IN 사용)
SELECT first_name
FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager WHERE to_date = '9999-01-01');

-- LEFT JOIN ... IS NULL 로 변환
SELECT e.first_name
FROM employees e
LEFT JOIN dept_manager dm ON e.emp_no = dm.emp_no AND dm.to_date = '9999-01-01'
WHERE dm.emp_no IS NULL; -- 매칭되는 매니저 정보가 없는 직원
  • 장점: NOT IN은 서브쿼리 결과에 NULL이 포함되면 예상과 다르게 동작할 수 있고 최적화가 어려울 수 있지만, LEFT JOIN ... IS NULL은 더 명확하고 최적화에 유리한 경우가 많습니다.

서브쿼리 사용 시 고려사항

  • 최신 버전 사용: MySQL 버전이 높아질수록 서브쿼리 최적화 기능이 향상되므로, 가급적 최신 버전을 사용하는 것이 좋습니다.
  • EXPLAIN 확인: 서브쿼리를 사용한 쿼리는 반드시 EXPLAIN으로 실행 계획을 확인하여 DEPENDENT SUBQUERY나 비효율적인 접근 방식(type: ALL 등)이 있는지 점검해야 합니다.
  • 상관 서브쿼리 최소화: 가능하면 상관 서브쿼리 대신 JOIN이나 구체화된 서브쿼리(Materialized Subquery)를 사용하도록 유도합니다.
  • JOIN 변환 고려: 성능 문제가 발생하거나 실행 계획이 비효율적이라고 판단되면, JOIN으로 변환하는 것을 적극적으로 고려합니다.
  • 인덱스 활용: 서브쿼리 내부의 WHERE 절이나 외부 쿼리와의 연결 조건(ON 절)에 사용되는 컬럼에는 적절한 인덱스가 생성되어 있어야 합니다.

서브쿼리는 강력한 기능이지만 성능 문제를 유발할 수 있는 잠재적 위험 요소입니다. MySQL 옵티마이저의 자동 최적화 기능을 최대한 활용하되, EXPLAIN을 통해 실행 계획을 면밀히 분석하고 필요한 경우 JOIN으로 변환하는 등의 수동 최적화 전략을 병행하는 것이 중요합니다.

서브쿼리는 복잡한 SQL 로직을 구현하는 데 매우 유용한 도구임이 분명하지만, 성능 측면에서는 양날의 검과 같습니다. 스칼라 서브쿼리의 반복 실행 가능성, 인라인 뷰의 임시 테이블 생성 부담, 중첩 서브쿼리(특히 상관 서브쿼리나 NOT IN 등)의 비효율적 처리 가능성은 여전히 주의해야 할 부분입니다. 최신 MySQL은 서브쿼리 구체화, IN-to-EXISTS 변환, 다양한 세미 조인 기법 등을 통해 자동으로 서브쿼리를 최적화하려는 노력을 꾸준히 수행하고 있습니다.

하지만 MySQL 옵티마이저의 자동 최적화에만 의존하기보다는, EXPLAIN을 통해 실제 실행 계획을 면밀히 분석하는 것이 무엇보다 중요합니다. 실행 계획에서 DEPENDENT SUBQUERY와 같은 비효율적인 패턴이 발견되거나 전반적인 성능이 만족스럽지 않다면, 서브쿼리를 JOIN으로 변환하는 수동 최적화 전략을 적극적으로 고려해야 합니다. IN이나 EXISTS는 INNER JOIN으로, 스칼라 서브쿼리는 LEFT JOIN으로, NOT IN이나 NOT EXISTS는 LEFT JOIN ... IS NULL 패턴으로 변환하는 것은 옵티마이저가 더 다양한 최적화 기법(조인 순서 변경, 인덱스 활용 등)을 적용할 여지를 열어주어 성능을 극적으로 개선할 수 있는 가장 효과적인 방법 중 하나입니다. 결국, 서브쿼리의 편리함을 누리면서도 성능을 확보하기 위해서는 자동 최적화의 한계를 인지하고, EXPLAIN 기반의 분석과 필요시 JOIN 변환과 같은 능동적인 최적화 노력을 병행하는 균형 잡힌 접근 방식이 필수적입니다.

 

 

 

 

티스토리툴바