JOIN에서 비정규화와 인덱스처리로 조회 성능 개선하기

TL;DR

상품 목록 조회 시 좋아요 수 정렬을 위해 Like 테이블과 JOIN하니 인덱스가 적용되지 않아 성능이 나빴다. Product 테이블에 likeCount 컬럼을 비정규화하고 인덱스를 추가해 성능을 개선했다. 읽기가 쓰기보다 압도적으로 많은 상황에서는 정합성 동기화 비용보다 조회 성능 개선이 더 중요하다고 판단했다.

문제 상황

커머스 서비스에서 상품 목록 API에 다음 기능을 추가하려고 했다.

요구사항

• 브랜드별 필터링
• 좋아요 수 기준 정렬

로컬에서 소량 데이터로 테스트할 땐 괜찮았는데, 10만 건 데이터를 넣고 테스트하니 성능이 나빴다.

AS-IS: 초기 구현 (인덱스 없음)

좋아요 수로 정렬하기 위해 Like 테이블과 LEFT JOIN 후 COUNT를 했다.

SELECT
    p.id,
    p.name,
    p.price,
    b.name AS brand_name,
    COUNT(l.id) AS like_count
FROM loopers_product p
LEFT JOIN loopers_brand b ON p.brand_id = b.id
LEFT JOIN loopers_like l ON p.id = l.product_id
WHERE p.brand_id = 42
GROUP BY p.id, p.name, p.price, b.name
ORDER BY COUNT(l.id) DESC
LIMIT 20 OFFSET 0;

문제점

• Like 테이블과 LEFT JOIN
• GROUP BY로 매번 좋아요 수 집계
• COUNT() 연산을 매 조회마다 수행

인덱스가 아예 없는 상태에서 EXPLAIN을 돌려보니 심각한 성능 문제가 보였다.

EXPLAIN SELECT
    p.id,
    p.name,
    p.price,
    b.name AS brand_name,
    COUNT(l.id) AS like_count
FROM loopers_product p
LEFT JOIN loopers_brand b ON p.brand_id = b.id
LEFT JOIN loopers_like l ON p.id = l.product_id
WHERE p.brand_id = 42
GROUP BY p.id, p.name, p.price, b.name
ORDER BY COUNT(l.id) DESC
LIMIT 20;

+----+-------------+-------+-------+---------------------+---------------------+---------+--------------+-------+------+--------------------------------------+
| id | select_type | table | type  | possible_keys       | key                 | key_len | ref          | rows  | filt | Extra                                |
+----+-------------+-------+-------+---------------------+---------------------+---------+--------------+-------+------+--------------------------------------+
|  1 | SIMPLE      | p     | ALL   |                     |                     |         |              | 99611 | 1    | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | const | PRIMARY             | PRIMARY             | 8       | const        | 1     | 100  |                                      |
|  1 | SIMPLE      | l     | ref   | idx_like_product_id | idx_like_product_id | 8       | loopers.p.id | 1     | 100  | Using index                          |
+----+-------------+-------+-------+---------------------+---------------------+---------+--------------+-------+------+--------------------------------------+

문제점

• type: ALL: 풀 테이블 스캔 (99,611개 행 전체 스캔)
• Using temporary: 임시 테이블 생성 (GROUP BY 때문)
• Using filesort: 정렬을 위해 파일 정렬 수행 (ORDER BY COUNT 때문)

 

개선 1: 브랜드 필터링 인덱스 추가

우선 브랜드 필터링부터 개선하기 위해 인덱스를 추가했다.

CREATE INDEX idx_product_brand_id ON loopers_product(brand_id);

브랜드 인덱스 추가 후 EXPLAIN

EXPLAIN SELECT
    p.id,
    p.name,
    p.price,
    b.name AS brand_name,
    COUNT(l.id) AS like_count
FROM loopers_product p
LEFT JOIN loopers_brand b ON p.brand_id = b.id
LEFT JOIN loopers_like l ON p.id = l.product_id
WHERE p.brand_id = 42
GROUP BY p.id, p.name, p.price, b.name
ORDER BY COUNT(l.id) DESC
LIMIT 20;

+----+-------------+-------+-------+----------------------+----------------------+---------+--------------+------+------+--------------------------------------+
| id | select_type | table | type  | possible_keys        | key                  | key_len | ref          | rows | filt | Extra                                |
+----+-------------+-------+-------+----------------------+----------------------+---------+--------------+------+------+--------------------------------------+
|  1 | SIMPLE      | p     | ref   | idx_product_brand_id | idx_product_brand_id | 8       | const        | 1024 | 10   | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | const | PRIMARY              | PRIMARY              | 8       | const        | 1    | 100  |                                      |
|  1 | SIMPLE      | l     | ref   | idx_like_product_id  | idx_like_product_id  | 8       | loopers.p.id | 1    | 100  | Using index                          |
+----+-------------+-------+-------+----------------------+----------------------+---------+--------------+------+------+--------------------------------------+

개선 효과

• type: ALL → ref: 인덱스를 사용한 조회로 변경
• 검사 행 수: 99,611 → 1,024로 대폭 감소 (약 97배 개선)

