[SQL] NOT IN 쿼리를 사용할 때 발생하는 문제점과 해결방법

NOT IN

쿼리는 직관적이고 사용하기 쉬운 SQL 구문이지만, 대규모 데이터셋을 처리할 때 심각한 성능 저하를 초래할 수 있다.

NOT IN의 문제점을 살펴보고, 효율적인 쿼리를 작성하기 위한 대안을 알아보겠습니다.

 

문제점

NOT IN은 부정 조건으로, 대부분의 데이터베이스 관리 시스템(DBMS)에서 전체 테이블 스캔이나 인덱스 풀 스캔을 유발한다. 이는 데이터베이스 옵티마이저가 효율적인 실행 계획을 세우기 어렵게 만들고, 쿼리 성능을 저하시키는 주요 원인이다.

SELECT *
FROM orders
WHERE customer_id NOT IN (SELECT id FROM inactive_customers);

 

• 전체 테이블 스캔: NOT IN은 주어진 조건에 맞지 않는 레코드를 찾기 위해 테이블의 모든 데이터를 읽어야 할 수 있다. 대용량 테이블에서 매우 비효율적이다.
• 인덱스 비효율성: IN 절은 인덱스 Range Scan을 통해 빠르게 처리될 수 있지만, NOT IN은 인덱스를 제대로 활용하지 못하는 경우가 많다.
• 서브쿼리 성능 저하: NOT IN에 사용되는 서브쿼리의 결과가 클수록, 이를 처리하는 데 필요한 시간과 자원이 증가한다.
• NULL 값 문제: NOT IN은 NULL 값을 포함하는 서브쿼리와 함께 사용될 때 예기치 못한 결과를 초래할 수 있다. 예를 들어, column NOT IN (1, 2, NULL) 쿼리는 column의 값이 1, 2, NULL이 아닌 레코드를 반환해야 할 것 같지만, 실제로는 항상 빈 결과 집합을 반환한다. 이는 NULL과의 비교 연산이 항상 UNKNOWN을 반환하기 때문이다.

 

해결방법

1. NOT EXISTS 활용

SELECT o.*
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM inactive_customers ic WHERE o.customer_id = ic.id);

NOT EXISTS는 서브쿼리에서 주어진 조건과 일치하는 레코드가 하나라도 존재하지 않으면 외부 쿼리의 현재 행을 결과에 포함한다. NOT EXISTS는 행 단위로 평가되므로, 일치하는 첫 번째 행을 찾으면 더 이상 서브쿼리를 검색하지 않는다. 이는 DBMS가 '존재하지 않음'을 확인하기 위해 특별히 최적화된 방식이며, 대규모 데이터셋에서 NOT IN보다 훨씬 더 나은 성능을 제공한다.

2. LEFT JOIN + IS NULL

SELECT o.*
FROM orders o
LEFT JOIN inactive_customers ic ON o.customer_id = ic.id
WHERE ic.id IS NULL;

LEFT JOIN은 두 테이블의 모든 레코드를 결합하고, 일치하는 값이 없는 경우 오른쪽 테이블의 컬럼에 NULL 값을 채운다. IS NULL 절을 사용하면 오른쪽 테이블에 일치하는 레코드가 없는 행만 선택할 수 있다. 이 방법은 서브쿼리 결과가 작을 때 특히 효율적이며, 인덱스를 효과적으로 활용할 수 있다. 특히, 조인에 사용되는 컬럼에 기본 키 인덱스가 있는 경우 성능이 향상된다.

 

성능 비교

일반적으로 NOT EXISTS는 NOT IN보다 훨씬 빠르다. 특히 대규모 데이터셋에서 NOT IN은 수십 초 또는 수분 이상 걸릴 수 있는 반면, NOT EXISTS는 일반적으로 수 초 내에 결과를 반환한다. LEFT JOIN + IS NULL은 서브쿼리 결과가 작은 경우 NOT EXISTS와 비슷한 성능을 제공할 수 있다.

결론

NOT IN은 사용하기에는 간단하지만, 대규모 데이터셋에서 심각한 성능 문제를 일으킬 수 있다. 따라서 가능한 경우 NOT EXISTS 또는 LEFT JOIN + IS NULL과 같은 대안을 사용하는 것이 좋다. NOT EXISTS는 일반적으로 가장 안정적인 성능을 제공하며, LEFT JOIN + IS NULL은 서브쿼리 결과가 작은 경우 좋은 대안이 될 수 있다. 쿼리 성능은 데이터베이스 구조, 데이터 양, 인덱스 등 다양한 요인에 따라 달라질 수 있으므로, 실제 환경에서 쿼리 성능을 테스트하고 최적화하는 것이 중요하다.