Subquery 최적화: semi-join, materialization, dependent subquery 진단

Subquery는 업무 조건을 자연스럽게 표현한다. “결제 완료 주문이 있는 고객”, “전체 평균보다 큰 주문”, “고객별 최근 주문 시각” 같은 요구는 IN, EXISTS, scalar subquery로 간결하게 작성할 수 있다. 문제는 SQL의 중첩 모양만 보고 실제 실행 횟수를 판단하기 어렵다는 데 있다. MySQL은 subquery를 outer query에 합쳐 semi-join으로 실행할 수도 있고, 결과를 내부 임시 테이블로 만든 뒤 재사용할 수도 있으며, outer row마다 dependent subquery를 반복할 수도 있다.

운영 관점에서 중요한 질문은 “subquery가 있는가”가 아니라 옵티마이저가 query block 사이의 의존성을 제거했는가, 중간 결과를 한 번 만들었는가, 아니면 outer row 수만큼 다시 실행하는가이다. 이 글은 MySQL 8.0 이상을 기준으로 semi-join, materialization, dependent subquery의 실행 경로와 진단 순서를 정리한다. 예제의 비용과 row 추정치는 데이터 분포와 minor version에 따라 달라질 수 있으므로 select_type, key, rows, Extra와 iterator 구조를 함께 읽어야 한다.

1. Subquery 최적화의 핵심은 실행 경계를 바꾸는 것이다

파서는 SQL을 outer query block과 하나 이상의 inner query block으로 나눈다. 이후 옵티마이저는 의미가 보존되는 범위에서 경계를 제거하거나, 별도 결과 집합으로 고정하거나, 의존성을 유지한다.

flowchart TD
    Q[Subquery가 포함된 SQL] --> C{Outer row 값에 의존하는가}
    C -- 아니오 --> S{Semi-join 변환이 가능한가}
    C -- 예 --> D{Decorrelate 또는 semi-join 변환 가능한가}
    S -- 예 --> J[Semi-join 전략 선택]
    S -- 아니오 --> M[Materialization 또는 독립 실행]
    D -- 예 --> J
    D -- 아니오 --> P[Dependent subquery]
    J --> T[Table pullout / FirstMatch / LooseScan / Duplicate Weedout / Materialization]
    M --> R[중간 결과를 한 번 생성하고 재사용]
    P --> L[Outer row마다 inner query 평가 가능]
    T --> X[최종 실행 계획]
    R --> X
    L --> X

세 경로는 다음처럼 구분할 수 있다.

실행 형태	핵심 의미	대표 계획 신호	주요 리스크
semi-join	inner table은 일치 여부만 제공하며 outer row 중복을 만들지 않는다	query block이 `SIMPLE`로 합쳐짐, `FirstMatch(...)`, `LooseScan`, `Start temporary; End temporary` 등	잘못된 통계로 비효율적인 조인 순서 또는 전략 선택
materialization	subquery 결과를 내부 임시 테이블로 한 번 만들고 조회한다	`MATERIALIZED`, `<subqueryN>`, TREE의 `Materialize`	큰 중간 결과, 메모리·임시 디스크 사용, 생성 비용
dependent subquery	inner query가 outer row의 값을 참조하여 반복 평가될 수 있다	`DEPENDENT SUBQUERY`, TREE의 dependent subquery 노드	outer rows × inner 접근 비용의 증폭

DEPENDENT SUBQUERY가 보인다고 무조건 장애는 아니다. outer row가 매우 적고 inner lookup이 unique index 한 건 조회라면 충분히 효율적일 수 있다. 반대로 실행 계획에 MATERIALIZED가 보인다고 무조건 느린 것도 아니다. 동일한 비상관 결과를 outer row마다 다시 계산하는 것보다 한 번 만들어 인덱스로 조회하는 편이 훨씬 저렴할 수 있다.

2. Semi-join은 “존재 여부 조인”이다

IN과 EXISTS의 일반적인 목적은 inner query가 반환한 값을 최종 결과에 모두 붙이는 것이 아니라, 조건에 맞는 row가 하나라도 존재하는지 확인하는 것이다. 일반 inner join은 inner table에 일치하는 row가 여러 건이면 outer row를 여러 번 반환한다. Semi-join은 일치 여부만 전달하므로 outer row를 한 번만 반환한다.

예를 들어 한 고객에게 결제 완료 주문이 다섯 건 있어도 다음 SQL은 그 고객을 한 번만 반환해야 한다.

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'PAID'
);

MySQL은 형태상 correlated subquery인 이 SQL을 decorrelate하고 semi-join 후보로 바꿀 수 있다. 변환에 성공하면 “고객 한 건을 읽고 subquery를 호출하는 절차”가 아니라, 고객과 주문의 접근 순서 및 인덱스를 한 조인 문제 안에서 비교한다.

2.1 주요 semi-join 전략

MySQL 8.0은 비용과 SQL 구조에 따라 여러 전략을 선택한다.

