N+1 문제 해결 과정

나는 N+1 문제를 실제로 겪어보진 못했고, 블로그 읽기만 주구장창했었다
드디어 N+1 문제를 겪어보았다! ㅎㅎ

[길잡] 프로젝트에서 전체 퀘스트 목록들을 조회하는 api를 개발했다.
다음은 N+1 문제가 존재하는 코드를 보기 전 퀘스트 관련 로직을 처리하는 서비스 클래스이다.

@Transactional(readOnly = true)
    fun getQuestList(questId: Long?, position: Position, size: Long): List<QuestResponseDto> {
        val questList =
            questRepository.findByIdLessThanAndOrderByIdDesc(questId = questId, position = position, size = size)

        return questList.map {
            questMapper.toDto(it, userMapper.toDto(it.quest.user, it.point))
        }
    }

Dto로 변환할 때, it.quest.user 에서 생길 때 추가적으로 user를 조회하는 쿼리가 생긴다.
다음은 queryDsl로 작성한 쿼리 메서드이다.

private fun selectQuestList(builder: BooleanBuilder, size: Long) = query.select(
        Projections.constructor(
            QuestSupportVo::class.java,
            quest,
            ability.point,
            JPAExpressions.select(questParticipation.count()).from(questParticipation)
                .where(questParticipation.quest.eq(quest))
        )
    ).distinct()
        .from(quest)
        .where(builder)
        .leftJoin(ability).on(ability.position.eq(quest.user.position).and(ability.user.id.eq(quest.user.id)))
        .orderBy(quest.id.desc())
        .limit(size)
        .fetch()

실제 조회하는 쿼리를 보자

select
        distinct quest0_.quest_id as col_0_0_,
        ability1_.point as col_1_0_,
        (select
            count(questparti3_.participant_id) 
        from
            quest_participation questparti3_ 
        where
            questparti3_.quest_id=quest0_.quest_id) as col_2_0_,
        quest0_.quest_id as quest_id1_1_,
        quest0_.created_at as created_2_1_,
        quest0_.modified_at as modified3_1_,
        quest0_.detail as detail4_1_,
        quest0_.difficulty as difficul5_1_,
        quest0_.is_real_quest as is_real_6_1_,
        quest0_.name as name7_1_,
        quest0_.position as position8_1_,
        quest0_.thumbnail as thumbnai9_1_,
        quest0_.writer_id as writer_10_1_ 
    from
        quest quest0_ 
    left outer join
        user user2_ 
            on quest0_.writer_id=user2_.user_id 
    left outer join
        ability ability1_ 
            on (
                ability1_.position=user2_.position 
                and ability1_.user_id=quest0_.writer_id
            ) 
    order by
        quest0_.quest_id desc limit ?
        
-- N+1 문제
Hibernate: 
    select
        user0_.user_id as user_id1_10_0_,
        user0_.created_at as created_2_10_0_,
        user0_.modified_at as modified3_10_0_,
        user0_.authority as authorit4_10_0_,
        user0_.intro as intro5_10_0_,
        user0_.nickname as nickname6_10_0_,
        user0_.position as position7_10_0_,
        user0_.social_id as social_i8_10_0_ 
    from
        user user0_ 
    where
        user0_.user_id=?
Hibernate: 
    select
        user0_.user_id as user_id1_10_0_,
        user0_.created_at as created_2_10_0_,
        user0_.modified_at as modified3_10_0_,
        user0_.authority as authorit4_10_0_,
        user0_.intro as intro5_10_0_,
        user0_.nickname as nickname6_10_0_,
        user0_.position as position7_10_0_,
        user0_.social_id as social_i8_10_0_ 
    from
        user user0_ 
    where
        user0_.user_id=?
Hibernate: 
    select
        user0_.user_id as user_id1_10_0_,
        user0_.created_at as created_2_10_0_,
        user0_.modified_at as modified3_10_0_,
        user0_.authority as authorit4_10_0_,
        user0_.intro as intro5_10_0_,
        user0_.nickname as nickname6_10_0_,
        user0_.position as position7_10_0_,
        user0_.social_id as social_i8_10_0_ 
    from
        user user0_ 
    where
        user0_.user_id=?
Hibernate: 
    select
        user0_.user_id as user_id1_10_0_,
        user0_.created_at as created_2_10_0_,
        user0_.modified_at as modified3_10_0_,
        user0_.authority as authorit4_10_0_,
        user0_.intro as intro5_10_0_,
        user0_.nickname as nickname6_10_0_,
        user0_.position as position7_10_0_,
        user0_.social_id as social_i8_10_0_ 
    from
        user user0_ 
    where
        user0_.user_id=?

N+1 문제는 ORM을 사용하면 가장 쉽게 접할 수 있는 문제 중에 하나이다.
1번의 쿼리를 날렸지만, 추가로 N번 더 쿼리문을 날려야 하는 상황이다.

나는 FetchJoin으로 N+1 문제를 해결했다!
FetchJoin으로 user 정보도 한꺼번에 가져와보도록 해보겠다.

