NOT IN 쿼리를 사용할 때 발생할 수 있는 문제와 최적화 방법

 

매일메일 백엔드 질문을 참고해 개인적으로 학습한 내용을 정리하였습니다.
오류가 있다면 언제든 피드백 주시면 바로 반영하겠습니다..!

 

예시 쿼리

 

SELECT p
FROM Post p
WHERE p.id NOT IN :postIds

 

위 쿼리는 특정 ID 목록(:postIds)에 포함되지 않은 게시글(Post)를 조회하는 아주 직관적인 방식임

 

발생할 수 있는 문제점

 

1. 전체 테이블 스캔 (Full Table Scan), 인덱스 풀 스캔(Full Index Scan)

 

NOT IN은 부정 조건이라서 DB 옵티마이저가 효율적인 실행 계획을 세우기 어려움

 

=> 전체 테이블 스캔이나 인덱스 풀 스캔을 유발함

 

DB 옵티마이저는 SQL 쿼리를 실행할 때, 가장 빠르고 효율적인 실행 계획을 세우는 DB의 두뇌같은 역할임

DB는 단순히 쿼리를 그대로 실행하지 않고 내부적으로 이걸 어떤 순서로, 어떤 인덱스를 써서 실행할까를 계산하는데 
이 과정을 Query Optimizer가 수행함

NOT IN은 전체 데이터나 테이블을 스캔한 후 조건에 맞지 않는 레코드를 필터링 하는 부정 조건이기에 DB 옵티마이저가 효율적인 실행 계획을 세우기 어려움

 

2. 인덱스 활용 어려움

IN은 인덱스 Range Scan을 통해 빠르게 조회할 수 있지만, NOT IN은 인덱스 활용도가 현저히 떨어짐
(NOT IN은 인덱스 보다 느린 Full Scan이나 Full Index Scan을 유발하기 때문)

 

3. NULL 포함 시 의도치 않은 결과

 

NULL 값 처리 로직으로 인한 예상치 못한 결과가 발생할 수 있음

 

=> NULL 과의 비교는 UNKNOWN이 되어 불일치로 간주되기 때문에 column NOT IN (1, 2, NULL) 은 항상 빈 결과를 반환함

 

4. 많은 값이 들어올 때 오버헤드

 

위의 예시 쿼리에서 :postIds에 수천 개의 ID가 들어오면 파싱, 최적화, 메모리 사용 측면에서 부담이 커짐

(대량의 값을 IN 절에 넣으면 실행 계획 생성이 늘어나고, 파싱 및 최적호 단계에서 추가적인 오버헤드가 발생함)

 

최적화 방안

 

NOT EXISTS 사용

 

SELECT p 
FROM Post p
WHERE NOT EXISTS (
    SELECT 1 
    FROM Post temp
    WHERE temp.id = p.id AND temp.id IN :postIds
)

 

NOT EXISTS는 행 단위로 조건을 검사하고, 조건을 만족하면(매칭되는 첫 행을 찾자마자) 바로 exit함

=> DBMS가 존재하지 않음 을 확인하기 위해 특별히 최적화된 방식임

 

이는 인덱스 활용이 더 유리하고, 확장성 좋은 쿼리이며 NULL 값이 포함되어도 정확하게 처리됨
(대규모 데이터셋에서 가장 안정적이고 확장성 있는 성능을 제공함)

 

LEFT JOIN + IS NULL 사용

 

SELECT p 
FROM Post p 
LEFT JOIN (
    SELECT temp.id 
    FROM Post temp 
    WHERE temp.id IN :postIds
) filtered ON p.id = filtered.id
WHERE filtered.id IS NULL

 

작동 방식을 살펴보면 Post 테이블에서 조건에 맞는 ID들만 추린 서브쿼리 filtered를 만들고 LEFT JOIN함

 

이 때 p.id가 :postIds에 포함되면 매칭되는 filtered.id가 있고 NULL이 아니지만 만약 p.id가 :postIds에 없으면 매칭이 되지 않고 filtered.id가 NULL이 될 것임

 

=> filtered.id에 IS NULL 조건을 사용하여 :postIds에 포함되지 않은 p.id만 남김

 

 

서브쿼리가 작을 때 아주 빠르고
(JOIN 대상이 작아지면 JOIN 연산 시 비교할 row 수가 줄어들어 처리 속도가 빨라짐)

 

JOIN에 PK 인덱스가 있으면 최적화 효과가 큼

 

PK는 무조건 유일한 값이 1개이므로 JOIN, WHERE 조건에서 정확히 1건(최대 1개)만 찾으면 끝남
=> 옵티마이저가 가장 빠른 알고리즘(Index Seek) 선택 가능

일반 인덱스는 중복 데이터가 있을 수 있어서 같은 조건에 해당하는 레코드를 여러 개 찾아야 할 수 도 있음
=> 옵티마이저는 추가로 테이블 접근(lookup) 필요

 

만약 postIds가 많다면?

 

• SQL 쿼리 길이 제한 문제
  
  
• IN 안에 수 천 개 값이 들어가면 옵티마이저가 처리 전략을 정하기 힘들어지므로 Full Table Scan을 선택하거나 파싱 속도 및 네트워크 트래픽 증가가 생김
  
  
• Hibernate는 IN절 파라미터가 너무 많으면 예외 발생함
  (QueryParameterBindingException)

 

위와 같은 문제들이 발생할 수 있기에

 

 

임시 테이블에 저장 + JOIN

 

-- 1. postIds를 임시 테이블(PostIdsTemp)에 insert
-- 2. JOIN으로 처리
SELECT p.*
FROM Post p
JOIN PostIdsTemp pid ON p.id = pid.id

 

아예 임시 테이블에 저장하고 JOIN으로 처리하는 것이 더 안전할 수 있음

(JOIN은 인덱스를 탈 수 있어 빠르고 postIds 개수에 제한이 없음)

 

배치 처리

 

// postIds가 5000개면 → 1000개씩 5번 나눠서 5번 쿼리 실행
for (List<Long> batch : batchSplit(postIds, 1000)) {
    query.setParameter("postIds", batch).getResultList();
}

 

Hibernate/JPA에서는 IN/NOT IN 사용 시 컬렉션이 너무 크면 쿼리를 나눠서 배치 처리를 고려할 수 있음

 

이렇게 하면 쿼리 길이 제한을 우회할 수 있고 Hibernate 내부 제약도 피할 수 있는데 클라이언트-서버 간 통신 횟수가 늘어나므로 총 응답 시간에 주의해야하고 결과를 직접 merge/중복 제거 해야될 수 도 있음

 

Native SQL로 직접 처리

 

@Query(value = "SELECT * FROM post p WHERE p.id IN (SELECT id FROM temp_ids)", nativeQuery = true)

 

 

JPA의 JPQL은 내부적으로 안전장치가 많고, 복잡한 최적화를 하기 어렵기에 성능이 중요한 쿼리는 아예 Native SQL로 직접 작성하여 최적화하는 것도 고려해 보는게 좋음