본문 바로가기

Back-End/JPA

N+1 문제 해결하기 ( fetch join, @EntityGraph )

댓글과 대댓글을 조회하는 API를 개발하였는데, 검색이 비이상적으로 오래 걸려 Query문을 다시 봤다.

 

1. 기존 Query
    @Query(value = "SELECT c FROM Comment c WHERE c.goodsId = ?1 AND c.id = c.parentIdx "
            + "AND c.memberName not in ?2 ORDER BY c.regDate DESC, c.commentRate DESC ")
    Page<Comment> findCommentsByGoodsIdExcludeAdminComment(Long goodsId,
            Collection<String> excludeMembers, Pageable pageable);
Hibernate: select comment0_.good_comment_idx as good_com1_17_, comment0_.comment_img as comment_2_17_, comment0_.comment_msg as comment_3_17_, comment0_.comment_rate as comment_4_17_, comment0_.good_idx as good_idx5_17_, comment0_.mem_idx as mem_idx6_17_, comment0_.mem_name as mem_name7_17_, comment0_.modify_date as modify_d8_17_, comment0_.parent_idx as parent_i9_17_, comment0_.reg_date as reg_dat10_17_, comment0_.user_img as user_im11_17_ from good_comment comment0_ where comment0_.good_idx=? and comment0_.good_comment_idx=comment0_.parent_idx and (comment0_.mem_name not in  (? , ?)) order by comment0_.reg_date DESC, comment0_.comment_rate DESC limit ?
Hibernate: select replies0_.parent_idx as parent_i9_17_0_, replies0_.good_comment_idx as good_com1_17_0_, replies0_.good_comment_idx as good_com1_17_1_, replies0_.comment_img as comment_2_17_1_, replies0_.comment_msg as comment_3_17_1_, replies0_.comment_rate as comment_4_17_1_, replies0_.good_idx as good_idx5_17_1_, replies0_.mem_idx as mem_idx6_17_1_, replies0_.mem_name as mem_name7_17_1_, replies0_.modify_date as modify_d8_17_1_, replies0_.parent_idx as parent_i9_17_1_, replies0_.reg_date as reg_dat10_17_1_, replies0_.user_img as user_im11_17_1_ from good_comment replies0_ where ( replies0_.good_comment_idx != replies0_.parent_idx) and replies0_.parent_idx=?
Hibernate: select replies0_.parent_idx as parent_i9_17_0_, replies0_.good_comment_idx as good_com1_17_0_, replies0_.good_comment_idx as good_com1_17_1_, replies0_.comment_img as comment_2_17_1_, replies0_.comment_msg as comment_3_17_1_, replies0_.comment_rate as comment_4_17_1_, replies0_.good_idx as good_idx5_17_1_, replies0_.mem_idx as mem_idx6_17_1_, replies0_.mem_name as mem_name7_17_1_, replies0_.modify_date as modify_d8_17_1_, replies0_.parent_idx as parent_i9_17_1_, replies0_.reg_date as reg_dat10_17_1_, replies0_.user_img as user_im11_17_1_ from good_comment replies0_ where ( replies0_.good_comment_idx != replies0_.parent_idx) and replies0_.parent_idx=?
2. INNER JOIN문 추가 - 정답 X
    @Query(value =
            "SELECT c FROM Comment c JOIN  c.replies WHERE c.goodsId = ?1 AND c.id = c.parentIdx "
                    + "AND c.memberName not in ?2 ORDER BY c.regDate DESC, c.commentRate DESC ")
    Page<Comment> findCommentsByGoodsIdExcludeAdminComment(Long goodsId,
            Collection<String> excludeMembers, Pageable pageable);
Hibernate: select comment0_.good_comment_idx as good_com1_17_, comment0_.comment_img as comment_2_17_, comment0_.comment_msg as comment_3_17_, comment0_.comment_rate as comment_4_17_, comment0_.good_idx as good_idx5_17_, comment0_.mem_idx as mem_idx6_17_, comment0_.mem_name as mem_name7_17_, comment0_.modify_date as modify_d8_17_, comment0_.parent_idx as parent_i9_17_, comment0_.reg_date as reg_dat10_17_, comment0_.user_img as user_im11_17_ from good_comment comment0_ inner join good_comment replies1_ on comment0_.good_comment_idx=replies1_.parent_idx and ( replies1_.good_comment_idx != replies1_.parent_idx) where comment0_.good_idx=? and comment0_.good_comment_idx=comment0_.parent_idx and (comment0_.mem_name not in  (? , ?)) order by comment0_.reg_date DESC, comment0_.comment_rate DESC limit ?
Hibernate: select replies0_.parent_idx as parent_i9_17_0_, replies0_.good_comment_idx as good_com1_17_0_, replies0_.good_comment_idx as good_com1_17_1_, replies0_.comment_img as comment_2_17_1_, replies0_.comment_msg as comment_3_17_1_, replies0_.comment_rate as comment_4_17_1_, replies0_.good_idx as good_idx5_17_1_, replies0_.mem_idx as mem_idx6_17_1_, replies0_.mem_name as mem_name7_17_1_, replies0_.modify_date as modify_d8_17_1_, replies0_.parent_idx as parent_i9_17_1_, replies0_.reg_date as reg_dat10_17_1_, replies0_.user_img as user_im11_17_1_ from good_comment replies0_ where ( replies0_.good_comment_idx != replies0_.parent_idx) and replies0_.parent_idx=?
Hibernate: select replies0_.parent_idx as parent_i9_17_0_, replies0_.good_comment_idx as good_com1_17_0_, replies0_.good_comment_idx as good_com1_17_1_, replies0_.comment_img as comment_2_17_1_, replies0_.comment_msg as comment_3_17_1_, replies0_.comment_rate as comment_4_17_1_, replies0_.good_idx as good_idx5_17_1_, replies0_.mem_idx as mem_idx6_17_1_, replies0_.mem_name as mem_name7_17_1_, replies0_.modify_date as modify_d8_17_1_, replies0_.parent_idx as parent_i9_17_1_, replies0_.reg_date as reg_dat10_17_1_, replies0_.user_img as user_im11_17_1_ from good_comment replies0_ where ( replies0_.good_comment_idx != replies0_.parent_idx) and replies0_.parent_idx=?

