커버링 인덱스 vs 클러스터링 인덱스

이번에 성능 개선을 하려고 커버링 인덱스를 적용했었는데요, Optimizer가 커버링 인덱스 대신, 클러스터링 인덱스를 선택하더라고요...? 무조건 커버링 인덱스가 빠르지!라는 생각을 가지고 있었는데, 잘못된 생각이더라고요. 그래서 이번 포스팅에서는 커버링 인덱스와 클러스터링 인덱스를 비교해보려고 합니다.

 

---

우선 커버링 인덱스를 적용해야겠다고 생각한 상황은 다음과 같습니다.

 

- Client 측에서 5초 주기로 지속적인 통계 조회 요청을 합니다.

- API가 많이 호출될 것이라고 생각했고, 성능 향상을 극한으로 하면 좋지 않을까?라는 생각을 했습니다.

@Entity
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class OrganizationStatistic extends BaseTimeEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToOne(fetch = FetchType.LAZY)
    private Organization organization;

    @Embedded
    private FeedbackAmount feedbackAmount;

 

@Embeddable
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class FeedbackAmount {

    private long feedbackTotalCount;
    private long feedbackConfirmedCount;
    private long feedbackWaitingCount;

    public FeedbackAmount(
            final long feedbackTotalCount,
            final long feedbackConfirmedCount,
            final long feedbackWaitingCount
    ) {
        this.feedbackTotalCount = feedbackTotalCount;
        this.feedbackConfirmedCount = feedbackConfirmedCount;
        this.feedbackWaitingCount = feedbackWaitingCount;
    }

 

Entity 객체의 구조입니다. OrganizationStatistic과 FeedbackAmount는 일대일 단방향 관계를 맺도록 설계했습니다.

public OrganizationStatisticResponse getStatistic(final UUID organizationUuid) {
        final Organization organization = findOrganizationBy(organizationUuid);
        final OrganizationStatistic organizationStatistic = 
                organizationStatisticRepository.findByOrganizationId(organization.getId());
        return OrganizationStatisticResponse.of(organizationStatistic.getFeedbackAmount());
    }

 

리팩토링 전의 코드인데요, organizationId를 통해 organizationStatistic을 찾고, organizationStatistic의 feedbackAmount()를 넘겨주는 형식입니다.

 

현 상황에서 organizationStatistic 객체를 가져올 필요가 없다고 생각했는데요, 반드시 필요한 값인 FeedbackAmount만 가져와도 되겠다는 생각을 했습니다.

 

FeedbackAmount의 필드값은 3개인데요,

where절의 organizationId 포함해 4개의 복합인덱스를 걸어 5초 주기로 조회되는 API의 성능을 향상시키는건 좋은 개선방안이라고 생각했습니다.

 

리팩토링 이후의 코드인데요,

JPA Projection을 통해 FeedbackAmount 객체를 만들었고, 이후에 organizationId, feedbackTotalCount, feedbackConfirmedCount, feedbackWaitingCount 총 4개의 복합인덱스를 생성했습니다.

    @Query("""
            SELECT new feedzupzup.backend.organization.domain.FeedbackAmount(
                  s.feedbackAmount.feedbackTotalCount,
                  s.feedbackAmount.feedbackConfirmedCount,
                  s.feedbackAmount.feedbackWaitingCount)
            FROM OrganizationStatistic s
            WHERE s.organization.id = :organizationId
            """)
    FeedbackAmount findFeedbackAmountByOrganizationId(@Param("organizationId") Long organizationId);

 

 

이후에 해당 쿼리의 실행계획이 커버링 인덱스로 적용이 되었나 확인해봤는데요,

 

(잘 안 보이시는 분들을 위한 확대)

 

엇 이럴 수가... 커버링 인덱스를 사용하지 않더라고요... 

 

커버링 인덱스는 세컨더리 인덱스 내부에서 해결이 되고, 클러스터링 테이블에 접근을 하지 않으니 더 빨라야 할 텐데 왜 이런 결과가 나온 걸까요?

---

결론부터 말하자면, unique 인덱스가 걸려있으면, 커버링 인덱스보다 클러스터링 인덱스가 더 빠를 수 있다!입니다.

 

 

현재 orgnizationId, totalCount, confirmedCount, waitingCount 총 4개의 복합 인덱스를 걸어놨으니, 다음과 같은 그림의 세컨더리 인덱스 구조로 이뤄져 있을 겁니다. (중간 브랜치 노드는 그림에서 생략했습니다)

SELECT feedback_total_count, feedback_confirmed_count, feedback_waiting_count
FROM organization_statistic
WHERE organization_id = 171;

 

실제로 사용되는 쿼리는 다음과 같은데요, 해당 쿼리를 탐색할 때 세컨더리 인덱스를 사용한다면 다음과 같은 순서로 값을 반환할 겁니다.

여기서 중요한 건 1번과 3번입니다. 

 

1번의 경우에는 테이블 full scan은 아니고, 적절한 범위 별로 페이지가 나눠져 있습니다. 즉, B+Tree 기반으로 테이블 탐색이 일어난다고 생각해 주시면 될 것 같아요.

 

3번의 경우에는, 쿼리문에 필요한 모든 값들(orgId, tc, cc, wc)이 전부 세컨더리 인덱스 테이블에 존재하니, 클러스터링 테이블에 접근을 할 필요가 없습니다. 이 상황을 '커버링 인덱스'라고 하는 것이고요.

 

그렇다면 옵티마이저가 선택한 organizationId는 어떤 구조로 값을 가져올까요?

그림을 보면 알 수 있듯이, 클러스터링 인덱스에 접근해야 하는 비용이 존재합니다. 간략하게 정리해 보자면, 클러스터링 인덱스를 사용한다면 I/O가 2번 발생하고, 커버링 인덱스를 사용하면 I/O가 1번 발생하는 것이죠.

 

만약 찾으려는 값의 결과가 10개라면, 클러스터링 인덱스를 사용한다면 약 11번의 I/O(인덱스 스캔 1회, 데이터 탐색 10회)가 발생하고, 커버링 인덱스를 사용하면 인덱스 스캔 비용만 발생해 약 1번의 I/O가 발생합니다.

 

이 경우에는 옵티마이저가 당연히 커버링 인덱스를 적용할 텐데요, 한 가지 예외 상황이 있습니다. 바로 인덱스에 Unique 조건이 달려있는 상황입니다.

 

인덱스에 Unique 조건이 달려있다는 건, 하나의 레코드만을 보장한다는 뜻입니다. 이 경우에는 클러스터링 인덱스와 커버링 인덱스가 I/O 한 번 밖에 차이 나지 않아 컬럼의 개수 또한 중요합니다.

 

현재 세컨더리 인덱스의 경우에는 4개의 컬럼이 존재합니다. 컬럼이 많다는 건, 많은 양의 인덱스가 생성이 된다는 뜻이고, 이는 읽어야 할 페이지 수가 많아지고, 트리의 depth가 깊어질 수 있는 것이죠. 반면 Unique 인덱스의 경우, 2개의 컬럼(orgId, pk)만으로 구성되어 가볍습니다. 이 경우에는 읽어야 하는 페이지 수가 적고, 트리의 depth 또한 줄어드는 것이죠. 옵티마이저는 해당 비용까지 전부 고려해 계산한 듯싶습니다. 즉, 추가 I/O 1회  < 더 많은 컬럼의 인덱스 탐색의 비용이 더 크다고 이라고 결론을 내린 것이죠. 

 

추가로 Unique 제약조건을 제거한다면, 옵티마이저가 커버링 인덱스를 선택할까? 궁금증이 생겨 테스트를 해봤습니다.

 

unique 제약 조건을 삭제한 후 실행계획을 살펴보니, 옵티마이저가 커버링 인덱스를 사용하는 모습을 확인할 수 있었습니다. 값이 하나로 보장이 되지 않는다면, 찾는 값만큼 추가 I/O가 발생하니 당연한 결과라고 생각합니다.

 

커버링 인덱스의 경우에는, unique 제약조건이 걸려있지 않다면 성능 비교해 보며 도입해 볼 만하지 않나 싶네요ㅎ_ㅎ

 

개발 세계에서 '무조건'이라는 단어는 피해야 한다는 걸 한 번 더 느끼는 시간이었습니다.  이해가 안 되는 부분이 있다면, 댓글 남겨주시면 감사하겠습니다.