MySQL의 각 절에 대한 서브쿼리 이해와 성능 최적화

서브쿼리는 쿼리의 여러 위치에서 사용할 수 있는데

대표적으로 SELECT, FROM, WHERE 절에 사용될 수 있다.

하지만 사용되는 위치에 따라 쿼리의 성능 영향도와 MySQL 서버의 최적화 방법은 완전히 달라진다.

각 절마다 어떻게 최적화되는지 그리고 어떻게 쿼리를 작성해야 성능에 도움이 될지 살펴보자.

---

SELECT 절에 사용된 서브쿼리

SELECT 절에 사용된 서브쿼리는 내부적으로 임시 테이블을 만들지도 않고 비효율적으로 실행하지 않기 때문에

서브쿼리가 인덱스만 적절히 사용한다면 크게 주의할 사항은 없다.

(단, 상관 서브쿼리의 경우 다름)

 

일반적으로 SELECT 절에 서브쿼리를 사용하면 그 서브쿼리는 항상 컬럼과 레코드가 하나인 결과를 반환해야 한다.

즉, 값이 NULL이든 아니든 관계 없이 레코드가 딱 1건이 존재해야한다.

 

다음 예시로 정상적으로 실행되는 상황과 오류가 발생하는 상황을 알아보자.

쿼리 1

SELECT emp_no, (SELECT dept_name FROM departments WHERE dept_name = 'ooo')
FROM dept_emp LIMIT 10;

이 쿼리는 서브쿼리의 결과가 NULL이기 때문에 1건의 레코드가 반환되어 정상적으로 실행된다.

 

쿼리 2

SELECT emp_no, (SELECT dept_name FROM departments)
FROM dept_emp LIMIT 10;

이 쿼리는 서브쿼리가 여러 행을 반환하기 때문에 SELECT 절의 서브쿼리는 단일 값만 반환해야 한다는 뜻인

"Subquery returns more than 1 row” 오류 메시지를 반환한다.

 

쿼리 3

SELECT emp_no, (SELECT dept_no, dept_name FROM departments WHERE dept_name = 'ooo')
FROM dept_emp LIMIT 10;

이 쿼리는 서브쿼리가 두 개의 열을 반환하기 때문에 SELECT 절의 서브쿼리는 단일 열만 반환해야 한다는 뜻인

"Operand should contain 1 column(s)” 오류 메시지를 반환한다.

 

정리하면 SELECT 절의 서브쿼리에는 단일 행, 단일 열의 값만 반환하는 스칼라 서브쿼리만 사용할 수 있다.

가끔 JOIN으로 처리할 수 있는 쿼리도 SELECT 서브쿼리로 작성하는 경우가 있는데

JOIN으로 처리할 때가 성능이 조금 더 빠르기 때문에 가능하면 JOIN으로 작성하는 것이 좋다.

이유는 아래에서 설명한다.

SELECT
(SELECT dept_no, dept_name FROM departments WHERE dept_name = 'ooo')
FROM dept_emp 
LIMIT 10;

 

상관 서브쿼리와 단일 서브쿼리

상관 서브쿼리는 메인 쿼리의 테이블과 연관되어 실행되는 서브쿼리이다.

SELECT 이름,
       (SELECT AVG(급여) 
        FROM 직원 B 
        WHERE B.부서 = A.부서)
FROM 직원 A;

메인 쿼리의 각 행마다 서브쿼리가 실행되는 특징이 있다.

 

단일 서브쿼리는 연관되지 않고 단독으로 실행되는 서브쿼리이다.

SELECT 이름,
       (SELECT AVG(급여) FROM 직원) as 회사평균급여
FROM 직원;

반복되지 않고 한 번만 실행되어 결과를 반환하는 특징이 있다.

 

중요한점은 상관 서브쿼리의 반복 실행이다.

상관 서브쿼리를 사용한다면 A 테이블의 행 수만큼 서브쿼리가 반복해서 실행된다.

만약 A 테이블의 행이 100개라면 서브쿼리는 100번이나 반복되는 것이다.

이는 매 실행마다 테이블을 새로 읽으면서 같은 연산을 여러번 수행해야해 성능 저하를 줄 수 있다.

따라서 가능하다면 아래와 같이 일반 조인을 사용해 서브 쿼리의 결과를 조인으로 매칭하는 것이 좋을 것이다.

SELECT A.이름, B.부서평균급여
FROM 직원 A
JOIN (
    SELECT 부서, AVG(급여) as 부서평균급여
    FROM 직원
    GROUP BY 부서
) B ON A.부서 = B.부서;

---

FROM 절에 사용된 서브쿼리

FROM 절에 서브쿼리를 사용하면 옵티마이저가 서브 쿼리의 외부 쿼리로 병합하는 최적화를 수행한다.

하지만 모든 서브쿼리를 외부 쿼리로 병합할 수 있는 것은 아니다.

대표적으로 다음과 같은 기능이 서브쿼리에 사용되면 FROM 절의 서브쿼리는 외부 쿼리와 병합이 불가능하다.

• 집계 함수 사용
• DISTINCT
• GROUP BY 또는 HAVING
• LIMIT
• UNION 키워드
• SELECT 절에 서브쿼리가 사용된 경우
• 사용자 변수에 값을 할당할 경우

 

외부 쿼리와 병합이 불가능하면 다음과 같이