Table pullout: subquery table을 outer query block으로 끌어올려 일반 조인처럼 최적화한다.
FirstMatch: 현재 outer row와 일치하는 첫 inner row를 찾으면 다음 outer row로 이동한다. 존재 확인에 필요한 추가 탐색을 생략한다.
LooseScan: 적절한 인덱스를 이용해 같은 key group의 대표 값만 건너뛰며 읽는다.
Duplicate Weedout: 일반 조인에 가까운 탐색을 수행하되 이미 반환한 outer row 조합을 임시 구조로 제거한다.
Materialization: subquery 결과를 중복 제거된 내부 테이블로 만든 뒤 lookup한다. Semi-join의 구현 전략으로 materialization이 선택될 수 있다.

따라서 semi-join과 materialization은 항상 상호 배타적인 분류가 아니다. “논리적 변환은 semi-join이고 물리적 구현은 materialization”인 계획도 가능하다. 계획을 읽을 때는 query block 변환과 실제 접근 전략을 구분해야 한다.

2.2 변환을 제한하는 구조

Semi-join 변환은 결과 의미를 보존할 수 있을 때만 가능하다. 복잡한 집계, window function, UNION, HAVING, 일부 LIMIT 구조, side effect나 query block 경계를 보존해야 하는 표현은 변환 후보에서 제외될 수 있다. 정확한 제한은 MySQL 버전마다 확장되므로 문법 목록을 외우기보다 실제 계획을 확인하는 편이 안전하다.

NOT IN은 NULL의 영향을 특히 주의해야 한다. Subquery 결과에 NULL이 하나라도 있으면 3-valued logic 때문에 예상과 다른 결과가 나올 수 있다. 반존재 조건은 nullable 여부를 확인한 뒤 NOT EXISTS로 명시하는 편이 의도를 더 분명하게 표현하는 경우가 많다.

3. 재현 환경 만들기

다음 데이터는 절대 성능 benchmark가 아니라 계획과 결과 의미를 관찰하기 위한 축소 예제다. 고객 여섯 명과 주문 열여섯 건을 만들고 통계를 갱신한다.

DROP TABLE IF EXISTS sq_orders;
DROP TABLE IF EXISTS sq_customers;

CREATE TABLE sq_customers (
  customer_id BIGINT NOT NULL PRIMARY KEY,
  customer_name VARCHAR(40) NOT NULL,
  region VARCHAR(12) NOT NULL,
  active TINYINT NOT NULL,
  KEY ix_region_active (region, active, customer_id)
) ENGINE=InnoDB;

CREATE TABLE sq_orders (
  order_id BIGINT NOT NULL PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status VARCHAR(12) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  ordered_at DATETIME NOT NULL,
  KEY ix_customer_status_date (customer_id, status, ordered_at),
  KEY ix_status_customer (status, customer_id)
) ENGINE=InnoDB;

INSERT INTO sq_customers VALUES
  (1, '고객-1', 'SEOUL', 1),
  (2, '고객-2', 'SEOUL', 1),
  (3, '고객-3', 'BUSAN', 1),
  (4, '고객-4', 'SEOUL', 0),
  (5, '고객-5', 'DAEJEON', 1),
  (6, '고객-6', 'BUSAN', 1);

INSERT INTO sq_orders VALUES
  (101, 1, 'PAID',      120.00, '2026-07-01 09:00:00'),
  (102, 1, 'PAID',       80.00, '2026-07-02 09:00:00'),
  (103, 1, 'REFUND',     80.00, '2026-07-03 09:00:00'),
  (104, 2, 'PENDING',    50.00, '2026-07-01 10:00:00'),
  (105, 2, 'PAID',      150.00, '2026-07-02 10:00:00'),
  (106, 2, 'PAID',      300.00, '2026-07-03 10:00:00'),
  (107, 3, 'PAID',      400.00, '2026-07-01 11:00:00'),
  (108, 3, 'CANCELLED', 100.00, '2026-07-02 11:00:00'),
  (109, 3, 'REFUND',    400.00, '2026-07-03 11:00:00'),
  (110, 4, 'PAID',       20.00, '2026-07-01 12:00:00'),
  (111, 4, 'PAID',       70.00, '2026-07-02 12:00:00'),
  (112, 5, 'PENDING',    30.00, '2026-07-01 13:00:00'),
  (113, 5, 'CANCELLED',  40.00, '2026-07-02 13:00:00'),
  (114, 6, 'PAID',       90.00, '2026-07-01 14:00:00'),
  (115, 6, 'PAID',      110.00, '2026-07-02 14:00:00'),
  (116, 6, 'SHIPPED',   110.00, '2026-07-03 14:00:00');

ANALYZE TABLE sq_customers, sq_orders;
SELECT COUNT(*) AS customer_count FROM sq_customers;
SELECT COUNT(*) AS order_count FROM sq_orders;

실행 결과(MySQL 8.0.x):

