03. 복합 인덱스와 컬럼 순서 상세

이 문서를 통해 아래 질문들에 답할 수 있게 됩니다.

• 복합 인덱스에서 컬럼 순서는 왜 중요한가?
• 동등 조건, 범위 조건, 정렬 조건을 어떤 순서로 봐야 하는가?
• 백엔드 목록 API의 인덱스는 어떻게 설계해야 하는가?

개요

복합 인덱스는 두 개 이상의 컬럼을 하나의 정렬 기준으로 묶은 인덱스다. 실무에서 느린 목록 API를 고칠 때 단일 인덱스보다 복합 인덱스가 더 중요한 경우가 많다.

핵심은 인덱스가 컬럼들을 독립적으로 저장하는 것이 아니라 정해진 순서로 정렬한다는 점이다.

CREATE INDEX idx_orders_member_status_created
ON orders (member_id, status, created_at DESC);

이 인덱스는 먼저 member_id로 정렬되고, 같은 회원 안에서 status로 정렬되고, 같은 상태 안에서 created_at 최신순으로 정렬된다.

원리

복합 인덱스는 왼쪽 접두어가 중요하다. (member_id, status, created_at) 인덱스는 일반적으로 다음 조건에 잘 맞는다.

WHERE member_id = ?

WHERE member_id = ?
  AND status = ?

WHERE member_id = ?
  AND status = ?
ORDER BY created_at DESC

하지만 status만으로 찾는 쿼리에는 같은 효과를 기대하기 어렵다.

SELECT id
FROM orders
WHERE status = 'PAID';

인덱스가 member_id별로 먼저 나뉘어 있기 때문에 status만으로는 정렬된 큰 구간을 바로 찾기 어렵다. DB마다 skip scan 같은 최적화가 있을 수 있지만, 기본 판단은 왼쪽부터 쓰는 것이다.

실행 계획에서는 이 원칙이 Index Cond나 사용 key length, rows 추정치로 드러난다. 선행 컬럼이 빠졌는데도 인덱스 이름이 보일 수 있지만, 실제로는 넓은 인덱스 범위를 훑고 뒤에서 필터링할 수 있다. 따라서 “복합 인덱스를 사용했다”보다 “선행 컬럼으로 얼마나 좁혔는가”를 봐야 한다.

동등 조건과 컬럼 순서

동등 조건은 복합 인덱스 앞쪽에 배치하기 좋다.

SELECT id, created_at
FROM orders
WHERE member_id = 10
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

이 쿼리는 다음 인덱스가 자연스럽다.

CREATE INDEX idx_orders_member_status_created_id
ON orders (member_id, status, created_at DESC, id DESC);

member_id와 status로 구간을 좁힌 뒤, 그 안에서 최신순으로 앞 20개만 읽는다. id DESC는 같은 created_at에서 순서를 안정화한다.

범위 조건이 들어올 때

범위 조건은 그 뒤 컬럼의 활용도를 제한할 수 있다.

SELECT id
FROM orders
WHERE created_at >= timestamp '2026-01-01'
  AND created_at < timestamp '2026-02-01'
  AND status = 'PAID';

(created_at, status) 인덱스는 날짜 범위로 먼저 넓게 읽은 뒤 status를 필터링할 수 있다. 반대로 status = 'PAID'인 주문 중 특정 기간을 보는 것이 더 선택적이면 다음이 나을 수 있다.

CREATE INDEX idx_orders_status_created
ON orders (status, created_at);

정답은 데이터 분포와 API 패턴에 따라 달라진다. 그래서 인덱스 설계는 SQL만 보고 끝내지 않고 실제 rows, 실행 계획, 호출 빈도를 함께 본다.

관리자 검색처럼 optional 조건이 많은 API는 대표 조건 조합과 SLA를 먼저 정해야 한다.

SELECT id, status, created_at
FROM orders
WHERE (:status IS NULL OR status = :status)
  AND (:memberId IS NULL OR member_id = :memberId)
ORDER BY created_at DESC
LIMIT 50;

이런 만능 SQL은 모든 조건 조합에서 좋은 인덱스를 타기 어렵다. 호출 빈도가 높은 대표 조합을 정하고, 나머지는 느려도 되는 관리자 기능인지, 검색 엔진이나 별도 read model로 분리할지 결정해야 한다.

정렬과 LIMIT

목록 API에서는 정렬을 인덱스가 지원하는지 확인해야 한다.

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

후보 인덱스는 다음과 같다.

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

이 인덱스는 게시판과 상태를 동등 조건으로 좁히고, 그 구간 안에서 이미 원하는 순서로 읽게 한다. 백엔드 목록 API는 보통 “필터 조건 + 정렬 + limit” 형태이므로, 정렬 컬럼까지 인덱스에 포함하는 설계가 중요하다.

