[트러블 슈팅] 복합키 인덱스 최적화

⚠️ 문제 상황

운영 기간이 늘어날수록 예약 스케줄을 출력해주는 API의 성능이 저하되는 문제가 발생하였다. 따라서 실제로 스케줄이 렌더링 되는 시간이 느려지는 문제도 발생했다.

🕵️ 원인 분석

• 풀 테이블 스캔 발생: WHERE 조건에서 grade와 class_date를 사용하여 데이터를 조회하는데, 인덱스가 적용되지 않아 전체 테이블을 검색하는 문제가 발생.
• 쿼리 실행 시간이 길어짐: 테이블 크기가 증가할수록 쿼리 성능 저하가 발생.
• 카디널리티 부족: grade와 class_date의 조합에 대한 인덱스가 없기 때문에 검색 범위가 넓어지고 실행 시간이 증가함.

✅ 해결 방법

시도할 수 있는 최적화 방법

1. 단일 컬럼 인덱스 적용: grade 또는 class_date 각각에 대한 인덱스를 생성하여 조회 속도를 개선할 수 있음.
2. 커버링 인덱스 적용: SELECT * 대신 필요한 컬럼만 조회하도록 변경하고, 추가적인 인덱스를 활용하여 쿼리 성능을 향상시킬 수 있음.
3. 쿼리 구조 변경: WHERE 조건을 단순화하거나 JOIN을 줄여 실행 계획을 최적화할 수 있음.
4. 쿼리 캐싱 활성화: 동일한 쿼리가 반복적으로 실행될 경우, 캐싱을 통해 DB 부하를 줄일 수 있음.

복합 키 인덱스를 선택한 이유

• 검색 범위 최적화: grade와 class_date는 함께 사용될 확률이 높으므로, 개별 인덱스보다 복합 인덱스를 적용하면 범위 검색 속도를 개선할 수 있음.
• 불필요한 데이터 스캔 최소화: WHERE 절에서 두 개의 조건을 함께 사용하므로, idx_grade_classdate 복합 인덱스를 적용하면 풀 테이블 스캔을 방지하고 최소한의 데이터만 검색할 수 있음.
• 카디널리티 향상: grade의 값(1, 2)은 유니크하지 않지만, class_date와 결합하면 검색 범위를 더욱 좁힐 수 있어 효율적인 인덱스 검색이 가능함.

🎯 적용 결과

• 쿼리 실행 속도 개선: EXPLAIN ANALYZE 결과, 실행 시간이 90ms → 0.4ms로 단축됨.
• 풀 테이블 스캔 제거: ALL 스캔에서 range 검색으로 변경되어 인덱스를 효과적으로 활용함.
• 성능 최적화 완료: 인덱스 적용을 통해 필요한 데이터만 빠르게 검색 가능.

 

인덱스 최적화를 위한 성능 개선 과정

---

인덱스 적용 이전: EXPLAIN을 통한 실행 계획 확인

mysql> EXPLAIN SELECT * 
    -> FROM codingzoneclass 
    -> WHERE grade = 2 
    -> AND class_date BETWEEN '2025-02-03' AND '2025-02-07';
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | codingzoneclass | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  285 |     1.11 | Using where |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 풀 테이블 스캔 발생: WHERE 조건에서 grade와 class_date를 사용하여 데이터를 조회하는데, 인덱스가 적용되지 않아 전체 테이블을 검색하는 문제가 발생.

복합 키 인덱스 적용 이후: EXPLAIN을 통한 실행 계획 확인

CREATE INDEX idx_grade_classdate ON codingzoneclass (grade, class_date);

mysql> EXPLAIN SELECT * 
    -> FROM codingzoneclass 
    -> WHERE grade = 2 
    -> AND class_date BETWEEN '2025-02-03' AND '2025-02-07';
+----+-------------+-----------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
| id | select_type | table           | partitions | type  | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | codingzoneclass | NULL       | range | idx_grade_classdate | idx_grade_classdate | 1027    | NULL |   15 |   100.00 | Using index condition |
+----+-------------+-----------------+------------+-------+---------------------+---------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)

• 인덱스 적용 후 EXPLAIN을 실행하여 쿼리 실행 방식 개선 확인.

EXPLAIN ANALYZE를 이용한 실제 측정치 확인

mysql> EXPLAIN ANALYZE SELECT * 
    -> FROM codingzoneclass 
    -> WHERE grade = 2 
    -> AND class_date BETWEEN '2025-02-03' AND '2025-02-07';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                       |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index range scan on codingzoneclass using idx_grade_classdate over (grade = 2 AND '2025-02-03' <= class_date <= '2025-02-07'), with index condition: ((codingzoneclass.grade = 2) and (codingzoneclass.class_date between '2025-02-03' and '2025-02-07'))  (cost=7.01 rows=15) (actual time=0.366..0.446 rows=15 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

분석 결과

Index range scan	idx_grade_classdate 인덱스를 사용하여 범위 검색을 수행. (최적화됨)
with index condition	MySQL이 WHERE 조건을 인덱스에서 직접 평가함. (불필요한 row scan 감소)
cost=7.01	MySQL의 예상 실행 비용이 7.01로, 매우 낮음.
rows=15	예상 검색 행 수: 15개. 전체 테이블을 읽지 않고 필요한 행만 검색.
actual time=0.366..0.446 ms	실제 실행 시간: 0.366 ~ 0.446ms로 매우 빠름.
loops=1	MySQL이 1회 실행으로 모든 데이터를 가져옴.

---

성능 최적화 확인

풀 테이블 스캔이 발생하지 않음

• Index range scan이 적용되어 불필요한 row scan이 발생하지 않음.
• idx_grade_classdate 인덱스를 사용하여 grade와 class_date를 빠르게 조회.

실행 시간 최적화

• actual time=0.366..0.446 ms로 쿼리가 1ms 미만에서 실행됨.

어플리케이션 레벨의 성능 측정

인덱스 적용 이전의 어플리케이션 레벨의 성능 수치

[QUERY EXECUTION TIME] 90 ms [TOTAL EXECUTION TIME] 90 ms

인덱스 적용 이후의 어플리케이션 레벨의 성능 수치

[QUERY EXECUTION TIME] 19 ms [TOTAL EXECUTION TIME] 19 ms