mysql> CREATE TABLE sq_customers (...);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE sq_orders (...);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO sq_customers VALUES (...);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> INSERT INTO sq_orders VALUES (...);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE sq_customers, sq_orders;
+------------------------------+---------+----------+----------+
| Table                        | Op      | Msg_type | Msg_text |
+------------------------------+---------+----------+----------+
| mysql_tech_note.sq_customers | analyze | status   | OK       |
| mysql_tech_note.sq_orders    | analyze | status   | OK       |
+------------------------------+---------+----------+----------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*) AS customer_count FROM sq_customers;
+----------------+
| customer_count |
+----------------+
|              6 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS order_count FROM sq_orders;
+-------------+
| order_count |
+-------------+
|          16 |
+-------------+
1 row in set (0.00 sec)

작은 표에서는 table scan이 index lookup보다 싸게 선택될 수 있다. 아래 예제는 지연 시간 수치를 비교하는 것이 아니라 변환 신호와 반복 실행 가능성을 확인하는 데 목적이 있다.

4. EXISTS가 semi-join으로 변환되는지 확인하기

먼저 semi-join 관련 optimizer switch를 활성화하고 EXISTS를 설명한다. 운영 서버에서는 전역 값을 바꾸지 말고 현재 세션에서만 실험해야 한다.

SET SESSION optimizer_switch =
  'semijoin=on,firstmatch=on,loosescan=on,duplicateweedout=on,materialization=on';

EXPLAIN
SELECT c.customer_id, c.customer_name
FROM sq_customers AS c
WHERE c.active = 1
  AND EXISTS (
    SELECT 1
    FROM sq_orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'PAID'
  );

실행 결과(MySQL 8.0.x):

mysql> SET SESSION optimizer_switch =
    ->   'semijoin=on,firstmatch=on,loosescan=on,duplicateweedout=on,materialization=on';

Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN
    -> SELECT c.customer_id, c.customer_name
    -> FROM sq_customers AS c
    -> WHERE c.active = 1
    ->   AND EXISTS (
    ->     SELECT 1
    ->     FROM sq_orders AS o
    ->     WHERE o.customer_id = c.customer_id
    ->       AND o.status = 'PAID'
    ->   );

+----+-------------+-------+------------+------+--------------------------------------------+--------------------+---------+-------------------------------------+------+----------+----------------------------+
| id | select_type | table | partitions | type | possible_keys                              | key                | key_len | ref                                 | rows | filtered | Extra                      |
+----+-------------+-------+------------+------+--------------------------------------------+--------------------+---------+-------------------------------------+------+----------+----------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | PRIMARY                                    | NULL               | NULL    | NULL                                |    6 |    16.67 | Using where                |
|  1 | SIMPLE      | o     | NULL       | ref  | ix_customer_status_date,ix_status_customer | ix_status_customer | 58      | const,mysql_tech_note.c.customer_id |    1 |   100.00 | Using index; FirstMatch(c) |
+----+-------------+-------+------------+------+--------------------------------------------+--------------------+---------+-------------------------------------+------+----------+----------------------------+
2 rows in set (0.00 sec)

성공적으로 semi-join으로 변환되면 DEPENDENT SUBQUERY가 별도 행으로 남지 않고 두 테이블이 같은 query block의 SIMPLE table로 나타날 수 있다. 선택된 전략에 따라 Extra에는 FirstMatch(c), temporary 기반 중복 제거 신호 등이 보일 수 있다. 작은 예제에서는 비용 판단에 따라 구체적인 전략이 달라져도, 별도 dependent query block이 사라졌는지가 첫 번째 확인점이다.

같은 조건을 일반 join으로 단순 치환하면 중복 의미가 달라진다는 점도 확인해야 한다.

SELECT c.customer_id, c.customer_name
FROM sq_customers AS c
WHERE c.active = 1
  AND EXISTS (
    SELECT 1
    FROM sq_orders AS o
    WHERE o.customer_id = c.customer_id
      AND o.status = 'PAID'
  )
ORDER BY c.customer_id;

SELECT c.customer_id, c.customer_name
FROM sq_customers AS c
JOIN sq_orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'PAID'
WHERE c.active = 1
ORDER BY c.customer_id, o.order_id;

실행 결과(MySQL 8.0.x):

mysql> SELECT c.customer_id, c.customer_name
    -> FROM sq_customers AS c
    -> WHERE c.active = 1
    ->   AND EXISTS (
    ->     SELECT 1
    ->     FROM sq_orders AS o
    ->     WHERE o.customer_id = c.customer_id
    ->       AND o.status = 'PAID'
    ->   )
    -> ORDER BY c.customer_id;

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | 고객-1      |
|           2 | 고객-2      |
|           3 | 고객-3      |
|           6 | 고객-6      |
+-------------+---------------+
4 rows in set (0.00 sec)

mysql> SELECT c.customer_id, c.customer_name
    -> FROM sq_customers AS c
    -> JOIN sq_orders AS o
    ->   ON o.customer_id = c.customer_id
    ->  AND o.status = 'PAID'
    -> WHERE c.active = 1
    -> ORDER BY c.customer_id, o.order_id;

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | 고객-1      |
|           1 | 고객-1      |
|           2 | 고객-2      |
|           2 | 고객-2      |
|           3 | 고객-3      |
|           6 | 고객-6      |
|           6 | 고객-6      |
+-------------+---------------+
7 rows in set (0.00 sec)

