인덱스를 활용한 쿼리 최적화

이프·2025년 7월 12일
mysql성능 최적화인덱스

back-end

목록 보기
12/16

목적

MVP 기능 구현이 끝나고 남은 기간 내 성능 최적화를 수행한다.

왜 성능 최적화를 할까?

  1. UX 개선
    사용자 입장에서 생각해보자.
    처음 서비스가 막 출시되고 데이터를 조회하는데 시간이 지날수록 서비스 처리 속도가 느리다면, 사용하고 싶지 않을 것이다. 특히, 대한민국은 빠름의 국가라서 느리면 매우 답답한 상황에 서비스를 이용하지 않을 수도 있다.

  2. DeadLock 방지
    Command성 Query를 일부러 길게 가져가면서 Query 접근을 조금이라도 줄이면서 DB Connection을 아끼는 등의 장점을 가질 수 있다.

    다만, 이 장점만 바라보고 Command에서 사용하는 Query에도 Index를 설정하지 않게 된다면 스레드 간 Connection을 점유하면서 결국 데드락이 발생할 수 있다.

    CQRS, 분산 아키텍처, Application Lock 등 또 다양한 방법으로 처리할 수도 있지만, Monolith 에서는 Command 성 쿼리에도 Index를 걸어주는게 옳다고 생각한다.

  3. 확장성 확보
    서비스 성장에 따른 데이터 증가에 미리 대비하여 안정적인 서비스 운영 기반을 마련한다.

쿼리 최적화 전

// Command 성 Query
public interface PointTransactionRepository extends JpaRepository<PointTransaction, Long> {

	@Lock(LockModeType.PESSIMISTIC_WRITE)
	@Query("""
		SELECT pt.balanceAfter 
				FROM PointTransaction pt 
						WHERE pt.memberId = :memberId 
								ORDER BY pt.id DESC 
										LIMIT 1
		""")
	Optional<PointAmount> findLatestBalance(@Param("memberId") Long memberId);
}

// Query
public interface PointTransactionQueryRepository {

	MemberPointSummary findMemberPointSummary(Long memberId);

	CursorTemplate<Long, MyPointTransactionDto> getPointTransaction(Long memberId, Long cursor, TransactionType type);

	Map<Long, BigDecimal> findEarnedPointByMember(List<Long> memberIds);

	PageTemplate<PointTransactionDto> findPointTransactionByMember(
		Long memberId,
		PointTransactionSearchCondition condition
	);

	List<PointTransactionDto> findPointTransactionByMemberForExcel(Long memberId);
}

최적화할 코드 정보이다. 조회에 적용되는 것은 총 6개이다.
데이터는 100만건을 기준으로 확인한다.

최신 잔액 조회

EXPLAIN SELECT BALANCE_AFTER 
FROM POINT_TRANSACTIONS 
WHERE MEMBER_ID = 5000 
ORDER BY POINT_TRANSACTION_ID DESC 
LIMIT 1;

ANALYZE

PK Reverse
actual total time: 7.08ms
actual max rows:14100

포인트 내역 요약

EXPLAIN SELECT
    COALESCE(
            (SELECT pt2.BALANCE_AFTER
             FROM POINT_TRANSACTIONS pt2
             WHERE pt2.MEMBER_ID = 5000
             ORDER BY pt2.POINT_TRANSACTION_ID DESC
            LIMIT 1), 0
    ) as current_balance,
    COALESCE(SUM(CASE WHEN pt.TYPE = 'EARN' THEN pt.POINT_AMOUNT ELSE 0 END), 0) as total_earned,
    COALESCE(SUM(CASE WHEN pt.TYPE = 'SPEND' THEN pt.POINT_AMOUNT ELSE 0 END), 0) as total_spent
FROM POINT_TRANSACTIONS pt
WHERE pt.MEMBER_ID = 5000;

ANALYZE

PK Reverse
actual total time: 150ms
actual max rows: 1e+6

포인트 내역 조회 (커서 페이징)

EXPLAIN SELECT
    POINT_TRANSACTION_ID,
    TARGET_TYPE,
    DESCRIPTION,
    CASE WHEN TYPE='EARN' THEN POINT_AMOUNT ELSE 0 END as earn_amount,
    CASE WHEN TYPE='SPEND' THEN POINT_AMOUNT ELSE 0 END as spend_amount,
    BALANCE_AFTER,
    CREATED_DATE