코드 예시

다음 Spring Data JPA 메서드는 단순해 보이지만 SQL 패턴은 명확하다.

Slice<Post> findByBoardIdAndStatusOrderByCreatedAtDescIdDesc(
    Long boardId,
    PostStatus status,
    Pageable pageable
);

이 메서드가 자주 호출된다면 Repository 이름만 보고 끝내지 말고 다음 SQL과 인덱스를 함께 생각한다.

SELECT p.id, p.title, p.created_at
FROM posts p
WHERE p.board_id = ?
  AND p.status = ?
ORDER BY p.created_at DESC, p.id DESC
LIMIT ?;

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

Page를 쓰면 count 쿼리가 추가될 수 있다. 전체 건수가 꼭 필요하지 않은 무한 스크롤에는 Slice나 cursor 방식이 더 나을 수 있다.

Page의 count 쿼리는 본문 조회와 다른 인덱스를 필요로 할 수 있다. 목록 본문은 (board_id, status, created_at desc, id desc)가 잘 맞아도, count(*) WHERE board_id=? AND status=?는 정렬 컬럼이 필요 없고 호출 빈도에 따라 별도 판단이 필요하다.

실전 팁

• 복합 인덱스는 실제 API의 SQL 모양에서 출발한다.
• 동등 조건으로 좁히고, 범위 조건과 정렬 조건을 그 다음에 본다.
• ORDER BY가 인덱스 순서와 맞지 않으면 DB가 별도 정렬을 수행할 수 있다.
• 같은 컬럼 조합의 중복 인덱스를 만들지 않는다. (a, b)가 있으면 (a)가 항상 불필요한지는 DB와 쿼리에 따라 확인해야 하지만, 중복 가능성은 반드시 점검한다.
• 정렬 안정성을 위해 created_at만 쓰지 말고 id 같은 tie-breaker를 함께 둔다.
• optional 검색 조건은 모든 조합을 최적화하려 하지 말고, 실제 호출량이 높은 조합과 SLA가 있는 조합을 먼저 고른다.
• PostgreSQL partial index처럼 특정 조건에만 맞는 인덱스는 강력하지만, MySQL과 이식성이 다르므로 DBMS별 지원을 확인한다.
• 인덱스 컬럼 순서는 단순 선택도 순서가 아니라 동등 조건, 범위 조건, 정렬, limit, count 쿼리까지 같이 본다.

위험 신호!

• WHERE에 있는 컬럼을 발견할 때마다 단일 인덱스를 추가한다.
• 복합 인덱스의 왼쪽 컬럼이 실제 쿼리에서 자주 빠진다.
• 목록 API가 느린데 실행 계획에서 Sort와 많은 rows를 확인하지 않는다.
• Page count 쿼리 부하를 무시한다.
• 운영 중 인덱스를 추가하면서 기존 쓰기 부하와 락을 검토하지 않는다.
• optional 조건을 (:param IS NULL OR col = :param) 형태로 모두 합쳐놓고 실행 계획을 대표 값으로 확인하지 않는다.
• 목록 조회용 인덱스 하나로 count, 검색, export, 관리자 통계까지 모두 해결하려고 한다.

확인 질문

• 복합 인덱스에서 왼쪽 컬럼이 중요한 이유는 무엇인가?
  • 인덱스가 왼쪽 컬럼부터 차례로 정렬되므로 선행 컬럼 조건이 있어야 뒤 컬럼을 효율적으로 활용하기 쉽기 때문이다.
• 목록 API 인덱스 설계에서 ORDER BY를 함께 봐야 하는 이유는 무엇인가?
  • 인덱스가 정렬 순서를 지원하면 DB가 많은 행을 정렬하지 않고 필요한 개수만 순서대로 읽을 수 있기 때문이다.
• created_at 정렬에 id를 함께 넣는 이유는 무엇인가?
  • 같은 시각에 생성된 행 사이의 순서를 안정적으로 만들어 pagination 중 중복과 누락을 줄이기 위해서다.
• optional 검색 API의 인덱스를 설계할 때 가장 먼저 정해야 할 것은 무엇인가?
  • 모든 조건 조합을 최적화하려 하지 말고 실제 호출량, SLA, 데이터량이 큰 대표 조건 조합과 느려도 되는 운영/관리자 경로를 구분해야 한다.

참고 문서

• PostgreSQL Documentation, Multicolumn Indexes
• PostgreSQL Documentation, Indexes and ORDER BY
• MySQL Reference Manual, Multiple-Column Indexes