첫 결과는 고객 1, 2, 3, 6을 한 번씩 반환한다. 두 번째 결과는 결제 주문 건수만큼 고객을 반복한다. 성능 개선을 이유로 EXISTS를 JOIN으로 다시 쓸 때 무조건 DISTINCT를 붙이면 정렬·중복 제거 비용이 새로 생길 수 있다. 먼저 필요한 결과의 cardinality를 명확히 하고 semi-join 변환 여부를 확인해야 한다.

5. Materialization은 반복 계산을 lookup으로 바꾼다

비상관 IN subquery는 outer row와 무관한 결과를 만든다. 이 결과를 매번 다시 구하는 대신 내부 임시 테이블에 한 번 저장하고 중복을 제거한 뒤 lookup하면 반복 비용을 줄일 수 있다. 다음 예제는 관찰을 위해 semi-join을 끄고 subquery materialization을 활성화한다.

SET SESSION optimizer_switch =
  'semijoin=off,materialization=on,subquery_materialization_cost_based=off';

EXPLAIN FORMAT=TREE
SELECT c.customer_id, c.customer_name
FROM sq_customers AS c
WHERE c.active = 1
  AND c.customer_id IN (
    SELECT o.customer_id
    FROM sq_orders AS o
    WHERE o.status = 'REFUND'
  );

실행 결과(MySQL 8.0.x):

mysql> SET SESSION optimizer_switch =
    ->   'semijoin=off,materialization=on,subquery_materialization_cost_based=off';
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN FORMAT=TREE
    -> SELECT c.customer_id, c.customer_name ...;

-> Filter: ((c.`active` = 1) and <in_optimizer>(...))  (cost=0.85 rows=1)
    -> Table scan on c  (cost=0.85 rows=6)
    -> Select #2 (subquery in condition; run only once)
        -> Filter: (c.customer_id = `<materialized_subquery>`.customer_id)
            -> Limit: 1 row(s)
                -> Index lookup on <materialized_subquery> using <auto_distinct_key>
                    -> Materialize with deduplication  (cost=0.652 rows=2)
                        -> Covering index lookup on o using ix_status_customer
                           (status='REFUND')  (cost=0.452 rows=2)
1 row in set (0.00 sec)

TREE 계획에서 Select #2 (subquery in condition; run only once), Materialize with deduplication, <auto_distinct_key> lookup이 이어지면 subquery 결과를 한 번 내부 구조로 만들고 중복 제거한 뒤 outer customer key로 조회하는 경로다. Traditional EXPLAIN에서는 같은 실행이 단순히 SUBQUERY로 표시될 수 있으므로 materialization을 확정할 때 TREE 또는 JSON 계획을 함께 확인해야 한다. 실제 계획은 다음 비용의 균형으로 결정된다.

Inner query를 한 번 읽고 중간 결과를 쓰는 비용
중복 제거와 임시 인덱스 구성 비용
Outer row마다 materialized table을 lookup하는 비용
Materialization 없이 다른 조인 순서나 index lookup을 사용하는 비용
중간 결과가 메모리 한도를 넘어 임시 디스크로 전환될 가능성

Materialization은 독립적인 subquery에 특히 자연스럽지만, 모든 subquery를 “캐시한다”는 뜻은 아니다. 데이터 변경과 statement 일관성, NULL 의미, 비결정적 표현식, 상관 관계에 따라 적용 가능 여부가 달라진다. optimizer_switch를 강제로 고정한 위 예제는 실행 형태를 관찰하기 위한 것이며, 운영 쿼리에 그대로 강제하라는 권고가 아니다.

설정을 원래의 일반적인 비교 상태로 되돌린다.

SET SESSION optimizer_switch =
  'semijoin=on,firstmatch=on,loosescan=on,duplicateweedout=on,materialization=on,subquery_materialization_cost_based=on';

SELECT c.customer_id, c.customer_name
FROM sq_customers AS c
WHERE c.active = 1
  AND c.customer_id IN (
    SELECT o.customer_id
    FROM sq_orders AS o
    WHERE o.status = 'REFUND'
  )
ORDER BY c.customer_id;

실행 결과(MySQL 8.0.x):

mysql> SET SESSION optimizer_switch =
    ->   'semijoin=on,firstmatch=on,loosescan=on,duplicateweedout=on,materialization=on,subquery_materialization_cost_based=on';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT c.customer_id, c.customer_name
    -> FROM sq_customers AS c
    -> WHERE c.active = 1
    ->   AND c.customer_id IN (
    ->     SELECT o.customer_id
    ->     FROM sq_orders AS o
    ->     WHERE o.status = 'REFUND'
    ->   )
    -> ORDER BY c.customer_id;

+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|           1 | 고객-1      |
|           3 | 고객-3      |
+-------------+---------------+
2 rows in set (0.00 sec)

이 데이터에서는 환불 주문이 있는 활성 고객 1과 3이 반환된다. 실행 결과가 같더라도 옵티마이저는 semi-join, semi-join materialization, 독립 subquery materialization 등 서로 다른 내부 경로를 선택할 수 있다.

6. Dependent subquery의 비용은 outer rows에 의해 증폭된다

