CS 스터디 (DB) 15 - 커버링 인덱스에 대해 설명해주세요.

커버링 인덱스(Covering Index)란? 

- 커버링 인덱스(Covering Index)란 특정 쿼리가 인덱스에 포함된 컬럼만으로 모든 데이터를 처리할 수 있어, 데이터 테이블(데이터 페이지)에 접근하지 않아도 되는 인덱스를 의미합니다.

- 즉, 인덱스 레벨에서 쿼리 결과를 완성할 수 있기 때문에, 디스크 I/O를 최소화하여 조회 성능을 크게 향상시킬 수 있습니다. 

 

 

일반적인 Non-Clustered Index의 조회 흐름 

- 일반적으로 RDBMS는 논-클러스터는 인덱스를 통해 레코드의 위치(Row Pointer)를 찾고, 해당 위치의 데이터 페이지를 읽어 실제 값을 가져오는 방식으로 처리합니다.

- 커버링 인덱스는 이러한 논-클러스터드 인덱스의 구조적 한계를 보완하는 기법으로, 인덱스에 필요한 모든 컬럼이 포함되어 있다면 테이블 접근을 생략할 수 있어 디스크 I/O를 줄이고 성능을 크게 향상시킬 수 있습니다.

 

 

커버링 인덱스 확인 방법 (MySQL 기준) 및 EXPLAN 실행 결과 확인

- 예를 들어, 다음과 같이 email과 name의 인덱스를 생성했다고 가정해 보겠습니다. 

CREATE INDEX idx_email_name ON user(email, name);

- 이후 다음과 같은 쿼리가 실행된다면, 

SELECT name FROM user WHERE email = 'example@example.com';

- WHERE절에 있는 조건 `email`과 SELECT절에 있는 컬럼 `name` 모두 인덱스에 포함되어 있으므로, 테이블을 조회할 필요 없이 인덱스만으로 결과를 반환할 수 있습니다. 

500

- MySQL에서는 EXPLAIN 명령을 통해 이를 확인할 수 있으며, 실행 계획의 Extra 필드에 Using index 또는 Using index, Using where가 표시되면 커버링 인덱스가 적용된 것입니다.

[Before: 인덱스 없음]

-> Table scan on user  
   Filter: email = 'user50000@example.com'  
   Rows examined: 10,000  
   Actual time: 3.98 ms

---

[After: Covering Index 적용]

-> Covering index lookup on user using idx_email_name  
   Condition: email = 'user50000@example.com'  
   Rows examined: 1  
   Actual time: 0.025 ms

- MySQL에서 테스트해 본 결과 1 건의 테스트 데이터를 기준으로 인덱스가 없을 때와, Covering Index를 적용했을 때의 차이를 확인한 결과 

- 3.98ms -> 0.025ms로 속도가 향상되었고, DB 접근 row 수도 1만 건 -> 1건으로 줄었음을 확인하였습니다. 

 

 

커버링 인덱스의 장점 

- 데이터 페이지를 참조하지 않기 때문에, 디스크 I/O가 감소하고 응답 속도가 향상됩니다.

- 테이블 접근 없이 인덱스 메모리 상에서 데이터를 처리하므로, 디스크의 비순차적인 읽기(Random I/O)를 회피할 수 있습니다.

- 인덱스는 테이블보다 구조가 작고 자주 접근되므로, Buffer Pool 또는 Page Cache에 더 오래 머물게 됩니다.

- 그 결과 메모리 히트율이 향상되고, 추가적인 디스크 I/O를 줄일 수 있습니다.

- 옵티마이저는 covering index가 사용 가능한 경우 더 단순한 계획을 선택하며, 결과적으로 쿼리 튜닝 및 성능 예측이 쉬워집니다.

 

커버링 인덱스 적용 조건 및 주의 사항

- SELECT, WHERE, GROUP BY, ORDER BY 절에 포함된 모든 컬럼이 인덱스 정의에 포함되어 있어야 합니다.

- 컬럼의 순서 또한 인덱스 생성 순서와 일치해야 합니다.

 

 

커버링 인덱스의 한계점 

- 커버링 인덱스는 인덱스 크기 증가로 인한 디스크 사용량과 메모리 캐시 부담이 있습니다.

- INSERT·UPDATE·DELETE 작업 시 인덱스 유지 비용 증가로 인해 쓰기 성능이 저하될 수 있다는 한계가 있습니다.

- 모든 컬럼이 인덱스에 포함되어야 하므로 인덱스 설계가 복잡해집니다. 

- 정렬이나 집계 연산에서는 컬럼 순서나 범위 조건의 영향을 크게 받아 적용에 제약이 따릅니다.

