- Published on
MySQL·PostgreSQL에서 느린 IN 쿼리, JOIN이 더 빠른 이유
- Authors
- Name
- 스타차일드
- https://x.com/ETFBITX
서비스에서 종종 마주치는 패턴이 있습니다. 애플리케이션이 어떤 ID 목록을 만들고, 그 목록을 IN (...) 으로 넘겨 한 번에 조회합니다. 데이터가 작을 때는 잘 동작하지만, 목록이 커지거나 트래픽이 늘면 갑자기 쿼리가 느려지고 CPU가 치솟습니다. 반면 같은 조건을 JOIN으로 풀었더니 오히려 빨라지는 경우가 많습니다.
이 현상은 “IN은 느리고 JOIN은 빠르다” 같은 단순한 규칙 때문이 아니라, 옵티마이저가 목록을 처리하는 방식(정렬/해시/반복 루프), 통계 정확도, 인덱스 사용 가능성, 그리고 중간 결과의 크기가 맞물리면서 발생합니다. MySQL과 PostgreSQL 모두에서 재현되지만, 내부 전략은 조금씩 다릅니다.
왜 큰 IN 리스트가 느려질까
1) 파싱·플래닝 비용과 플랜 캐시 비효율
애플리케이션이 매번 다른 길이의 IN 리스트를 만들면 SQL 텍스트 자체가 매번 달라집니다. 그러면 DB는 다음 비용을 반복해서 지불할 수 있습니다.
- SQL 파싱 및 최적화(플래닝)
- 상수 목록 처리(정렬/중복 제거/자료형 정규화)
- 플랜 캐시(준비된 문/서버 캐시) 재사용 실패
특히 PostgreSQL은 바인드 파라미터를 잘 쓰면 플랜 재사용이 되지만, “리터럴로 박힌 긴 IN” 은 매번 새 쿼리로 취급되는 경우가 많습니다.
2) 선택도 추정 실패로 인한 잘못된 조인 순서
옵티마이저는 “이 조건으로 몇 건이 나올지(카디널리티)”를 추정해 조인 순서와 인덱스 사용을 결정합니다. 그런데 큰 IN 리스트는 통계만으로 정확히 예측하기 어려워서 다음이 흔히 발생합니다.
- 실제로는 결과가 매우 적은데, 많이 나올 거라 추정해 풀스캔/해시 조인을 선택
- 실제로는 결과가 많은데, 적게 나올 거라 추정해 비효율적인 중첩 루프를 선택
통계가 오래되었거나 데이터 분포가 치우친 경우(핫 키)에는 더 심해집니다. PostgreSQL에서는 통계/오토베큠 상태가 특히 중요하니, 관련 점검은 PostgreSQL VACUUM 안 도는 이유 7가지와 해법도 함께 참고하면 좋습니다.
3) 인덱스가 있어도 “랜덤 I/O 폭탄” 이 될 수 있음
WHERE id IN (...) 은 인덱스를 탈 가능성이 높지만, 목록이 커지면 결국 인덱스 룩업을 수천~수만 번 반복하게 됩니다.
- 각 룩업은 랜덤 페이지 접근
- 버퍼캐시 히트가 낮으면 디스크/스토리지 I/O 증가
- 결과를 모으는 과정에서 정렬/머지 비용 추가
반면 JOIN으로 바꾸면, DB가 더 큰 단위로 접근하거나(비트맵/머지), 해시 테이블을 만들어 한 번에 매칭하는 등 더 효율적인 전략을 선택할 여지가 생깁니다.
JOIN이 더 빨라지는 핵심 메커니즘
1) “목록” 을 테이블로 만들면 옵티마이저가 진짜 조인 문제로 풀 수 있음
IN 리스트는 논리적으로는 “집합 포함” 이지만, 물리적으로는 DB가 여러 방식으로 구현합니다. 목록을 **테이블 형태(임시 테이블, CTE, derived table)**로 제공하면, 옵티마이저는 다음을 할 수 있습니다.
- 조인 알고리즘 선택(해시 조인, 머지 조인, 중첩 루프)
- 조인 순서 재배치
- 중복 제거 및 통계 기반 비용 계산
즉, “상수 나열” 보다 “관계(테이블) 간 조인”으로 표현할수록 DB가 최적화하기 쉬워집니다.
2) 해시 조인(특히 PostgreSQL)로 한 번에 매칭
PostgreSQL은 해시 조인을 적극적으로 사용합니다. ID 목록을 작은 테이블로 만들면, DB는 목록을 해시 테이블로 빌드하고 큰 테이블을 스캔하며 매칭하는 방식으로 빠르게 처리할 수 있습니다.
IN리스트가 커질수록 해시 조인이 유리해질 수 있음- 중첩 루프의 반복 인덱스 룩업보다 CPU/메모리 친화적
다만 해시 조인은 work_mem 설정이 작으면 디스크 스필이 발생할 수 있어, 실행 계획에서 Hash / Hash Join 과 Disk 사용 여부를 확인해야 합니다.
3) MySQL에서는 세미조인 최적화/derived merge 여부가 관건
MySQL은 IN (subquery) 형태에 대해 세미조인 변환, materialization 등의 최적화를 수행할 수 있습니다. 반면 “리터럴 IN 리스트”는 길이에 따라 처리 비용이 커지며, 옵티마이저가 선택할 수 있는 전략이 제한될 수 있습니다.
실무에서는 MySQL에서도 ID 목록을 임시 테이블로 적재한 뒤 JOIN 하는 패턴이 가장 예측 가능하고 튜닝 포인트가 명확합니다.
재현 예시: IN vs JOIN
아래 예시는 주문 테이블에서 특정 사용자 집합의 최근 주문을 조회하는 상황을 가정합니다.
느려지기 쉬운 패턴: 큰 IN 리스트
SELECT o.id, o.user_id, o.created_at
FROM orders o
WHERE o.user_id IN (101, 205, 309, 412, ... /* 수천~수만 개 */)
AND o.created_at >= NOW() - INTERVAL 7 DAY
ORDER BY o.created_at DESC
LIMIT 100;
문제는 다음과 같습니다.
user_id인덱스가 있어도 룩업 반복이 커짐created_at조건과 결합될 때 인덱스 선택이 애매해짐ORDER BY ... LIMIT때문에 정렬/탑N 전략이 꼬일 수 있음
더 예측 가능한 패턴: 목록 테이블과 JOIN
PostgreSQL: unnest 로 목록을 테이블화
WITH ids AS (
SELECT unnest($1::bigint[]) AS user_id
)
SELECT o.id, o.user_id, o.created_at
FROM ids
JOIN orders o ON o.user_id = ids.user_id
WHERE o.created_at >= NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 100;
- 애플리케이션은
bigint[]배열 파라미터로 전달 - 옵티마이저는
ids를 작은 입력으로 보고 조인 전략을 고르기 쉬움
MySQL: 임시 테이블에 적재 후 JOIN
CREATE TEMPORARY TABLE tmp_user_ids (
user_id BIGINT PRIMARY KEY
) ENGINE=MEMORY;
-- 애플리케이션에서 bulk insert
INSERT INTO tmp_user_ids (user_id) VALUES (101), (205), (309);
SELECT o.id, o.user_id, o.created_at
FROM tmp_user_ids t
JOIN orders o ON o.user_id = t.user_id
WHERE o.created_at >= NOW() - INTERVAL 7 DAY
ORDER BY o.created_at DESC
LIMIT 100;
PRIMARY KEY로 중복 제거 및 조인 비용 감소- 필요하면
ENGINE=InnoDB로 바꿔 대량 목록도 안정적으로 처리
EXISTS가 더 나은 경우도 있다
JOIN으로 바꾸면 중복이 생길 수 있는 형태라면 EXISTS 가 더 안전합니다. 특히 “필터링만 하고 컬럼은 원본 테이블에서만 가져오는” 경우에 적합합니다.
SELECT o.id, o.user_id, o.created_at
FROM orders o
WHERE EXISTS (
SELECT 1
FROM tmp_user_ids t
WHERE t.user_id = o.user_id
)
AND o.created_at >= NOW() - INTERVAL 7 DAY;
PostgreSQL에서는 EXISTS 가 세미조인으로 최적화되는 경우가 많고, MySQL도 상황에 따라 세미조인 변환이 가능합니다.
인덱스 설계: IN이든 JOIN이든 결국 여기서 갈린다
1) 조인 키 인덱스는 기본
orders(user_id)또는 더 나은 복합 인덱스
2) 필터 + 정렬이 함께 있으면 복합 인덱스 고려
위 예시처럼 created_at 조건과 정렬이 함께 있으면 아래가 강력합니다.
- MySQL:
INDEX(user_id, created_at)또는 쿼리 패턴에 맞춘 순서 - PostgreSQL:
btree (user_id, created_at)+ 정렬 방향 고려
단, ORDER BY created_at DESC LIMIT 100 을 진짜로 빠르게 만들려면 “어떤 사용자 집합에 대한 최신 100개”인지에 따라 인덱스가 달라질 수 있습니다. 사용자 집합이 넓으면 created_at 중심, 좁으면 user_id 중심이 유리합니다.
3) 목록 테이블에도 인덱스(또는 PK)를 줘라
임시 테이블/CTE로 만든 목록이 커질수록, 그쪽에도 인덱스가 없으면 조인 시 불필요한 비용이 생깁니다.
- MySQL 임시 테이블:
PRIMARY KEY(user_id)권장 - PostgreSQL:
unnest는 인덱스를 만들 수 없으니, 정말 큰 목록이면TEMP TABLE로 옮기고 인덱스를 고려
실행 계획으로 확인하는 체크리스트
PostgreSQL
EXPLAIN (ANALYZE, BUFFERS)로 확인- 체크 포인트
Hash Join인지Nested Loop인지Rows추정과 실제(actual) 차이가 큰지BUFFERS에서shared hit대비read가 과도한지- 해시 조인에서
Disk스필이 있는지(work_mem이슈)
EXPLAIN (ANALYZE, BUFFERS)
WITH ids AS (
SELECT unnest(ARRAY[101,205,309]::bigint[]) AS user_id
)
SELECT o.id
FROM ids
JOIN orders o ON o.user_id = ids.user_id
WHERE o.created_at >= NOW() - INTERVAL '7 days';
MySQL
EXPLAIN과 가능하면EXPLAIN ANALYZE(8.0+) 활용- 체크 포인트
type이ALL로 떨어지는지(풀스캔)rows가 비정상적으로 큰지Using temporary,Using filesort가 뜨는지- 조인 순서가 기대와 맞는지
EXPLAIN ANALYZE
SELECT o.id
FROM tmp_user_ids t
JOIN orders o ON o.user_id = t.user_id
WHERE o.created_at >= NOW() - INTERVAL 7 DAY;
실무에서의 선택 가이드
1) IN을 써도 괜찮은 경우
- 목록이 매우 작고(예: 수십 개 이하) 쿼리가 자주 변하지 않음
- 플랜 캐시/준비된 문을 잘 활용하고 있음
- 인덱스 룩업 비용이 충분히 낮고(캐시 히트 높음) 병목이 아님
2) JOIN/임시 테이블로 바꾸는 게 유리한 경우
- 목록이 수천 개 이상으로 커짐
- 같은 패턴이 초당 여러 번 반복됨
IN리스트 길이가 매번 달라 플랜 재사용이 안 됨- 실행 계획에서 중첩 루프 + 대량 룩업, 또는 비효율적 정렬이 보임
3) PostgreSQL에서는 “배열 파라미터 + unnest” 를 먼저 고려
- 애플리케이션에서 바인딩이 쉽고, SQL 텍스트가 고정됨
- 계획 재사용 가능성이 높아짐
4) MySQL에서는 “임시 테이블 + bulk insert + join” 이 가장 안정적
- 대량 목록 처리에 예측 가능
- 목록을 재사용할 수 있으면 세션 단위 캐싱도 가능
흔한 함정: JOIN으로 바꿨는데 더 느려진다
- 목록 테이블에 인덱스/PK가 없음
- 조인 후 중복이 늘어 결과가 폭증함(원래
IN은 중복 목록을 묵시적으로 무시하는 효과가 있었을 수 있음) - 조인 순서가 바뀌면서 큰 테이블을 먼저 스캔함
- PostgreSQL에서 해시 조인이 스필 나서 디스크를 씀
이때는 다음 순서로 해결합니다.
- 실행 계획 비교(
EXPLAIN ANALYZE) - 목록 테이블 PK/인덱스 추가
- 중복 제거(예: 임시 테이블 PK로 강제)
- 통계 최신화 및 설정 점검(PostgreSQL은 VACUUM/ANALYZE 포함)
결론: “IN vs JOIN” 이 아니라 “목록을 어떻게 전달하느냐”
큰 IN 리스트가 느린 이유는 문법의 문제가 아니라, DB가 그 목록을 최적화 가능한 형태로 다루기 어렵기 때문입니다. 목록을 테이블로 만들고 JOIN(또는 EXISTS)로 표현하면 옵티마이저가 조인 알고리즘과 순서를 선택할 수 있어, 대량 조건에서 더 빠르게 동작하는 경우가 많습니다.
정리하면 다음 한 줄이 실무에서 가장 유용합니다.
- 작은 목록은
IN도 OK, 큰 목록은 “테이블화해서 JOIN/EXISTS” 가 정답에 가깝다.
성능이 애매하면 감으로 결정하지 말고, MySQL은 EXPLAIN ANALYZE, PostgreSQL은 EXPLAIN (ANALYZE, BUFFERS) 로 실행 계획을 확인한 뒤, 목록 전달 방식을 바꾸는 쪽부터 접근하는 것이 가장 빠릅니다.