JPA n+1 정리 page, projection.

하도 정리글이 많아서 나도 테스트해보면서 정리 할꺼임.

* 나는 일대다(1:N), 다대일(N:1) 그리고 단방향, 양방향에 따라 문제가 다른 줄 알았는데 아니었음.
어차피 각자 단방향으로 연결된 양방향느낌(?) 인거니까 n+1 이 뜨면 성능상 문제가 발생할 수 있음.

---

테스트

 

각설하고 테스트는 아래처럼 진행한다.

Team (1) : Member( N ) 둘다 LAZY 모드로 fetch 설정 해놓았다.

############################################################팀
public class Team {

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

  private String name;

  private String nick;

  @OneToMany(mappedBy = "team", fetch = FetchType.LAZY)
  private List<Member> members;

}

############################################################멤버
public class Member {

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

  private String name;

  @ManyToOne(fetch = FetchType.LAZY)
  @JoinColumn(name = "team_id")
  private Team team;
  
}

 

DB 구조.

 

---

우선 Team 으로 Member를 조회한다.

    System.out.println("------------------------");
    rpTeam.findAll();
    System.out.println("------------------------");
    
    ###########################################결과
    ------------------------
    Hibernate: 
        select
            team0_.team_id as team_id1_7_,
            team0_.name as name2_7_,
            team0_.nick as nick3_7_ 
        from
            team team0_
    ------------------------

 

LAZY 모드이기 때문에 team 객체만 가져온다.

EAGER 모드로 변경하면 아래 그림처럼 3번 Member를 호출하게 된다.

 

이전 게시물에서 테스트 했던 내용이므로 N:1 은 생략하도록 하고 N:1 은 이 글을, 1:N 테스트는 이 글을 참조하길 바람.

결과적으로 n번 더 조회하는 문제가 발생했다.

---

 

해결방안

 

해결방안으로 가장 많이 나와있는 fetch join, EntityGraph 그리고 Fetch(FetchMode.SUBSELECT) 세가지를 확인해보겠다.

---

1. fetch join

1-1. OneToMany 

@Query("select team from TEAM team join fetch team.members") 어노테이션을 활용하여 패치 조인한다.
inner join 되는 것이 특징이다.

@RepositoryRestResource 
public interface TeamRepository extends JpaRepository<Team, Long> {
  @Query("select team from TEAM team join fetch team.members")
  List<Team> findAll();
}

결과 : 
------------------------
Hibernate: 
    select
        team0_.team_id as team_id1_7_0_,
        members1_.member_id as member_i1_0_1_,
        team0_.name as name2_7_0_,
        team0_.nick as nick3_7_0_,
        members1_.name as name2_0_1_,
        members1_.team_id as team_id3_0_1_,
        members1_.team_id as team_id3_0_0__,
        members1_.member_id as member_i1_0_0__ 
    from
        team team0_ 
    inner join
        member members1_ 
            on team0_.team_id=members1_.team_id
------------------------

 

1-2. ManyToOne

마찮가지임. 별다를게 없다.

@RepositoryRestResource 
public interface MemberRepository extends JpaRepository<Member, Long> {
  @Query("select m from MEMBER m join fetch m.team")
  List<Member> findAll();
}

------------------------
Hibernate: 
    select
        member0_.member_id as member_i1_0_0_,
        team1_.team_id as team_id1_7_1_,
        member0_.name as name2_0_0_,
        member0_.team_id as team_id3_0_0_,
        team1_.name as name2_7_1_,
        team1_.nick as nick3_7_1_ 
    from
        member member0_ 
    inner join
        team team1_ 
            on member0_.team_id=team1_.team_id
------------------------

---

2. EntityGraph

EntityGraph 는 left outer join 으로 패치조인 된다. 

##########################팀
@RepositoryRestResource 
public interface MemberRepository extends JpaRepository<Member, Long> {
  @EntityGraph(attributePaths = "team")
  Page<Member> findAll(Pageable pageable);
}
##########################멤버
@RepositoryRestResource 
public interface TeamRepository extends JpaRepository<Team, Long> {
  @EntityGraph(attributePaths = "members")
  List<Team> findAll();
}
###########################결과

