복합 인덱스는 언제 필요할까?New

📚 Overview

복합 인덱스는 여러 컬럼을 묶어 추가하면 조회 성능이 좋아지는 기능이라고 생각하기 쉽다.

하지만 실제로는 컬럼의 개수보다 애플리케이션이 데이터를 어떤 조건으로 찾고, 어떤 순서로 정렬하며, 어떤 단위로 나누어 반환하는지가 더 중요하다.

예를 들어 다음과 같은 조회가 있다고 가정해보자.

• 특정 상태(status)의 데이터만 조회한다.
• 기본 키(id)를 기준으로 최신순 정렬한다.
• 페이지 단위로 결과를 반환한다.

데이터가 적을 때는 단일 인덱스만으로도 큰 문제가 발생하지 않을 수 있다. 하지만 데이터가 쌓이고 호출량이 증가하면, 데이터베이스는 인덱스를 사용하고도 최종 결과보다 훨씬 많은 데이터를 읽거나 별도의 정렬 작업을 수행하게 된다.

그렇다면 복합 인덱스는 어떤 상황에서 필요하고, 어떤 기준으로 설계해야 할까?

1. 복합 인덱스가 필요하다는 신호

처음부터 모든 조회 조건을 복합 인덱스로 만들 필요는 없다.

우선 운영 환경에서 실제로 느린 요청을 찾고, 해당 요청이 어떤 방식으로 데이터를 읽는지 확인하는 것이 먼저다.

실행 계획에서는 다음과 같은 신호를 살펴볼 수 있다.

• 전체 테이블을 확인하고 있는 경우
• 인덱스를 사용하지만 필요한 결과보다 훨씬 많은 행을 읽는 경우
• 별도의 정렬 작업이나 임시 테이블을 사용하는 경우
• 데이터가 증가할수록 응답 시간이 함께 증가하는 경우

예를 들어 다음과 같은 쿼리가 있다고 해보자.

SELECT id, status, title
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY id DESC
LIMIT 20;

status에 단일 인덱스가 있다면 데이터베이스는 PUBLISHED 상태의 데이터를 찾는 데에는 도움을 받을 수 있다.

하지만 해당 범위에서 다시 id 기준으로 정렬해야 한다면, 조건에 맞는 데이터를 더 많이 읽은 뒤 정렬하는 작업이 추가될 수 있다. 결과는 20건뿐인데 데이터베이스가 그보다 훨씬 많은 행을 확인하고 있다면, 현재 인덱스가 실제 조회 경로를 충분히 표현하지 못하고 있을 가능성이 있다.

2. 조회 경로에 맞는 복합 인덱스

앞선 조회 조건과 정렬 순서를 함께 고려하면 다음과 같은 인덱스를 후보로 검토할 수 있다.

CREATE INDEX idx_posts_status_id
ON posts (status, id DESC);

이 인덱스는 먼저 status 조건으로 조회 범위를 좁힌 뒤, 해당 범위 안에서 id 순서대로 데이터를 탐색할 수 있도록 설계한 것이다.

즉, 데이터베이스가 다음 두 작업을 하나의 인덱스 경로에서 처리할 수 있도록 돕는다.

• status = 'PUBLISHED' 조건으로 대상 범위 좁히기
• 해당 범위에서 id DESC 순서로 데이터 탐색하기

다만 id가 항상 실제 생성 시간의 순서를 의미하는 것은 아니다. 애플리케이션에서 최신순을 표현하려는 목적이라면 created_at이 더 정확한 정렬 기준일 수도 있다.

CREATE INDEX idx_posts_status_created_at
ON posts (status, created_at DESC, id DESC);

동일한 생성 시각을 가진 데이터의 순서를 안정적으로 유지하려면 id를 보조 정렬 기준으로 함께 사용하는 방식도 고려할 수 있다.

복합 인덱스는 단순히 컬럼을 여러 개 추가하는 기능이 아니다. 실제 쿼리의 WHERE, ORDER BY, LIMIT이 어떤 순서로 연결되는지를 인덱스에 표현하는 설계에 가깝다.

3. 컬럼의 순서가 중요한 이유

특히 (status, id)와 (id, status)는 서로 다른 인덱스다.

두 인덱스 모두 같은 컬럼을 포함하고 있지만, 데이터베이스가 인덱스를 탐색하는 방식은 달라진다.

-- 상태로 범위를 좁힌 뒤 id 순서로 조회
(status, id)

-- id 순서가 우선인 인덱스
(id, status)