private fun selectQuestList(builder: BooleanBuilder, size: Long) = query.select(
        Projections.constructor(
            QuestSupportVo::class.java,
            quest,
            ability.point,
            JPAExpressions.select(questParticipation.count()).from(questParticipation)
                .where(questParticipation.quest.eq(quest))
        )
    ).distinct()
        .from(quest)
        .where(builder)
        .leftJoin(ability).on(ability.position.eq(quest.user.position).and(ability.user.id.eq(quest.user.id)))
        .leftJoin(quest.user, user)
        .fetchJoin()
        .orderBy(quest.id.desc())
        .limit(size)
        .fetch()

    select
        distinct quest0_.quest_id as col_0_0_,
        ability1_.point as col_1_0_,
        (select
            count(questparti4_.participant_id) 
        from
            quest_participation questparti4_ 
        where
            questparti4_.quest_id=quest0_.quest_id) as col_2_0_,
        user3_.user_id as user_id1_10_2_,
        quest0_.quest_id as quest_id1_1_0_,
        user3_.user_id as user_id1_10_1_,
        quest0_.created_at as created_2_1_0_,
        quest0_.modified_at as modified3_1_0_,
        quest0_.detail as detail4_1_0_,
        quest0_.difficulty as difficul5_1_0_,
        quest0_.is_real_quest as is_real_6_1_0_,
        quest0_.name as name7_1_0_,
        quest0_.position as position8_1_0_,
        quest0_.thumbnail as thumbnai9_1_0_,
        quest0_.writer_id as writer_10_1_0_,
        user3_.created_at as created_2_10_1_,
        user3_.modified_at as modified3_10_1_,
        user3_.authority as authorit4_10_1_,
        user3_.intro as intro5_10_1_,
        user3_.nickname as nickname6_10_1_,
        user3_.position as position7_10_1_,
        user3_.social_id as social_i8_10_1_ 
    from
        quest quest0_ 
    left outer join
        user user2_ 
            on quest0_.writer_id=user2_.user_id 
    left outer join
        ability ability1_ 
            on (
                ability1_.position=user2_.position 
                and ability1_.user_id=quest0_.writer_id
            ) 
    left outer join
        user user3_ 
            on quest0_.writer_id=user3_.user_id 
    order by
        quest0_.quest_id desc limit ?

join문을 통해 한꺼번에 user 정보를 가져왔다.
이제 추가적인 쿼리는 하지 않는다.

하지만 여기서

    left outer join
        user user2_ 
            on quest0_.writer_id=user2_.user_id 

    left outer join
        user user3_ 
            on quest0_.writer_id=user3_.user_id 

중복된 join문이 있는 걸 알 수 있다. fetchJoin을 먼저 한다면, 다시 Join을 하지 않는다.

최종 쿼리 메서드!

    private fun selectQuestList(builder: BooleanBuilder, size: Long) = query.select(
        Projections.constructor(
            QuestSupportVo::class.java,
            quest,
            ability.point,
            JPAExpressions.select(questParticipation.count()).from(questParticipation)
                .where(questParticipation.quest.eq(quest))
        )
    ).distinct()
        .from(quest)
        .where(builder)
        .leftJoin(quest.user, user)
        .fetchJoin()
        .leftJoin(ability).on(ability.position.eq(quest.user.position).and(ability.user.id.eq(quest.user.id)))
        .orderBy(quest.id.desc())
        .limit(size)
        .fetch()

최종 쿼리문!

select
        distinct quest0_.quest_id as col_0_0_,
        ability2_.point as col_1_0_,
        (select
            count(questparti4_.participant_id) 
        from
            quest_participation questparti4_ 
        where
            questparti4_.quest_id=quest0_.quest_id) as col_2_0_,
        user1_.user_id as user_id1_10_1_,
        quest0_.quest_id as quest_id1_1_0_,
        quest0_.created_at as created_2_1_0_,
        quest0_.modified_at as modified3_1_0_,
        quest0_.detail as detail4_1_0_,
        quest0_.difficulty as difficul5_1_0_,
        quest0_.is_real_quest as is_real_6_1_0_,
        quest0_.name as name7_1_0_,
        quest0_.position as position8_1_0_,
        quest0_.thumbnail as thumbnai9_1_0_,
        quest0_.writer_id as writer_10_1_0_,
        user1_.created_at as created_2_10_1_,
        user1_.modified_at as modified3_10_1_,
        user1_.authority as authorit4_10_1_,
        user1_.intro as intro5_10_1_,
        user1_.nickname as nickname6_10_1_,
        user1_.position as position7_10_1_,
        user1_.social_id as social_i8_10_1_ 
    from
        quest quest0_ 
    left outer join
        user user1_ 
            on quest0_.writer_id=user1_.user_id 
    left outer join
        ability ability2_ 
            on (
                ability2_.position=user1_.position 
                and ability2_.user_id=quest0_.writer_id
            ) 
    order by
        quest0_.quest_id desc limit ?

fetchJoin은 N+1 쿼리 문제를 해결하는 방법 중 하나이고, EntityGraph, BatchSize 등 다른 해결 방법도 있다고 들었다. 이것들도 접하면 블로그 포스팅해야지!