likes 풀스캔 제거와 집계 쿼리 분리

조용현·2026년 3월 29일
Jmeterslow query성능테스트인덱스

문제 해결

목록 보기
7/15
post-thumbnail

배경

post 약 100만 건, comment 약 1,180만 건이 적재된 상태에서 실제 서비스에 더 가까운 조건을 만들기 위해 hot post/comment 기준으로 likes 450만 건을 추가한 뒤 부하 테스트를 진행했습니다.

테스트는 JMeter 100개 스레드, ramp-up 300초, duration 600초 조건으로 진행했습니다. 서버와 MySQL은 EC2 t2.micro 동일 인스턴스의 Docker 환경에서 실행했고, HikariCP 최대 커넥션은 30으로 설정했습니다.

문제 원인

에러 폭발

likes 추가 전과 동일한 조건으로 테스트를 돌리자 에러가 대량 발생했습니다.

popping_before_query_noIndex

에러율: 15.53%
평균 응답시간: 10,460ms
P90: 30,000ms
Throughput: 8.88 req/s
HikariPool-1 - Connection is not available, request timed out after 30000ms
(total=30, active=30, idle=0, waiting=...)

DB 커넥션이 소진되며 요청이 줄을 서다 타임아웃으로 실패하는 패턴이었습니다.

Slow Query Log 확인

Slow Query Log 기준: long_query_time=1s + log_queries_not_using_indexes=ON

Slow Query Log를 보니 likes 테이블에서 450만 건을 전체 스캔하는 쿼리가 찍혀 있었습니다.

Query_time: 47.2s  Rows_examined: 4,500,000

SELECT l.target_id,
       SUM(CASE WHEN l.type = 'LIKE' THEN 1 ELSE 0 END),
       SUM(CASE WHEN l.type = 'DISLIKE' THEN 1 ELSE 0 END),
       MAX(CASE WHEN l.user_id = :userId AND l.type = 'LIKE' THEN 1 ELSE 0 END),
       MAX(CASE WHEN l.user_id = :userId AND l.type = 'DISLIKE' THEN 1 ELSE 0 END)
FROM likes l
WHERE l.target_type = 'COMMENT'
  AND l.target_id IN (...)
GROUP BY l.target_id

댓글 반응 집계 쿼리가 likes 테이블 전체를 풀스캔하고 있었습니다.

인덱스가 사용되지 못한 이유

당시 likes 테이블에는 Hibernate가 자동 생성한 UK 두 개만 있었습니다.

UK (user_id, target_type, target_id, type)
UK (guest_identifier, target_type, target_id, type)

집계 쿼리의 WHERE 조건은 target_type, target_id로 시작합니다. UK의 선두 컬럼은 user_id / guest_identifier이므로 Leftmost Prefix Rule에 의해 두 UK 모두 사용할 수 없었습니다. 450만 건 데이터가 추가되기 전까지는 풀스캔이 문제가 되지 않았지만, 규모가 커지면서 병목이 드러났습니다.

해결 과정

1단계: 인덱스 추가

집계 쿼리에 맞는 인덱스를 추가했습니다.

@Table(
    name = "likes",
    indexes = {
        @Index(name = "idx_like_target", columnList = "target_type,target_id")
    },
    uniqueConstraints = { ... }
)

결과

popping_before_query_index

지표인덱스 없음인덱스 추가
에러율15.53%0%
평균 응답시간10,460ms163ms
P9030,000ms540ms
Throughput8.88 req/s96.7 req/s

에러가 사라지고 응답시간이 정상화됐습니다. 하지만 이 인덱스는 문제를 해결한 것이 아니라 덮은 것에 가까웠습니다.

2단계: 근본 원인 제거

풀스캔을 유발한 쿼리는 다음 레포지토리 메서드였습니다.

@Query(value = """
    SELECT l.target_id AS targetId,
           SUM(CASE WHEN l.type = 'LIKE' THEN 1 ELSE 0 END) AS likeCount,
           SUM(CASE WHEN l.type = 'DISLIKE' THEN 1 ELSE 0 END) AS dislikeCount,
           MAX(CASE WHEN l.user_id = :userId AND l.type = 'LIKE' THEN 1 ELSE 0 END) AS likedByMe,
           MAX(CASE WHEN l.user_id = :userId AND l.type = 'DISLIKE' THEN 1 ELSE 0 END) AS dislikedByMe
    FROM likes l
    WHERE l.target_type = 'COMMENT'
      AND l.target_id IN (:commentIds)
    GROUP BY l.target_id
    """, nativeQuery = true)