(status, id)는 status가 조회의 시작 조건으로 사용되는 패턴에 적합하다. 반면 (id, status)는 id가 먼저 정렬이나 범위 조건에 사용되는 패턴에서 더 자연스럽게 활용될 수 있다.

복합 인덱스의 앞쪽 컬럼을 생략하면 뒤쪽 컬럼만으로는 인덱스를 원하는 방식으로 활용하기 어려울 수 있다. 따라서 자주 사용하는 컬럼을 아무 순서로나 나열하는 것만으로는 충분하지 않다.

다음과 같은 내용을 함께 확인해야 한다.

• 실제 WHERE 조건이 무엇인지
• ORDER BY의 컬럼과 방향이 무엇인지
• 페이지 단위 조회가 OFFSET 기반인지 커서 기반인지
• 첫 번째 인덱스 컬럼이 실제 조회에서 항상 사용되는지
• 조건의 선택도와 데이터 분포가 어떤지

페이지가 깊어질수록 OFFSET 기반 조회는 앞선 데이터를 건너뛰기 위해 더 많은 행을 확인할 수 있다. 이런 경우에는 마지막으로 조회한 id를 기준으로 다음 페이지를 가져오는 커서 기반 조회도 함께 검토할 수 있다.

SELECT id, status, title
FROM posts
WHERE status = 'PUBLISHED'
  AND id < 1200
ORDER BY id DESC
LIMIT 20;

이 쿼리 역시 (status, id DESC) 인덱스가 조회 조건과 정렬 순서를 표현하는 데 도움을 줄 수 있다.

4. 실행 계획과 운영 환경에서 검증하기

복합 인덱스는 개발 단계에서 미리 추가한다고 항상 좋은 결과를 보장하지 않는다.

인덱스를 추가하기 전에는 먼저 실제 실행 계획을 확인한다.

EXPLAIN ANALYZE
SELECT id, status, title
FROM posts
WHERE status = 'PUBLISHED'
ORDER BY id DESC
LIMIT 20;

이때 단순히 인덱스 이름이 표시되는지만 보지 않고, 예상한 조회 경로가 실제로 사용되었는지 확인해야 한다.

• 예상한 인덱스를 사용하는가?
• 실제로 읽은 행의 수가 줄었는가?
• 정렬이나 임시 테이블이 사라졌는가?
• 응답 시간이 개선되었는가?
• 특정 데이터 분포에서만 좋아진 것은 아닌가?

인덱스 추가에는 비용도 있다.

• 저장 공간을 추가로 사용한다.
• INSERT, UPDATE, DELETE 시 인덱스도 함께 갱신해야 한다.
• 인덱스가 많아질수록 데이터 변경 비용과 관리 비용이 증가한다.
• 사용하지 않는 인덱스가 쌓이면 어떤 인덱스를 유지해야 하는지 판단하기 어려워진다.

따라서 인덱스를 추가한 뒤에는 같은 조건으로 다시 측정하고, 실제 운영 요청의 응답 시간과 데이터베이스 부하가 개선되었는지 확인해야 한다.

AI는 쿼리를 분석하고 인덱스 후보를 제안하거나 실행 계획을 해석하는 데 도움을 줄 수 있다. 하지만 어떤 요청이 운영 환경에서 중요한지, 현재 성능 저하가 사용자 경험에 어떤 영향을 주는지, 인덱스 추가 비용을 감당할 수 있는지는 코드만 보고 판단하기 어렵다.

🤔 Understanding

복합 인덱스가 필요한 순간은 컬럼이 여러 개 사용될 때가 아니라, 실제 조회 경로가 기존 인덱스만으로 효율적으로 표현되지 않을 때라고 생각한다.

먼저 운영 환경에서 느린 요청을 찾고, 실행 계획을 통해 데이터베이스가 얼마나 많은 데이터를 읽는지 확인해야 한다. 그다음 실제 조회 조건과 정렬, 페이지 단위 조회 방식에 맞춰 인덱스를 설계하고 적용 후 같은 조건에서 다시 측정해야 한다.

복합 인덱스의 컬럼 순서도 중요하지만, 그보다 먼저 해결하려는 조회 패턴이 분명해야 한다. (status, id)와 (id, status) 중 어느 쪽이 더 좋은지는 인덱스 자체가 아니라 애플리케이션이 데이터를 찾는 방식에 따라 달라진다.

AI가 코드를 빠르게 구현해주는 시대일수록 운영 환경을 관찰하고 실제 병목을 정의하며 개선 전후를 검증하는 개발자의 역할은 더 중요해지고 있다.

결국 인덱스는 많이 추가하는 것이 목표가 아니라, 반복되는 조회 경로를 적은 비용으로 표현하는 도구에 가깝다.