[Spring] 페이징 성능 개선하기 2. 커버링 인덱스 사용하기

2. 커버링 인덱스 사용하기

No Offset 페이징을 사용할 수 없는 상황이라면 커버링 인덱스로 성능을 개선할 수 있다.

커버링 인덱스란, 쿼리를 충족시키는데 필요한 모든 데이터를 갖고 있는 인덱스를 말한다.

즉, select, where, order by, limit, group by 등에서 사용되는 모든 컬럼이 Index 컬럼 안에 다 포함되는 경우다.

 

select 절까지 포함하게 되면 너무 많은 컬럼이 인덱스에 포함되기 때문에 실제론 커버링 인덱스는 select 를 제외한 나머지만 우선적으로 수행한다.

SELECT *
FROM items
WHERE [조건문]
ORDER BY id DESC
OFFSET [페이지 번호]
LIMIT [페이지 사이즈]

위와 같은 페이징 쿼리를 아래처럼 처리한 코드를 얘기한다.

SELECT *
FROM items as i 
JOIN (SELECT id
        FROM items
        WHERE [조건문]
        ORDER BY id DESC
        OFFSET [페이지 번호]
        LIMIT [페이지 사이즈]) as temp ON temp.id = i.id

위 쿼리에서 커버링 인덱스가 사용된 부분이 JOIN에 있는 쿼리이다.

SELECT id
FROM items
WHERE [조건문]
ORDER BY id DESC
OFFSET [페이지 번호]
LIMIT [페이지 사이즈]

select 절을 비롯해 order by, where 등 내 모든 항목이 인덱스 컬럼으로만 이루어지게 하여 인덱스 내부에서 쿼리가 완성될 수 있도록 하는 방식이다.

이렇게 커버링 인덱스로 빠르게 걸러낸 id를 통해 실제 select 절의 항목들을 빠르게 조회하는 방식이다.

2-1. 커버링 인덱스 사용하기

일반적으로 인덱스를 이용해 조회되는 쿼리에서 가장 큰 성능 저하를 일으키는 부분은 인덱스를 검색하고 대상이 되는 row의 나머지 컬럼 값을 데이터 블록에서 읽을 때이다.

- 페이징 쿼리와 무관하게 인덱스를 추가해도 느린 쿼리는 select절 때문이다.

커버링 인덱스를 태우지 않은 일반 조회 쿼리는 order by, offset ~ limit 을 수행할 때도 데이터 블록으로 접근을 하게 된다.

반대로 커버링 인덱스 방식을 사용하면 where, order by, offset ~ limit 을 인덱스 검색으로 빠르게 처리하고, 이미 다 걸러진 몇 개의 row에 대해서만 데이터 블록에 접근하기 때문에 성능의 이점을 얻게 된다.

 

앞서 1. No Offset을 사용한 페이징 성능 개선에서 사용된 페이징 쿼리는 아래와 같다.

SELECT id, book_no, book_type, name
FROM book
WEHRE name LIKE 'a%'
ORDER BY id DESC
LIMIT 10 OFFSET 10000;

SELECT에서 사용된 book_no, book_type은 인덱스 (idx_book_1(name))에 포함하지 않기 때문에 커버링 인덱스가 될 수 없다.

2-2. 구현 코드

 Querydsl 

Querydsl에서 커버링 인덱스를 사용해야 한다면 2개의 쿼리로 분리해서 진행할 수 밖에 없다.

(이유는 Querydsl에서 from 절의 서브 쿼리를 지원하지 않기 때문이다.)

- 커버링 인덱스를 활용해 조회 대상의 PK를 조회

- 해당 PK로 필요한 컬럼 항목들 조회

pubblic List<BookPaginationDto> paginationCoveringIndex(String name, int pageNo, int pageSize) {

    // 1. 커버링 인덱스로 대상 조회 (id만 포함하여 커버링 인덱스를 활용해 빠르게 조회)
    List<Long> ids = queryFactory
                        .select(book.id)
                        .from(book)
                        .where(book.name.like(name + "%"))
                        .orderBy(book.id.desc())
                        .limit(pageSize)
                        .offset(pageNo * pageSize)
                        .fetch();
    
    // 1-1. 대상이 없을 경우 추가 쿼리 수행할 필요 없이 바로 변환
    if (CollectionUtils.isEmpty(ids)) {
        return new ArrayList<>();
    }
    
    // 2. 
    return queryFactory
             .select(Projections.fields(BookPaginationDto.class,
                     book.id.as("bookId"),
                     book.name,
                     book.bookNo,
                     book.bookType))
             .from(book)
             .where(book.id.in(ids))
             .orderBy(book.id.desc()) // where in id 만 있어 결과 정렬이 보장되지 않는다.
             .fetch();
}

JdbcTemplate

문자열 쿼리를 직접 사용하는 방식인데, 이렇게 진행하면 querydsl를 쓸 때처럼 쿼리를 분리할 필요 없이, 커버링 인덱스를 from 절에 그대로 사용하면 된다.

public List<BookPaginationDto> paginationCoveringIndexSql(String name, int pageNo, int pageSize) {
String query =
        "SELECT i.id, book_no, book_type, name " +
        "FROM book as i " +
        "JOIN (SELECT id " +
        "       FROM book " +
        "       WHERE name LIKE '?%' " +
        "       ORDER BY id DESC " +
        "       LIMIT ? " +
        "       OFFSET ?) as temp on temp.id = i.id";

return jdbcTemplate
        .query(query, new BeanPropertyRowMapper<>(BookPaginationDto.class),
                name,
                pageSize,
                pageNo * pageSize);
}

2-3. 단점

커버링 인덱스 방식은 일반적인 페이징 방식에서는 거의 대부분 적용할 수 있는 효과적인 개선 방법이지만 단점이 존재한다.

 

- 너무 많은 인덱스가 필요하다.

결국 쿼리의 모든 항목이 인덱스에 포함되어야 하기 때문에 느린 쿼리가 발생할 때마다 인덱스가 신규 생성될 수도 있다.

- 인덱스 크기가 너무 커진다.

인덱스도 결국 데이터기 때문에 너무 많은 항목이 들어가면 성능 이슈가 발생할 수 있다. 

- 데이터 양이 많아지고, 페이지 번호가 뒤로 갈 수록 No Offset에 비해 느리다.

 

 

 

 출처 

https://jojoldu.tistory.com/529 

2. 페이징 성능 개선하기 - 커버링 인덱스 사용하기