부분 인덱스 — WHERE로 인덱스를 작게 만드는 기술

부분 인덱스(partial index)는 테이블 전체가 아니라 WHERE 조건을 만족하는 일부 행만 대상으로 만드는 인덱스다. 인덱스를 작게 유지하면서, 자주 조회하는 특정 부분집합의 검색을 빠르게 한다. PostgreSQL과 SQLite가 지원하고, 잘만 쓰면 "인덱스는 큰데 정작 쓰는 건 일부"인 낭비를 없앨 수 있다.

솔직히 이 기능은 알기 전과 후가 갈린다. 나는 deleted_at IS NULL 같은 조건을 매번 붙이는 소프트 삭제 테이블에서 이걸 처음 제대로 써보고 "왜 진작 안 썼지" 싶었다.

일부만 인덱싱한다는 발상

보통 인덱스는 모든 행을 담는다. 그런데 실제 쿼리를 보면 특정 조건의 행만 계속 찾는 경우가 많다. 활성 사용자만, 미처리 주문만, 삭제 안 된 문서만. 이럴 때 전체를 인덱싱하는 건 낭비다. 필요한 부분만 인덱싱하면 크기가 줄고, 작으니 캐시에 잘 올라가고, 쓰기 부담도 준다.

-- 미처리 주문만 조회가 잦다면
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';
-- 이 쿼리가 부분 인덱스를 탄다
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

포인트는 쿼리의 조건이 인덱스의 조건을 포함해야 옵티마이저가 이 인덱스를 쓴다는 것. 위 예에서 status = 'pending'이 쿼리에 그대로 있어야 한다. 만약 주문의 99%가 이미 완료 상태라면, 이 부분 인덱스는 전체 인덱스의 몇 퍼센트 크기밖에 안 된다.

소프트 삭제와 궁합

가장 자주 만나는 실전 사례다. 소프트 삭제를 쓰면 거의 모든 쿼리에 deleted_at IS NULL이 붙는다. 그런데 시간이 지나면 삭제된 행이 테이블의 상당 비율을 차지한다. 전체 인덱스에는 죽은 행까지 다 들어간다.

CREATE INDEX idx_users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

이렇게 하면 살아있는 사용자만 인덱싱된다. 게다가 부분 인덱스로 유니크 제약을 걸면 재밌는 일이 가능하다. 삭제된 행끼리는 이메일이 겹쳐도 되고, 살아있는 행끼리만 유일하게 만들 수 있다.

-- 활성 사용자 사이에서만 이메일 유일
CREATE UNIQUE INDEX uq_users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

이건 일반 유니크 제약으로는 못 하는 것이라, 소프트 삭제 설계할 때 아주 요긴하다.

불리언·상태값의 편중을 노리기

또 하나 좋은 대상이 값이 심하게 치우친 컬럼이다. 예컨대 "미발송 알림" 플래그. 전체 알림 중 미발송은 극소수인데, 워커는 계속 이 소수를 찾는다.

CREATE INDEX idx_notif_unsent
  ON notifications (created_at)
  WHERE sent = false;

수백만 건이 쌓여도 인덱스에는 아직 안 보낸 수백 건만 들어간다. 워커 쿼리가 항상 빠르다.

주의할 점

• 인덱스 조건에 쓰는 값은 불변(immutable)이어야 한다. WHERE created_at > now()처럼 시점에 따라 달라지는 조건은 못 쓴다. now()는 계속 변하니까.
• 쿼리의 WHERE가 부분 인덱스 조건을 논리적으로 함의해야 인덱스를 탄다. 옵티마이저가 이걸 증명 못 하면 그냥 무시하고 풀 스캔한다. 애매하면 EXPLAIN으로 확인하는 습관을 들여라.
• 조건이 파라미터로 계속 바뀌는 쿼리에는 안 맞는다. 부분 인덱스는 "고정된 특정 부분집합"을 반복 조회할 때 빛난다.

정리하면 부분 인덱스는 "전부"가 아니라 "일부"에 집중하는 도구다. 데이터가 한쪽으로 쏠려 있거나, 늘 같은 조건이 붙는 쿼리가 있다면 후보다. 개인적으로는 소프트 삭제 유니크 하나만으로도 배울 값어치가 충분하다고 본다.

자주 묻는 질문

MySQL에서도 부분 인덱스를 쓸 수 있나요?

InnoDB에는 조건부 부분 인덱스가 없습니다. 다만 컬럼 앞부분만 인덱싱하는 프리픽스 인덱스는 있는데, 이건 개념이 다릅니다. 비슷한 효과가 필요하면 생성 컬럼에 인덱스를 걸거나 테이블을 분리하는 우회가 쓰입니다. PostgreSQL·SQLite는 네이티브로 지원합니다.

부분 인덱스가 정말 쓰이는지 어떻게 확인하나요?

EXPLAIN으로 실행계획에 해당 인덱스 이름이 뜨는지 봅니다. 안 뜬다면 쿼리의 WHERE가 인덱스 조건을 포함하지 않았거나, 옵티마이저가 함의를 증명하지 못한 경우가 많습니다.

부분 유니크 인덱스는 소프트 삭제에만 쓰나요?

가장 흔한 사례일 뿐 그 외에도 씁니다. 예를 들어 "기본 배송지는 사용자당 하나"처럼 특정 조건에서만 유일성을 강제하고 싶을 때 WHERE is_default = true 부분 유니크 인덱스가 깔끔합니다.

부분 인덱스와 커버링을 함께 쓸 수 있나요?

가능합니다. WHERE 조건과 INCLUDE를 함께 지정하면, 특정 부분집합에 대해 테이블을 안 읽는 인덱스가 됩니다. 좁고 뜨거운 쿼리 경로를 최적화할 때 둘의 조합이 강력합니다.