FROM POINT_TRANSACTIONS
WHERE MEMBER_ID = 5000
  AND POINT_TRANSACTION_ID < 400000
ORDER BY POINT_TRANSACTION_ID DESC
    LIMIT 10;

ANALYZE
PK Reverse
actual total time: 32.2ms
actual max rows: 497055

아래는 필터링에 type을 추가한 결과입니다.

ANALYZE
PK Reverse
actual total time: 69.3ms
actual max rows: 163294

여러 사용자 별 지급된 포인트

EXPLAIN SELECT MEMBER_ID, SUM(POINT_AMOUNT) as total_earned
FROM POINT_TRANSACTIONS
WHERE TYPE = 'EARN'
  AND MEMBER_ID IN (1000, 2000, 3000, 4000, 5000)
GROUP BY MEMBER_ID;

ANALYZE
Full Scan
actual total time: 198ms
actual max rows: 1e+6

사용자 포인트 지급 내역 (관리자 페이징)

EXPLAIN ANALYZE
SELECT
    POINT_TRANSACTION_ID,
    MEMBER_ID,
    TARGET_TYPE,
    DESCRIPTION,
    POINT_AMOUNT,
    BALANCE_AFTER,
    TYPE,
    CREATED_DATE
FROM POINT_TRANSACTIONS
WHERE MEMBER_ID = 5000
ORDER BY POINT_TRANSACTION_ID DESC
    LIMIT 20 OFFSET 0;

ANALYZE
PK REVERSE
actual total time: 188ms
actual max rows: 1e+6

엑셀 다운로드용 전체 조회

EXPLAIN SELECT
    POINT_TRANSACTION_ID,
    TARGET_TYPE,
    DESCRIPTION,
    POINT_AMOUNT,
    BALANCE_AFTER,
    TYPE,
    CREATED_DATE
FROM POINT_TRANSACTIONS
WHERE MEMBER_ID = 5000
ORDER BY POINT_TRANSACTION_ID DESC;

ANALYZE
PK REVERSE
actual total time: 238ms
actual max rows: 1e+6

성능 저하 포인트 식별

현재 MySQL 예상대로 동작하는 것도 잘 없고 성능이 많이 느린 것을 확인할 수 있다.

API 조회 요청이 가장 잦을 것으로 보이는 것은 포인트 내역 요약인데, 여러 사용자 100명이 같은 시간에 조회한다면 15초(150ms × 100명)나 소요되게 된다. 실제 서버에서 DB Connection 오버헤드와 네트워크 통신 비용까지 고려하면 더 소요될 것이다.

쿼리 최적화 후

인덱스 선택 기준으로 크게 4가지가 있다.
1. 선택도
2. 카디널리티
3. 활용도 (가장 중요)
4. DML 작업 빈도

현재 활용되는 필드는 memberId, PK, type 이 있다.
이 중 memberId가 활용도에서 가장 높고 선택도가 높다.
그 다음으로 PK가 활용도가 가장 높고 카디널리티는 높다.
type은 1번 활용하지만 카디널리티가 낮고 선택도는 높다.

우선 가장 명확한 것은 memberId와 PK가 가장 중요하고, type은 1회만 사용되므로 type을 추가했을 때와 추가하지 않았을 때를 비교해본다.

index 키 후보는 {memberId, PK}, {memberId, type}으로 Left-Most Prefix 원칙을 적용하면 효과적으로 개선할 수 있다.

최신 잔액 조회

ANLAYZE
ref
actual total time: 7.08ms -> 5.7ms
actual max rows: 14100 -> 1

포인트 내역 요약

ANLAYZE
ref
actual total time: 150ms -> 1.84ms
actual max rows: 1e+6 -> 97

포인트 내역 조회 (커서 페이징)

ANLAYZE
using index (ref)
actual total time: 32.2ms -> 0.268ms
actual max rows: 497055 -> 10

아래는 필터링에 type을 추가한 결과입니다.

ANALYZE
using index (ref)
actual total time: 69.3ms -> 0.545ms
actual max rows: 163294 -> 13

