인덱스 설계와 카디널리티

상품 목록 조회에 숨어있는 인덱스 설계 이야기

1. 상품 목록 조회, 뭐가 문제인데?

우리는 쇼핑몰에서 당연하듯이 "좋아요 순 정렬", "브랜드 필터" 같은 조회 기능을 사용한다. 겉으로 보기엔 단순한 기능이지만, 데이터가 10만 건을 넘어가는 순간 쿼리 한 줄의 차이가 응답 시간을 수십 배까지 벌려놓는다.

이번 과제에서 상품 목록 API의 조회 조건은 크게 세 가지였다.

브랜드 필터링 (brand_id)
좋아요 순 정렬 (like_count DESC)
Soft Delete 필터 (deleted_at IS NULL)
인덱스 없이 이 조건들을 조합하면 MySQL은 매번 테이블 전체를 훑으면서 조건에 맞는 행을 찾고, 그 결과를 메모리에서 정렬한다. 10만 건 정도면 사용자가 체감할 수준의 지연이 발생할 수 있다.

 

2. 인덱스를 설계하기 전에 — 카디널리티부터 생각하자

인덱스를 "아무 컬럼에나 붙이면 빨라진다"고 생각하기 쉽지만, 인덱스의 효율은 카디널리티(Cardinality)에 달려 있다. 카디널리티란 해당 컬럼이 가지는 고유한 값의 수를 의미한다.

컬럼카디널리티설명

컬럼	카디널리티	설명
id	매우 높음	행마다 고유값. PK 가장 효율적
brand_id	중간	브랜드 수에 비례. 수십~수백 개 수준
deleted_at	매우 낮음	대부분 NULL, 일부만 값 있음. 사실상 2종류
like_count	중간~높음	0부터 수천까지 분포. 정렬 기준으로 사용

여기서 핵심은 복합 인덱스를 구성할 때 카디널리티가 높은 컬럼을 앞에 배치해야 한다는 원칙이다.

그런데 우리 쿼리를 다시 보면:

SELECT * FROM product

WHERE brand_id = ? AND deleted_at IS NULL

ORDER BY like_count DESC

brand_id는 등호 조건(=)이고, deleted_at은 IS NULL 조건이고, like_count는 정렬 기준이다. 복합 인덱스에서 등호 조건에 사용되는 컬럼을 앞에 두고, 정렬 기준을 뒤에 두면 MySQL이 인덱스만으로 필터링과 정렬을 동시에 처리할 수 있다. 이를 인덱스의 "leftmost prefix" 규칙이라고 한다.

 

3. 복합 인덱스 설계

이 규칙을 적용해서 두 개의 복합 인덱스를 설계했다.

@Entity
@Getter
@Table(name = "product", indexes = {
    @Index(name = "idx_product_brand_deleted_like", columnList = "brand_id, deleted_at, like_count"),
    @Index(name = "idx_product_deleted_like", columnList = "deleted_at, like_count")
})
public class ProductModel extends BaseEntity {

    private Long brandId;
    private String name;

각 인덱스의 역할은 이렇다.

idx_product_brand_deleted_like (brand_id, deleted_at, like_count)

가장 핵심적인 인덱스다. 다음 쿼리를 커버한다:

-- 브랜드 필터 + 좋아요 순 정렬

WHERE brand_id = 3 AND deleted_at IS NULL ORDER BY like_count DESC

brand_id로 후보군을 좁히고 → deleted_at IS NULL로 활성 상품만 필터링하고 → like_count 순서대로 이미 정렬된 상태로 인덱스를 타기 때문에, MySQL이 별도의 filesort 없이 결과를 반환할 수 있다.

idx_product_deleted_like (deleted_at, like_count)

브랜드 필터 없이 전체 상품을 좋아요 순으로 조회할 때 사용된다:

-- 전체 상품 좋아요 순 정렬

WHERE deleted_at IS NULL ORDER BY like_count DESC

왜 deleted_at의 카디널리티가 낮은데 인덱스에 포함시켰나?
deleted_at은 사실상 NULL 아니면 값이 있는 2종류뿐이라 단독 인덱스로는 효율이 매우 낮다. 하지만 복합 인덱스의 중간에 배치하면 이야기가 달라진다. brand_id로 범위를 좁힌 후 deleted_at IS NULL 조건까지 인덱스 레벨에서 처리하면, 실제 테이블 데이터를 읽지 않고도 필터링이 끝난다. 카디널리티가 낮더라도 등호/IS NULL 조건으로 사용되면 복합 인덱스 내에서 충분히 의미가 있다.

 

4. 좋아요 테이블 인덱스

좋아요 테이블(product_like)에도 자주 사용되는 쿼리 패턴에 맞춰 인덱스를 추가했다.

@Entity
@Getter
@Table(name = "product_like",
        uniqueConstraints = @UniqueConstraint(columnNames = {"member_id", "product_id"}),
        indexes = {
            @Index(name = "idx_like_product_deleted", columnList = "product_id, deleted_at"),
            @Index(name = "idx_like_member_deleted", columnList = "member_id, deleted_at")
        })
public class LikeModel extends BaseEntity {

