MariaDB 복합 인덱스로 느린 목록 조회 개선하기

MariaDB 복합 인덱스로 느린 목록 조회 개선하기

인덱스는 붙였는데 왜 안 빨라질까

커뮤니티 서비스를 운영하다 보면 가장 먼저 느려지는 곳이 목록 조회다. 게시글이 수천, 수만 건으로 쌓이면 "특정 카테고리의 글을 최신순으로 20개" 같은 단순한 쿼리조차 눈에 띄게 버벅인다.

이럴 때 흔히 하는 대응이 "일단 인덱스를 걸어보자"다. 그런데 인덱스를 붙였는데도 속도가 그대로인 경우가 많다. 원인은 대부분 조회 패턴과 맞지 않는 인덱스를 만들었기 때문이다. 인덱스는 무작정 컬럼에 붙인다고 효과가 나는 게 아니라, 쿼리가 데이터를 찾고 정렬하는 방식에 맞춰 설계해야 한다.

실제로 Kraft(kraft.io.kr)를 운영하면서도 데이터가 늘자 게시글 목록이 느려진 적이 있는데, category_id로 필터링하면서 created_at으로 정렬하는 쿼리에 맞는 인덱스가 없어 매 요청마다 정렬 작업(filesort)이 발생하고 있었다. 이 글에서는 그 상황을 예시로 복합 인덱스를 어떻게 설계하는지 정리한다.

인덱스와 선두 컬럼 원칙

MariaDB의 기본 인덱스는 B-Tree 구조다. 컬럼 값을 정렬된 상태로 보관하기 때문에, 특정 값을 찾거나 정렬된 순서로 읽어올 때 전체 데이터를 훑지 않고 필요한 부분만 빠르게 접근할 수 있다.

여러 컬럼을 묶은 복합 인덱스(composite index)에서 핵심은 컬럼 순서다. 복합 인덱스는 맨 앞 컬럼부터 차례대로 정렬되기 때문에, 선두 컬럼(leftmost prefix) 부터 조건에 사용해야 인덱스를 탈 수 있다. (A, B) 인덱스는 A 단독 조회나 A AND B 조회에는 쓰이지만, B 단독 조회에는 쓰이지 못한다. 전화번호부가 "성 → 이름" 순으로 정렬돼 있어, 성을 모르면 이름만으로는 빠르게 찾지 못하는 것과 같은 원리다.

이제 Kraft의 목록 조회 쿼리를 보자.

SELECT id, title, author_id, created_at
FROM post
WHERE category_id = 3
ORDER BY created_at DESC
LIMIT 20;

이 쿼리는 category_id로 필터링하고 created_at으로 정렬한다. 여기에 맞는 인덱스는 다음과 같다.

CREATE INDEX idx_post_category_created
    ON post (category_id, created_at);

category_id를 선두에 둔 이유는 등호(=) 조건으로 대상 범위를 먼저 좁히기 위해서다. 그다음 created_at을 두면, 좁혀진 범위 안에서 이미 정렬된 순서로 값을 읽을 수 있어 별도의 정렬 작업이 사라진다. 즉 이 인덱스 하나가 필터링과 정렬을 동시에 해결한다. 반대로 순서를 (created_at, category_id)로 뒤집으면, 등호 조건인 category_id가 선두가 아니게 되어 이점을 제대로 살리지 못한다.

EXPLAIN으로 검증하기

인덱스를 만들었으면 반드시 실행 계획을 확인해야 한다. 추측이 아니라 EXPLAIN이 근거다.

EXPLAIN
SELECT id, title, author_id, created_at
FROM post
WHERE category_id = 3
ORDER BY created_at DESC
LIMIT 20;

결과에서 볼 지점은 세 가지다. type이 ALL(전체 스캔)이 아니라 ref로 나오는지, key에 방금 만든 idx_post_category_created가 잡히는지, 그리고 Extra에서 Using filesort가 사라졌는지다. 정렬 작업이 인덱스로 대체되면 Using filesort 표시가 없어진다. 인덱스를 걸었는데도 여전히 Using filesort가 보인다면, 컬럼 순서나 정렬 방향을 다시 살펴봐야 한다는 신호다.

커버링 인덱스로 한 걸음 더