- JOIN 쿼리에서는 여러 테이블을 함께 처리하기 때문에 인덱스만으로 결과를 완성하기 어려운 구조적 한계도 존재합니다.

- 실제 동작은 사용하는 DBMS의 옵티마이저와 인덱스 구현 방식에 따라 달라질 수 있어, 적용 전 실행 계획을 통해 검증이 필요합니다. 

 

 

커버링 인덱스는 언제 사용하면 좋을까? 

1. 읽기 성능이 중요한 쿼리

- 읽기(SELECT) 빈도가 매우 높고 응답 시간이 중요한 쿼리에 가장 효과적입니다.

- ex) 조회 전용 API, 대시보드용 통계 쿼리, 자동완성 검색어 조회, 상위 N개 목록 조회 등

 

2. 결과 컬럼 수가 적고, 고정된 경우 

- 인덱스는 컬럼 수가 많아질수록 크기가 커지고, 유지 비용도 증가합니다.

- 따라서 조회 대상 컬럼이 몇 개로 고정되어 있고, 그 구성이 자주 변경되지 않는 경우 covering index를 적용하는 것이 적절합니다.

 

3. 자주 반복되는 동일 쿼리 패턴이 있는 경우 

- 특정 쿼리 패턴이 빈번하게 반복되면서 동일한 컬럼 집합을 조회하는 경우 covering index는 캐시 효율을 극대화할 수 있습니다.

- 특히 API 서버에서 발생하는 특정 조회 쿼리가 100ms 이하의 SLA를 요구하는 환경에서 매우 유용합니다.

 

 

-- 꼬리질문 

 

GROUP BY에도 커버링 인덱스가 적용될 수 있나요?

- 가능합니다. 단, 인덱스 컬럼의 순서와 GROUP BY 절의 컬럼 순서가 정확히 일치해야 합니다.

CREATE INDEX idx_abc ON table(a, b, c);

쿼리	커버링 인덱스 적용 여부
GROUP BY (a, b)	✅ 적용 가능 (좌측 정렬 규칙 만족)
GROUP BY (b, c)	❌ 적용 불가 (a를 건너뜀)
GROUP BY (a, b, c, d)	❌ 적용 불가 (d는 인덱스 컬럼 아님)

 

 

 

 

범위 조건(BETWEEN, <, >, etc.)이 있는 경우에도 커버링 인덱스가 적용되나요?

CREATE INDEX idx_col1_col2 ON table(col1, col2);
SELECT col2 FROM table WHERE col1 BETWEEN 10 AND 100 AND col2 = 'X';

- 범위 조건(BETWEEN, <, > 등)이 인덱스의 앞부분 컬럼에 사용될 경우, 그 이후 컬럼의 정렬 상태가 보장되지 않아 인덱스 활용이 제한될 수 있습니다.

- 예: col1에 범위 조건이 사용되면, 그 다음 컬럼인 col2의 정렬이 깨질 수 있어 인덱스가 col2까지 효율적으로 이어지지 않으며, 결과적으로 covering index가 제대로 적용되지 않을 수 있습니다.

 

 

Spring에서 커버링 인덱스 적용

- Spring Data JPA를 사용할 경우, @Table 어노테이션의 indexes 속성을 통해 인덱스를 정의할 수 있습니다.
- 이 방식은 Hibernate가 DDL 생성하는 시점에 활용할 수 있습니다.

@Entity
@Table(name = "user", indexes = {
    @Index(name = "idx_email_name", columnList = "email, name")
})
public class User {
    // ...
}

 

 

결론 

커버링 인덱스(Covering Index)는 논-클러스터드 인덱스에서 발생하는 추가적인 테이블 접근 비용을 제거하여, 인덱스만으로 쿼리 결과를 반환할 수 있도록 설계된 고성능 최적화 기법입니다. 이를 통해 디스크 I/O를 최소화하고, 읽기 중심의 쿼리에 대해 응답 시간을 크게 단축할 수 있습니다. 다만, 인덱스 크기 증가, 쓰기 작업의 성능 저하, 인덱스 설계 복잡도 증가, 그리고 JOIN이나 집계, 정렬이 포함된 복합 쿼리에서의 제약과 같은 한계도 존재합니다.

또한 covering index가 유효하려면 SELECT, WHERE, GROUP BY, ORDER BY 등에서 사용되는 모든 컬럼이 인덱스에 포함되어 있어야 하므로, 설계 시 신중한 고려가 필요합니다. 따라서 covering index는 읽기 빈도가 높고, 쿼리 패턴이 고정된 컬럼 집합에 대해 전략적으로 적용할 때 가장 큰 효과를 발휘합니다.

 

너무 재밌다