[Database] 커버링 인덱스를 이용한 페이징 성능 개선

🎯 커버링 인덱스란 ?

---

커버링 인덱스(Covering Index) 란 쿼리를 처리할 때, 해당 인덱스로만 원하는 데이터를 모두 조회할 수 있어 데이터 영역(테이블)에 접근할 필요가 없는 인덱스를 말합니다.

 

즉, 테이블에 접근하지 않고 쿼리를 해결하기 때문에 조회 속도가 매우 빠른것이 특징입니다. 

 

하지만, 쿼리에서 사용하는 모든 값들에 대한 인덱스를 만들면 너무 많은 값들이 인덱스에 포함될 수 있는 단점이 있습니다. 그렇기 때문에 실제로 커버링 인덱스에 태우는 부분은 select를 제외한 나머지 값들에 대해서만 인덱스를 만드는 것입니다.

 

예를 들어 다음과 같은 쿼리가 있다 가정해봅시다.

 

SELECT *
FROM homes 
ORDER BY id DESC
OFFSET 페이지번호
LIMIT 페이지사이즈

 

위 쿼리를 아래와 같이 처리해 JOIN 에 있는 값들에 대해서만 인덱스를 만드는 것 입니다.

 

SELECT *
FROM homes
JOIN (SELECT id
	  FROM homes
      ORDER BY home_id DESC
      OFFSET 페이지번호
      LIMIT 페이지 사이즈) as temp on temp.id = homes.id

 

JOIN 안에 들어간 서브쿼리에 대해서만 인덱스를 만들어 커버링 인덱스를 만든다면 조회하려는 home_id 에 대해 빠르게 접근하고 찾은 home_id 에 대한 데이터 영역에 접근해 빠른 성능을 보장할 수 있습니다.

 

정리해보면, where, order by, offset .. 등의 필요한 값들만을 인덱스 검색으로 빠르게 처리한 뒤, 걸러진 row 에 대해서만 테이블에 접근하는 것 입니다.

 

🎯 커버링를 이용한 성능 개선

---

 

✅ 쿼리 튜닝 전 코드

    @Override
    public List<HomeOverviewResponse> findSellHomePage(Long homeId, int pageSize) {
        BooleanBuilder condition = new BooleanBuilder();

        if (homeId != null) {
            condition.and(qHome.id.lt(homeId)); // 내림차순 커서 페이징 조건
        }

        condition.and(qHome.homeStatus.eq(HomeStatus.FOR_SALE));

        return query
                .select(qHome, qUser)
                .from(qHome)
                .join(qUser).on(qHome.userId.eq(qUser.id))
				.join(qHome.homeAddress).fetchJoin()
                .where(condition)
                .orderBy(qHome.id.desc())
                .limit(pageSize)
                .fetch()
                .stream()
                .map(tuple -> HomeMapper.INSTANCE.toOverviewResponse(
                        tuple.get(qHome), tuple.get(qUser)))
                .toList();
    }

 

현재 No Offset 를 이용한 페이징 조회 쿼리 입니다. 해당 쿼리는 페이지1 에서 페이지10으로 조회하는 상황에 대해서는 사용할 수 없습니다. (이전 home_id 를 모르기 때문) 

 

✅ 인덱스 생성

@Entity
@Getter
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "home", indexes = {
        @Index(name = "idx_home_user_status", columnList = "home_id, user_id, home_status"), // 커버링인덱스에 사용할 인덱스
        @Index(name = "idx_home_home_status", columnList = "home_status"),
        @Index(name = "idx_home_user", columnList = "user_id")
})
public class Home extends BaseTimeEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "home_id", nullable = false)
    private Long id;

    @Column(name = "user_id", nullable = false)
    private Long userId;

 

home_id(PK) 를 조회할때 where, order_by 에 필요한 필드값을 이용해 인덱스를 생성했습니다.

 

✅ 커버링 인덱스를 적용한 쿼리

    @Override
    public List<HomeOverviewResponse> findSellHomePage(Pageable pageable) {
        //커버링 인덱스를 통한 조회 대상 id 찾기
        List<Long> ids = query.select(qHome.id)
                .from(qHome)
                .where(qHome.homeStatus.eq(HomeStatus.FOR_SALE))
                .orderBy(qHome.id.desc())
                .limit(pageable.getPageSize())
                .offset(pageable.getOffset())
                .fetch();

        if(ids.isEmpty()) return Collections.unmodifiableList(new ArrayList<>());

        return query.select(qHome, qUser)
                .from(qHome)
                .join(qUser).on(qHome.userId.eq(qUser.id))
                .join(qHome.homeAddress).fetchJoin()
                .where(qHome.id.in(ids))
                .orderBy(qHome.id.desc())
                .fetch()
                .stream()
                .map(tuple -> HomeMapper.INSTANCE.toOverviewResponse(
                        tuple.get(qHome), tuple.get(qUser)))
                .toList();
    }

 