하지만 여전히 Using temporary; Using filesort가 남아있었다. 좋아요 순 정렬 문제는 해결되지 않았다.

 

왜 정렬에서 인덱스가 적용되지 않았나

브랜드 필터링은 개선됐지만, ORDER BY COUNT(l.id) DESC는 집계 함수라 인덱스를 탈 수 없다는 걸 알게 되었다.

인덱스는 컬럼 값에 대해 생성되는데, COUNT는 조회 시점에 계산되는 값이라 미리 인덱스를 만들 수 없다.

그럼 어떻게 해야 하나?

좋아요 수를 미리 Product 테이블에 저장해두면 되지 않을까 생각했다.

 

개선 2: 비정규화 (likeCount 컬럼 추가)

결정 과정

매번 COUNT를 계산하는 대신, Product 테이블에 like_count 컬럼을 추가하기로 했다.

고민했던 점

정규화를 깨는 것이 과연 옳은가? 좋아요가 생기거나 취소될 때마다 Product 테이블도 업데이트해야 한다. 동기화 로직이 복잡해지고, 실수로 좋아요 수가 틀어질 수도 있다.

하지만 다음 이유로 비정규화를 선택했다.

1. 읽기 빈도 >> 쓰기 빈도
   • 상품 목록 조회는 초당 수십~수백 번
   • 좋아요 등록/취소는 비교적 드물다
2. 좋아요 수는 정확도보다 성능이 중요
   • 1~2 정도 차이는 사용자 경험에 큰 영향 없음
   • 목록 로딩이 느린 게 더 문제
3. 인덱스를 탈 수 있다
   • 컬럼으로 저장하면 인덱스 생성 가능
   • ORDER BY에서 인덱스 활용 가능

구현

Product 엔티티 수정

@Table(
    name = "loopers_product",
    indexes = [
        Index(name = "idx_product_brand_id", columnList = "brand_id"),
        Index(name = "idx_product_like_count", columnList = "like_count"),
    ],
)
class Product(
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Long = 0,

    @Column(nullable = false)
    val brandId: Long,

    @Column(nullable = false)
    var likeCount: Long = 0,

    // ...
) {
    fun incrementLikeCount() {
        this.likeCount++
    }

    fun decrementLikeCount() {
        if (this.likeCount > 0) {
            this.likeCount--
        }
    }
}

 

좋아요 등록/취소 시 동기화

@Service
class LikeService(
    private val likeRepository: LikeRepository,
    private val productService: ProductService,
) {
    @Transactional
    fun addLike(userId: Long, productId: Long) {
        likeRepository.save(Like.of(userId, productId))

        val product = productService.getProduct(productId)
        product.incrementLikeCount()
    }

    @Transactional
    fun removeLike(userId: Long, productId: Long) {
        val like = likeRepository.findByUserIdAndProductId(userId, productId)
            ?: throw CoreException(ErrorType.LIKE_NOT_FOUND)

        likeRepository.delete(like)

        val product = productService.getProduct(productId)
        product.decrementLikeCount()
    }
}

 

TO-BE: 비정규화 후 쿼리

SELECT
    p.id,
    p.name,
    p.price,
    b.name AS brand_name,
    p.like_count,
    p.created_at,
    p.updated_at
FROM loopers_product p
LEFT JOIN loopers_brand b ON p.brand_id = b.id
WHERE p.brand_id = 42
ORDER BY p.like_count DESC
LIMIT 20 OFFSET 0;

변경 사항

• Like 테이블 JOIN 제거
• GROUP BY 제거
• COUNT() → p.like_count로 변경

인덱스 없을 때

EXPLAIN SELECT
    p.id,
    p.name,
    p.price,
    b.name AS brand_name,
    p.like_count
FROM loopers_product p
LEFT JOIN loopers_brand b ON p.brand_id = b.id
WHERE p.brand_id = 42
ORDER BY p.like_count DESC
LIMIT 20;

+----+-------------+-------+-------+---------------+------+---------+-------+-------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref   | rows  | filt | Extra                       |
+----+-------------+-------+-------+---------------+------+---------+-------+-------+------+-----------------------------+
|  1 | SIMPLE      | p     | ALL   |               |      |         |       | 99611 | 1    | Using where; Using filesort |
|  1 | SIMPLE      | b     | const | PRIMARY       | PRIMARY | 8    | const | 1     | 100  |                             |
+----+-------------+-------+-------+---------------+------+---------+-------+-------+------+-----------------------------+

여전히 풀 테이블 스캔이다. 브랜드 인덱스를 추가해보자.

브랜드 인덱스만 추가했을 때

EXPLAIN SELECT
    p.id,
    p.name,
    p.price,
    b.name AS brand_name,
    p.like_count
FROM loopers_product p
LEFT JOIN loopers_brand b ON p.brand_id = b.id
WHERE p.brand_id = 42
ORDER BY p.like_count DESC
LIMIT 20;