List<CommentReactionSummary> findReactionSummaryForMember(
        @Param("commentIds") Collection<Long> commentIds,
        @Param("userId") Long userId);

쿼리 수를 줄이기 위해 likeCount, dislikeCount(집계)와 likedByMe, dislikedByMe(개인화)를 한 쿼리에서 한꺼번에 처리하다 보니 WHERE 조건이 target_type, target_id로 시작할 수밖에 없었고, user_id 선두의 UK는 사용하지 못했습니다.

UK 컬럼 순서를 바꾸는 방법은 왜 안 되나

UK 순서를 target_type → target_id → user_id → type으로 바꾸면 이 집계 쿼리는 인덱스를 탈 수 있습니다. 하지만 그러면 개인화 쿼리(WHERE user_id = ?)가 선두 조건이 맞지 않아 풀스캔이 됩니다.

UK 선두집계 쿼리 (WHERE target_type, target_id)개인화 쿼리 (WHERE user_id)
user_id (현재)풀스캔 ❌인덱스 ✅
target_type인덱스 ✅풀스캔 ❌

집계와 개인화의 WHERE 선두가 서로 달라, 하나의 UK로 두 쿼리를 동시에 커버할 수 없습니다. 어느 방향이든 인덱스를 하나 더 추가해야 하는 구조입니다.

WHERE user_id = ?를 집계 쿼리에 추가하는 방법은 왜 안 되나

SUM(likeCount)는 특정 유저가 아닌 전체 유저의 좋아요 합계입니다. WHERE user_id = ?를 추가하면 해당 유저의 행만 남아 집계 값이 깨집니다. 집계와 개인화를 한 쿼리에서 처리하는 한, WHERE 선두는 target_type, target_id일 수밖에 없습니다.

결국 인덱스로 이 구조를 해결하려면 idx_like_target을 별도로 관리해야 하고, 데이터가 커질수록 이 인덱스의 유지 비용도 함께 커집니다. 쿼리 구조 자체를 바꾸는 것이 더 근본적인 해결이었습니다.

그런데 comment 테이블에는 이미 like_count, dislike_count 컬럼이 비정규화되어 저장되어 있었습니다.

likes 테이블을 집계해야 하는 것  → likedByMe, dislikedByMe (사용자별)
comment 테이블에 이미 있는 것    → likeCount, dislikeCount  (비정규화 컬럼)

count 조회를 위해 450만 건 likes 테이블을 집계할 이유가 없었습니다. 쿼리를 분리했습니다.

① count → comment 테이블 직접 조회

SELECT c.id, c.like_count, c.dislike_count
FROM comment c
WHERE c.id IN (:commentIds)

② 개인화 → likes 테이블, user_id 선두 조건

SELECT l.target_id,
       MAX(CASE WHEN l.type = 'LIKE'    THEN 1 ELSE 0 END) AS likedByMe,
       MAX(CASE WHEN l.type = 'DISLIKE' THEN 1 ELSE 0 END) AS dislikedByMe
FROM likes l
WHERE l.user_id = :userId
  AND l.target_type = 'COMMENT'
  AND l.target_id IN (:commentIds)
GROUP BY l.target_id

user_id가 선두 조건이 되어 UK (user_id, target_type, target_id, type)를 그대로 사용할 수 있습니다. idx_like_target 인덱스가 더 이상 필요 없어졌습니다.

인덱스 구조 정리

// 변경 전
@Table(
    name = "likes",
    indexes = {
        @Index(name = "idx_like_target", columnList = "target_type,target_id")  // 제거
    },
    uniqueConstraints = { ... }
)

// 변경 후: UK만으로 충분
@Table(
    name = "likes",
    uniqueConstraints = {
        @UniqueConstraint(name = "uk_like_user_target_type",
                columnNames = {"user_id", "target_type", "target_id", "type"}),
        @UniqueConstraint(name = "uk_like_guest_target_type",
                columnNames = {"guest_identifier", "target_type", "target_id", "type"})
    }
)

테스트 결과

popping_noIndex

likes 인덱스 구성을 기준으로 3가지를 비교했습니다.

구성likes 인덱스
① 쿼리 분리 전 / No-IndexUK만
② 쿼리 분리 전 / IndexUK + idx_like_target
③ 쿼리 분리 후 / No-IndexUK만
지표① 분리 전 / No-Index② 분리 전 / Index③ 분리 후 / No-Index
에러율15.53%0%0%
평균 응답시간10,460ms163ms167ms
P9030,000ms540ms593ms
Throughput8.88 req/s96.7 req/s96.5 req/s