인덱스로 필터·정렬은 해결했지만, 조회하는 컬럼 값 자체는 여전히 테이블에서 다시 읽어와야 한다. 이 테이블 접근까지 없애는 것이 커버링 인덱스(covering index)다. 쿼리가 필요로 하는 모든 컬럼을 인덱스에 포함시키면, 인덱스만 읽고 결과를 만들 수 있다.

CREATE INDEX idx_post_list_cover
    ON post (category_id, created_at, id, title, author_id);

이렇게 하면 EXPLAIN의 Extra에 Using index가 표시되고, 테이블 본문을 건드리지 않는다. 다만 커버링 인덱스는 만능이 아니다. 포함 컬럼이 늘어날수록 인덱스 크기가 커지고 쓰기 비용도 늘기 때문에, 정말 자주 호출되는 핵심 쿼리에만, 그리고 포함 컬럼 수가 적을 때 선별적으로 적용하는 게 좋다.

Spring Boot(JPA)에서 인덱스 정의하기

JPA 엔티티에서는 @Table의 indexes 속성으로 인덱스를 선언한다.

@Entity
@Table(
    name = "post",
    indexes = {
        @Index(
            name = "idx_post_category_created",
            columnList = "category_id, created_at"
        )
    }
)
public class Post {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "category_id", nullable = false)
    private Long categoryId;

    private String title;

    @Column(name = "author_id")
    private Long authorId;

    @Column(name = "created_at", nullable = false)
    private LocalDateTime createdAt;
}

한 가지 주의할 점이 있다. @Index는 Hibernate가 DDL을 생성할 때(ddl-auto=create/update)만 실제 인덱스를 만든다. 운영 환경에서 이 옵션에 의존하는 건 위험하므로, 실제 인덱스는 Flyway나 Liquibase 같은 마이그레이션 도구로 관리하고, 엔티티의 @Index는 "이 테이블엔 이런 인덱스가 있다"는 문서 역할로 두는 편이 안전하다. JPA가 최적의 인덱스를 알아서 만들어 주지는 않는다는 점을 늘 기억하자.

인덱스를 무효화하는 흔한 실수

인덱스를 잘 설계해도 쿼리를 잘못 쓰면 못 탄다. 대표적인 경우 몇 가지를 보자.

먼저, 인덱스 컬럼에 함수나 연산을 씌우면 인덱스를 사용할 수 없다.

-- 인덱스 무효: created_at에 함수 적용
WHERE DATE(created_at) = '2026-07-01'

-- 개선: 범위 조건으로 바꾼다
WHERE created_at >= '2026-07-01 00:00:00'
  AND created_at <  '2026-07-02 00:00:00'

LIKE에서 앞쪽 와일드카드(%)도 인덱스를 무력화한다.

WHERE title LIKE '%스프링%'   -- 인덱스 못 탐 (전체 스캔)
WHERE title LIKE '스프링%'    -- 앞부분 일치는 인덱스 사용 가능

이 밖에도 성별, 처리 상태(boolean)처럼 값의 종류가 적은(카디널리티가 낮은) 컬럼을 단독으로 인덱싱하면 걸러지는 양이 적어 효과가 미미하다. 또한 인덱스는 조회를 빠르게 하는 대신 INSERT/UPDATE마다 함께 갱신되므로, 쓰기가 잦은 테이블에 인덱스를 남발하면 쓰기 성능이 떨어진다. 인덱스는 공짜가 아니다.

마무리

인덱스는 "정렬해 둔 지름길"이다. 지름길을 아무 데나 뚫는다고 빨라지지 않듯, 인덱스도 실제 쿼리가 어떻게 필터링하고 정렬하는지(WHERE, ORDER BY, JOIN)를 먼저 분석한 뒤 그 패턴에 맞춰 설계해야 한다. 그리고 만든 뒤엔 반드시 EXPLAIN으로 의도대로 동작하는지 확인하자. 복합 인덱스의 선두 컬럼 원칙, 필터와 정렬을 동시에 해결하는 컬럼 순서, 그리고 필요할 때만 쓰는 커버링 인덱스 — 이 세 가지만 잡아도 목록 조회 성능은 크게 달라진다.

다음 글에서는 인덱스와 함께 조회 동작에 큰 영향을 주는 트랜잭션 격리 수준과 락(lock)을 다뤄볼 예정이다.