+----+-------------+-------+-------+----------------------+----------------------+---------+-------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys        | key                  | key_len | ref   | rows | filt | Extra                       |
+----+-------------+-------+-------+----------------------+----------------------+---------+-------+------+------+-----------------------------+
|  1 | SIMPLE      | p     | ref   | idx_product_brand_id | idx_product_brand_id | 8       | const | 1025 | 10   | Using where; Using filesort |
|  1 | SIMPLE      | b     | const | PRIMARY              | PRIMARY              | 8       | const | 1    | 100  |                             |
+----+-------------+-------+-------+----------------------+----------------------+---------+-------+------+------+-----------------------------+

브랜드 필터링은 인덱스를 타지만, 정렬에서 Using filesort가 발생한다.

좋아요 인덱스도 추가했을 때

CREATE INDEX idx_product_like_count ON loopers_product(like_count);

EXPLAIN SELECT
    p.id,
    p.name,
    p.price,
    b.name AS brand_name,
    p.like_count
FROM loopers_product p
LEFT JOIN loopers_brand b ON p.brand_id = b.id
WHERE p.brand_id = 42
ORDER BY p.like_count DESC
LIMIT 20;

+----+-------------+-------+-------+----------------------+----------------------+---------+-------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys        | key                  | key_len | ref   | rows | filt | Extra                       |
+----+-------------+-------+-------+----------------------+----------------------+---------+-------+------+------+-----------------------------+
|  1 | SIMPLE      | p     | ref   | idx_product_brand_id | idx_product_brand_id | 8       | const | 1025 | 100  | Using filesort              |
|  1 | SIMPLE      | b     | const | PRIMARY              | PRIMARY              | 8       | const | 1    | 100  |                             |
+----+-------------+-------+-------+----------------------+----------------------+---------+-------+------+------+-----------------------------+

개선 효과

• Using temporary 제거: 임시 테이블 생성 불필요 (GROUP BY 제거)
• Like 테이블 JOIN 제거: 테이블 스캔 1개 감소
• filtered: 10 → 100: WHERE 조건 필터링 효율 개선

하지만 여전히 Using filesort가 남아있다. 이는 MySQL이 단일 인덱스만 선택하기 때문이다.

• brand_id 인덱스로 필터링 → 그 후 like_count로 정렬 (filesort 발생)
• 또는 like_count 인덱스로 정렬 → 그 후 brand_id로 필터링

둘 중 하나만 선택할 수 있어서 정렬이나 필터링 중 하나는 인덱스를 활용하지 못한다.

 

트레이드오프

비정규화의 대가

장점

• 조회 성능 대폭 향상
• JOIN, GROUP BY 제거
• 임시 테이블 생성 제거

단점

• 좋아요 등록/취소 시 Product 테이블도 업데이트 필요
• 동기화 로직이 실패하면 정합성 깨질 수 있음
• 스토리지 비용 증가 (컬럼 하나 추가)

처음에는 "정합성이 깨지면 어떡하지?"라는 걱정이 컸다. 하지만 다음과 같이 생각을 정리했다.

1. 트랜잭션으로 묶으면 정합성 보장
   • 좋아요 등록과 카운트 증가를 같은 트랜잭션에서 처리
   • 둘 중 하나라도 실패하면 롤백
2. 배치로 주기적 보정 가능
   • 매일 새벽에 실제 COUNT와 비교해 보정
   • 만약을 대비한 안전장치
3. 좋아요 수는 완벽한 정확도가 필요 없음
   • 결제 금액이나 재고처럼 절대 틀리면 안 되는 게 아님
   • 1~2 차이는 사용자가 인지 못 함

 

배운 점

1. EXPLAIN으로 정확히 파악하자

처음엔 "쿼리가 느린 것 같은데..."라는 감으로 접근했다. 하지만 EXPLAIN을 돌려보니 정확히 어디가 문제인지 알 수 있었다.

Using temporary; Using filesort가 나오면 인덱스를 못 타고 있다는 신호다. 이걸 보고 비정규화 방향으로 결정할 수 있었다.

 

2. 정규화는 절대 선이 아니다

처음엔 "정규화를 깨는 건 나쁜 설계 아닌가?"라는 생각이 강했다. 하지만 서비스 특성에 따라 비정규화가 더 나은 선택일 수 있다는 걸 느꼈다.

읽기가 압도적으로 많고, 약간의 동기화 비용이 드는 상황에서는 비정규화가 합리적이다.

 

3. 단계적으로 개선하자

인덱스 없음 → 브랜드 인덱스 추가 → 비정규화 순으로 단계적으로 적용했다.

각 단계마다 EXPLAIN으로 성능을 측정하며 어떤 개선이 얼마나 효과가 있는지 파악할 수 있었다.

 

마무리

처음엔 "인덱스 하나 추가하면 되겠지"라고 생각했다. 하지만 집계 함수는 인덱스를 탈 수 없다는 걸 알게 되었고, 비정규화라는 선택을 하게 되었다.

정규화를 깨는 게 처음엔 찝찝했지만, 읽기가 압도적으로 많은 커머스 특성상 합리적인 선택이었다고 생각한다.

EXPLAIN을 통해 정확한 병목 지점을 파악하고, 단계적으로 개선하는 습관을 가져야겠다.