②와 ③의 수치가 동등합니다. 쿼리 분리 후에는 idx_like_target 없이 UK만으로도 동일한 성능을 유지했습니다. likes 풀스캔이 구조적으로 제거됐음을 확인했습니다.

트레이드오프

count를 comment 테이블에서 읽기 때문에 likes 테이블과 comment 테이블의 like_count / dislike_count가 정합성을 유지해야 합니다. 좋아요 요청이 실패하거나 비정상 종료되는 경우 두 값이 어긋날 수 있습니다.

쿼리가 하나에서 둘로 늘었습니다. comment 조회와 likes 개인화 조회가 분리되어 트랜잭션 내에서 두 번의 DB 왕복이 발생합니다. 다만 집계 인덱스(idx_like_target)를 별도로 관리할 필요가 없어졌고, 두 쿼리 모두 기존 인덱스(PK, UK)를 활용합니다.

정합성 보완: likeCount 재집계 배치

비정규화 구조의 정합성 위험을 보완하기 위해 매일 새벽 4시에 likes 테이블을 재집계해 post.likeCount, comment.likeCount와 비교하고, 불일치가 있으면 자동으로 보정하는 배치 job을 추가했습니다.

-- post 기준 (comment도 동일한 구조)
UPDATE post p
JOIN (
    SELECT target_id,
           SUM(CASE WHEN type = 'LIKE'    THEN 1 ELSE 0 END) AS like_count,
           SUM(CASE WHEN type = 'DISLIKE' THEN 1 ELSE 0 END) AS dislike_count
    FROM likes
    WHERE target_type = 'POST'
    GROUP BY target_id
) l ON p.id = l.target_id
SET p.like_count    = l.like_count,
    p.dislike_count = l.dislike_count
WHERE p.like_count != l.like_count
   OR p.dislike_count != l.dislike_count

불일치가 발생한 경우 보정 건수를 warn 로그로 남겨 이상 징후를 추적할 수 있도록 했습니다.

배치 스케일 한계

현재 배치는 likes 테이블 전체를 GROUP BY로 집계하는 구조라 데이터 규모가 커질수록 실행 시간이 선형으로 증가합니다. 규모가 커지면 배치 자체가 slow query가 되어 MySQL 전체 성능을 끌어내릴 수 있습니다.

다음 단계로 고려할 수 있는 방향은 두 가지입니다.

  • 증분 집계: likes 테이블에 updated_at 컬럼을 추가하고, 마지막 배치 실행 시각 이후 변경된 target_id만 재집계합니다. likes 총량이 늘어도 배치 범위는 "오늘 변동분"으로 고정되어 실행 시간이 데이터 규모에 비례하지 않습니다.
  • CDC(Change Data Capture): Debezium으로 MySQL binlog를 읽어 likes INSERT/DELETE 이벤트 발생 시 실시간으로 카운트를 갱신합니다. 배치 자체가 필요 없어지지만 Kafka + Debezium 인프라가 추가되므로 현재 규모에서는 적합하지 않습니다.

또한 배치 실행 시간이 24시간을 초과하는 경우 다음 스케줄과 겹칠 수 있습니다. @Scheduled(cron = "...") 대신 @Scheduled(fixedDelay = ...) 방식으로 변경하면 이전 실행이 완료된 후 다음 실행이 시작되도록 보장할 수 있습니다.

결론

이번 문제는 단순히 인덱스가 부족해서가 아니라, 집계와 개인화를 하나의 쿼리에서 함께 처리하면서 likes 테이블이 불필요하게 넓은 범위를 읽도록 만든 구조에서 시작됐습니다. idx_like_target을 추가하자 에러율은 15.53%에서 0%로, 평균 응답시간도 10,460ms에서 163ms로 정상화됐지만, 이는 구조적 문제를 인덱스로 보완한 임시 해결에 가까웠습니다.

쿼리를 분리한 뒤에는 count는 comment 테이블의 비정규화 컬럼에서, 개인화는 user_id 선두 조건으로 기존 UK를 활용하게 되어, 별도 집계 인덱스 없이도 평균 167ms, 에러율 0%를 유지할 수 있었습니다. 비정규화 count의 정합성을 위해 재집계 배치가 필요하다는 트레이드오프는 남아 있습니다.

참고

profile
조용현
백엔드 개발자
이전 포스트

JVM 플래그 최적화

다음 포스트

게시글 목록 COUNT 쿼리 제거

0개의 댓글