N+1 정복기 기본편4

조현근·2022년 11월 25일
n+1
0
post-thumbnail

이번엔 2개 이상의 자식 테이블에 fetch join을 하게 되면 어떤 문제가 발생하는지 확인해보겠습니다.

학습테스트를 위해 사용된 엔티티 입니다.
Team5

@Entity
public class Team5 {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "team_id")
    private Long id;

    private String name;

    @OneToMany(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "team_id")
    private List<Member5> members = new ArrayList<>();

    @OneToMany(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "team_id")
    private List<Coach5> coaches = new ArrayList<>();

    protected Team5() {
    }

    public Team5(String name, List<Member5> members, List<Coach5> coaches) {
        this.name = name;
        this.members = members;
        this.coaches = coaches;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public List<Member5> getMembers() {
        return members;
    }

    public List<Coach5> getCoaches() {
        return coaches;
    }
}

Member5

@Entity
public class Member5 {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "member_id")
    private Long id;

    private String name;

    protected Member5() {
    }

    public Member5(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }
}

Coach5

@Entity
public class Coach5 {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "coach_id")
    private Long id;

    private String name;

    protected Coach5() {
    }

    public Coach5(String name) {
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }
}

우선 N+1이 발생하는 학습테스트를 만들어보았습니다.

Team5Service

@Service
public class Team5Service {

    private final Team5Repository team5Repository;

    public Team5Service(Team5Repository team5Repository) {
        this.team5Repository = team5Repository;
    }

    @Transactional(readOnly = true)
    public List<String> findAllMembersAndCoachesName() {
        List<Team5> teams = team5Repository.findAll();
        List<String> membersName = teams.stream()
                .flatMap(team -> team.getMembers().stream())
                .map(Member5::getName)
                .collect(Collectors.toList());
        List<String> coachesName = teams.stream()
                .flatMap(team -> team.getCoaches().stream())
                .map(Coach5::getName)
                .collect(Collectors.toList());

        membersName.addAll(coachesName);
        return membersName;
    }
}

학습 테스트

@BeforeEach
void setUp() {
    for (int i = 0; i < 10; ++i) {
        Member5 member = new Member5("member");
        Coach5 coach = new Coach5("coach");

        Team5 team = new Team5("team", List.of(member), List.of(coach));
        team5Repository.save(team);
    }

    em.flush();
    em.clear();
}

@DisplayName("N+1 문제가 발생한다.")
@Test
void N_1_Occur() {
    System.out.println("@@@ N+1 @@@");
    List<String> membersAndCoachesName = team5Service.findAllMembersAndCoachesName();
    System.out.println("@@@ N+1 @@@");

    assertThat(membersAndCoachesName).hasSize(20);
}

학습 테스트 결과

@@@ N+1 @@@
Hibernate: 
    select
        team5x0_.team_id as team_id1_14_,
        team5x0_.name as name2_14_ 
    from
        team5 team5x0_
Hibernate: 
    select
        members0_.team_id as team_id3_6_0_,
        members0_.member_id as member_i1_6_0_,
        members0_.member_id as member_i1_6_1_,
        members0_.name as name2_6_1_ 
    from
        member5 members0_ 
    where
        members0_.team_id=?
Hibernate: 
    select
        members0_.team_id as team_id3_6_0_,
        members0_.member_id as member_i1_6_0_,
        members0_.member_id as member_i1_6_1_,
        members0_.name as name2_6_1_ 
    from
        member5 members0_ 
    where
        members0_.team_id=?
Hibernate: 
    select
        members0_.team_id as team_id3_6_0_,
        members0_.member_id as member_i1_6_0_,
        members0_.member_id as member_i1_6_1_,
        members0_.name as name2_6_1_ 
    from
        member5 members0_ 
    where
        members0_.team_id=?
Hibernate: 
    select
        members0_.team_id as team_id3_6_0_,
        members0_.member_id as member_i1_6_0_,
        members0_.member_id as member_i1_6_1_,
        members0_.name as name2_6_1_ 
    from
        member5 members0_ 
    where
        members0_.team_id=?
