[MySQL] 쿼리 최적화의 기본: IN과 NOT IN 연산자의 동작 원리와 성능 분석

 

데이터베이스를 다루는 개발자와 DBA에게 있어 SQL 쿼리의 가독성과 성능 사이의 균형을 맞추는 것은 언제나 중요한 과제입니다. 그중에서도 IN과 NOT IN 연산자는 여러 개의 조건을 간결하게 표현할 수 있어 매우 빈번하게 사용됩니다.

하지만 단순히 코드가 깔끔해진다는 이유만으로 이 연산자들을 무분별하게 사용하면 예기치 못한 성능 저하나 논리적 오류를 마주할 수 있습니다. 특히 MySQL 버전에 따라 해당 연산자들을 처리하는 방식(실행 계획)에 상당한 차이가 존재합니다. 이번 글에서는 MySQL 환경에서 IN과 NOT IN이 실제로 어떻게 동작하는지, 그리고 버전에 따라 어떤 변화가 있었는지 깊이 있게 살펴보겠습니다.

1. IN 연산자의 기본 동작과 최적화 (IN vs OR)

기본적으로 IN 연산자는 괄호 안에 나열된 값들 중 하나라도 일치하면 참(True)을 반환하는 논리 연산자입니다. 논리적으로는 여러 개의 OR 조건을 나열한 것과 동일한 의미를 가집니다.

예를 들어, status IN ('Active', 'Pending') 조건은 status = 'Active' OR status = 'Pending'과 같습니다.

하지만 내부 처리 방식에는 차이가 있습니다. MySQL 옵티마이저는 IN 목록에 있는 값들을 정렬하고 **이진 검색(Binary Search)**을 수행하여 일치 여부를 확인합니다. 반면 OR 연산자는 순차적으로 평가됩니다. 따라서 비교할 값의 목록이 길어질수록 OR보다 IN 연산자가 훨씬 효율적으로 동작할 가능성이 높습니다.

또한, 인덱스가 존재하는 컬럼에 대해 IN을 사용할 경우, MySQL은 이를 범위 검색(Range Scan)으로 처리하여 빠르게 데이터를 조회할 수 있습니다.

2. 대량의 IN 목록과 eq_range_index_dive_limit

실무에서 자주 발생하는 문제는 IN 절 안에 수천 개의 상수 값을 넣을 때 발생합니다. MySQL은 실행 계획을 수립할 때 인덱스 통계 정보를 활용하여 비용을 계산하는데, 이를 **인덱스 다이브(Index Dive)**라고 합니다.

IN 목록의 개수가 적을 때는 인덱스 다이브가 정확한 실행 계획을 만드는 데 도움을 줍니다. 하지만 목록이 너무 많아지면 인덱스 다이브 자체에 시간이 오래 걸려 쿼리 분석 속도가 느려질 수 있습니다.

이를 제어하기 위해 MySQL 5.6부터 eq_range_index_dive_limit 시스템 변수가 도입되었습니다. IN 목록의 개수가 이 변수 값(기본값은 버전별로 상이하나 보통 200)을 초과하면, MySQL은 인덱스 다이브를 포기하고 일반적인 인덱스 통계 정보만을 사용하여 실행 계획을 수립합니다. 이 경우 실행 계획의 정확도가 떨어질 수 있으므로, 너무 많은 상수를 IN 절에 넣는 것은 주의해야 합니다.

3. NOT IN 사용 시 NULL의 함정

NOT IN 연산자는 초보 개발자들이 가장 흔하게 실수하는 부분 중 하나입니다. 바로 NULL 값의 처리 방식 때문입니다.

SQL에서 NULL은 '알 수 없는 값(Unknown)'을 의미합니다. 만약 NOT IN 리스트 안에 단 하나라도 NULL이 포함되어 있다면, 쿼리 결과는 아무것도 반환되지 않을 수 있습니다.