OneToMany------------------------
Hibernate: 
    select
        team0_.team_id as team_id1_7_0_,
        members1_.member_id as member_i1_0_1_,
        team0_.name as name2_7_0_,
        team0_.nick as nick3_7_0_,
        members1_.name as name2_0_1_,
        members1_.team_id as team_id3_0_1_,
        members1_.team_id as team_id3_0_0__,
        members1_.member_id as member_i1_0_0__ 
    from
        team team0_ 
    left outer join
        member members1_ 
            on team0_.team_id=members1_.team_id
ManyToOne------------------------
Hibernate: 
    select
        member0_.member_id as member_i1_0_0_,
        team1_.team_id as team_id1_7_1_,
        member0_.name as name2_0_0_,
        member0_.team_id as team_id3_0_0_,
        team1_.name as name2_7_1_,
        team1_.nick as nick3_7_1_ 
    from
        member member0_ 
    left outer join
        team team1_ 
            on member0_.team_id=team1_.team_id
-----------------------------------

---

3. Fetch(FetchMode.SUBSELECT) 

내가 이것도 테스트 해서 스크린샷 찍으려 했는데 시간이 너무 없어서 더 좋은 자료를 공유한다..

이 글을 참조해서 더 많은 해결방안과 자세한 설명을 참조하는게 서로에게 윈윈이다.

 

 

---

---

Pageable

사실 여기부터가 이글을 쓰고자하는 취지다.

나는 OneToMany 는 관심이 없었다. 

ManyToOne으로 Pageable 객체를 인자값으로 받고 싶었다. 그리고 거기에 projection을 끼얹어서 결과를 보고싶었다고 ㅅㅂ

그런데 딱 나같은 상황의 예제가 없기도 했고.. 이참에 그냥 공부할 겸 테스트를 이것저것 해봤다 

---

1. OneToMany는 @Query() 로 fetch join하게 됐을 때. Pageable을 가질 수 없다.

@RepositoryRestResource 
public interface TeamRepository extends JpaRepository<Team, Long> {
  @Query("select team from TEAM team join fetch team.members")
  Page<Team> findAll(Pageable pageable);
}

----------------
query specified join fetching, but the owner of the fetched association was not present in the select list
에러가 발생한다.
----------------
Caused by: org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list [FromElement{explicit,not a collection join,fetch join,fetch non-lazy properties,classAlias=null,role=smp.test.Team.members,tableName=member,tableAlias=members1_,origin=team team0_,columns={team0_.team_id,className=smp.test.Member}}] [select count(team) from smp.test.Team team join fetch team.members]
	at org.hibernate.QueryException.generateQueryException(QueryException.java:120)
QueryException.java:120
	at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)

찾아보면 패치 조인은 Entity 객체의 EntityGraph 를 참조할 때 사용하기 때문에 오류가 발생한다고 하는데

일단 알겠고 다음 결과를 보자 

---

2. @EntityGraph를 사용하면 그래도 일단 결과는 나온다.

@RepositoryRestResource 
public interface TeamRepository extends JpaRepository<Team, Long> {
  @EntityGraph(attributePaths = "members")
  Page<Team> findAll(Pageable pageable);
}

------------------------
[2022-10-27 23:07:53.872] [WARN] o.h.h.i.ast.QueryTranslatorImpl:389 : HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!
Hibernate: 
    select
        team0_.team_id as team_id1_7_0_,
        members1_.member_id as member_i1_0_1_,
        team0_.name as name2_7_0_,
        team0_.nick as nick3_7_0_,
        members1_.name as name2_0_1_,
        members1_.team_id as team_id3_0_1_,
        members1_.team_id as team_id3_0_0__,
        members1_.member_id as member_i1_0_0__ 
    from
        team team0_ 
    left outer join
        member members1_ 
            on team0_.team_id=members1_.team_id
------------------------

