🔧 인덱스 최적화를 통한 쿼리 성능 개선 사례 분석

개요

---

데이터베이스 최적화 중 인덱스를 사용하여 자주 조회되는 쿼리의 성능을 개선합니다.
이번 최적화 작업에서는 seat 테이블을 대상으로 인덱스를 적용하여 쿼리 응답 시간을 단축시켰습니다.

주요 성과

• 예약상태별 조회와 특정 일정의 좌석 조회에서 각각 63%와 70% 의 성능 향상 확인했습니다.
• 특정 예약에 대한 모든 좌석 조회에서는 예상과 달리 인덱스 적용 후 성능이 오히려 감소하기도 했습니다.

성능 개선 분석 및 전략

---

시스템 전제

• 콘서트 1개 당 10개의 스케줄러가 존재하고, 각 스케줄러에는 50개의 좌석이 배정되어 있습니다.
• seat 테이블에는 총 720,773개의 데이터가 존재합니다.
• 인기 콘서트 조회 기준은 결제가 완료된 일정기간동안 좌석 예약 수가 가장 많은 콘서트 순으로 조회합니다.

인덱스 적용 전 자주 조회되는 쿼리 분석

seat 테이블에서 자주 실행되는 쿼리 유형을 파악합니다.

• 예약상태별 조회
  • 쿼리: SELECT * FROM hhplus_study.seat WHERE status = 'RESERVED'
  • 실행시간: 491ms (실행: 432ms, 가져오기: 59ms)
  • 결과: 107개의 행 조회.
• 특정 예약에 대한 모든 좌석 조회
  • 쿼리: SELECT * FROM hhplus_study.seat WHERE reservation_id = 57
  • 실행시간: 474ms (실행: 409ms, 가져오기: 65ms)
  • 결과: 50개의 행 조회.
• 특정 일정의 좌석 조회
  • 쿼리: SELECT * FROM hhplus_study.seat WHERE schedule_id = 10
  • 실행시간: 479ms (실행: 398ms, 가져오기: 81ms)
  • 결과: 50개의 행 조회.

인덱스 적용 전 쿼리 실행 계획 분석

각 쿼리에 대해 EXPLAIN 명령어를 사용하여 실행 계획을 분석했습니다.

전체 조회 및 분석

EXPLAIN 결과를 보면, 쿼리가 풀 스캔을 수행하고 있음을 알 수 있습니다.
풀 스캔은 테이블의 모든 행을 순차적으로 탐색하는 방식으로, 데이터 양이 많을수록 성능이 저하됩니다.

예약 상태별 조회 및 분석

예약 상태별 조회 시 status = 'RESERVED' 조건을 적용한 쿼리가 풀 스캔을 수행하며, 약 71만 개의 행을 스캔하고 있어 성능이 비효율적입니다.

인덱스 적용

자주 조회되는 컬럼들에 대해 인덱스를 추가해보았습니다.

• 좌석
  • schedule_id
  • status
  • reservation_id
  • schedule_id, status
• 콘서트 일정
  • concert_id
• 예약
  • reservation_status, reserved_at

인덱스 적용 후 전체 조회 및 분석

• 예약상태별 조회

  • 쿼리: SELECT * FROM hhplus_study.seat WHERE status = 'RESERVED'
  • 인덱스 적용 전 실행시간: 491ms (실행: 432ms, 가져오기: 59ms)
  • 인덱스 적용 후 실행시간: 178ms
  • 실행시간 개선: 491ms에서 178ms로, 약 63% 개선됨.
    인덱스를 적용하여 상태별 조회가 훨씬 빨라졌습니다.

• 특정 예약에 대한 모든 좌석 조회

  • 쿼리: SELECT * FROM hhplus_study.seat WHERE reservation_id = 57
  • 인덱스 적용 전 실행시간: 474ms (실행: 409ms, 가져오기: 65ms)
  • 인덱스 적용 후 실행시간: 676ms
  • 실행시간 변화: 인덱스 적용 후 실행시간이 676ms로 오히려 늘어나거나 비슷했습니다. reservation_id가 인덱스에 추가되어있는데도 성능 변화가 없는 이유는 Explain 분석글에서 설명드리겠습니다.

• 특정 일정의 좌석 조회

  • 쿼리: SELECT * FROM hhplus_study.seat WHERE schedule_id = 10
  • 인덱스 적용 전 실행시간: 479ms (실행: 398ms, 가져오기: 81ms)
  • 인덱스 적용 후 실행시간: 146ms
  • 실행시간 개선: 479ms에서 146ms로, 약 70% 개선됨. 일정 ID에 따른 조회가 인덱스 - 덕분에 훨씬 빨라졌습니다.

인덱스 적용 후 쿼리 실행 계획 분석

각 쿼리에 대해 EXPLAIN 명령어를 사용하여 실행 계획을 분석했습니다.

예약 상태별 조회 및 분석

status = 'RESERVED' 조건을 사용하여 인덱스가 적용된 테이블에서 데이터를 조회했을 때, 사용가능한 인덱스인 idx_status를 key로 사용하였고, Using index condition이 추가되어 인덱스를 통한 조건 필터링이 적용된 것을 확인할 수 있었습니다.

특정 예약에 대한 모든 좌석 조회 및 분석

reservation_id만 조회하는 쿼리에서는 인덱스가 적용되지 않는 이유는 해당 인덱스가 schedule_id와 reservation_id 두 컬럼에 대해 복합키로 구성되어 있기 때문입니다.

쿼리가 reservation_id만 조건으로 사용하면, schedule_id가 포함된 복합 인덱스를 효율적으로 사용할 수 없게 됩니다.
그 결과, MySQL은 풀 스캔을 진행하게 되며 성능이 저하됩니다.
이를 해결하기 위해서는 reservation_id에 대해서 별도의 단일 인덱스를 추가하거나, schedule_id를 포함한 조건이 추가되면 복합 인덱스를 효과적으로 활용할 수 있습니다.

주간 인기 콘서트 조회 분석 및 성능 최적화

인덱스 적용 전 주간 인기 콘서트 조회

인덱스 적용 전 실행시간: 418ms (실행: 387ms, 가져오기: 31ms)

인덱스 적용 후 주간 인기 콘서트 조회

인덱스 적용 후 실행시간: 60ms (실행: 26ms, 가져오기: 34ms)

분석

• 서브쿼리를 사용하기 전 실행속도가 느려진 문제를 발견: 인덱스를 설정하였는데도 인덱스 설정 전 쿼리가 더 빠른 현상을 발견하였습니다. EXPLAIN ANALYZE로 분석해보니 중첩 루프 조인에서 seat_3 테이블을 풀 스캔 하고 있는 것을 발견하여 서브쿼리로 수정하였습니다.
• 서브쿼리로 필터링 : reservation_2 테이블에서 reservation_status = 'CONFIRMED'와 reserved_at 범위 조건에 맞는 데이터만 먼저 필터링하였고, 서브쿼리에서 필요한 데이터만 추출하여 seat_3 테이블과 조인하였고 조회 속도가 향상된 것을 확인하였습니다.

마무리

인덱스 최적화는 자주 조회되는 쿼리의 성능을 개선하여 시스템의 효율성을 높여줍니다.
하지만 모든 경우에 인덱스가 항상 성능을 개선하는 것은 아니며, 데이터의 분포와 조회 패턴에 따라 인덱스를 선택적으로 적용하는 것이 중요합니다.
데이터가 적거나 특정 조건이 자주 변경되는 경우에는 성능 저하를 초래할 수 있습니다.