예를 들어 id NOT IN (1, 2, NULL)이라는 조건이 있다고 가정해 봅시다. 논리적으로는 id <> 1 AND id <> 2 AND id <> NULL 로 풀이됩니다. 데이터베이스에서 NULL과의 비교 연산은 언제나 Unknown(False 취급)이 되므로, 전체 조건이 거짓이 되어버려 결과 집합이 0건이 되는 현상이 발생합니다.

반면, NOT EXISTS 연산자는 이러한 NULL 문제에서 자유롭습니다. 따라서 서브쿼리나 리스트에 NULL이 포함될 가능성이 있다면 NOT IN 대신 NOT EXISTS를 사용하거나, IS NOT NULL 조건을 명시적으로 추가하여 데이터를 정제해야 합니다.

4. 서브쿼리와 버전별 최적화 변화

IN (SELECT ...) 형태의 서브쿼리 사용은 MySQL 버전에 따라 성능 차이가 가장 극명하게 드러나는 부분입니다.

MySQL 5.5 이하 버전

과거 5.5 버전까지는 IN 서브쿼리의 최적화가 매우 취약했습니다. 옵티마이저가 서브쿼리를 외부 쿼리에 종속적인 형태(Dependent Subquery)로 변환하여 실행했습니다. 즉, 외부 테이블의 레코드마다 서브쿼리를 반복해서 실행하는 구조였기 때문에, 데이터가 많아질수록 성능이 급격히 저하되었습니다.

MySQL 5.6 이후 (Semi-Join 최적화)

5.6 버전부터는 서브쿼리 최적화에 혁신적인 변화가 있었습니다. 세미 조인(Semi-Join) 최적화와 구체화(Materialization) 전략이 도입되었습니다. 옵티마이저는 서브쿼리의 결과를 임시 테이블로 만들어 조인하는 방식으로 처리하거나, 서브쿼리를 일반 조인 형태로 재작성하여 성능을 대폭 개선했습니다.

MySQL 8.0

8.0 버전에서는 이러한 최적화가 더욱 성숙해졌으며, 옵티마이저가 상황에 따라 더 다양한 전략(예: FirstMatch, Loosescan 등)을 유연하게 선택합니다. 따라서 최신 버전을 사용 중이라면 IN 서브쿼리에 대한 성능 우려가 과거에 비해 많이 줄어들었습니다.

결론 및 실무 적용 팁

MySQL에서 IN과 NOT IN은 강력하고 편리한 도구이지만, 그 내부 동작을 이해하지 못하고 사용하면 성능 이슈의 원인이 됩니다. 특히 대용량 데이터를 다루거나 복잡한 서브쿼리를 작성할 때는 더욱 주의가 필요합니다.

핵심 요약 및 팁:

1. 단순 나열은 OR보다 IN이 유리합니다: 비교할 값이 여러 개라면 가독성과 이진 검색 효율을 위해 IN을 사용하는 것이 좋습니다.
2. NULL 처리에 주의하세요: NOT IN을 사용할 때 비교 대상 목록에 NULL이 포함되면 결과가 나오지 않습니다. 데이터 정합성을 위해 NULL 포함 여부를 반드시 확인하거나 NOT EXISTS로 대체하는 것을 고려하십시오.
3. MySQL 버전을 확인하세요: 레거시 시스템(5.5 이하)을 운영 중이라면 IN 서브쿼리 사용을 지양하고 JOIN으로 풀어쓰는 것이 필수적입니다. 5.6 이상이라면 옵티마이저를 믿되, 실행 계획(EXPLAIN)을 통해 세미 조인 최적화가 적용되는지 확인하는 습관을 들여야 합니다.
4. 대량의 IN 목록을 주의하세요: 수천 개의 값을 IN 절에 넣으면 eq_range_index_dive_limit 설정에 따라 실행 계획이 부정확해질 수 있습니다. 이 경우 임시 테이블을 생성하여 조인하는 방식이 더 나을 수 있습니다.

추가적인 정보는 MySQL 공식 문서를 참고하시기 바랍니다.

MySQL 공식 문서 - Comparison Functions and Operators https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html