여러 사용자 별 지급된 포인트

ANALYZE
using index (ref)
actual total time: 198ms -> 2.12ms
actual max rows: 1e+6 -> 471

아래는 타입 전용 복합키를 추가한 결과입니다.

CREATE INDEX idx_member_id_type
ON POINT_TRANSACTIONS(MEMBER_ID, TYPE DESC);

ANLYZE
using index (ref)
actual total time: 198ms -> 2.48ms
actual max rows: 1e+6 -> 341

사실상 성능은 조금 떨어졌는데, 거의 동일합니다.
그리고 해당 페이지는 관리자 페이지이므로 드라마틱한 성능 개선도 필요 없습니다.
최종적으로 비용 낭비 + 성능 저하를 식별하고 해당 복합 인덱스를 추가하는 것은 오버엔지니어링이란 결론이 났습니다.

사용자 포인트 지급 내역 (관리자 페이징)

ANALYZE
PK REVERSE
actual total time: 188ms -> 0.644ms
actual max rows: 1e+6 -> 10

엑셀 다운로드용 전체 조회

ANALYZE
PK REVERSE
actual total time: 238ms -> 0.686ms
actual max rows: 1e+6 -> 97

성능 개선 완료

단 하나의 인덱스 설정으로 포인트 시스템 전체 성능 극적 향상

CREATE INDEX idx_member_id_desc ON POINT_TRANSACTIONS(MEMBER_ID, POINT_TRANSACTION_ID DESC);

📊 성능 개선 결과

  • 최신 잔액 조회

    • 시간: 7.08ms → 5.7ms (19% 향상)
    • 스캔량: 14,100건 → 1건 (99.99% 감소)

  • 포인트 내역 요약

    • 시간: 150ms → 1.84ms (98.8% 향상, 81배 빨라짐)
    • 스캔량: 1,000,000건 → 97건 (99.99% 감소, 10,309배 효율 향상)

  • 포인트 내역 조회 (커서 페이징)

    • 시간: 32.2ms → 0.268ms (99.2% 향상, 120배 빨라짐)
    • 스캔량: 497,055건 → 10건 (99.998% 감소, 49,706배 효율 향상)

  • 포인트 내역 조회 (타입 필터)

    • 시간: 69.3ms → 0.545ms (99.2% 향상, 127배 빨라짐)
    • 스캔량: 163,294건 → 13건 (99.99% 감소, 12,561배 효율 향상)

  • 여러 사용자 별 지급된 포인트

    • 시간: 198ms → 2.12ms (98.9% 향상, 93배 빨라짐)
    • 스캔량: 1,000,000건 → 471건 (99.95% 감소, 2,123배 효율 향상)

  • 사용자 포인트 지급 내역 (관리자 페이징)

    • 시간: 188ms → 0.644ms (99.7% 향상, 292배 빨라짐)
    • 스캔량: 1,000,000건 → 10건 (99.999% 감소, 100,000배 효율 향상)

  • 엑셀 다운로드용 전체 조회

    • 시간: 238ms → 0.686ms (99.7% 향상, 347배 빨라짐)
    • 스캔량: 1,000,000건 → 97건 (99.99% 감소, 10,309배 효율 향상)

평균 속도 향상: 150배 빨라짐
평균 효율성 향상: 99.98% 스캔량 감소
최고 성과: 관리자 페이징 347배 속도 향상, 100,000배 효율성 개선

마치며

내가 생각한 최적화의 황금률

  • 측정 우선: 추측보다 실제 데이터 기반 분석
  • 패턴 파악: 가장 자주 사용되는 WHERE 절 조건 식별
  • 단순함 추구: 복잡한 인덱스보다 핵심 하나로 최대 효과
  • 비용 고려: 성능 개선 vs 유지보수 비용 균형

"사용자 경험 개선, 시스템 안정성 확보, 확장성 준비"

이제 이 경험과 방법론을 바탕으로 다른 도메인에서도 체계적으로 성능 최적화를 경험할 예정이다!

profile
이프
if (이런 시나리오는 어떨까?) then(테스트로 검증하고 해결) else(다음 시나리오 고민)
이전 포스트

동시성 통합 테스트와 해결

다음 포스트

멱등키를 이용한 API 설계

0개의 댓글