다음 scalar subquery는 고객마다 최근 주문 시각을 계산한다. o.customer_id = c.customer_id 때문에 inner query는 outer row에 의존한다.

EXPLAIN
SELECT c.customer_id,
       c.customer_name,
       (
         SELECT MAX(o.ordered_at)
         FROM sq_orders AS o
         WHERE o.customer_id = c.customer_id
       ) AS last_order_at
FROM sq_customers AS c
WHERE c.region = 'SEOUL';

실행 결과(MySQL 8.0.x):

mysql> EXPLAIN
    -> SELECT c.customer_id,
    ->        c.customer_name,
    ->        (
    ->          SELECT MAX(o.ordered_at)
    ->          FROM sq_orders AS o
    ->          WHERE o.customer_id = c.customer_id
    ->        ) AS last_order_at
    -> FROM sq_customers AS c
    -> WHERE c.region = 'SEOUL';

+----+--------------------+-------+------------+------+-------------------------+-------------------------+---------+-------------------------------+------+----------+-------------+
| id | select_type        | table | partitions | type | possible_keys           | key                     | key_len | ref                           | rows | filtered | Extra       |
+----+--------------------+-------+------------+------+-------------------------+-------------------------+---------+-------------------------------+------+----------+-------------+
|  1 | PRIMARY            | c     | NULL       | ref  | ix_region_active        | ix_region_active        | 50      | const                         |    3 |   100.00 | NULL        |
|  2 | DEPENDENT SUBQUERY | o     | NULL       | ref  | ix_customer_status_date | ix_customer_status_date | 8       | mysql_tech_note.c.customer_id |    2 |   100.00 | Using index |
+----+--------------------+-------+------------+------+-------------------------+-------------------------+---------+-------------------------------+------+----------+-------------+
2 rows in set (0.00 sec)

Traditional EXPLAIN에서 scalar query block이 DEPENDENT SUBQUERY로 남아 있으면 outer 고객 한 건마다 inner 접근이 일어날 수 있다. 위 예제에는 (customer_id, status, ordered_at) 인덱스가 있어 customer prefix로 범위를 좁힐 수 있지만, 운영 표에서 다음 조건이 겹치면 비용이 빠르게 커진다.

Outer query가 수십만 row를 반환한다.
Inner predicate의 선두 컬럼을 지원하는 인덱스가 없다.
한 outer row마다 inner table의 많은 row를 읽고 집계한다.
같은 outer key가 반복되지만 결과를 재사용하기 어렵다.
Select list에 여러 scalar subquery가 있어 반복 접근이 중첩된다.
ORDER BY ... LIMIT 1, aggregate, 함수 적용 때문에 인덱스 활용이 제한된다.

대략적인 비용 모델은 다음처럼 생각할 수 있다.

총 작업량 ≈ outer 접근 비용 + (outer 실제 row 수 × inner 1회 접근 비용)

이 식은 정확한 optimizer cost 공식이 아니라 운영자가 증폭 지점을 찾기 위한 사고 모델이다. Outer 쪽 추정치가 100배 틀리거나 inner lookup이 예상보다 넓으면 전체 지연도 비슷한 방향으로 증폭될 수 있다.

6.1 Aggregate join으로 다시 쓰는 선택지

고객별 최근 주문을 대량으로 조회한다면 먼저 주문을 고객별로 집계하고 한 번 조인하는 형태를 비교할 수 있다.

EXPLAIN
SELECT c.customer_id,
       c.customer_name,
       x.last_order_at
FROM sq_customers AS c
LEFT JOIN (
  SELECT o.customer_id, MAX(o.ordered_at) AS last_order_at
  FROM sq_orders AS o
  GROUP BY o.customer_id
) AS x
  ON x.customer_id = c.customer_id
WHERE c.region = 'SEOUL';

실행 결과(MySQL 8.0.x):

mysql> EXPLAIN
    -> SELECT c.customer_id,
    ->        c.customer_name,
    ->        x.last_order_at
    -> FROM sq_customers AS c
    -> LEFT JOIN (
    ->   SELECT o.customer_id, MAX(o.ordered_at) AS last_order_at
    ->   FROM sq_orders AS o
    ->   GROUP BY o.customer_id
    -> ) AS x
    ->   ON x.customer_id = c.customer_id
    -> WHERE c.region = 'SEOUL';

+----+-------------+------------+------------+-------+--------------------------------------------+-------------------------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys                              | key                     | key_len | ref                           | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+--------------------------------------------+-------------------------+---------+-------------------------------+------+----------+-------------+
|  1 | PRIMARY     | c          | NULL       | ref   | ix_region_active                           | ix_region_active        | 50      | const                         |    3 |   100.00 | NULL        |
|  1 | PRIMARY     | <derived2> | NULL       | ref   | <auto_key0>                                | <auto_key0>             | 8       | mysql_tech_note.c.customer_id |    2 |   100.00 | NULL        |
|  2 | DERIVED     | o          | NULL       | index | ix_customer_status_date,ix_status_customer | ix_customer_status_date | 63      | NULL                          |   16 |   100.00 | Using index |
+----+-------------+------------+------------+-------+--------------------------------------------+-------------------------+---------+-------------------------------+------+----------+-------------+
3 rows in set (0.00 sec)

