dongzzirit 님의 블로그

1. 커버링 인덱스란?

커버링 인덱스(Covering Index)는 인덱스 자체에 쿼리에서 필요한 모든 컬럼이 포함되어 있어 테이블 데이터를 조회하지 않고 인덱스만으로 결과를 반환할 수 있는 인덱스입니다.

커버링 인덱스의 특징

• WHERE 조건과 SELECT 컬럼 모두를 인덱스에 포함시킵니다.
• 테이블 접근이 제거되므로 랜덤 I/O 비용이 줄어들어 성능이 크게 향상됩니다.

일반 인덱스와의 차이

• 일반 인덱스: WHERE 조건을 최적화하지만 SELECT 컬럼이 인덱스에 없으면 테이블 데이터를 추가로 조회해야 합니다.
• 커버링 인덱스: 모든 조건과 결과 컬럼이 인덱스에 포함되므로 테이블 접근 없이 바로 결과를 반환합니다.

2. 커버링 인덱스를 적용한 성능 비교

테스트 환경

• 데이터 개수: 10만 개
• 조회 조건: WHERE grade = 'SILVER' AND gender = 'Male'
• 쿼리: SELECT * FROM customer WHERE grade = 'SILVER' AND gender = 'Male' LIMIT 0, 20000;

3. 단계별 성능 비교

단계인덱스 유형스캔된 행 수실제 반환된 행 수비용 (cost) 실행 시간특징

4. 단계별 분석

1. 인덱스 적용 전 (테이블 전체 스캔)

• 인덱스 없음: 테이블 전체를 스캔하며 조건에 맞는 데이터를 필터링합니다.
• 스캔된 행 수: 99,256개
• 비용: 10,238
• 실행 시간: 65.5ms
• 결론: 성능이 가장 나쁘며, 테이블 전체 스캔으로 인해 I/O 비용이 큽니다.

2. 단일 컬럼 인덱스 (idx_grade)

• 인덱스 사용: grade 컬럼만 최적화됨. gender 조건은 추가 필터링이 발생합니다.
• 스캔된 행 수: 49,628개
• 비용: 3,420
• 실행 시간: 44.7ms
• 결론: 스캔된 행 수가 절반으로 줄었지만, gender 조건이 추가로 처리되므로 완벽한 최적화는 되지 않았습니다.

3. 멀티컬럼 인덱스 (idx_grade_gender)

• 인덱스 사용: grade와 gender 조건을 동시에 최적화합니다.
• 스캔된 행 수: 24,436개
• 비용: 3,382
• 실행 시간: 36.6ms
• 결론: 두 조건을 인덱스에서 처리하면서 추가 필터링 비용이 제거되어 성능이 크게 개선되었습니다.

4. 커버링 인덱스 (idx_covering_all_columns)

• 인덱스 사용: WHERE 조건 최적화 + 모든 컬럼이 인덱스에 포함되어 테이블 접근이 제거되었습니다.
• 스캔된 행 수: 24,392개
• 비용: 3,377
• 실행 시간: 34.9ms
• 특징:
  • Using index가 실행 계획에 나타남.
  • 인덱스만으로 모든 데이터를 반환 → 랜덤 I/O 최소화.
• 결론: 가장 빠른 성능을 보여주며, 테이블 접근 비용이 완전히 제거되었습니다.

5. 결론 및 추천

성능 개선 순서

1. 인덱스 적용 전 → 성능이 가장 낮음 (테이블 전체 스캔).
2. 단일 컬럼 인덱스 → WHERE 조건 일부만 최적화되므로 추가 필터링 비용 발생.
3. 멀티컬럼 인덱스 → 두 조건을 동시에 최적화.
4. 커버링 인덱스 → WHERE + SELECT를 인덱스만으로 처리해 성능이 가장 좋음.

최적화 추천

모든 컬럼이 필요한 경우: 커버링 인덱스가 가장 효율적입니다.

CREATE INDEX idx_covering_all_columns ON customer (grade, gender, birth, created_date, is_deleted, visit_cnt, employee_id, id, address(50), email(50), memo(50), name(50), phone(20));

INSERT/UPDATE 성능 저하를 감안해야 하므로 읽기 중심 테이블에 적용하는 것이 좋습니다.