QueryDSL 에서는 from 절의 서브 쿼리를 지원하지 않아 총 2개의 쿼리를 만들어야 합니다.

 

(1) 커버링 인덱스를 활용한 조회 대상 PK 조회쿼리

(2) 해당 PK 로 필요한 컬럼 항목들 조회쿼리

 

첫번째 쿼리에서 페이징 조회를 하는데, where 과 orderBy 등에 필요한 모든 필드값에 대한 인덱스를 Home 엔티티에 걸었습니다.

두번째 쿼리에서는 첫번째 쿼리에서 찾은 id 값을 이용해 필요한 데이터를 select 해서 DTO 로 변환 후 반환합니다. ( Projections 을 이용한 DTO 조회로 개선이 필요해보이네요 ㅜ )

 

이때 커버링 인덱스가 적용됐는지를 확인할 수 있습니다.

 

✅ 커버링 인덱스가 적용됐는지 확인하는 방법 

 

커버링 인덱스가 적용됐는지 확인하기 위해서는 실행계획의 Extra 필드에서 Using Index 가 명시되어 있는지 확인해야 합니다.

 

EXPLAIN SELECT
        h1_0.home_id 
    FROM
        home h1_0 
    JOIN
        `user` u1_0 
            ON h1_0.user_id = u1_0.user_id 
    WHERE
        h1_0.home_status = 'FOR_SALE'
    ORDER BY
        h1_0.home_id DESC 
    LIMIT
        0, 10;

 

첫번째 쿼리 (조회 대상 PK 조회 쿼리) 에 대한 실행 계획 결과 입니다. Using Index 가 Extra 에 찍혀 있는 걸 확인할 수 있습니다.

 

Using where : 인덱스를 사용한 조건을 필터링 

Backward index scan : 인덱스를 DESC 정렬로 읽고 있음

Using Index : 커버링 인덱스가 성공적으로 적용되어 테이블 접근 없이 인덱스로만 데이터 반환

 

 

자 ! 이제 첫번째 쿼리(home pk를 찾는 쿼리) 에서 커버링 인덱스가 적용된 것을 확인할 수 있습니다. 이제 튜닝한 쿼리에 대한 성능을 비교해 봅시다. 

 

    @Test
    void 집_페이징_조회_테스트() {
        //when
        long start = System.currentTimeMillis(); // 시작 시간 측정
        List<HomeOverviewResponse> sellHomePage = homeRepository.findSellHomePage(PageRequest.of(1999,15));
        long end = System.currentTimeMillis(); // 종료 시간 측정

        //then
        System.out.println("findSellHomePage 실행 시간: " + (end - start) + "ms");
        Assertions.assertThat(sellHomePage.size()).isEqualTo(15);
    }

 

인덱스 적용전 소요된 시간 

 

 

커버링 인덱스 적용 후 소요된 시간

 

 

총 100,000건의 데이터를 대상으로 테스트한 결과, 커버링 인덱스를 적용했을 때 조회 시간이 970ms에서 388ms로 약 60% 성능 개선이 이루어졌음을 확인할 수 있었습니다. 다만, No Offset 방식보다는 상대적으로 조회 속도가 느린 편입니다.

 

 

No Offset의 단점을 보완하고자 커버링 인덱스를 활용해 성능을 개선해보았지만, 이 역시 몇 가지 단점을 가지고 있습니다. 커버링 인덱스를 위해 많은 인덱스를 생성해야 하며, 그로 인해 인덱스 크기가 커지고 INSERT나 UPDATE와 같은 쓰기 작업의 성능이 저하될 수 있습니다.

 

또한, 커버링 인덱스를 사용한 조회는 초반 페이지에서는 빠르지만, 페이지가 뒤로 갈수록 성능 저하가 발생할 수 있습니다. 반면 No Offset은 페이지가 뒤로 가더라도 비교적 안정적인 성능을 유지하는 장점이 있습니다.

 

결론적으로, 데이터 특성과 사용자의 UX 요구사항을 고려하여 커버링 인덱스와 No Offset 중 적절한 방법을 선택해 사용하는 것이 중요할 것 같습니다.