이 rewrite는 dependent lookup을 제거하는 대신 주문 집계와 materialization 비용을 부담할 수 있다. Outer 대상 고객이 세 명뿐이라면 인덱스 기반 dependent lookup이 더 저렴할 수 있고, 전체 고객을 한꺼번에 조회한다면 선집계가 유리할 수 있다. SQL 모양만으로 결론을 내리지 말고 실제 cardinality와 EXPLAIN ANALYZE의 loops를 비교해야 한다.

또한 rewrite가 항상 의미적으로 동일한지 확인해야 한다. LEFT JOIN과 scalar aggregate의 NULL 처리, 중복 customer row, 필터 적용 시점, transaction snapshot은 원래 SQL과 같아야 한다.

7. 실행 계획을 읽는 순서

Subquery가 포함된 SQL은 다음 순서로 읽으면 진단이 빨라진다.

7.1 Query block이 남았는지 확인한다

Traditional EXPLAIN의 id와 select_type을 먼저 본다.

모두 SIMPLE로 합쳐졌다면 semi-join 또는 다른 flattening 변환 가능성을 확인한다.
MATERIALIZED가 있으면 어느 subquery가 중간 결과로 만들어지는지 본다.
DEPENDENT SUBQUERY가 있으면 어떤 outer column에 의존하는지 찾는다.
UNCACHEABLE SUBQUERY가 있으면 비결정적 표현식이나 외부 상태 의존성을 점검한다.

단, select_type 하나만으로 최종 물리 전략을 모두 알 수는 없다. EXPLAIN FORMAT=TREE와 EXPLAIN ANALYZE를 함께 사용하면 iterator의 materialization, lookup, 실제 rows와 loops를 더 직접적으로 볼 수 있다.

7.2 Outer 실제 rows와 loops를 확인한다

운영과 유사한 읽기 전용 환경에서 EXPLAIN ANALYZE를 사용할 때는 실제 SQL이 끝까지 실행된다는 점을 주의해야 한다. 큰 쿼리는 부하를 만들 수 있으며 DML에는 특히 신중해야 한다. 확인할 항목은 다음과 같다.

Outer iterator의 actual rows
Dependent inner iterator의 loops
Loop당 읽은 actual rows
추정 rows와 실제 rows의 차이
Materialize iterator의 생성 row 수와 재조회 횟수
예상하지 못한 table scan, temporary table, sort

7.3 인덱스는 inner predicate의 형태에 맞춘다

Dependent lookup의 인덱스는 단순히 상관 컬럼 하나만 포함한다고 충분하지 않을 수 있다. 다음 query shape를 함께 고려한다.

equality 상관 조건: o.customer_id = c.customer_id
추가 필터: o.status = 'PAID'
정렬 또는 최근 한 건: ORDER BY o.ordered_at DESC LIMIT 1
반환 컬럼과 covering 가능성

예를 들어 고객별 최근 결제 주문을 찾는다면 (customer_id, status, ordered_at) 순서가 후보가 된다. 그러나 status 선택도, 조회 기간, write amplification, 다른 쿼리와의 공용성까지 검토해야 하므로 예제 인덱스를 운영 표에 그대로 추가해서는 안 된다.

8. Performance Schema에서 문제 SQL을 좁히기

Execution plan은 한 statement의 구조를 설명하지만, 먼저 어떤 SQL을 분석할지 찾아야 한다. events_statements_summary_by_digest에서 호출 횟수, 총 rows examined, 반환 row 수를 함께 보면 반복 호출되거나 비효율적으로 많은 row를 읽는 후보를 좁힐 수 있다.

SELECT LEFT(DIGEST_TEXT, 90) AS digest_text,
       COUNT_STAR,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT,
       ROUND(SUM_TIMER_WAIT / 1000000000000, 6) AS total_seconds
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = DATABASE()
  AND DIGEST_TEXT IS NOT NULL
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 5;

실행 결과(MySQL 8.0.x):

mysql> SELECT LEFT(DIGEST_TEXT, 90) AS digest_text,
    ->        COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
    ->        ROUND(SUM_TIMER_WAIT / 1000000000000, 6) AS total_seconds
    -> FROM performance_schema.events_statements_summary_by_digest
    -> WHERE SCHEMA_NAME = DATABASE() AND DIGEST_TEXT IS NOT NULL
    -> ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 5;

