1. VoteItem 조회 쿼리 개선: 서브쿼리 최적화

먼저 가장 단순한 형태인 VoteItem 조회 쿼리부터 분석했습니다. 투표 항목을 조회하면서 현재 로그인한 사용자의 투표 여부를 exists 서브쿼리로 판단하는 구조입니다.

[문제 상황 분석]

select
    vije1_0.post_id,
    vije1_0.vote_item_id,
    vije1_0.item_name,
    vije1_0.count,
    exists(select
               1
           from
               vote_participants vpje1_0
           where
               vpje1_0.status in ('ACTIVE')
             and vpje1_0.vote_item_id=vije1_0.vote_item_id
             and vpje1_0.user_id=1)
from
    vote_items vije1_0
where
    vije1_0.status in ('ACTIVE')
  and vije1_0.post_id in (1, 2, 3)

실행 계획을 확인했을 때 vote_items 테이블은 이미 Index Condition을 사용 중이었습니다. 별도로 인덱스를 설정하지 않았음에도 post_id에 인덱스가 걸려 있는 이유는 MySQL의 특성 때문입니다.

<aside> ☝🏼

외래키 인덱스 (Foreign Key Index)

외래 키 인덱스 (Foreign Key Index) MySQL(InnoDB)은 외래 키 제약 조건을 생성할 때, 해당 외래 키 컬럼에 인덱스가 없다면 내부적으로 인덱스를 자동 생성합니다. 이는 참조 무결성 체크를 효율적으로 수행하기 위함입니다.

실제로 위와 같이 vote_items의 FK인 post_id가 인덱스로 걸려있는 것을 확인했습니다.

• 공식문서 확인하기 </aside>

하지만 진짜 병목은 내부 서브쿼리인 vote_participants에 있었습니다. 실행 계획상 DEPENDENT SUBQUERY로 분류되어, 외부 쿼리의 결과 행 수만큼 반복 실행되는 구조였습니다.

<aside> ☝🏼

DEPENDENT SUBQUERY

외부 쿼리에서 전달받은 값을 사용하여 서브쿼리가 실행되는 구조입니다. 외부 쿼리의 결과가 N개라면 서브쿼리도 N번 실행될 가능성이 높아 성능 저하의 원인이 됩니다.

</aside>

[해결 방법: 복합 인덱스 생성]

사용자가 특정 항목에 투표했는지 여부는 (user_id, vote_item_id) 조합으로 결정됩니다. 하나의 투표에는 한 번만 참여할 수 있다는 비즈니스 로직을 반영하여 유니크 복합 인덱스를 생성했습니다.

-- (vote_participants에 vote_item_id, user_id 유니크 키 추가)
ALTER TABLE vote_participants ADD UNIQUE KEY uq_user_voteitem (user_id, vote_item_id)

<aside> ❓

1. 유니크 제약조건을 건 이유

   • 요구사항 관점 : 투표 참여자는 하나의 투표에는 한 번만 참여 가능하다.
   • 성능 관점 : 유니크 인덱스로 결과가 최대 1건임이 보장되면 EXISTS 또는 LIMIT 1 조회는 첫 매칭을 찾는 즉시 탐색을 중단할 수 있어 불필요한 스캔이 줄어 성능에 유리하다.

2. status를 복합인덱스에 포함하지 않은 이유

   저희 서비스에서는 VoteItem / VoteParticipant 테이블에 소프트 딜리트가 아닌 하드 딜리트 정책을 사용하고 있습니다. 즉, 실제 테이블에는 status = ‘ACTIVE’인 데이터만 존재합니다. 따라서, where문을 통해 추가 필터로 걸러내고 있더라도 성능에 큰 영향을 주는 요소는 아니라고 판단했습니다.

</aside>

[개선 결과]

-> Filter: (vije1_0.`status` = 'ACTIVE')  (cost=11.4 rows=2.33) (actual time=0.127..0.145 rows=7 loops=1)
    -> Index range scan on vije1_0 using FK4hktdf9rr9dbg7b3txcx5qoom over (post_id = 204) OR (post_id = 206) OR (post_id = 208), with index condition: (vije1_0.post_id in (204,206,208))  (cost=11.4 rows=7) (actual time=0.122..0.139 rows=7 loops=1)
-> Select #2 (subquery in projection; dependent)
		-> Single-row index lookup on vpje1_0 using uq_user_voteitem (user_id=1, vote_item_id=vije1_0.vote_item_id)
						(actual time=0.0109..0.0109 rows=0 loops=7)
				-> Filter: (vpje1_0.status='ACTIVE') (actual time=0.0111..0.0111 rows=0 loops=7)
						-> Limit: 1 row(s) (actual time=0.0112..0.0112 rows=0 loops=7)

• 쿼리 실행시간 분석