물론 warning 메시지 처럼 패치 조인을 했기 때문에 모든 row를 조회 한뒤에 가져온 결과이고 Limit 이 없는걸로 보아 pageable 객체를 사용할 순 없다. < 이것도 해결방안은 있는듯하지만 나는 안쓸껀데 알빠노?

---

근데 중요한건 사실 이게 뭔 말인지 잘 모르겠다.

나는 @Query() 를 사용해서 가져오는 결과나, EntityGraph를 사용한 결과나 같은줄 알았다.

인터넷에서 아무리 찾아봐도 저 둘을 "패치 조인이다" 라고만 한단말이지? 

 

이 내용이 어떤건지 아는 분 제발 댓글 부탁드린다..

어찌됐건 둘다 pageable 을 사용할 수 없는건 매한가지다. 둘다 쓰지말자.

그럼 인철님 블로그에서 찾아본 SUBSELECT 는 어떨까?

---

3. @Fetch(FetchMode.SUBSELECT)

이건 조금 다른게 위에서 진행한 패치 조인'들' @Query() , @EntityGraph는 LAZY 로 둬도 알아서 EAGER 로 가져오는데. @Fetch(FetchMode.SUBSELECT) 이건 LAZY로 둬도 참조시점에 SubSelect 가 발생한다.

@Fetch(FetchMode.SUBSELECT)
@OneToMany(mappedBy = "team", fetch = FetchType.EAGER)
private List<Member> members;

  
@RepositoryRestResource 
public interface TeamRepository extends JpaRepository<Team, Long> {
  Page<Team> findAll(Pageable pageable);
}

결과
------------------------
Hibernate: 
    select
        team0_.team_id as team_id1_7_,
        team0_.name as name2_7_,
        team0_.nick as nick3_7_ 
    from
        team team0_ limit ?
Hibernate: 
    select
        members0_.team_id as team_id3_0_1_,
        members0_.member_id as member_i1_0_1_,
        members0_.member_id as member_i1_0_0_,
        members0_.name as name2_0_0_,
        members0_.team_id as team_id3_0_0_ 
    from
        member members0_ 
    where
        members0_.team_id in (
            select
                team0_.team_id 
            from
                team team0_
        )
------------------------

 

이 방법이 제일 깔끔한거 아닌가싶다.

---

4. 그럼 @ManyToOne은?

 

4-1. @Query() 으로 하면 동일하게 오류 뱉는다.

4-2.@EntityGraph는?? 잘 된다.  뻐어어엌예아ㅏㅏ 

@RepositoryRestResource 
public interface MemberRepository extends JpaRepository<Member, Long> {
  @EntityGraph(attributePaths = "team")
  Page<Member> findAll(Pageable pageable);
}

결과
------------------------
Hibernate: 
    select
        member0_.member_id as member_i1_0_0_,
        team1_.team_id as team_id1_7_1_,
        member0_.name as name2_0_0_,
        member0_.team_id as team_id3_0_0_,
        team1_.name as name2_7_1_,
        team1_.nick as nick3_7_1_ 
    from
        member member0_ 
    left outer join
        team team1_ 
            on member0_.team_id=team1_.team_id limit ?
Hibernate: 
    select
        count(member0_.member_id) as col_0_0_ 
    from
        member member0_
------------------------

대체 왜..? 왜 되는거지? 사실 안되는건가? 이미 메모리에 불러와놓고 warning만 안뜨는건가? 나중에 원인 분석하면 내용 추가해라 미친글쓴이 색기야

4-3. 그런데 이제 @Fetch(SUBSELECT)는 안된다.

Use of FetchMode.SUBSELECT not allowed on ToOne associations
네... 그렇다고 합니다.. *ToOne에서는 쓰지맙시다.

---

Projection

 

그래 .. 이게 가장 하고 싶었다. 내가 원하는 데이터를 뿌려주고 싶었어. + 페이징 해서 

N+1 해결 + Paging + Projection 으로 원하는 데이터만 가져오기.. 이게 궁극적으로 하고 싶었다..

 

근데 너무 졸리니까 다음 게시물에 마저 작성하겠다.. 이제 자야겠다