Hibernate: 
    select
        members0_.team_id as team_id3_6_0_,
        members0_.member_id as member_i1_6_0_,
        members0_.member_id as member_i1_6_1_,
        members0_.name as name2_6_1_ 
    from
        member5 members0_ 
    where
        members0_.team_id=?
Hibernate: 
    select
        members0_.team_id as team_id3_6_0_,
        members0_.member_id as member_i1_6_0_,
        members0_.member_id as member_i1_6_1_,
        members0_.name as name2_6_1_ 
    from
        member5 members0_ 
    where
        members0_.team_id=?
Hibernate: 
    select
        members0_.team_id as team_id3_6_0_,
        members0_.member_id as member_i1_6_0_,
        members0_.member_id as member_i1_6_1_,
        members0_.name as name2_6_1_ 
    from
        member5 members0_ 
    where
        members0_.team_id=?
Hibernate: 
    select
        members0_.team_id as team_id3_6_0_,
        members0_.member_id as member_i1_6_0_,
        members0_.member_id as member_i1_6_1_,
        members0_.name as name2_6_1_ 
    from
        member5 members0_ 
    where
        members0_.team_id=?
Hibernate: 
    select
        members0_.team_id as team_id3_6_0_,
        members0_.member_id as member_i1_6_0_,
        members0_.member_id as member_i1_6_1_,
        members0_.name as name2_6_1_ 
    from
        member5 members0_ 
    where
        members0_.team_id=?
Hibernate: 
    select
        members0_.team_id as team_id3_6_0_,
        members0_.member_id as member_i1_6_0_,
        members0_.member_id as member_i1_6_1_,
        members0_.name as name2_6_1_ 
    from
        member5 members0_ 
    where
        members0_.team_id=?
Hibernate: 
    select
        coaches0_.team_id as team_id3_0_0_,
        coaches0_.coach_id as coach_id1_0_0_,
        coaches0_.coach_id as coach_id1_0_1_,
        coaches0_.name as name2_0_1_ 
    from
        coach5 coaches0_ 
    where
        coaches0_.team_id=?
Hibernate: 
    select
        coaches0_.team_id as team_id3_0_0_,
        coaches0_.coach_id as coach_id1_0_0_,
        coaches0_.coach_id as coach_id1_0_1_,
        coaches0_.name as name2_0_1_ 
    from
        coach5 coaches0_ 
    where
        coaches0_.team_id=?
Hibernate: 
    select
        coaches0_.team_id as team_id3_0_0_,
        coaches0_.coach_id as coach_id1_0_0_,
        coaches0_.coach_id as coach_id1_0_1_,
        coaches0_.name as name2_0_1_ 
    from
        coach5 coaches0_ 
    where
        coaches0_.team_id=?
Hibernate: 
    select
        coaches0_.team_id as team_id3_0_0_,
        coaches0_.coach_id as coach_id1_0_0_,
        coaches0_.coach_id as coach_id1_0_1_,
        coaches0_.name as name2_0_1_ 
    from
        coach5 coaches0_ 
    where
        coaches0_.team_id=?
Hibernate: 
    select
        coaches0_.team_id as team_id3_0_0_,
        coaches0_.coach_id as coach_id1_0_0_,
        coaches0_.coach_id as coach_id1_0_1_,
        coaches0_.name as name2_0_1_ 
    from
        coach5 coaches0_ 
    where
        coaches0_.team_id=?
Hibernate: 
    select
        coaches0_.team_id as team_id3_0_0_,
        coaches0_.coach_id as coach_id1_0_0_,
        coaches0_.coach_id as coach_id1_0_1_,
        coaches0_.name as name2_0_1_ 
    from
        coach5 coaches0_ 
    where
        coaches0_.team_id=?
Hibernate: 
    select
        coaches0_.team_id as team_id3_0_0_,
        coaches0_.coach_id as coach_id1_0_0_,
        coaches0_.coach_id as coach_id1_0_1_,
        coaches0_.name as name2_0_1_ 
    from
        coach5 coaches0_ 
    where
        coaches0_.team_id=?
