복합 인덱스로 성능 최적화하기

지난 글 인덱스 적용 전/후 성능 비교에서 단일 인덱스(idx_grade)를 적용해 쿼리의 성능을 일부 개선했지만, 기대했던 만큼 성능이 크게 향상되지 않았습니다.

단일 인덱스를 사용하면 한 가지 조건만 최적화되기 때문에 다른 조건(gender = 'Male')은 여전히 추가 필터링 단계를 거쳐야 했습니다. 결과적으로 스캔된 데이터는 절반으로 줄었지만, 실행 시간과 비용은 여전히 비효율적이었습니다.

이번에는 복합 인덱스를 적용해 두 조건(grade와 gender)을 동시에 처리하고, 어떤 성능 개선이 이루어지는지 확인해 보겠습니다.

---

1. 복합 인덱스 생성

기존 쿼리

SELECT * FROM customer WHERE grade = 'Silver' AND gender = 'Male';

복합 인덱스 생성:

복합 인덱스를 사용하면 두 조건을 동시에 인덱스 단계에서 처리할 수 있습니다.

CREATE INDEX idx_grade_gender ON customer (grade, gender);

---

2. 실행 계획 비교

단일 인덱스 사용 (기존)

Filter: (customer.gender = 'Male') 
  -> Index lookup on customer using idx_grade (grade='Silver')

• 스캔된 행 수: 49,628개.
• 실제 반환된 행 수: 12,562개.
• 추가 필터링: gender 조건을 따로 처리해야 함.
• 비용(cost): 약 3,420.

---

복합 인덱스 사용 (새로운 인덱스 적용 후)

Filter: (customer.gender = 'Male') 
  -> Index lookup on customer using idx_grade (grade='Silver')

• 스캔된 행 수: 24,436개 → 절반으로 감소.
• 실제 반환된 행 수: 12,562개.
• 추가 필터링 없음: grade와 gender 조건이 인덱스에서 동시에 처리됨.
• 비용(cost): 약 3,382 → 더 낮아짐.

---

3. 성능 비교

항목단일 인덱스 (idx_grade) 복합 인덱스 (idx_grade_gender)

인덱스 사용	idx_grade	idx_grade_gender
추가 필터링	있음 (gender 필터링)	없음
스캔된 행 수	49,628개	24,436개
실제 반환된 행 수	12,562개	12,562개
비용(cost)	약 3,420	약 3,382
실행 시간	0.137 ~ 44.7ms	0.321 ~ 36.6ms

---

4. 개선된 점

1. 추가 필터링 단계 제거:
   복합 인덱스(idx_grade_gender)를 사용하면서 grade와 gender 조건이 인덱스 단계에서 처리되었습니다.
   • Using where 대신 바로 결과 반환이 가능해졌습니다.
2. 스캔된 행 수 감소:
   기존에 idx_grade로 약 49,628개의 행을 읽었지만, 복합 인덱스는 약 24,436개만 스캔하게 되었습니다.
3. 실행 시간 단축:
   쿼리 실행 시간이 평균 0.034 ~ 0.039초로 대폭 단축되었습니다.
4. 비용 감소:
   MySQL의 쿼리 비용(cost)도 기존보다 낮아졌습니다.

---

5. 결론

복합 인덱스(idx_grade_gender)를 생성하면서 WHERE 절의 두 조건을 동시에 최적화할 수 있었습니다.

• 단일 인덱스에서는 추가 필터링이 발생했습니다.
• 복합 인덱스는 인덱스 단계에서 모든 조건을 처리하므로 I/O 비용이 감소하고 실행 시간이 크게 단축되었습니다.

---

6. 다음 예고: 커버링 인덱스로 SELECT 최적화

복합 인덱스는 WHERE 조건 최적화에 강력하지만, SELECT *처럼 모든 컬럼을 조회하는 경우에는 랜덤 I/O 비용이 여전히 발생합니다.

다음 글에서는 커버링 인덱스를 활용해 테이블 조회를 완전히 없애고, SELECT 성능을 극대화하는 방법을 다루겠습니다.