[BE] 인덱스 설정 및 개선사항

[bugoverdose]

설명

• 인덱스를 통해 조회 성능을 개선해보자
• MySQL DB에 테이블별로 100만개의 데이터를 저장하고 서비스에서 실행하는 각종 쿼리들을 실행해보자
• 인덱스 설정 전/후에 따른 실행계획 및 실제 소요시간의 차이를 비교해보자

최종적으로 설정한 인덱스 내역

CREATE INDEX coupon_sender_meeting_date ON coupon (sender_member_id, meeting_date);
CREATE INDEX coupon_receiver_meeting_date ON coupon (receiver_member_id, meeting_date);
CREATE INDEX coupon_history_coupon_id ON member_history (coupon_id);
CREATE INDEX coupon_history_host_member_id ON member_history (host_member_id);

---

coupon 테이블

findAllBySender 쿼리 성능 개선

핵심: sender_member_id 컬럼 단독 활용

• from coupon join member on coupon.sender_member_id = member.id
• where coupon.sender_member_id = 1

그러므로 sender_member_id 컬럼에 대해 단독으로 인덱스를 설정하거나, (sender_member_id, meeting_date) 순서로 인덱스를 함께 설정

• 성능 개선: 430배 개선 (0.776초 => 0.0018초)

• 실행계획 변화

findAllByReceiver 쿼리 성능 개선

핵심: receiver_member_id 컬럼 단독 활용 위와 동일하므로 생략

findAllByMemberAndMeetingDate 쿼리 성능 개선

핵심: sender_member_id, receiver_member_id, meeting_date 활용

• meeting_date 조건을 단독으로 사용되는 쿼리는 없음.
• sender_member_id와 receiver_member_id는 위의 쿼리들에서 단독으로 활용됨.

from coupon 
join member as member1 on coupon.sender_member_id = member1.id
join member as member2 on coupon.receiver_member_id = member2.id
where (coupon.receiver_member_id = 2 or coupon.sender_member_id = 1)
    and coupon.meeting_date >= '2023-01-01 00:00:00'

인덱스 설정 전: SLOW

기본 소요시간: 0.760초

실행계획: coupon 테이블에 대해 Full Table Scan 실행 => BAD!

개별 인덱스를 단독으로 설정하는 방법: GOOD

성능개선: 230배 개선 (0.760초 => 0.0033초)

실행계획: 3가지 단독 인덱스에 대해 INDEX MERGE 발생

복수 컬럼에 대해 인덱스 설정: GOOD

아래와 같이 쌍으로 등록하는 경우, 기본적으로 인덱스를 단독으로 등록한 경우에 비해 조금 더 개선되는 것으로 보임.

성능개선: 361배 개선 (0.760초 => 0.0021초)

create index coupon_sender_meeting_date ON coupon (sender_member_id, meeting_date);
create index coupon_receiver_meeting_date ON coupon (receiver_member_id, meeting_date);

실행계획: 2가지 인덱스에 대해 INDEX MERGE 발생

순서를 잘못 설정하는 경우: No Effect

인덱스 컬럼 쌍에서 meeting_date 기준으로 정렬한 이후에 sender_member_id 기준으로 정렬하는 경우, 인덱스를 타지 않음.

create index coupon_meeting_date_sender ON coupon (meeting_date, sender_member_id);

실행계획: Full Table Scan인 점은 동일하며, filtered 비율 감소 (13% => 3.3%)

Low Cardinaliy 이슈

• cardinality가 낮은 coupon_status 컬럼에 대해 인덱스를 설정하는 것은 쿼리 성능에 유의미한 차이가 없음!

from coupon
inner join member on coupon.receiver_member_id = member.id
where coupon.receiver_member_id = 2
   and coupon.coupon_status = 'READY'

receiver_member_id 컬럼에만 인덱스가 설정된 경우

coupon_status 컬럼에도 인덱스를 설정하는 경우

---

member_history 테이블

findAllByCouponIdOrderByCreatedTimeDesc

• coupon_id 컬럼에 대해 인덱스 설정 필요.

from member_history
left outer join coupon on member_history.coupon_id = coupon.id
where coupon.id = 1

성능개선: 460배 개선 (0.783초 => 0.0017초)

실행계획 변화

findAllByHostMemberOrderByCreatedTimeDesc

• host_member_id 컬럼에 대해 인덱스 설정 필요.

성능개선: 240배 개선 (0.609 => 0.0025)

실행계획 변화