Hibernate: 
    select
        coaches0_.team_id as team_id3_0_0_,
        coaches0_.coach_id as coach_id1_0_0_,
        coaches0_.coach_id as coach_id1_0_1_,
        coaches0_.name as name2_0_1_ 
    from
        coach5 coaches0_ 
    where
        coaches0_.team_id=?
Hibernate: 
    select
        coaches0_.team_id as team_id3_0_0_,
        coaches0_.coach_id as coach_id1_0_0_,
        coaches0_.coach_id as coach_id1_0_1_,
        coaches0_.name as name2_0_1_ 
    from
        coach5 coaches0_ 
    where
        coaches0_.team_id=?
Hibernate: 
    select
        coaches0_.team_id as team_id3_0_0_,
        coaches0_.coach_id as coach_id1_0_0_,
        coaches0_.coach_id as coach_id1_0_1_,
        coaches0_.name as name2_0_1_ 
    from
        coach5 coaches0_ 
    where
        coaches0_.team_id=?
@@@ N+1 @@@

N+1이 발생함을 알 수 있습니다.
이를 개선하기 위해 Member와 Coach테이블에 모두 fetch join을 걸게 된다면 어떤 문제가 발생하는지 확인해보겠습니다.

Team5Repository에 다음과 같이 fetch join을 두 번 사용하는 메서드를 만들어보았습니다.

Team5Repository

@Query(value = "SELECT DISTINCT t FROM Team5 t JOIN FETCH t.members JOIN FETCH t.coaches")
List<Team5> findAllFetchJoin();
}

그리고 학습테스트에서 아무런 일을 하지 않는 테스트코드를 작성하고 ApplicationContext를 띄워보겠습니다.
학습테스트

@DisplayName("multiBagException이 발생한다.")
@Test
void fetchJoin_Exception() {
    // APPLICATION CONTEXT가 뜨면서 MultipleBagFetchException이 발생한다!
}

결과

ApplicationContext가 뜨지 못하고 MultipleBagFetchException이 발생하는 것을 알 수 있습니다.

왜 Hibernate에서 MultipleBagFetchException이 발생하게 했는지는 아래 포스팅에 정리해두었습니다.
MultipleBagFetchException

그렇다면 두 개 이상의 자식 테이블을 모두 가져오고 싶다면 어떻게 해야 할까요?

fetch join과 batch size를 사용

하나의 자식 테이블에 대해선 fetch join을, 나머지 자식 테이블은 batch size를 이용해 해결할 수 있습니다. Member 테이블에 대해선 fetch join으로 데이터를 가져오고 Coach테이블에 대해선 @BatchSize를 통해 데이터를 가져오면 MultipleBagException을 회피할 수 있습니다.
변경된 코드를 보여드리겠습니다.

Repository

@Query(value = "SELECT DISTINCT t FROM Team5 t JOIN FETCH t.members")
List<Team5> findAllFetchJoin();

쿼리문엔 member테이블만 fetch join으로 가져옵니다.

Team

@Entity
public class Team5 {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "team_id")
    private Long id;

    private String name;

    @OneToMany(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "team_id")
    private List<Member5> members = new ArrayList<>();

    @BatchSize(size = 100)
    @OneToMany(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "team_id")
    private List<Coach5> coaches = new ArrayList<>();

    protected Team5() {
    }

    public Team5(String name, List<Member5> members, List<Coach5> coaches) {
        this.name = name;
        this.members = members;
        this.coaches = coaches;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public List<Member5> getMembers() {
        return members;
    }

    public List<Coach5> getCoaches() {
        return coaches;
    }
}

Coach테이블에 대해선 @BatchSize를 통해 데이터를 가져오도록 할 수 있습니다.
이제 어떤 쿼리가 나가는지 학습테스트를 통해 확인해보겠습니다.

학습테스트

@DisplayName("fetch join과 batch size로 multipleBagException을 해결한다.")
@Test
void fetchJoin_BatchSize() {
    System.out.println("@@@ fetch join with batch size @@@");
    List<String> membersAndCoachesName = team5Service.findAllMembersAndCoachesName_fetchJoin();
    System.out.println("@@@ fetch join with batch size @@@");

    assertThat(membersAndCoachesName).hasSize(20);
}

결과