    private Long memberId;
    private Long productId;

인덱스커버하는 쿼리

idx_like_product_deleted	WHERE product_id = ? AND deleted_at IS NULL → 상품별 좋아요 수 COUNT
idx_like_member_deleted	WHERE member_id = ? AND deleted_at IS NULL → 회원이 좋아요한 목록 조회

이미 (member_id, product_id) unique constraint가 있지만, 이것만으로는 deleted_at IS NULL 조건이 포함된 쿼리를 효율적으로 처리할 수 없다. soft delete 패턴을 쓰는 이상 deleted_at을 포함한 복합 인덱스가 별도로 필요하다.

 

5. 정렬을 DB로 내리기

인덱스를 아무리 잘 설계해도, 정렬을 Java 애플리케이션 레벨에서 하면 인덱스의 정렬 효과를 전혀 활용할 수 없다.

기존 코드를 보면:

public List<ProductDetailInfo> getProductsWithDetail(Long brandId, ProductSortType sort) {
    return productService.getActiveProducts(brandId).stream()
            .map(this::toDetailInfo)
            .filter(Objects::nonNull)
            .sorted(comparatorOf(sort))   // Java Comparator로 메모리에서 정렬
            .toList();
}

DB에서 전체 데이터를 가져온 다음 Java의 Comparator로 정렬하고 있다. 이러면 인덱스에 like_count를 아무리 넣어도 MySQL의 ORDER BY 최적화가 동작하지 않는다.

이걸 DB 레벨 정렬로 바꿨다:

// TO-BE: DB 정렬
public List<ProductDetailInfo> getProductsWithDetail(Long brandId, ProductSortType sort) {
    return productService.getActiveProducts(brandId, sort).stream()
            .map(this::toDetailInfo)
            .filter(Objects::nonNull)
            .toList();   // 정렬은 이미 DB에서 완료됨
}

ProductSortType을 Spring Data JPA의 Sort로 변환하는 부분은 인프라 레이어에서 처리했다:

private Sort toJpaSort(ProductSortType sort) {
    return switch (sort) {
        case LATEST -> Sort.by(Sort.Direction.DESC, "createdAt");
        case PRICE_ASC -> Sort.by(Sort.Direction.ASC, "price");
        case LIKES_DESC -> Sort.by(Sort.Direction.DESC, "likeCount");
    };
}

도메인 레이어는 ProductSortType enum만 알고 있고, Spring Data의 Sort 클래스는 인프라 레이어에 격리되어 있다. DDD 구조를 유지하면서도 DB 정렬을 활용할 수 있게 된 셈이다.

6. AS-IS vs TO-BE 비교

쿼리 실행 횟수

AS-IS: 상품 N개 조회 시
→ SELECT * FROM product WHERE brand_id = ? AND deleted_at IS NULL (1회)
→ SELECT COUNT(*) FROM product_like WHERE product_id = ? AND deleted_at IS NULL (×N회)
→ Java 메모리에서 정렬
= 총 N+1 쿼리 + filesort

TO-BE: 상품 N개 조회 시
→ SELECT * FROM product WHERE brand_id = ? AND deleted_at IS NULL
ORDER BY like_count DESC (1회)
→ like_count는 product 테이블 컬럼에서 직접 읽음
= 총 1 쿼리 + 인덱스 정렬

7. 고민한 점

인덱스를 설계하면서 가장 많이 고민한 건 "인덱스를 몇 개까지 만들어야 하는가" 였다. 인덱스가 많아지면 조회는 빨라지지만, 쓰기(INSERT/UPDATE) 시 인덱스를 갱신하는 비용이 늘어난다. 특히 like_count는 좋아요 등록/취소마다 업데이트되는 컬럼이라, 이 컬럼이 포함된 인덱스가 많을수록 쓰기 부하가 커진다.

결국 현재 조회 패턴에서 실제로 사용되는 쿼리 조합만을 기준으로 인덱스를 설계했다. "나중에 필요할 수도 있으니까"라는 이유로 인덱스를 추가하는 건 오히려 성능을 해칠 수 있다는 걸 이번에 체감했다. 인덱스도 결국 비용이 드는 자료구조이고, 필요한 만큼만 존재해야 한다.

또 하나 고민했던 건 deleted_at 컬럼의 위치였다. 카디널리티가 낮으니 인덱스에 넣는 게 의미가 있나 싶었는데, 복합 인덱스에서 등호 조건으로 사용되면 그 다음 컬럼의 정렬 효과를 살릴 수 있기 때문에, "카디널리티가 낮다 = 인덱스에 불필요하다"는 단순한 공식이 항상 맞는 건 아니라는 걸 배웠다. 결국 인덱스 설계는 카디널리티만 보는 게 아니라, 실제 쿼리에서 해당 컬럼이 어떤 역할을 하는지까지 함께 봐야 한다.