서브쿼리가 먼저 실행되어 임시 테이블(DERIVED) 생성하고 생성된 임시 테이블을 기반으로 외부 쿼리 실행하게 된다.

EXPLAIN SELECT * 
FROM (
    SELECT emp_no, MAX(salary) as max_salary
    FROM salaries 
    GROUP BY emp_no
) AS derived_table;

임시 테이블을 생성하지 않았다면 외부 쿼리의 각 행마다 서브쿼리가 반복적으로 실행되어야 하겠지만

병합이 불가능하더라도 이런식으로 옵티마이저는 각각 다른 방법으로 최적화를 수행하는 것을 볼 수 있다.

---

WHERE 절에 사용된 서브쿼리

SELECT 절의 서브쿼리와 마찬가지로

WHERE 절에서 단일 값과의 비교(=)를 할 때는 스칼라 서브쿼리(단일 행, 단일 열)만 사용할 수 있다.

WHERE 절의 서브쿼리는 SELECT, FROM 절보다는 다양한 형태로 사용될 수 있는데

크게 3가지로 구분되는데 이를 하나씩 알아보자.

 

동등 또는 크다 작다 비교 ( = (서브쿼리))

단일 행을 가지는 서브쿼리와 튜플을 가지는 서브쿼리에 대해 비교하려한다.

 

우선 아래는 단일 행을 가지는 서브쿼리를 비교하는 쿼리이다.

EXPLAIN SELECT emp_no, hire_date
FROM employees
WHERE hire_date = (
    SELECT hire_date
    FROM employees
    WHERE hire_date > '1999-01-01'
    LIMIT 1
);

 

아래는 튜플을 가지는 서브쿼리를 비교하는 쿼리이다. (hire_date와 gender가 복합 인덱스가 존재)

EXPLAIN SELECT emp_no, hire_date
FROM employees
WHERE (hire_date, gender) = (
    SELECT hire_date, gender
    FROM employees
    WHERE hire_date > '1999-01-01'
    LIMIT 1
);

 

실행 결과를 비교하면

단일 행을 가지는 서브쿼리의 경우 외부 쿼리에는 ref=const로 표시되어 있다.

이는 서브 쿼리의 결과가 단일 상수 값으로 대체되어 외부 쿼리가 해당 값이 있는 위치를 바로 찾을 수 있다.

 

하지만 튜플을 가지는 서브 쿼리의 경우 결과가 (hire_date, gender) 쌍이므로

옵티마이저는 hire_date로 먼저 필터링한 후, 그 결과에서 gender까지 확인해야 하므로 더 많은 중간 결과를 처리해야 한다.

따라서 rows를 보면 수 많은 레코드에 접근할 것을 볼 수 있다.

 

이처럼  단일 값 비교가 아닌 튜플 방식이 사용되면 외부 쿼리는 인덱스를 사용하더라도 효율적으로 사용하지 못할 수 있음을 주의해야한다.

 

IN 비교 (IN (서브쿼리))

실제 조인은 아니지만 테이블의 레코드가 다른 테이블의 레코드를 이용한 표현식과 일치하는지를 체크하는 형태를 “세미 조인”이라고 한다.

(세미 조인에 대해서는 아래 포스팅에서 설명한다.)

2025.01.28 - [◼ DB] - MySQL의 최적화 조인들에 대해 알아보자

 

아래 쿼리의 경우 WHERE 절에 사용된 IN (서브쿼리) 형태의 조건을 조인의 한 방식인 세미 조인으로 처리한다.

EXPLAIN SELECT emp_no
FROM employees
WHERE emp_no IN (
    SELECT emp_no
    FROM dept_manager
);

여기서는 세미 조인의 최적화 전략 중 하나인 MATERIALIZATION 전략을 사용해

서브쿼리를 실행하여 결과를 도출하고 그 결과를 임시 테이블에 저장해 외부 쿼리를 처리한 것을 볼 수 있다.

 

NOT IN 비교 (NOT IN (서브쿼리))

IN 비교와 비슷하지만 안티 세미 조인이라고도 한다.

옵티마이저는 안티 세미 조인 쿼리가 사용되면 다음 두 가지 방법으로 최적화를 수행한다.

• NOT EXISTS
• MATERIALIZATION

EXPLAIN SELECT emp_no
FROM employees
WHERE emp_no NOT IN (
    SELECT emp_no
    FROM dept_manager
);

 

부정 조건의 특성 때문인지 두 가지 최적화 모두 성능 향상에 큰 도움을 주지 못한다고 한다.

항상은 아니겠지만 선택도에 따라 성능이 좌지우지 될 수 있을 것 같다.

 

위 실행 계획에서도 세미 조인은 employees 테이블을 따로 스캔하지 않았지만

안티 세미 조인에서는 존재하지 않는 것을 확인하기 위해 employees 테이블의 299,920행을 스캔하고 임시 테이블과 조인이 일어났다.

 

만약 성능 개선이 필요하다면 WHERE 절에 추가 조건을 넣어 검색 대상 데이터 자체를 줄이는 것이 효과적일 것이다.

필자는 외부 쿼리에 스캔 수가 많기 때문에 외부 쿼리에 대한 WHERE 조건을 추가해 개선해 보았다.

EXPLAIN SELECT emp_no
FROM employees
WHERE emp_no NOT IN (
    SELECT emp_no
    FROM dept_manager
)
AND hire_date > '1999-01-01';

---

참고 자료

• Real MySQL 8.0 2권