Cursor기반 페이지네이션으로 성능 개선하기

현재 [강아지 모아보기]페이지에서 5가지 속성(브리더의 인증여부, 견종, 분양가능상태, 성별, 크기)을 기반으로 다중검색이 가능한 상황이다.

기존 코드는 아래와 같다.

@Transactional(readOnly = true)
public ResponseEntity<?> getDogs(Pageable pageable, Long dogTypeId, String verification,
                                 Boolean isAvailable, Gender gender, Size size) {

    Specification<Dog> spec = Specification
            .where(DogSpecification.isVerified(verification))
            .and(DogSpecification.hasDogType(dogTypeId))
            .and(DogSpecification.filterAvailableDog(isAvailable))
            .and(DogSpecification.filterByGender(gender))
            .and(DogSpecification.filterBySize(size));

    Page<Dog> dogs = dogRepository.findAll(spec, pageable);

    return response.success(HttpStatus.OK, dogs.map(SimpleDogDto::new));
}

사용자들이 많이 호출할 것이라고 예상되는 분양가능상태와 브리더의 인증여부의 상세코드를 보자.

public static Specification<Dog> isVerified(String isVerified) {
      return (root, query, cb) -> {
          if(isVerified == null) {
              return cb.conjunction();
          } else if (!isVerified.isEmpty()) {
              Boolean verified = "yes".equalsIgnoreCase(isVerified.trim())
              return cb.equal((root.get("breeder").get("isVerified")), verified);
          }
          return cb.conjunction();
      };
  }

사용자들이 많이 호출할 것이라고 예상되는 분양가능상태, 브리더의 인증여부를 기반으로 데이터를 조회시, 아래 쿼리를 기반으로 조회하게 된다.

SELECT d.*
FROM dog d
INNER JOIN breeder b
ON d.breeder_id = b.id
WHERE b.is_verified = true
and d.status = 'AVAILABLE';

현재, 강아지 데이터가 약 700만건이 있을때, 거의 끝번째의 페이지 번호를 조회하게 되면 아래 쿼리를 기반으로 조회할 것이다.

SELECT d.*
FROM dog d
INNER JOIN breeder b
ON d.breeder_id = b.id
WHERE b.is_verified = true
and d.status = 'AVAILABLE'
LIMIT 8 OFFSET 6500000;

위 쿼리를 조회하는데 약 30초가 걸렸다. 이 쿼리를 어떻게 개선할지 생각해보자.

EXPLAIN키워드를 통해 위 쿼리문이 어떻게 동작하는지 살펴보았다.

첫번째 Breeder테이블에서 인덱스를 사용하지 않고 테이블 전체 스캔(ALL)을 하였다.

테이블 전체스캔을 통해 인증된 브리더인지 검색한 것으로 확인이 된다.

이후, 약 8521개의 Dog테이블이 Breeder테이블과 조인을 통해 인증된 브리더를 가져온 후, WHERE d.status = ‘AVAILABLE’ 조건을 통해 분양가능한 강아지를 조회했다.

브리더는 강아지에 비해 데이터 건수가 매우 적기 때문에 인증된 브리더인지 검색을 위해 Breeder테이블에 별도의 인덱스를 생성할 필요성을 느끼지 못했다.

하지만 Dog테이블은 대용량이기 때문에 Dog테이블에서 breeder_id와 status컬럼을 기반으로 복합인덱스를 생성하여 다시 위 쿼리를 조회해보자.

 

Offset기반의 페이지네이션이 느린 이유

페이징 쿼리가 뒤로갈수록 느린 이유는 결국 앞에서 읽었던 행을 다시 읽어야 하기 때문이다.
예를 들어 offset 10000, limit 20 이라 하면 최종적으로 10,020개의 행을 읽어야 한다. (10,000부터 20개를 읽어야하니) 그리고 이 중 앞의 10,000 개 행을 버리게 된다.
(실제 필요한건 마지막 20개뿐이니) 뒤로 갈수록 버리지만 읽어야 할 행의 개수가 많아 점점 뒤로 갈수록 느려지는 것이다.

CREATE INDEX idx_dog_offset ON dog (status, breeder_id);

위 명령어를 통해 복합인덱스를 생성하였다. 복합인덱스를 생성후, 해당 쿼리를 조회하였으나 10초정도 개선이 되었을 뿐 여전히 느린 것을 확인하였다.

그래서 No offset방식을 고려하게 되었다.

No offset
No Offset 방식은 바로 이 부분에서 조회 시작 부분을 인덱스로 빠르게 찾아 매번 첫 페이지만 읽도록 하는 방식이다.
(클러스터 인덱스인 PK를 조회 시작 부분 조건문으로 사용했기 때문에 빠르게 조회된다.)

SELECT *
FROM items
WHERE 조건문
AND id < 마지막조회ID # 직전 조회 결과의 마지막 id
ORDER BY id DESCLIMIT 페이지사이즈

 

이전에 조회된 결과를 한번에 건너뛸수 있게 마지막 조회 결과의 ID를 조건문에 사용하는 것으로 이는 매번 이전 페이지 전체를 건너 뛸 수 있음을 의미한다.
no offset을 사용하게 되면, 아래와 같은 형태로 구현이 된다.

SELECT d.*
FROM dog d
         INNER JOIN breeder b ON d.breeder_id = b.id
WHERE b.is_verified = true
  AND d.status = 'AVAILABLE'
  AND d.id > 6500000
ORDER BY d.id
LIMIT 8;

30초에서 4초로 개선되었다. 이 결과, 커서기반 페이지네이션을 채택하게 되었다.

이에 따라, 코드를 아래와 같이 수정하여 FE가 페이지번호를 요청하도록 수정하였다.

@Transactional(readOnly = true)
public ResponseEntity<?> getDogs(Long lastId, Integer size, Long dogTypeId, String verification,
                                 Boolean isAvailable, Gender gender, Size sizeFilter) {

    Specification<Dog> spec = Specification
            .where(DogSpecification.isVerified(verification))
            .and(DogSpecification.hasDogType(dogTypeId))
            .and(DogSpecification.filterAvailableDog(isAvailable))
            .and(DogSpecification.filterByGender(gender))
            .and(DogSpecification.filterBySize(sizeFilter));

    // 커서 기반 페이지네이션을 위해 lastId를 사용하여 조건 추가
    if (lastId != null) {
        spec = spec.and(DogSpecification.filterByIdGreaterThan(lastId));
    }

    Page<Dog> dogs = dogRepository.findAll(spec, pageable);

    return response.success(HttpStatus.OK, dogs.map(SimpleDogDto::new));
}