1번과의 차이는 아래 조건이다.

good_comment comment0_  inner join good_comment replies1_ on comment0_.good_comment_idx=replies1_.parent_idx and ( replies1_.good_comment_idx != replies1_.parent_idx) 
  • 빨라진 이유 :  inner join으로 인해 good_comment 검색결과가 1565개-> 575개로 줄었고, 그로 인해 lazy로 호출하던 reply 검색이 1000개가 줄어든 것이다.
  • 정답이냐고? 이건 잘못된 방법이다. inner join으로 하였기 때문에 대댓글이 있는 good_comment만 검색했기 때문에 빨라진 것이다. 즉, inner join을 사용하면 안된다.
3. fetch join ( 패치 조인 ) 사용하기

가장 일반적인 방법은 패치 조인을 사용하는 것이다. 패치 조인은 SQL 조인을 사용해서 연관된 엔티티를 함께 조회하므로 N+1 문제가 발생하지 않는다.

SELECT m FROM Member m join fetch m.orders

하지만 나는 Pageable도 사용해야 하는데, fetch join은 pagable과 함께 사용할 수 없다.

 

그리고 Fetch Join 사용 시 주의할 점도 있다.

@Entity
@Table(name = "Orders")
public class OrderEntity {
    @OneToMany
    @JoinColumn(name = "order_id")
    Set<OrderItemEntity> orderItems;

}
  • MultipleBagFetchException 발생
  • Bag : Hibernate에서 중복 요소를 허용하는 비순차 컬렉션
  • 2개 이상의 @OneToMany 컬렉션(Bag)에 대한 EAGER fetch 시 그 결과 만들어지는 카테시안 곱에서 어느행이 유효한 중복을 포함하고 어느 행이 그렇지 않은지 판단할 수 없어 Bag 컬렉션으로 변환될 수 없기 때문에 예외가 발생한다.
  • List를 Set으로 변경하면 중복을 비허용
  • 입력 순서가 필요하다면 LinkedHashSet 등의 다른 자료구조 이용

 

4. @EntityGraph 사용하기

Spring JPA 2.1부터 지원하기 시작한 annotation이다. 엔티티의 연관된 관계를 로딩할때 성능을 높여주기 위해 나온 것이다. 즉, 하나의 select query로 조회가 되도록 지원한다.

 

    @EntityGraph(attributePaths = "replies")
    @Query(value = "SELECT c FROM Comment c WHERE c.goodsId = ?1 AND c.id = c.parentIdx "
            + "AND c.memberName not in ?2 ORDER BY c.regDate DESC, c.commentRate DESC")
    Page<Comment> findCommentsByGoodsIdExcludeAdminComment(Long goodsId,
            Collection<String> excludeMembers, Pageable pageable);

아래와 같이 쿼리문이 생성된 걸 확인할 수 있고

Hibernate: select comment0_.good_comment_idx as good_com1_17_0_, replies1_.good_comment_idx as good_com1_17_1_, comment0_.comment_img as comment_2_17_0_, comment0_.comment_msg as comment_3_17_0_, comment0_.comment_rate as comment_4_17_0_, comment0_.good_idx as good_idx5_17_0_, comment0_.mem_idx as mem_idx6_17_0_, comment0_.mem_name as mem_name7_17_0_, comment0_.modify_date as modify_d8_17_0_, comment0_.parent_idx as parent_i9_17_0_, comment0_.reg_date as reg_dat10_17_0_, comment0_.user_img as user_im11_17_0_, replies1_.comment_img as comment_2_17_1_, replies1_.comment_msg as comment_3_17_1_, replies1_.comment_rate as comment_4_17_1_, replies1_.good_idx as good_idx5_17_1_, replies1_.mem_idx as mem_idx6_17_1_, replies1_.mem_name as mem_name7_17_1_, replies1_.modify_date as modify_d8_17_1_, replies1_.parent_idx as parent_i9_17_1_, replies1_.reg_date as reg_dat10_17_1_, replies1_.user_img as user_im11_17_1_, replies1_.parent_idx as parent_i9_17_0__, replies1_.good_comment_idx as good_com1_17_0__ from good_comment comment0_ left outer join good_comment replies1_ on comment0_.good_comment_idx=replies1_.parent_idx and ( replies1_.good_comment_idx != replies1_.parent_idx) where comment0_.good_idx=? and comment0_.good_comment_idx=comment0_.parent_idx and (comment0_.mem_name not in  (? , ?)) order by comment0_.reg_date DESC, comment0_.comment_rate DESC

 

차이는 아래의 조건 뿐이다.

from good_comment comment0_ left outer join good_comment

 

참고

https://thoughts-on-java.org/jpa-21-entity-graph-part-2-define/