+--------------------------------------------------------------------------------------------+------------+-------------------+---------------+---------------+
| digest_text                                                                                | COUNT_STAR | SUM_ROWS_EXAMINED | SUM_ROWS_SENT | total_seconds |
+--------------------------------------------------------------------------------------------+------------+-------------------+---------------+---------------+
| SELECT `c` . `customer_id` , `c` . `customer_name` FROM `sq_customers` AS `c` JOIN `sq_ord |          1 |                20 |             7 |        0.0002 |
| SELECT `c` . `customer_id` , `c` . `customer_name` FROM `sq_customers` AS `c` WHERE `c` .  |          1 |                10 |             4 |        0.0004 |
| SELECT @@`version_comment` LIMIT ?                                                         |          8 |                 8 |             8 |        0.0009 |
| SELECT `c` . `customer_id` , `c` . `customer_name` FROM `sq_customers` AS `c` WHERE `c` .  |          1 |                 6 |             2 |        0.0004 |
| SET SESSION `optimizer_switch` = ?                                                         |          3 |                 0 |             0 |        0.0001 |
+--------------------------------------------------------------------------------------------+------------+-------------------+---------------+---------------+
5 rows in set (0.00 sec)

이 조회만으로 dependent subquery를 자동 판정할 수는 없다. Digest는 statement 집계이며 내부 iterator의 loops를 직접 보여주지 않는다. 상위 후보의 실제 SQL과 실행 계획을 확보한 뒤 DEPENDENT SUBQUERY, MATERIALIZED, actual loops를 확인해야 한다. 또한 Performance Schema consumer와 instrument 설정, digest table 크기, reset 시점에 따라 수집 범위가 달라진다.

운영 진단에서는 다음 비율도 참고할 수 있다.

rows_examined_per_call = SUM_ROWS_EXAMINED / COUNT_STAR
rows_examined_per_row  = SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0)

두 값이 높다고 subquery가 원인이라고 단정할 수는 없지만, outer 반복과 inner full scan이 겹친 SQL은 두 지표에 흔적을 남길 가능성이 크다. 지표는 후보 선별에 사용하고 원인 판정은 실행 계획과 인덱스·데이터 분포 분석으로 수행한다.

9. 흔한 오해와 실패 패턴

9.1 “Correlated subquery는 항상 row-by-row로 실행된다”

MySQL 8.0은 일부 correlated EXISTS와 IN을 decorrelate하여 semi-join으로 바꿀 수 있다. SQL 문법의 상관 관계와 최종 실행 계획의 의존성은 같지 않다. 반드시 EXPLAIN으로 query block이 남았는지 확인한다.

9.2 “Subquery를 JOIN으로 바꾸면 무조건 빠르다”

일반 join은 중복 row를 만들 수 있다. 이를 DISTINCT로 덮으면 추가 정렬·temporary 비용이 생기고 원래 의도가 흐려질 수 있다. Semi-join 변환이 이미 적용된 SQL을 수동 join으로 바꿔도 이익이 없을 수 있다.

9.3 “Materialization은 항상 메모리 캐시다”

Materialized 결과는 내부 임시 테이블이며 크기와 row 형태에 따라 메모리 또는 디스크 자원을 사용할 수 있다. 큰 결과는 생성 지연과 임시 저장 공간 압력을 유발한다. Created_tmp_tables, Created_tmp_disk_tables 같은 전역 집계만으로 특정 statement를 단정하지 말고 statement 계획과 세션·워크로드 변화를 함께 본다.

9.4 `NOT IN`과 `NOT EXISTS`를 같은 의미로 취급한다

Nullable column을 포함한 NOT IN은 UNKNOWN을 만들어 결과를 제거할 수 있다. Anti-join 형태로 다시 쓸 때는 inner key의 NULL 가능성과 원하는 업무 의미를 먼저 명시한다.

9.5 Hint와 optimizer switch를 영구 해법으로 고정한다

SEMIJOIN, NO_SEMIJOIN, SUBQUERY, NO_MERGE 계열 hint와 optimizer_switch는 비교 실험과 긴급 회피에 유용하다. 그러나 데이터 분포와 MySQL 버전이 바뀌면 과거에 유리했던 강제가 오히려 나빠질 수 있다. 통계, 인덱스, predicate, cardinality 오류를 먼저 고치고 강제 계획은 변경 근거와 제거 조건을 함께 관리한다.

10. Aurora MySQL에서의 운영 해석

Aurora MySQL도 MySQL 호환 옵티마이저와 EXPLAIN을 사용하므로 semi-join, materialization, dependent subquery의 기본 진단 원리는 같다. 다만 다음 차이를 운영 판단에 반영해야 한다.

Aurora MySQL의 정확한 지원 문법과 optimizer 동작은 선택한 major/minor engine version에 종속된다. Community MySQL의 최신 예제를 그대로 가정하지 말고 대상 cluster version에서 검증한다.
optimizer_switch 변경은 parameter group의 cluster·instance 적용 범위와 재부팅 필요 여부를 확인한다. 단일 SQL 비교는 우선 session 범위에서 수행한다.
Reader instance에서 실행 계획을 검증할 때도 writer와 같은 통계·데이터 snapshot을 보는지, replica lag 또는 workload 차이가 있는지 확인한다.
Performance Insights와 Enhanced Monitoring은 문제 statement와 대기 유형을 좁히는 데 유용하지만, semi-join 전략이나 dependent loops 자체는 EXPLAIN/EXPLAIN ANALYZE로 확인해야 한다.
Materialization으로 임시 작업량이 늘면 로컬 임시 스토리지와 메모리 압력을 함께 관찰한다. Aurora의 분산 저장 계층이 모든 내부 임시 작업 비용을 없애 주는 것은 아니다.
버전 업그레이드 전에는 대표 subquery SQL의 plan regression을 별도 검증한다. 옵티마이저 개선으로 전략 선택이 달라질 수 있기 때문이다.

11. 운영 점검표

SQL 의미와 변환 가능성

IN, EXISTS, scalar subquery가 표현하는 cardinality와 NULL `IN`, `EXISTS`, scalar subquery가 표현하는 cardinality와 `NULL` 의미를 문서화했는가?
일반 join으로 rewrite할 때 outer row 중복이 생기지 않는지 확인했는가?
Semi-join을 막는 집계, UNION, LIMIT Semi-join을 막는 집계, `UNION`, `LIMIT`, window function 등 query block 경계가 있는가?
비결정적 표현식이나 side effect 때문에 결과 재사용이 제한되는가?

실행 계획

Traditional EXPLAIN에서 SIMPLE, MATERIALIZED, DEPENDENT SUBQUERY Traditional `EXPLAIN`에서 `SIMPLE`, `MATERIALIZED`, `DEPENDENT SUBQUERY`를 확인했는가?
EXPLAIN FORMAT=TREE에서 Materialize `EXPLAIN FORMAT=TREE`에서 `Materialize`, lookup, scan 구조를 확인했는가?
안전한 환경에서 EXPLAIN ANALYZE 안전한 환경에서 `EXPLAIN ANALYZE`의 outer actual rows와 inner loops를 비교했는가?
추정 rows와 실제 rows 차이가 크다면 통계와 데이터 skew를 점검했는가?

인덱스와 자원

Correlated predicate의 equality 컬럼이 inner index 선두에서 지원되는가?
추가 필터, 정렬, LIMIT 1 추가 필터, 정렬, `LIMIT 1`, covering 요구까지 인덱스 순서에 반영했는가?
Materialized 중간 결과의 크기와 temporary storage 압력을 추정했는가?
새 인덱스의 쓰기 비용과 중복 인덱스 가능성을 검토했는가?

변경과 검증

Rewrite 전후 결과 집합이 NULL Rewrite 전후 결과 집합이 `NULL`, 중복, outer join 조건까지 동일한가?
운영과 유사한 데이터 분포에서 latency뿐 아니라 rows examined와 loops를 비교했는가?
Hint나 optimizer_switch Hint나 `optimizer_switch`를 사용했다면 적용 범위와 제거 조건을 기록했는가?
MySQL 또는 Aurora engine version 변경 전후 대표 계획을 회귀 검증했는가?

12. 정리

Subquery 성능은 중첩 문법 자체보다 query block 사이의 의존성이 최종 계획에서 어떻게 처리되는지에 달려 있다. Semi-join은 존재 여부 조건을 하나의 조인 탐색 공간으로 바꾸고, materialization은 반복 가능한 결과를 한 번 생성해 lookup으로 전환하며, dependent subquery는 outer row 수만큼 inner 비용이 증폭될 가능성을 남긴다.

진단의 출발점은 무조건적인 rewrite가 아니다. select_type으로 경계를 확인하고, TREE 계획으로 물리 구조를 읽고, 안전한 환경에서 실제 rows와 loops를 측정해야 한다. 그다음 결과 의미를 보존하면서 인덱스, predicate, 선집계 또는 join rewrite를 비교한다. 다음 기술노트에서는 이 판단을 더 깊게 하기 위해 옵티마이저가 사용하는 비용 추정과 cardinality 오차가 조인 순서와 접근 경로에 어떤 영향을 주는지 살펴본다.

1. Subquery 최적화의 핵심은 실행 경계를 바꾸는 것이다

2. Semi-join은 “존재 여부 조인”이다

2.1 주요 semi-join 전략

2.2 변환을 제한하는 구조

3. 재현 환경 만들기

4. EXISTS가 semi-join으로 변환되는지 확인하기

5. Materialization은 반복 계산을 lookup으로 바꾼다

6. Dependent subquery의 비용은 outer rows에 의해 증폭된다

6.1 Aggregate join으로 다시 쓰는 선택지

7. 실행 계획을 읽는 순서

7.1 Query block이 남았는지 확인한다

7.2 Outer 실제 rows와 loops를 확인한다

7.3 인덱스는 inner predicate의 형태에 맞춘다

8. Performance Schema에서 문제 SQL을 좁히기

9. 흔한 오해와 실패 패턴

9.1 “Correlated subquery는 항상 row-by-row로 실행된다”

9.2 “Subquery를 JOIN으로 바꾸면 무조건 빠르다”

9.3 “Materialization은 항상 메모리 캐시다”

9.4 NOT IN과 NOT EXISTS를 같은 의미로 취급한다

9.5 Hint와 optimizer switch를 영구 해법으로 고정한다

10. Aurora MySQL에서의 운영 해석

11. 운영 점검표

SQL 의미와 변환 가능성

실행 계획

인덱스와 자원

변경과 검증

12. 정리

9.4 `NOT IN`과 `NOT EXISTS`를 같은 의미로 취급한다