JPA와 queryDSL을 사용해서 제목과 해시태그로 검색하는 기능을 구현했다. 그런데 검색을 했을 때 select문이 여러개가 나가는 현상이 발생해서 찾아봤다.

JPA n+1 문제

n+1문제

연관관계가 설정된 엔티티를 조회할 경우에 조회된 데이터의 개수(n)만큼 연관관계 조회 쿼리가 추가로 발생하여 데이터를 읽어오는 현상을 말한다.

테이블 간의 관계

챌린지에 대한 정보가 들어있는 챌린지 테이블과 해시태그의 정보가 있는 해시태그 테이블이 있고 챌린지와 해시태그를 매핑해주는 중간테이블이 존재한다.

상황 설명

챌린지를 조회하면 해당 챌린지의 해시태그도 가져와서 보여주어야한다. 또한, 검색 keyword가 챌린지 제목에 포함된 경우와 keyword와 일치하는 해시태그가 존재하는 챌린지를 검색해서 보여준다.

해결

application.yml 추가 설정

쿼리를 좀 더 편하게 관리하기 위해 추가 설정을 했다.

spring:
	jpa:
		show-sql: true //퀴리 출력
        properties:
          hibernate:
            format_sql: true //출력되는 쿼리의 포맷 예쁘게
            generate_statistics: true

만약 application.properties의 경우

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.generate_statistics=true

수정 전

@Override
public List<Challenge> findSearchTitleSortById(String keyword) {
    QChallengeHashTag challengehashtag=QChallengeHashTag.challengeHashTag;
    QChallenge challenge=QChallenge.challenge;
    QHashTag hashTag= QHashTag.hashTag;
    return queryFactory.selectFrom(challenge)
            .leftJoin(challengehashtag).on(challengehashtag.challenge.id.eq(challenge.id))
            .leftJoin(hashTag).on(challengehashtag.hashTag.id.eq(hashTag.id))
            .where(challenge.title.contains(keyword).or(hashTag.contents.eq(keyword)), challenge.vigibility.eq(true), challenge.startDate.after(LocalDate.now()))
            .orderBy(challenge.id.desc())
            .fetch();

}

Hibernate: 
    select
        c1_0.id,
        c1_0.authentication_type,
        c1_0.created_at,
        c1_0.deleted_at,
        c1_0.description,
        c1_0.end_date,
        c1_0.modified_at,
        c1_0.start_date,
        c1_0.title,
        c1_0.user_id,
        c1_0.vigibility 
    from
        challenge c1_0 
    left join
        challenge_hashtag c2_0 
            on c2_0.challenge_id=c1_0.id 
    left join
        hashtag h1_0 
            on c2_0.hash_tag_id=h1_0.id 
    where
        (
            c1_0.deleted_at is NULL
        ) 
        and (
            c1_0.title like ? escape '!' 
            or h1_0.contents=?
        ) 
        and c1_0.vigibility=? 
        and c1_0.start_date>? 
    order by
        c1_0.id desc
Hibernate: 
    select
        h1_0.id,
        h1_0.contents 
    from
        hashtag h1_0 
    where
        h1_0.id=?
Hibernate: 
    select
        h1_0.id,
        h1_0.contents 
    from
        hashtag h1_0 
    where
        h1_0.id=?
2023-04-23T18:02:58.662+09:00  WARN 78053 --- [nio-8080-exec-1] actStandardFragmentInsertionTagProcessor : [THYMELEAF][http-nio-8080-exec-1][challenges/list] Deprecated unwrapped fragment expression "fragments/head :: frag-header" found in template challenges/list, line 5, col 15. Please use the complete syntax of fragment expressions instead ("~{fragments/head :: frag-header}"). The old, unwrapped syntax for fragment expressions will be removed in future versions of Thymeleaf.
2023-04-23T18:02:58.714+09:00  WARN 78053 --- [nio-8080-exec-1] actStandardFragmentInsertionTagProcessor : [THYMELEAF][http-nio-8080-exec-1][challenges/list] Deprecated unwrapped fragment expression "fragments/nav :: menu" found in template challenges/list, line 9, col 72. Please use the complete syntax of fragment expressions instead ("~{fragments/nav :: menu}"). The old, unwrapped syntax for fragment expressions will be removed in future versions of Thymeleaf.
2023-04-23T18:02:58.737+09:00  WARN 78053 --- [nio-8080-exec-1] actStandardFragmentInsertionTagProcessor : [THYMELEAF][http-nio-8080-exec-1][challenges/list] Deprecated unwrapped fragment expression "fragments/footer :: frag-footer" found in template challenges/list, line 52, col 19. Please use the complete syntax of fragment expressions instead ("~{fragments/footer :: frag-footer}"). The old, unwrapped syntax for fragment expressions will be removed in future versions of Thymeleaf.
2023-04-23T18:02:58.740+09:00  INFO 78053 --- [nio-8080-exec-1] i.StatisticalLoggingSessionEventListener : Session Metrics {
    7215250 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    440875 nanoseconds spent preparing 5 JDBC statements;
    39466042 nanoseconds spent executing 5 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);
    82166 nanoseconds spent executing 3 partial-flushes (flushing a total of 0 entities and 0 collections)
}

