[Spring] QueryDSL DB IO 최적화

성능 최적화 이전의 코드와 성능 지표입니다.

 

우선 Entity에 대한 설명 간략하게 드리겠습니다.

public List<Resume> findSearchResume(SearchType searchType, SearchResumeRequest request, Pageable pageable) {

    QResume resume = QResume.resume;

    BooleanBuilder builder = new BooleanBuilder();

    if (searchType == SearchType.POSITION){
        builder.and(resume.user.position.eq(request.position()));
    }

    if(searchType == SearchType.TECHSTACKS && !request.techStacks().isEmpty()) {
        for (String techStack : request.techStacks()) {
            builder.and(resume.user.techStack.contains(techStack));
        }
    }

    if (searchType == SearchType.POSITIONANDTECHSTACKS){
        for (String techStack : request.techStacks()) {
            builder.and(resume.user.techStack.contains(techStack));
        }
        builder.and(resume.user.position.eq(request.position()));
    }

    return from(resume)
            .where(builder)
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .orderBy(resume.lastModifiedAt.desc())
            .fetch();
}

2024-04-24T17:15:53.294+09:00  INFO 46419 --- [nio-8080-exec-1] i.StatisticalLoggingSessionEventListener : Session Metrics {
    38833 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    927210 nanoseconds spent preparing 19 JDBC statements;
    9720581 nanoseconds spent executing 19 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    90792 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

 

1. OneToOne 관계의 entity fetchJoin으로 최적화

QueryDSL도 JPA와 마찬가지로 fetchJoin를 지원합니다. 따라서 ToOne 관계의 entity를 fetchJoin 해줍니다.

public List<Resume> findSearchResume(SearchType searchType, SearchResumeRequest request, Pageable pageable) {

    QResume resume = QResume.resume;
    // QUser 추가
    QUser user = QUser.user;

    BooleanBuilder builder = new BooleanBuilder();

    if (searchType == SearchType.POSITION){
        builder.and(resume.user.position.eq(request.position()));
    }

    if(searchType == SearchType.TECHSTACKS && !request.techStacks().isEmpty()) {
        for (String techStack : request.techStacks()) {
            builder.and(resume.user.techStack.contains(techStack));
        }
    }

    if (searchType == SearchType.POSITIONANDTECHSTACKS){
        for (String techStack : request.techStacks()) {
            builder.and(resume.user.techStack.contains(techStack));
        }
        builder.and(resume.user.position.eq(request.position()));
    }

    return from(resume)
            //fetchJoin 추가
            .leftJoin(resume.user, user)
            .fetchJoin()
            //
            .where(builder)
            .offset(pageable.getOffset())
            .limit(pageable.getPageSize())
            .orderBy(resume.lastModifiedAt.desc())
            .fetch();
}

2024-04-24T17:16:29.028+09:00  INFO 46486 --- [nio-8080-exec-1] i.StatisticalLoggingSessionEventListener : Session Metrics {
    33625 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    661666 nanoseconds spent preparing 13 JDBC statements;
    6062125 nanoseconds spent executing 13 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    67791 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

 

 

1차적으로 실행 속도와 JDBC statements(선언문)을 줄였습니다.

 

 

2.컬렉션 타입 관계

ToMany 혹은 컬렉션 타입의 경우 fetchJoin을 사용할 때 주의해야합니다.

데이터베이스의 조인 연산이 증가하여 발생하는 오버헤드 때문에 성능이 오히려 더 느려질 수가 있습니다.

하지만 저의 프로젝트의 경우 단순히 기술 스택만을 가지고 있는 컬렉션 필드이기 때문에 괜찮다고 판단하였습니다.

한번에 많은 데이터를 가져오는게 좋을지 아니면 쿼리를 날려서 가져오는게 좋을지는 개발자의 판단에 따라 달렸습니다.

개발에는 이에 대한 트레이드 오프는 항상 존재합니다. 충분히 고민을 하시고 프로젝트의 방향에 따라 설계하시기를 바랍니다.

 

    @Override
    public List<Resume> findSearchResume(SearchType searchType, SearchResumeRequest request, Pageable pageable) {
        log.info("start findSearchResume");

        QResume resume = QResume.resume;
        QUser user = QUser.user;
        QProfileImageMetaData profileImage = QProfileImageMetaData.profileImageMetaData;

        BooleanBuilder builder = new BooleanBuilder();

        if (searchType == SearchType.POSITION){
            builder.and(resume.user.position.eq(request.position()));
        }

        if(searchType == SearchType.TECHSTACKS && !request.techStacks().isEmpty()) {
            for (String techStack : request.techStacks()) {
                builder.and(resume.user.techStack.contains(techStack));
            }
        }

        if (searchType == SearchType.POSITIONANDTECHSTACKS){
            for (String techStack : request.techStacks()) {
                builder.and(resume.user.techStack.contains(techStack));
            }
            builder.and(resume.user.position.eq(request.position()));
        }

        return from(resume)
                .leftJoin(resume.user, user)
                .fetchJoin()
                // fetchJoin user.techStack 추가
                .leftJoin(user.techStack)
                .fetchJoin()
                .where(builder)
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .orderBy(resume.lastModifiedAt.desc())
                .fetch();
    }

2024-04-24T18:30:40.385+09:00  INFO 50378 --- [nio-8080-exec-1] i.StatisticalLoggingSessionEventListener : Session Metrics {
    38584 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    372666 nanoseconds spent preparing 7 JDBC statements;
    3527292 nanoseconds spent executing 7 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    74917 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

JDBC statements(선언문)을 줄였습니다 하지만 실행 속도는 느려졌습니다. 이를 보안하기 위해 @index 어노테이션을 추가해주겠습니다.

 

@index 어노테이션 추가

@Entity
@Getter
@NoArgsConstructor
@AllArgsConstructor
// DB index 추가
@Table(name = "user",
        indexes = { @Index(name = "tech_stack_index", columnList = "tech_stack") }
)
public class User extends BaseTimeEntity implements UserDetails {

2024-04-24T18:40:56.555+09:00  INFO 50896 --- [nio-8080-exec-1] i.StatisticalLoggingSessionEventListener : Session Metrics {
    38500 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    389958 nanoseconds spent preparing 7 JDBC statements;
    4434625 nanoseconds spent executing 7 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    62375 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}

 

성능 속도가 빨라진것을 확인할 수 있습니다.

 

JDBC statements

19 -> 7

DB IO 63.16% 감소

실행 속도

90792 -> 62375