[MySQL] 아직도 IN 연산자만 고집하는 엔지니어를 위한 EXISTS 파헤치기

선상원·2025년 2월 1일

mysql

목록 보기

11/12

🎯 들어가며

오늘의 주제는 “EXISTS 활용하기” 입니다.

현재 근무하고 있는 직장에서는 "쿼리문 검수 및 튜닝 업무"를 수행하고 있습니다.
서비스의 규모가 커지면 기존의 애플리케이션단에서 사용하던 쿼리는 점점 느려지고, 이는 서버 부하 및 고객에게 부정적인 경험으로 연결됩니다.

엔지니어가 작성한 쿼리문을 확인하면 대부분의 경우 JOIN 또는 WHERE 절에 IN 연산자를 사용하여 서브 쿼리를 작성하는 것을 볼 수 있었습니다. 다른 테이블의 데이터를 참조하여 조회할 때는 다양한 방법을 사용할 수 있지만,
조회하고자 하는 테이블의 데이터 양과 분포에 따라 최적화 방법이 달라집니다.

📚 기본 예제로 EXISTS 이해하기

예제 테이블

테이블: emp

empno	ename	hiredate	deptno
100	KING	1990-01-01	NULL
101	SANGWON	1996-05-23	10
102	HYOJU	1996-09-11	10
103	HYUNSU	1992-09-03	20
104	MINJU	2000-05-05	20
105	SANGWOO	1997-02-01	30

테이블: dept

deptno	dname
10	Engineer
20	Common Team
30	CS Team
40	Marketing

  ### EXISTS 예제 쿼리
  select a.*
    from test.emp a
   where exists (
                 select 1
                   from test.dept b
                  where a.deptno = b.deptno
                    and b.deptno = 10
                )
  ;

-- 결과: 2건
-- 101 SANGWON 1996-05-23 10
-- 102 HYOJU   1996-09-11 10

💼 실전 시나리오: 주문-상품 데이터 분석

다음과 같은 테이블 구조를 가정해보겠습니다.

📊 테이블 구성

▶ 테이블명: 상품
▶ 데이터: 10,000건

CREATE TABLE 상품 (
    item_id INT NOT NULL AUTO_INCREMENT,
    item_code VARCHAR(20) NOT NULL,
    create_date DATETIME NOT NULL DEFAULT NOW(),
    ...
    PRIMARY KEY (item_id),
    UNIQUE (item_code),
    KEY (create_date)
);

▶ 테이블명: 주문
▶ 데이터: 10,000,000건

CREATE TABLE 주문 (
    order_id INT NOT NULL AUTO_INCREMENT,
    order_code VARCHAR(20) NOT NULL,
    item_code VARCHAR(20) NOT NULL,
    ...
    create_date DATETIME NOT NULL DEFAULT NOW(),
    PRIMARY KEY (order_id),
    UNIQUE (order_code),
    KEY (create_date)
);

🎯 비즈니스 요구사항

"2023년 1년 동안 등록되었던 상품을 주문한 주문 건수를 조회하라"

데이터 분포 가정
▶ 전체 상품: 10,000건
▶ 2023년 등록 상품: 2,000건 (20%)

▶ 전체 주문: 10,000,000건
▶ 2023년 상품에 대한 주문: 약 2,000,000건 (평균 상품당 1,000건)
이 요구사항을 구현하는 3가지 쿼리 방식을 비교해보겠습니다.

🔍 쿼리 비교 및 실행 계획 분석

쿼리 1: INNER JOIN 방식

SELECT COUNT(DISTINCT a.order_code)
  FROM 주문 a
  JOIN 상품 b ON a.item_code = b.item_code
 WHERE b.create_date >= '2023-01-01'
   AND b.create_date < '2024-01-01';

실행 계획 (EXPLAIN)

EXPLAIN FORMAT=TREE
SELECT COUNT(DISTINCT a.order_code)
  FROM 주문 a
  JOIN 상품 b ON a.item_code = b.item_code
 WHERE b.create_date >= '2023-01-01'
   AND b.create_date < '2024-01-01';