@@@ fetch join with batch size @@@
Hibernate: 
    select
        distinct team5x0_.team_id as team_id1_14_0_,
        members1_.member_id as member_i1_6_1_,
        team5x0_.name as name2_14_0_,
        members1_.name as name2_6_1_,
        members1_.team_id as team_id3_6_0__,
        members1_.member_id as member_i1_6_0__ 
    from
        team5 team5x0_ 
    inner join
        member5 members1_ 
            on team5x0_.team_id=members1_.team_id
Hibernate: 
    select
        coaches0_.team_id as team_id3_0_1_,
        coaches0_.coach_id as coach_id1_0_1_,
        coaches0_.coach_id as coach_id1_0_0_,
        coaches0_.name as name2_0_0_ 
    from
        coach5 coaches0_ 
    where
        coaches0_.team_id in (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )
@@@ fetch join with batch size @@@

fetch join을 사용한 Member 테이블에 대해서 inner join을 사용한 쿼리 하나, @BatchSize를 사용한 Coach 테이블에 대해 in 절이 사용된 쿼리 하나로 총 2개의 쿼리로 N+1이 해결됨을 알 수 있습니다!!!

Set 자료구조를 이용해 해결

두 번째 방법은 자식 엔티티의 자료구조를 Set으로 사용하면 됩니다. 도메인적으로 Unique를 보장할 수 있다면 사용할 수 있는 방법입니다.
변경된 코드를 보여드리겠습니다.

Team

@Entity
public class Team5 {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "team_id")
    private Long id;

    private String name;

    @OneToMany(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "team_id")
    private List<Member5> members = new ArrayList<>();

    @OneToMany(cascade = CascadeType.PERSIST)
    @JoinColumn(name = "team_id")
    private Set<Coach5> coaches = new HashSet<>();

    protected Team5() {
    }

    public Team5(String name, List<Member5> members, Set<Coach5> coaches) {
        this.name = name;
        this.members = members;
        this.coaches = coaches;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public List<Member5> getMembers() {
        return members;
    }

    public Set<Coach5> getCoaches() {
        return coaches;
    }
}

자식 테이블을 표현하는 coach 엔티티의 자료구조를 Set으로 변경해주었습니다.

Repository

@Query(value = "SELECT DISTINCT t FROM Team5 t JOIN FETCH t.members JOIN FETCH t.coaches")
List<Team5> findAllFetchJoin();

쿼리는 fetch join을 두 번 하도록 변경했습니다.

이제 학습테스트를 통해 어떻게 쿼리가 나가는지 확인해보겠습니다.
학습테스트

@DisplayName("Set 자료구조를 이용해 multipleBagException을 해결한다.")
@Test
void fetchJoin_Set() {
    System.out.println("@@@ double fetch join with Set @@@");
    List<String> membersAndCoachesName = team5Service.findAllMembersAndCoachesName_fetchJoin();
    System.out.println("@@@ double fetch join with Set @@@");

    assertThat(membersAndCoachesName).hasSize(20);
}

결과

@@@ double fetch join with Set @@@
Hibernate: 
    select
        distinct team5x0_.team_id as team_id1_14_0_,
        members1_.member_id as member_i1_6_1_,
        coaches2_.coach_id as coach_id1_0_2_,
        team5x0_.name as name2_14_0_,
        members1_.name as name2_6_1_,
        members1_.team_id as team_id3_6_0__,
        members1_.member_id as member_i1_6_0__,
        coaches2_.name as name2_0_2_,
        coaches2_.team_id as team_id3_0_1__,
        coaches2_.coach_id as coach_id1_0_1__ 
    from
        team5 team5x0_ 
    inner join
        member5 members1_ 
            on team5x0_.team_id=members1_.team_id 
    inner join
        coach5 coaches2_ 
            on team5x0_.team_id=coaches2_.team_id
@@@ double fetch join with Set @@@

inner join이 두 번 적용된 쿼리 한 건이 나가는 것을 알 수 있습니다!!!!

참고

https://jojoldu.tistory.com/457

profile
조현근
안녕하세요!
이전 포스트

Blocking/Non-Blocking, Sync/Async

다음 포스트

인덱스(Index) 정복기 2

0개의 댓글