select문이 여러개가 나오는걸 확인할 수 있다. 이경우 query dsl의 fetch join을 사용하여 해결할 수 있다.

fetch join

조인하는 대상 데이터를 즉시 로딩해서 가져온다.

첫번째 시도

@Override
public List<Challenge> findSearchTitleSortById(String keyword) {
    QChallengeHashTag challengehashtag=QChallengeHashTag.challengeHashTag;
    QChallenge challenge=QChallenge.challenge;
    QHashTag hashTag= QHashTag.hashTag;
    return queryFactory.selectFrom(challenge)
            .leftJoin(challengehashtag).on(challengehashtag.challenge.id.eq(challenge.id))
            .fetchJoin()
            .leftJoin(hashTag).on(challengehashtag.hashTag.id.eq(hashTag.id))
            .fetchJoin()
            .where(challenge.title.contains(keyword).or(hashTag.contents.eq(keyword)), challenge.vigibility.eq(true), challenge.startDate.after(LocalDate.now()))
            .orderBy(challenge.id.desc())
            .fetch();

}

fetchJoin()을 사용했는데도 똑같았다... 그래서 찾아본 결과 querydsl에서 on()절로 매핑하면 두 엔티티가 연관관계라는 것을 인식하지 못한다. 그렇기 때문에 leftJoin대신 join으로 수정해서 해결했다.

두번째 시도

@Override
public List<Challenge> findSearchTitleSortById(String keyword) {
    QChallengeHashTag challengehashtag=QChallengeHashTag.challengeHashTag;
    QChallenge challenge=QChallenge.challenge;
    QHashTag hashTag= QHashTag.hashTag;
    return queryFactory.selectFrom(challenge)
            .leftJoin(challengehashtag).on(challengehashtag.challenge.id.eq(challenge.id))
            .fetchJoin()
            .leftJoin(hashTag).on(challengehashtag.hashTag.id.eq(hashTag.id))
            .fetchJoin()
            .where(challenge.title.contains(keyword).or(hashTag.contents.eq(keyword)), challenge.vigibility.eq(true), challenge.startDate.after(LocalDate.now()))
            .orderBy(challenge.id.desc())
            .fetch();

}

Hibernate: 
    select
        c1_0.id,
        c1_0.authentication_type,
        c2_0.challenge_id,
        c2_0.id,
        h1_0.id,
        h1_0.contents,
        c1_0.created_at,
        c1_0.deleted_at,
        c1_0.description,
        c1_0.end_date,
        c1_0.modified_at,
        c1_0.start_date,
        c1_0.title,
        c1_0.user_id,
        c1_0.vigibility 
    from
        challenge c1_0 
    join
        challenge_hashtag c2_0 
            on c1_0.id=c2_0.challenge_id 
    join
        hashtag h1_0 
            on h1_0.id=c2_0.hash_tag_id 
    where
        (
            c1_0.deleted_at is NULL
        ) 
        and (
            c1_0.title like ? escape '!' 
            or h1_0.contents=?
        ) 
        and c1_0.vigibility=? 
        and c1_0.start_date>? 
    order by
        c1_0.id desc
2023-04-23T17:56:46.808+09:00  WARN 77804 --- [nio-8080-exec-6] actStandardFragmentInsertionTagProcessor : [THYMELEAF][http-nio-8080-exec-6][challenges/list] Deprecated unwrapped fragment expression "fragments/head :: frag-header" found in template challenges/list, line 5, col 15. Please use the complete syntax of fragment expressions instead ("~{fragments/head :: frag-header}"). The old, unwrapped syntax for fragment expressions will be removed in future versions of Thymeleaf.
2023-04-23T17:56:46.808+09:00  WARN 77804 --- [nio-8080-exec-6] actStandardFragmentInsertionTagProcessor : [THYMELEAF][http-nio-8080-exec-6][challenges/list] Deprecated unwrapped fragment expression "fragments/nav :: menu" found in template challenges/list, line 9, col 72. Please use the complete syntax of fragment expressions instead ("~{fragments/nav :: menu}"). The old, unwrapped syntax for fragment expressions will be removed in future versions of Thymeleaf.
2023-04-23T17:56:46.819+09:00  WARN 77804 --- [nio-8080-exec-6] actStandardFragmentInsertionTagProcessor : [THYMELEAF][http-nio-8080-exec-6][challenges/list] Deprecated unwrapped fragment expression "fragments/footer :: frag-footer" found in template challenges/list, line 52, col 19. Please use the complete syntax of fragment expressions instead ("~{fragments/footer :: frag-footer}"). The old, unwrapped syntax for fragment expressions will be removed in future versions of Thymeleaf.
2023-04-23T17:56:46.844+09:00  INFO 77804 --- [nio-8080-exec-6] i.StatisticalLoggingSessionEventListener : Session Metrics {
    9087750 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    447209 nanoseconds spent preparing 3 JDBC statements;
    26491625 nanoseconds spent executing 3 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);
    12667 nanoseconds spent executing 3 partial-flushes (flushing a total of 0 entities and 0 collections)
}