MySQL EXISTS는 정말 모든 행을 확인할까?

English
#MySQL#Query Optimization#Database#Performance

쿼리를 작성하다 보면 이런 코드를 만날 때가 있다.

SELECT u.id, u.name, u.email, u.phone, u.created_at
FROM user u
WHERE u.user_type = 2
  AND u.is_deleted = 0
  AND NOT EXISTS (
    SELECT 1 FROM task t
    WHERE t.assigned_user_id = u.id
  );

태스크가 할당되지 않은 유저를 찾는 쿼리다. 그런데 EXISTS 를 보면 뭔가 찜찜하다. "존재하는지 확인한다"는 의미니까, 혹시 user 테이블의 모든 행에 대해 task 테이블을 뒤지는 건 아닐까? 테이블 크기가 크면 엄청 느려지는 거 아닐까?

결론부터 말하자면, 그렇지 않다. EXISTS는 생각보다 똑똑하게 동작한다.

EXISTS가 실제로 동작하는 방식

많은 사람들이 오해하는 부분이 바로 이거다. EXISTS가 user 테이블의 모든 행 에 대해 실행된다고 생각하는 것. 하지만 실제로는 WHERE 절의 다른 조건들이 먼저 필터링을 한다.

위 쿼리의 실행 순서를 생각해보자.

  1. u.user_type = 2 조건으로 user 테이블을 필터링
  2. u.is_deleted = 0 조건으로 추가 필터링
  3. 남은 행들에 대해서만 EXISTS 서브쿼리 실행

만약 user 테이블에 100만 건이 있는데, user_type = 2이고 is_deleted = 0인 유저가 1000명이라면? EXISTS는 1000번만 실행된다. 100만 번이 아니라.

이게 가능한 이유는 MySQL 옵티마이저가 WHERE 절의 조건들을 효율적인 순서로 재배치하고, 가능한 한 빨리 행을 걸러내기 때문이다. 인덱스가 잘 걸려있다면 user_type과 is_deleted 조건으로 대부분의 행을 빠르게 제외할 수 있다.

Short-circuit 평가의 힘

EXISTS의 또 다른 장점은 short-circuit 평가를 한다는 것이다. 서브쿼리에서 첫 번째 매칭되는 행을 찾으면 즉시 true를 반환하고 나머지는 확인하지 않는다.

NOT EXISTS (
  SELECT 1 FROM task t
  WHERE t.assigned_user_id = u.id
)

이 서브쿼리는 assigned_user_id = u.id인 task가 하나라도 있는지만 확인하면 된다. task가 0개면 false, 1개 이상이면 true. 그래서 SELECT 1을 쓰는 것이다. 실제 데이터가 필요한 게 아니라 존재 여부만 확인하면 되니까.

IN 절과 비교해보면 차이가 명확하다.

-- IN은 모든 값을 가져와서 비교
WHERE u.id IN (SELECT assigned_user_id FROM task)

-- EXISTS는 존재 여부만 확인
WHERE EXISTS (SELECT 1 FROM task WHERE task.assigned_user_id = u.id)

IN은 서브쿼리 결과를 모두 메모리에 올려놓고 비교하지만, EXISTS는 그럴 필요가 없다. 특히 NOT EXISTS 같은 경우는 더욱 효율적이다.

인덱스가 있으면 더 빠르다

EXISTS의 진가는 인덱스와 만났을 때 발휘된다. task.assigned_user_id에 인덱스가 걸려있다면, MySQL은 B-Tree를 탐색해서 O(log n) 시간에 해당 유저의 task가 있는지 확인할 수 있다.

인덱스가 없으면? 그때는 Full Table Scan이 발생한다. task 테이블의 모든 행을 순회하면서 assigned_user_id = u.id인지 확인해야 하니까. 이 경우에는 정말 느려질 수 있다.

-- 인덱스 생성
CREATE INDEX idx_task_assigned_user ON task(assigned_user_id);

이 한 줄만 추가해도 쿼리 성능이 극적으로 개선될 수 있다.

실제로 확인하는 방법

쿼리가 어떻게 실행되는지 궁금하면 EXPLAIN을 사용하면 된다.

EXPLAIN SELECT u.id, u.name, u.email, u.phone, u.created_at
FROM user u
WHERE u.user_type = 2
  AND u.is_deleted = 0
  AND NOT EXISTS (
    SELECT 1 FROM task t
    WHERE t.assigned_user_id = u.id
  );

EXPLAIN 결과에서 확인할 포인트는 이렇다.

  • type: index 또는 ref면 인덱스를 사용하는 것. ALL이면 Full Table Scan
  • rows: 실제로 스캔하는 행의 수 추정치
  • Extra: Using where, Using index 같은 추가 정보

만약 task 테이블에서 type이 ALL로 나온다면, 인덱스를 추가하는 걸 고려해야 한다.

정리하면

EXISTS는 단순히 "존재하는지 확인"하는 것처럼 보이지만, 실제로는 꽤 최적화된 방식으로 동작한다.

  • WHERE 절의 다른 조건들이 먼저 필터링을 하고, 남은 행에 대해서만 EXISTS가 실행된다
  • Short-circuit 평가로 첫 번째 매칭을 찾으면 즉시 멈춘다
  • 인덱스가 있으면 B-Tree 탐색으로 O(log n)에 결과를 찾는다

쿼리가 느리다고 느껴진다면, EXISTS 자체의 문제보다는 인덱스가 제대로 걸려있는지 먼저 확인해보는 게 좋다. EXPLAIN으로 실행 계획을 보면 병목이 어디인지 금방 알 수 있다.