분석
▶ MySQL은 기본적으로 Nested Loop Join을 사용
▶ 상품 테이블(1만 건)을 먼저 필터링 → 2,000건
▶ 각 상품별로 주문 테이블 인덱스 룩업 → 총 200만 건 조인 결과
▶ DISTINCT 처리로 인한 추가 오버헤드 발생

문제점
1. 대량의 중간 결과 생성: 200만 건의 조인 결과가 메모리에 생성됨
2. DISTINCT 오버헤드: 200만 건에서 중복 제거 작업 필요
3. 메모리 압박: 임시 테이블 생성으로 인한 메모리 사용량 증가

예상 실행 흐름
1. 상품 테이블 스캔: idx_item_code_create_date 인덱스 사용
▶ 2023년 상품 2,000건 추출 (빠름)
2. 각 상품에 대해 주문 테이블 조인
▶ 2,000개 상품 × 평균 1,000건 주문 = 200만 건 조인 결과
3. DISTINCT 처리
▶ 200만 건에서 중복 제거
▶ 임시 테이블 생성 가능
4. COUNT 집계

✅쿼리 2: EXISTS 방식 (권장)

SELECT COUNT(*)
  FROM 주문 a
 WHERE EXISTS (
               SELECT 1
                 FROM 상품 b
                WHERE a.item_code = b.item_code
                  AND b.create_date >= '2023-01-01'
                  AND b.create_date < '2024-01-01'
              );

실행 계획 (EXPLAIN)

EXPLAIN FORMAT=TREE
SELECT COUNT(*)
  FROM 주문 a
 WHERE EXISTS (
               SELECT 1
                 FROM 상품 b
                WHERE a.item_code = b.item_code
                  AND b.create_date >= '2023-01-01'
                  AND b.create_date < '2024-01-01'
              );

