1. 기존에 Pageable을 사용하던 방식
둘러보기 API는 미니홈 리스트를 각종 정렬 조건에 따라 반환하는 API이다. 기존에는 다음과 같이 Pageable 객체를 통해 간편하게 미니홈 리스트를 데이터베이스에서 조회해왔다.
@Repository
@RequiredArgsConstructor
public class MinihomeEntityRepository implements MinihomeRepository {
private final MinihomeJpaRepository minihomeJpaRepository;
private final MinihomeMetaJpaRepository minihomeMetaJpaRepository;
@Override
public Slice<Minihome> findAllByLikeCount(Pageable pageable) {
return minihomeMetaJpaRepository.findAllBy(pageable)
.map(minihomeMetaEntity -> {
MinihomeEntity minihomeEntity = minihomeJpaRepository.findById(minihomeMetaEntity.getMinihomeId())
.orElseThrow(() -> new BusinessException(ErrorCode.NOT_FOUND_MINIHOME));
return new Minihome(minihomeEntity.getId(), minihomeEntity.getUserId(), minihomeEntity.getTotalVisitorCnt(), minihomeMetaEntity.getLikeCount());
});
}
}
그러나 다음 문제가 있다. 예를 들어, 좋아요 순 조회시 파라미터로 sort=likeCount,desc&sort=minihomeId,desc&page=90000&size=10 을 넘겨준다고 가정하면 다음과 같이 동작한다.
- minihome_meta 테이블의 like_count 컬럼과 minihome_id 컬럼을 기준으로 정렬하여 size만큼의 행을 조회해온다. (쿼리 1개)
- 각 행에 대해 minihome 엔티티와 user 엔티티를 조회해온다. (쿼리 n + n개)
결과적으로 1 + n + n개의 쿼리가 실행된다.
100만 건의 데이터를 넣은 후 postman을 통해 10개의 요청을 보내 평균 값을 내 성능을 측정했다. 성능 테스트 결과, 하나의 요청에 대한 응답 속도가 약 872ms가 나왔다.
2. No Offset 방식으로 변경
Offset 방식은 limit, offset 쿼리를 통해 특정 페이지의 n개의 데이터를 조회한다. 그러나 이와 같은 페이징 쿼리는 뒤로 갈수록 느리다. 이유는 앞에서 읽었던 행을 다시 읽어야 하기 때문이다. 예를 들어, offset 10,000, limit 20이면, 최종적으로 10,020개의 행을 읽고, 앞의 10,000개의 행을 버리게 된다. 즉, 뒤로 갈수록 버리지만 읽어야 하는 행의 개수가 많아 점점 뒤로 갈수록 느려지는 것이다.
No Offset 방식은 조회 시작 부분을 인덱스로 빠르게 찾아 매번 첫 페이지만 읽는 방식이다. 예를 들어 다음 쿼리의 경우, 클러스터 인덱스인 PK를 조건문으로 사용했기 때문에 인덱스를 통해 조회해야 하는 행의 시작 부분으로 빠르게 접근하게 된다. 즉, 인덱스를 통해 매번 이전 페이지 전체를 건너 뛰게 되어, 아무리 페이지가 뒤로 가더라도 처음 페이지를 읽은 것과 동일한 성능을 갖게 된다.
SELECT *
FROM items
WHERE 조건문 AND id < 마지막조회ID
ORDER BY id DESC
LIMIT 페이지사이즈
이 경우에는 파라미터로 sort와 page를 넘길 필요가 없으며, 키가 되는 likeCount와 minihomeId, 그리고 한 페이지 당 보여줄 데이터의 개수(size)를 넘겨주면 된다. (likeCount=...&minihomeId=...&size=10)
@Repository
public interface MinihomeMetaJpaRepository extends JpaRepository<MinihomeMetaEntity, Long> {
@Query("select mm " +
"from MinihomeMetaEntity mm " +
"where mm.likeCount < :likeCount or (mm.likeCount = :likeCount and mm.minihomeId < :minihomeId) " +
"order by mm.likeCount desc, mm.minihomeId desc")
List<MinihomeMetaEntity> findOrderByLikeCountDescAndMinihomeIdDesc(Pageable pageable, @Param("likeCount") Long likeCount, @Param("minihomeId") Long minihomeId);
}- minihome_meta 테이블에서 like_count와 minihome_id를 기준으로 정렬한 후 size 만큼의 행을 가져온다. (쿼리 1개)
- 각 행에 대해 minihome 엔티티와 user 엔티티를 조회해온다. (쿼리 n + n개)
기존의 Pageable 방식과 흐름은 같으나, minihome_meta 테이블에 실행되는 쿼리가 Offset 방식이냐, No Offset 방식이냐의 차이이다. 그리고 이때 likeCount 컬럼에 인덱스를 걸어주었다. 따라서 no offset 방식으로 'where like_count < ...' 조건절이 수행될 때 인덱스를 타서 더 빠른 조회가 가능하다.
No Offset을 적용한 결과, 성능 테스트 결과로 337ms가 나왔다.
3. DTO로 조회
1 + n + n 문제를 개선해보기 위해 DTO로 조회하는 방식을 적용해보자. 이때 DTO로 조회하는 코드가 기존 Repository에 작성되면, Repository가 API 형식에 의존한다는 문제가 생긴다. 따라서 다음과 같이 별도의 Repository로 구분했다.
@Repository
public interface ExploreQueryRepository extends JpaRepository<MinihomeMetaEntity, Long> {
@Query("select new gachagacha.db.ExploreQueryDto(m.id, u.nickname, u.profile, m.totalVisitorCnt, mm.likeCount, u.score) " +
"from MinihomeMetaEntity mm " +
"join MinihomeEntity m on mm.minihomeId = m.id " +
"join UserEntity u on m.userId = u.id " +
"where mm.likeCount < :likeCount or " +
"(mm.likeCount = :likeCount and mm.minihomeId < :minihomeId) " +
"order by mm.likeCount desc, mm.minihomeId desc")
Slice<ExploreQueryDto> findExploreDtosByNoOffset(@Param("likeCount") long likeCount, @Param("minihomeId") long minihomeId, Pageable pageable);
}
DTO 클래스는 다음과 같고, 생성자가 반드시 있어야 한다.
@Getter
public class ExploreQueryDto {
private long minihomeId;
private String nickname;
private Profile profile;
private int totalVisitorCnt;
private long likeCount;
private int score;
public ExploreQueryDto(long minihomeId, String nickname, Profile profile, int totalVisitorCnt, long likeCount, int score) {
this.minihomeId = minihomeId;
this.nickname = nickname;
this.profile = profile;
this.totalVisitorCnt = totalVisitorCnt;
this.likeCount = likeCount;
this.score = score;
}
}
이 경우, 한 방 쿼리로 조회하므로 쿼리가 오직 1개만 수행된다.
그러나 성능 테스트 결과, 2.6s로 기존 방식보다도 낮은 성능을 보였다. 그 이유는 join 때문인데, 먼저 100만건의 데이터에 대해 join된 후 정렬 후 size 만큼의 행을 조회해오기 때문이다.
4. DTO로 조회 + 쿼리 최적화
따라서 DTO로 조회하되, join을 고려하여 쿼리를 최적화해보자.
@Service
@RequiredArgsConstructor
public class MinihomeService {
private final MinihomeMetaJpaRepository minihomeMetaJpaRepository;
private final ExploreQueryRepository exploreQueryRepository;
public Slice<ExploreQueryDto> exploreByLikeCount(Pageable pageable, long likeCount, long minihomeId) {
List<Long> minihomeIds = minihomeMetaJpaRepository.findOrderByLikeCountDescAndMinihomeIdDesc(pageable, likeCount, minihomeId).stream()
.map(minihomeMetaEntity -> minihomeMetaEntity.getMinihomeId())
.toList();
return exploreQueryRepository.findExploreDtosByMinihomeIds(minihomeIds);
}
}@Repository
public interface MinihomeMetaJpaRepository extends JpaRepository<MinihomeMetaEntity, Long> {
@Query("select mm " +
"from MinihomeMetaEntity mm " +
"where mm.likeCount < :likeCount or (mm.likeCount = :likeCount and mm.minihomeId < :minihomeId) " +
"order by mm.likeCount desc, mm.minihomeId desc")
List<MinihomeMetaEntity> findOrderByLikeCountDescAndMinihomeIdDesc(Pageable pageable, @Param("likeCount") Long likeCount, @Param("minihomeId") Long minihomeId);
}@Repository
public interface ExploreQueryRepository extends JpaRepository<MinihomeMetaEntity, Long> {
@Query("select new gachagacha.db.ExploreQueryDto(m.id, u.nickname, u.profile, m.totalVisitorCnt, mm.likeCount, u.score) " +
"from MinihomeMetaEntity mm " +
"join MinihomeEntity m on mm.minihomeId = m.id " +
"join UserEntity u on m.userId = u.id " +
"where m.id in :minihomeIds")
Slice<ExploreQueryDto> findExploreDtosByMinihomeIds(@Param("minihomeIds") List<Long> minihomeIds);
}- minihome_meta 테이블에서 like_count와 minihome_id를 기준으로 정렬한 후 size 만큼의 행을 가져온다. (쿼리 1개)
- 각 행에 대해 in절로 DTO 형태로 데이터를 조회해온다. (쿼리 1개)
성능 테스트 결과 184.6가 소모되었다.
5. 복합 인덱스 적용
no offset 방식을 적용하며, like_count 컬럼에 인덱스를 걸어주었다. 그리고 좋아요 수가 같을 경우 minihome_id를 기준으로 내림차순 정렬하여 조회한다.
따라서 like_count 컬럼에만 인덱스가 걸려있을 경우, 좋아요 수가 같은 경우에는 해당 데이터들에 대해 풀스캔을 하며 minihome_id를 기준으로 정렬 조회해온다. 즉, 다음과 같이 where 절의 or, and 연산에 주의하여 인덱스가 사용되는 범위를 고려해야 한다.
@Query("select mm " +
"from MinihomeMetaEntity mm " +
"where mm.likeCount < :likeCount or (mm.likeCount = :likeCount and mm.minihomeId < :minihomeId) " +
"order by mm.likeCount desc, mm.minihomeId desc")
List<MinihomeMetaEntity> findOrderByLikeCountDescAndMinihomeIdDesc(Pageable pageable, @Param("likeCount") Long likeCount, @Param("minihomeId") Long minihomeId);
따라서 like_count, minihome_id로 복합 인덱스를 걸어주었다.
CREATE INDEX idx_minihome_meta_like_count_minihome_id ON minihome_meta(like_count, minihome_id);
복합 인덱스를 걸기 전 후 성능을 비교해보면 결과는 다음과 같다.
- 복합 인덱스를 걸기 전(like_count에만 인덱스를 건 경우): 184.6ms
- 복합 인덱스를 건 후: 37.4ms
6. 커버링 인덱스 적용
기존에는 minihome_meta 테이블의 전체 필드를 조회해온 후 거기서 minihome_id를 추출하여 사용했다.
@Service
@RequiredArgsConstructor
public class MinihomeService {
private final MinihomeMetaJpaRepository minihomeMetaJpaRepository;
private final ExploreQueryRepository exploreQueryRepository;
public Slice<ExploreQueryDto> exploreByLikeCount(Pageable pageable, long likeCount, long minihomeId) {
List<Long> minihomeIds = minihomeMetaJpaRepository.findOrderByLikeCountDescAndMinihomeIdDesc(pageable, likeCount, minihomeId).stream()
.map(minihomeMetaEntity -> minihomeMetaEntity.getMinihomeId())
.toList();
return exploreQueryRepository.findExploreDtosByMinihomeIds(minihomeIds);
}
}
그러나 애플리케이션 단에서 minihome_id만 추출해서 사용할 것이라면 전체 컬럼이 아닌 minihome_id만 조회해오면 된다. 또한 minihome_id는 인덱스에 포함되는 컬럼이기 때문에 minihome_id만 조회하면 커버링 인덱스가 사용된다.
실제로 mysql workbench를 통해 실행 계획을 비교해보자. 전체 컬럼을 조회할 경우, 'EXPLAIN ANALYZE'를 통해 실행 계획을 확인해보면 다음과 같다.
<전체 컬럼 조회시>
-> Index range scan on mm using idx_minihome_meta_like_count_minihome_id
over (like_count < 500) OR (like_count = 500 AND minihome_id < 4000) (reverse),
with index condition: ((mm.like_count < 500) or ((mm.like_count = 500) and (mm.minihome_id < 4000)))
(cost=46814 rows=101521) (actual time=0.555..158 rows=50191 loops=1)
<minihome_id만 조회시>
-> Filter: ((mm.like_count < 500) or ((mm.like_count = 500) and (mm.minihome_id < 4000)))
(cost=20602 rows=101521) (actual time=5.43..27 rows=50191 loops=1)
-> Covering index range scan on mm using idx_minihome_meta_like_count_minihome_id
over (like_count < 500) OR (like_count = 500 AND minihome_id < 4000) (reverse)
(cost=20602 rows=101521) (actual time=5.42..23 rows=50191 loops=1)
그리고 'EXPLAIN'을 통해 확인해보면 다음과 같이 Extra 부분이 다르다.
- 전체 컬럼 조회시: Using index condition; Backward index scan (= 테이블 접근 필요)
- minihome_id만 조회시: Using where; Backward index scan; Using index (= 인덱스만으로 처리, 즉 커버링 인덱스 사용)
따라서 다음과 같이 minihome_id만 조회하도록 변경했으며, 이를 통해 커버링 인덱스가 사용되어 더 빠르다.
@Service
@RequiredArgsConstructor
public class MinihomeService {
private final ExploreQueryRepository exploreQueryRepository;
public Slice<ExploreQueryDto> exploreByLikeCount(Pageable pageable, long likeCount, long minihomeId) {
List<Long> minihomeIds = exploreQueryRepository.findOrderByLikeCountDescAndMinihomeIdDesc(pageable, likeCount, minihomeId);
return exploreQueryRepository.findExploreDtosByMinihomeIds(minihomeIds);
}
}@Repository
public interface ExploreQueryRepository extends JpaRepository<MinihomeMetaEntity, Long> {
@Query("select mm.minihomeId " +
"from MinihomeMetaEntity mm " +
"where mm.likeCount < :likeCount or (mm.likeCount = :likeCount and mm.minihomeId < :minihomeId) " +
"order by mm.likeCount desc, mm.minihomeId desc")
List<Long> findOrderByLikeCountDescAndMinihomeIdDesc(Pageable pageable, long likeCount, long minihomeId);
}- minihome_meta 테이블에서 like_count과 minihome_id를 기준으로 정렬하여(이때 복합 인덱스 사용), size개의 행의 minihome_id를 조회해온다. (이때 테이블 조회가 필요하지 않으며 인덱스만으로 처리 = 커버링 인덱스)
- size개의 minihome_id를 통해 DTO 형태로 데이터를 조회해온다.
6. 정리
각 방식의 성능을 비교해보면 다음과 같다.
- 기존의 Pageable 방식: 872ms
- No Offset 방식 적용: 337ms
- DTO로 조회: 2.6s
- DTO로 조회 + 쿼리 최적화: 184.6
- 복합 인덱스 적용: 37.4ms
결과적으로 기존 방식에 비해 872ms -> 37.4ms로 약 95.7% 개선되었다.
정리해보면, No Offset 방식을 적용하여 기존의 Offset 방식의 불필요한 테이블 스캔을 줄이고 키가 되는 컬럼에 대한 인덱스를 통해 성능을 높일 수 있었다.
그러나 여전히 1 + n + n 문제가 발생하는데, 이를 줄이기 위해 DTO 형태로 바로 조회하는 방법을 적용했다. 그러나 이 경우, 100만 건의 전체 데이터 간 조인이 일어난 후 정렬 및 limit 조회가 발생하여 조인으로 인해 성능이 떨어졌다.
따라서 minihome_meta 테이블에서 정렬 및 limit을 통해 size만큼의 데이터만 조회해오고, 그 후에 각 행에 대한 DTO 데이터를 조회하도록 쿼리를 최적화했다.
그리고 추가로 where 조건절의 구조를 파악하여, like_count에만 인덱스를 걸던 방식에서 like_count, minihome_id에 복합 인덱스를 걸어 성능을 높였다. 그리고 조회시 minihome_meta 테이블의 전체 컬럼을 조회하던 방식에서 minihome_id만 조회하도록 변경하여 복합 인덱스가 커버링 인덱스로 사용되도록 개선했다.
* 참고로, 쿼리 수를 줄인다고 해서 DTO 형태로 조회해올 경우, API 형식에 의존된 조회 코드가 생겨 유지보수 측면에서 복잡해질 수도 있다. 따라서 쿼리 수와 코드 상의 복잡성의 트레이드 오프를 고려해야 한다. 나의 경우, 둘러보기 조회 시 위와 같은 좋아요 순 외로도 가입 순, 스코어 순 등등 여러 조회 API가 있다. 그리고 이 API들의 응답 형식이 모두 같다. 따라서 DTO 클래스를 한 개 만들어놓으면, 여러 API에서 1+n+n개의 쿼리 대신 1개의 쿼리로 해당 DTO로 조회해올 수 있다. 즉, 해당 DTO를 사용하는 API가 여러 개 있으므로, DTO를 만들어 관리해도 좋다고 생각하여 해당 방식을 적용했다.
Reference