지난 글 인덱스 적용 전/후 성능 비교에서 단일 인덱스(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)

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 성능을 극대화하는 방법을 다루겠습니다.

데이터베이스에서 성능 최적화를 위해 인덱스를 적용하는 것은 일반적인 방법입니다. 하지만 때로는 인덱스를 추가했음에도 불구하고 속도가 기대만큼 개선되지 않는 상황을 경험하게 됩니다. 오늘은 이러한 상황을 실습과 함께 분석하고, 인덱스의 한계점과 해결 방안을 공유해보겠습니다.

1. 문제 상황

다음과 같은 쿼리를 실행한다고 가정해 봅시다.

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

• customer 테이블의 데이터 수: 약 100,000개.
• 조건: grade가 'Silver'이고, gender가 'Male'인 데이터를 조회.

2. 인덱스 적용 전 분석

실행 계획

'1', 'SIMPLE', 'customer', NULL, 'ALL', NULL, NULL, NULL, NULL, '99256', '1.00', 'Using where'

설명

• 타입: ALL → 테이블 전체 스캔이 수행됨.
• 스캔된 행 수: 약 99,256개.
• 비용(cost): 10,238 – 전체 데이터를 읽기 때문에 비용이 높음.
• 실제 실행 시간: 0.0738 ~ 65.5ms.
• 필터링: gender와 grade 조건을 전체 테이블에서 필터링.

결론

인덱스가 없기 때문에 MySQL은 테이블 전체를 스캔해야 합니다. 조건에 맞는 데이터만 가져오는 데 불필요한 I/O 비용이 발생하고 속도가 느립니다.

3. 인덱스 적용 후 분석

grade 컬럼에 인덱스를 생성해 봅니다.

CREATE INDEX idx_grade ON customer (grade);

실행 계획

'1', 'SIMPLE', 'customer', NULL, 'ref', 'idx_grade,idx_gender', 'idx_grade', '1023', 'const', '49628', '50.00', 'Using where' -> Filter: (customer.gender = 'Male') (cost=3420 rows=24814) 
-> Index lookup on customer using idx_grade (grade='Silver') (cost=3420 rows=49628)

설명

• 타입: ref → 인덱스(idx_grade)를 사용하여 일부 행만 조회.
• 스캔된 행 수: 49,628개 → 절반으로 감소.
• 필터링된 행 수: gender = 'Male' 조건을 추가로 적용해 12,562개 행만 반환.
• 비용(cost): 3,420 → 비용이 절반 이하로 감소.
• 실제 실행 시간:
  • Index Lookup: 0.137 ~ 42ms.
  • 추가 필터링: 0.147 ~ 44.7ms.

결론

• idx_grade 인덱스를 사용해 grade = 'Silver' 조건을 빠르게 처리했습니다.
• 하지만 gender = 'Male' 조건이 인덱스에 포함되지 않아 추가 필터링 단계가 발생했습니다.
• 실행 시간은 줄었지만, 여전히 최적화되지 않은 부분이 남아 있습니다.

4. 성능 비교

항목인덱스 적용 전인덱스 적용 후

개선된 점

1. 스캔된 행 수가 절반으로 줄어들었습니다.
2. 비용(cost)이 절반 이하로 감소했습니다.
3. 실행 시간도 개선되었지만, 여전히 추가 필터링이 남아있습니다.

5. 결론

단일 인덱스(idx_grade)를 적용한 결과, 데이터 접근 비용은 감소했지만 속도 개선이 완벽하지 않았습니다. 이유는 gender 조건에 대한 추가 필터링 작업이 남아있기 때문입니다.

해결 방안: 복합 인덱스 또는 커버링 인덱스를 활용하면 두 조건을 동시에 처리할 수 있어 더 나은 성능을 기대할 수 있습니다.

6. 다음 블로그 예고

다음 글에서는 복합 인덱스와 커버링 인덱스를 비교해보겠습니다.

마무리

이번 글을 통해 인덱스 적용 전/후 성능 차이를 비교하고, 단일 인덱스의 한계점을 확인했습니다. 다음 단계에서는 인덱스 설계를 더 최적화해 MySQL 성능을 극대화하는 방법을 배워보겠습니다.
👉 인덱스 설계를 더 최적화해 MySQL 성능을 극대화하는 방법