-- 결과 예시:
-- -> Aggregate: count(0)  (cost=1005245.00 rows=1)
--     -> Filter: exists(select #2)  (cost=1005245.00 rows=5000000)
--         -> Table scan on a  (cost=1005245.00 rows=10000000)
--         -> Index lookup on b using idx_item_code_create_date
--            (item_code=a.item_code), with index condition:
--            ((b.create_date >= '2023-01-01') and (b.create_date < '2024-01-01'))
--            (cost=0.25 rows=0.2)

주문 테이블의 각 행(1만 건)에 대해
1. 서브쿼리 실행: 상품 테이블에서 해당 item_code + 날짜 조건 검색
2. 인덱스 룩업: idx_item_code_create_date 복합 인덱스 활용
3. 첫 번째 매칭 발견 시 즉시 TRUE 반환 (Short-circuit evaluation)
4. 매칭 없으면 FALSE, 다음 주문 행으로 이동
▶ 결과: 2023년 상품 주문만 카운트에 포함

장점
1. 조기 종료 (Short-circuit): 매칭되는 첫 번째 행을 발견하면 즉시 종료
2. 불필요한 데이터 생성 없음: JOIN처럼 200만 건의 중간 결과를 생성하지 않음
3. 인덱스 활용 최적화: idx_item_code_create_date 복합 인덱스 활용
4. DISTINCT 불필요: 주문 테이블의 각 행당 한 번만 판단
5. 메모리 효율: 임시 테이블 생성 없음

성능 추정 (실제 환경 기준):
인덱스 룩업 비용:
▶ 주문 1천만 건 × 인덱스 룩업(~0.01ms) = 약 100초 (이론상)
▶ 실제 캐시 효과로 20-30초 가능

vs JOIN 방식:
▶ 200만 건 조인 결과 생성 = 약 10-15초
▶ DISTINCT 처리 (200만 건) = 약 15-25초
▶ 총 25-40초 + 메모리 부담

쿼리 3: IN 방식

SELECT COUNT(*)
  FROM 주문 a
 WHERE a.item_code IN (
                       SELECT b.item_code
                         FROM 상품 b
                        WHERE b.create_date >= '2023-01-01'
                          AND b.create_date < '2024-01-01'
                      );

실행 계획 (EXPLAIN)

EXPLAIN FORMAT=TREE
SELECT COUNT(*)
  FROM 주문 a
 WHERE a.item_code IN (
                       SELECT b.item_code
                         FROM 상품 b
                        WHERE b.create_date >= '2023-01-01'
                          AND b.create_date < '2024-01-01'
                      );

-- 결과 예시 (MySQL 8.0.18+):
-- -> Aggregate: count(0)  (cost=1005245.00 rows=1)
--     -> Filter: (a.item_code in (select #2))  (cost=1005245.00 rows=5000000)
--         -> Table scan on a  (cost=1005245.00 rows=10000000)
--         -> Materialize with deduplication  -- 또는 First Match
--            (cost=202.50..202.50 rows=2000)
--            -> Index range scan on b using idx_item_code_create_date
--               (cost=202.50 rows=2000)

MySQL 8.0의 세미 조인 최적화
MySQL 8.0부터는 IN 서브쿼리에 대해 다양한 세미 조인 전략을 사용합니다
1. First Match: EXISTS와 유사하게 첫 매칭 발견 시 중단
2. Materialization: 서브쿼리 결과를 임시 테이블로 구체화
3. Duplicate Weedout: 중복 제거 전략
4. LooseScan: 인덱스 스캔 최적화
5. Table Pull-out: 서브쿼리를 조인으로 변환

위 케이스에서 예상되는 옵티마이저 선택

-- Materialization 전략 선택 가능성이 높음
-- 이유: 서브쿼리 결과(2,000건)가 작아서 해시 테이블 생성이 효율적

실행 순서:
1. 서브쿼리 실행: 2023년 상품 2,000건 추출
2. 임시 해시 테이블 생성: 2,000개 item_code
3. 주문 1천만 건 스캔하며 해시 테이블 룩업
4. 매칭되는 주문 카운트 (약 200만 건)

문제점과 고려사항
MySQL 8.0의 세미 조인 최적화가 IN 연산자에도 적용되어 많은 경우 EXISTS와 유사한 성능을 보입니다. 하지만 다음과 같은 이유로 EXISTS 사용을 권장합니다

옵티마이저 의존성: 통계 정보의 부정확성으로 인한 잘못된 실행 계획 선택 가능

-- 예시: 통계가 오래된 경우
   -- 옵티마이저가 Materialization을 선택할 수 있음
   -- (실제로는 First Match가 더 효율적인 상황에서도)

대용량 주문 테이블 스캔 부담

-- Materialization 전략 시:
   -- 주문 1천만 건 전체 스캔 → 각 행마다 해시 테이블 룩업
   -- vs EXISTS: 인덱스 활용으로 스캔 범위 제한 가능

명시적 의도 표현: EXISTS는 "존재 여부 확인"이라는 의도를 명확히 표현

-- EXISTS: 명확하게 "존재하는가?"를 질문
   WHERE EXISTS (SELECT 1 FROM ...)
   
   -- IN: "값이 집합에 포함되는가?"를 질문
   -- (옵티마이저가 세미 조인으로 변환)
   WHERE item_code IN (SELECT ...)

예측 가능성: EXISTS는 항상 세미 조인 방식으로 동작
▶ IN은 옵티마이저가 상황에 따라 다른 전략 선택 가능
▶ 통계 정보 변화, 데이터 분포 변경 시 실행 계획이 달라질 수 있음

⚠️ 옵티마이저와 통계 정보

옵티마이저는 통계 정보와 인덱스를 기반으로 실행 계획을 결정합니다.
하지만 다음과 같은 상황에서 최적이 아닌 선택을 할 수 있습니다

📉 통계 정보 불일치 시나리오

-- 시나리오: 대량의 주문 데이터 정리 작업 후
DELETE FROM 주문 WHERE create_date < '2020-01-01';
-- 1천만 건 → 500만 건으로 감소

-- 하지만 통계 정보는 업데이트되지 않음
SELECT TABLE_ROWS 
FROM information_schema.TABLES 
WHERE TABLE_NAME = '주문';
-- 여전히 10,000,000으로 표시될 수 있음

-- 실제 데이터
SELECT COUNT(*) FROM 주문;
-- 5,000,000건

문제 발생
▶ 옵티마이저는 여전히 상품 테이블이 100만 건이라고 판단
▶ Materialization 전략 선택 (임시 테이블 생성)
▶ 실제로는 First Match가 더 효율적인 상황

해결 방법

-- 1. 통계 정보 갱신
ANALYZE TABLE 주문;
ANALYZE TABLE 상품;

-- 2. 통계 정보 확인
SELECT 
    TABLE_NAME,
    TABLE_ROWS,
    AVG_ROW_LENGTH,
    DATA_LENGTH
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME IN ('주문', '상품');

-- 3. 또는 명시적으로 EXISTS 사용하여 의도 명확화
WHERE EXISTS (...)

이처럼 옵티마이저는 예측 가능하게 동작하지만,
통계 정보와 실제 데이터의 불일치가 발생하면 차선의 선택을 할 수 있습니다.
EXISTS를 사용하면 이러한 변수를 줄이고 의도를 명확히 표현할 수 있습니다.

⚡ Hash Join

🆕 MySQL 8.0.18 이후의 Hash Join

"MySQL은 기본적으로 Nested Loop Join만 사용한다"는 과거의 이야기입니다.
MySQL 8.0.18부터는 Hash Join이 도입되어 조인 성능이 크게 개선되었습니다.

Hash Join 지원 현황
▶ MySQL 8.0.18 (2019년 10월): Inner Hash Join 도입
▶ MySQL 8.0.20 (2020년 4월): Outer, Semi, Anti Join에도 Hash Join 지원 확대
▶ MySQL 8.0.19 ~ 현재 (8.4): 계속 지원 및 개선

Hash Join 동작 확인

EXPLAIN FORMAT=TREE
SELECT COUNT(DISTINCT a.order_code)
  FROM 주문 a
  JOIN 상품 b ON a.item_code = b.item_code
 WHERE b.create_date >= '2023-01-01'
   AND b.create_date < '2024-01-01';

-- 결과 예시 (인덱스가 없거나 옵티마이저가 Hash Join 선택 시):
-- -> Aggregate: count(distinct a.order_code)
--     -> Inner hash join (b.item_code = a.item_code)
--         -> Filter: ((b.create_date >= '2023-01-01') 
--                     and (b.create_date < '2024-01-01'))
--             -> Table scan on b
--         -> Hash
--             -> Table scan on a

Hash Join의 장점
1. 대량 데이터 조인 시 Nested Loop보다 효율적
2. 메모리 내 해시 테이블 활용으로 빠른 매칭
3. equi-join 조건에서 최적화

그렇다면 JOIN도 괜찮을까?
상황에 따라 다릅니다.
▶ Hash Join 사용 가능 시 (인덱스 없음): JOIN 방식도 효율적
▶ 인덱스 사용 가능 시: Nested Loop with Index가 선택될 수 있음
▶ DISTINCT 필요 시: EXISTS가 여전히 유리 (중간 결과 없음)

❗ NOT EXISTS vs NOT IN 중요한 차이점

EXISTS와 IN은 긍정 조건에서는 비슷하지만, 부정 조건(NOT)에서는 큰 차이가 있습니다.

NULL 값 처리의 함정

-- 테스트 데이터
CREATE TABLE test_orders (
   order_id INT, item_code VARCHAR(20)
);

CREATE TABLE test_items (item_code VARCHAR(20));

INSERT INTO test_orders VALUES (1, 'A'), (2, 'B'), (3, 'C');
INSERT INTO test_items VALUES ('A'), (NULL);

-- NOT IN 사용 (위험!)
SELECT * 
  FROM test_orders
 WHERE item_code NOT IN (SELECT item_code FROM test_items);
-- 결과: 0건 (예상과 다름!)

-- NOT EXISTS 사용 (안전)
SELECT * 
  FROM test_orders a
 WHERE NOT EXISTS (
                   SELECT 1 
                     FROM test_items b 
                    WHERE a.item_code = b.item_code
                  );
-- 결과: 2건 (order_id 2, 3) ✅

왜 NOT IN은 0건을 반환할까?
▶ SQL의 3-valued logic (TRUE, FALSE, UNKNOWN) 때문입니다:

-- NOT IN의 내부 동작
WHERE item_code NOT IN ('A', NULL)
-- 전개하면:
WHERE item_code <> 'A' AND item_code <> NULL
-- NULL과의 비교는 항상 UNKNOWN
-- 'B' <> NULL → UNKNOWN
-- 'C' <> NULL → UNKNOWN
-- UNKNOWN AND TRUE → UNKNOWN
-- WHERE 절은 TRUE만 통과 → 결과 없음

📝 권장

-- ❌ 위험: NOT IN
WHERE item_code NOT IN (SELECT item_code FROM 상품)

-- ✅ 안전: NOT EXISTS
WHERE NOT EXISTS (
                 SELECT 1 
                   FROM 상품 b 
                  WHERE a.item_code = b.item_code
                 )

-- ✅ 또는 NULL 명시적 제외
WHERE item_code NOT IN (
                        SELECT item_code 
                          FROM 상품 
                         WHERE item_code IS NOT NULL
                       )

🎯 각 방식의 적합한 사용 사례

✅ EXISTS 사용을 권장하는 경우

1. 서브쿼리 결과가 많을 때

-- 상품 100만 건 중 2023년 데이터 10만 건
   WHERE EXISTS (
                 SELECT 1 
                   FROM 상품 b 
                  WHERE a.item_code = b.item_code
                    AND b.create_date >= '2023-01-01'
   )

2. 존재 여부만 확인하면 될 때

-- "이 주문이 처리된 상품인가?"
   WHERE EXISTS (SELECT 1 FROM 상품 b ...)

3. 부정 조건 (NOT EXISTS) 사용 시

-- NULL 안전 보장
   WHERE NOT EXISTS (...)

4. 복합 조건 체크 시

WHERE EXISTS (
              SELECT 1 
                FROM 상품 b
               WHERE a.item_code = b.item_code
                 AND b.stock > 0
                 AND b.status = 'ACTIVE'
                 AND b.create_date >= '2023-01-01'
             )

📝 IN 사용을 권장하는 경우

1. 서브쿼리 결과가 적고 명확할 때

-- 특정 카테고리 3개
   WHERE category_id IN (1, 2, 3)
   
   -- 또는 결과가 10건 이하인 서브쿼리
   WHERE dept_id IN (
                     SELECT dept_id 
                       FROM departments 
                      WHERE region = 'APAC'  -- 결과: 3건
                    )

2. 값 목록이 정적일 때

WHERE status IN ('PENDING', 'PROCESSING', 'SHIPPED')

3. 가독성이 중요하고 성능 차이가 미미할 때

-- 직관적인 표현
   WHERE item_code IN (SELECT item_code FROM featured_items)

🔗 JOIN 사용을 권장하는 경우

1. 서브쿼리 테이블의 다른 컬럼도 필요할 때

-- 상품명, 가격 등도 함께 조회
   SELECT a.order_code, b.item_name, b.price
     FROM 주문 a
     JOIN 상품 b ON a.item_code = b.item_code
    WHERE b.create_date >= '2023-01-01'

2. 집계 함수에서 조인 테이블 값 사용 시

SELECT a.order_date, SUM(b.price) as total_price
  FROM 주문 a
  JOIN 상품 b ON a.item_code = b.item_code
 GROUP BY a.order_date

3. Hash Join이 효율적인 대량 조인

-- 두 대용량 테이블 조인 (인덱스 없음)
SELECT /*+ NO_BNL(a, b) */ a.*, b.*
  FROM large_table_a a IGNORE INDEX (idx)
  JOIN large_table_b b IGNORE INDEX (idx) ON a.key = b.key

🎓 결론

⚠️ EXISTS는 강력한 성능 최적화 도구이지만, 만능은 아닙니다.

📌 핵심 정리

EXISTS의 강점
▶ 조기 종료(Short-circuit)로 불필요한 스캔 방지
▶ 중간 결과 집합 생성 없음
▶ NOT EXISTS의 NULL 안전성
IN의 쓰임
▶ 소량의 명확한 값 목록
▶ MySQL 8.0의 세미 조인 최적화 활용
▶ 단, 옵티마이저 의존성 고려 필요
JOIN의 재평가
▶ Hash Join 시대의 성능 개선
▶ 다른 컬럼 참조 시 필수
▶ 인덱스 전략과 함께 고려

🎯 최종 권장사항

쿼리 선택 플로우차트

1. 서브쿼리 테이블의 다른 컬럼이 필요한가?
   YES → JOIN 사용
   NO → 2번으로

2. 존재 여부만 확인하는가?
   YES → EXISTS 사용 (권장)
   NO → 3번으로

3. 서브쿼리 결과가 10건 이하의 명확한 값인가?
   YES → IN 사용 가능
   NO → EXISTS 사용 (권장)

4. NOT 조건인가?
   YES → NOT EXISTS 사용 (필수)
   NO → 상황에 따라 선택

DBA의 업무는 단순히 쿼리를 빠르게 만드는 것이 아니라, 데이터의 특성과 비즈니스 요구사항을 이해하고 최적의 균형점을 찾는 것입니다.
호출 빈도, 데이터 양, 인덱스 전략, 하드웨어 리소스 등 다양한 요소를 종합적으로 고려하여 판단해야 하며,
끊임없는 학습과 실험을 통해 어떤 상황에서도 최선의 쿼리를 작성할 수 있도록 노력해야 합니다.

💡 핵심 포인트

▶ EXISTS는 조건에 맞는 첫 번째 데이터를 찾으면 즉시 반환 - 불필요한 스캔 방지 및 메모리 효율
▶ 대용량 서브쿼리 테이블 참조 시 EXISTS 사용 권장 - JOIN은 중간 결과(200만 건) 생성 부담
▶ IN 연산자는 MySQL 8.0의 세미 조인 최적화가 적용되지만 통계 정보 의존성 존재 - EXISTS가 예측 가능하고 안정적
▶ NOT 조건에서는 반드시 NOT EXISTS 사용 - NOT IN은 NULL 값 존재 시 결과 없음 (치명적 버그)
▶ 통계 정보 갱신(ANALYZE TABLE)과 인덱스 전략이 핵심 - 옵티마이저의 올바른 판단을 위한 필수 작업
▶ 상황에 따른 최적의 방법 선택 - 다른 컬럼 필요 시 JOIN, 소량 값 목록은 IN, 존재 여부 확인은 EXISTS

선상원

쉼 없는 고민과 학습을 통해 가장 효율적인 데이터베이스 관리 방안을 찾고자 노력하는 DBA 입니다.

[MySQL] 아직도 IN 연산자만 고집하는 엔지니어를 위한 EXISTS 파헤치기

mysql

🎯 들어가며

📚 기본 예제로 EXISTS 이해하기

예제 테이블

테이블: emp

테이블: dept

💼 실전 시나리오: 주문-상품 데이터 분석

📊 테이블 구성

🎯 비즈니스 요구사항

"2023년 1년 동안 등록되었던 상품을 주문한 주문 건수를 조회하라"

🔍 쿼리 비교 및 실행 계획 분석

쿼리 1: INNER JOIN 방식

실행 계획 (EXPLAIN)

✅쿼리 2: EXISTS 방식 (권장)

실행 계획 (EXPLAIN)

쿼리 3: IN 방식

실행 계획 (EXPLAIN)

⚠️ 옵티마이저와 통계 정보

📉 통계 정보 불일치 시나리오

⚡ Hash Join

🆕 MySQL 8.0.18 이후의 Hash Join

❗ NOT EXISTS vs NOT IN 중요한 차이점

NULL 값 처리의 함정

📝 권장

🎯 각 방식의 적합한 사용 사례

✅ EXISTS 사용을 권장하는 경우

📝 IN 사용을 권장하는 경우

🔗 JOIN 사용을 권장하는 경우

🎓 결론

📌 핵심 정리

🎯 최종 권장사항

💡 핵심 포인트

[MySQL] PK, 어떤 데이터 타입이 제일 적절할까?

[MySQL] SQL문 최적화

0개의 댓글