[MariaDB] 데이터 존재 여부 확인 NOT EXISTS 사용 및 성능 최적화

A테이블에 데이터 약 10만~30만개, B테이블에 데이터 약 300만개 존재하는 상태에서 JOIN 사용 시 대용량 데이터 JOIN이 발생하여 속도 저하 및 쿼리 성능 저하 및 리소스 과다 사용하는 경우가 존재. 해당 쿼리를 효율적으로 변경하기 위해 NOT EXISTS 를 사용하기로 한다.

 

우선 목표는 A테이블에는 존재하나 B테이블에 미존재하는 A를 조회하고자 하였고, 참조키로 C를 사용하는 상황이다.

기존 쿼리

SELECT * FROM A LEFT OUTER JOIN B ON A.C = B.C WHERE B.C IS NULL

해당 쿼리의 경우 직관적이나 (A테이블 유지, B테이블 조인, B테이블에 C가 없는 A를 조회)

동작 방식을 보면

1. A과 B를 먼저 JOIN

2. 조인 결과를 전부 생성

3. 그 중 B가 NULL인 row만 필터링

하는데, 이 경우 B 테이블이 클수록 조인 비용이 증가하며 중간 결과셋이 커질 수 있다. 옵티마이저가 항상 최적화해주지 않음

 

수정 쿼리

SELECT *
FROM A
WHERE NOT EXISTS (
    SELECT 1
    FROM B
    WHERE B.C = A.C
);

 

동작 방식

1. A의 row를 하나 읽음

2. B에서 C = ? 조건으로 인덱스 탐색

3. 하나라도 발견되면 즉시 중단

4. 끝까지 못 찾으면 A row 반환

이다. 해당 EXISTS 를 사용하는 경우 존재 여부만 확인하여 불필요한 결과를 생성하지 않고, B가 커도 인덱스만 잘 타면 빠르다. MariaDB에서 ANTI JOIN으로 최적화 가능하다는 장점이 있다.

 

여기서 SEMI JOIN 과 ANTI JOIN 개념을 짚고 넘어가자면

SEMI JOIN : 조인은 하지만, 존재 여부만 확인 (EXISTS) B의 컬럼을 결과에 사용하지 않고, B에 여러 건이 있어도 A는 한 건만 반환된다.

ANTI JOIN : SEMI JOIN의 반대. 존재하지 않는 것만 조회 (NOT EXISTS) B에 한 건이라도 존재하면 제외, 없는 데이터 찾기에 최적

 

성능 차이는 대략 다음과 같다

LEFT JOIN + IS NULL	1x
NOT EXISTS	5 ~ 10 x

 

데이터 존재 여부만 확인하는 경우 EXISTS 를 사용하여 SEMI JOIN 을 사용하여 주면 되겠다.