상품 조회 중에 만난 N+1문제

 

 

 

 

 

N+1문제란?

JPA로 개발을 하면 자주 접하는 문제이다. 하위 엔티디들을 첫번쨰 조회할떄는 코빼기도 안보이다가 Lazy로 되어 있는  놈들은 필요할때가 되면 그제서야 조회를 시작한다.

시작할때 한방에 Eager로 조회를 하면 sql문을 많이 날릴필요가 없다.

저번 포스팅에서 작성한 페이징 처리를 위해서 작업을 마치고 조회 쿼리문을 세어보니 총 12번의 쿼리문이 나갔다.

Page<Product> findProductsByProductIdIsLessThanOrderByCreatedAtDesc(Long productId, Pageable pageRequest);

결과

2022-01-18 21:55:57.772 DEBUG 7944 --- [nio-8080-exec-2] org.hibernate.SQL                        : 
    select                                                1번
        product0_.product_id as product_1_1_,
        product0_.created_at as created_2_1_,
        product0_.updated_at as updated_3_1_,
        product0_.account_id as account10_1_,
        product0_.buyer_account_id as buyer_a11_1_,
        product0_.category as category4_1_,
        product0_.product_detail as product_5_1_,
        product0_.product_name as product_6_1_,
        product0_.product_price as product_7_1_,
        product0_.product_view_cnt as product_8_1_,
        product0_.seller_account_id as seller_12_1_,
        product0_.status as status9_1_ 
    from
        product product0_ 
    where
        product0_.product_id<? 
    order by
        product0_.created_at desc limit ?
Hibernate: 
    select                                               2번
        product0_.product_id as product_1_1_,
        product0_.created_at as created_2_1_,
        product0_.updated_at as updated_3_1_,
        product0_.account_id as account10_1_,
        product0_.buyer_account_id as buyer_a11_1_,
        product0_.category as category4_1_,
        product0_.product_detail as product_5_1_,
        product0_.product_name as product_6_1_,
        product0_.product_price as product_7_1_,
        product0_.product_view_cnt as product_8_1_,
        product0_.seller_account_id as seller_12_1_,
        product0_.status as status9_1_ 
    from
        product product0_ 
    where
        product0_.product_id<? 
    order by
        product0_.created_at desc limit ?
2022-01-18 21:55:57.772 TRACE 7944 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [5]
2022-01-18 21:55:57.772 DEBUG 7944 --- [nio-8080-exec-2] org.hibernate.SQL                        : 
    select                                               3번
        account0_.account_id as account_1_0_0_,
        account0_.created_at as created_2_0_0_,
        account0_.updated_at as updated_3_0_0_,
        account0_.access_token as access_t4_0_0_,
        account0_.account_email as account_5_0_0_,
        account0_.profile_image_url as profile_6_0_0_,
        account0_.profile_nickname as profile_7_0_0_,
        account0_.refresh_token as refresh_8_0_0_,
        account0_.user_name as user_nam9_0_0_ 
    from
        account account0_ 
    where
        account0_.account_id=?
Hibernate: 
    select                                              4번
        account0_.account_id as account_1_0_0_,
        account0_.created_at as created_2_0_0_,
        account0_.updated_at as updated_3_0_0_,
        account0_.access_token as access_t4_0_0_,
        account0_.account_email as account_5_0_0_,
        account0_.profile_image_url as profile_6_0_0_,
        account0_.profile_nickname as profile_7_0_0_,
        account0_.refresh_token as refresh_8_0_0_,
        account0_.user_name as user_nam9_0_0_ 
    from
        account account0_ 
    where
        account0_.account_id=?
2022-01-18 21:55:57.773 TRACE 7944 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [1]
2022-01-18 21:55:57.773 DEBUG 7944 --- [nio-8080-exec-2] org.hibernate.SQL                        : 
    select                                            5번
        count(product0_.product_id) as col_0_0_ 
    from
        product product0_ 
    where
        product0_.product_id<?
Hibernate: 
    select                                            6번
        count(product0_.product_id) as col_0_0_ 
    from
        product product0_ 
    where
        product0_.product_id<?
2022-01-18 21:55:57.773 TRACE 7944 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [5]
2022-01-18 21:55:57.774 DEBUG 7944 --- [nio-8080-exec-2] org.hibernate.SQL                        : 
    select                                            7번
        urllist0_.product_id as product_1_2_0_,
        urllist0_.url as url2_2_0_ 
    from
        product_url_list urllist0_ 
    where
        urllist0_.product_id=?
Hibernate: 
    select                                            8번
        urllist0_.product_id as product_1_2_0_,
        urllist0_.url as url2_2_0_ 
    from
        product_url_list urllist0_ 
    where
        urllist0_.product_id=?
2022-01-18 21:55:57.774 TRACE 7944 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [4]
2022-01-18 21:55:57.775 DEBUG 7944 --- [nio-8080-exec-2] org.hibernate.SQL                        : 
    select                                             9번
        urllist0_.product_id as product_1_2_0_,
        urllist0_.url as url2_2_0_ 
    from
        product_url_list urllist0_ 
    where
        urllist0_.product_id=?
Hibernate: 
    select                                              10번
        urllist0_.product_id as product_1_2_0_,
        urllist0_.url as url2_2_0_ 
    from
        product_url_list urllist0_ 
    where
        urllist0_.product_id=?
2022-01-18 21:55:57.775 TRACE 7944 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [3]
2022-01-18 21:55:57.775 DEBUG 7944 --- [nio-8080-exec-2] org.hibernate.SQL                        : 
    select                                              11번
        urllist0_.product_id as product_1_2_0_,
        urllist0_.url as url2_2_0_ 
    from
        product_url_list urllist0_ 
    where
        urllist0_.product_id=?
Hibernate: 
    select                                             12번
        urllist0_.product_id as product_1_2_0_,
        urllist0_.url as url2_2_0_ 
    from
        product_url_list urllist0_ 
    where
        urllist0_.product_id=?
2022-01-18 21:55:57.775 TRACE 7944 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [2]

보시다 시피 엄청 많다 

지금이야 개발환경이고 사용자가 없으니깐 상관이 없지만 이게 조회하는 요청이 만건 십만건 이렇게 들어오면 정말 좋겠다. 엄청한 연산을 해야한다는 것이다. 초기 배포시에는 최대한 저렴하게 하면 ec2의 cpu와 램은 아마 1코어에 1GB정도 일것이다. 그러면 최대한 줄일수 있는건 다줄여야 초기 서비스에 사용자들어게 원활한 서비스를 제공을 할수 있다.

그래서 최적화작업을 하기위해서 @EntityGraph를 사용해 주었다. 

@EntityGraph(attributePaths = {"account","urlList"})
Page<Product> findProductsByProductIdIsLessThanOrderByCreatedAtDesc(Long productId, Pageable pageRequest);

엔티티 그래프로 처음에 lazy들도 다 조회를 하게 만들고 테스트를 실행해보았다.

 

결과

2022-01-18 22:05:00.064  WARN 1008 --- [nio-8080-exec-2] o.h.h.internal.ast.QueryTranslatorImpl   : HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!
2022-01-18 22:05:00.064 DEBUG 1008 --- [nio-8080-exec-2] org.hibernate.SQL                        : 
    select                                               1번
        product0_.product_id as product_1_1_0_,
        account1_.account_id as account_1_0_1_,
        product0_.created_at as created_2_1_0_,
        product0_.updated_at as updated_3_1_0_,
        product0_.account_id as account10_1_0_,
        product0_.buyer_account_id as buyer_a11_1_0_,
        product0_.category as category4_1_0_,
        product0_.product_detail as product_5_1_0_,
        product0_.product_name as product_6_1_0_,
        product0_.product_price as product_7_1_0_,
        product0_.product_view_cnt as product_8_1_0_,
        product0_.seller_account_id as seller_12_1_0_,
        product0_.status as status9_1_0_,
        urllist2_.product_id as product_1_2_0__,
        urllist2_.url as url2_2_0__,
        account1_.created_at as created_2_0_1_,
        account1_.updated_at as updated_3_0_1_,
        account1_.access_token as access_t4_0_1_,
        account1_.account_email as account_5_0_1_,
        account1_.profile_image_url as profile_6_0_1_,
        account1_.profile_nickname as profile_7_0_1_,
        account1_.refresh_token as refresh_8_0_1_,
        account1_.user_name as user_nam9_0_1_,
        urllist2_.product_id as product_1_2_0__,
        urllist2_.url as url2_2_0__ 
    from
        product product0_ 
    left outer join
        account account1_ 
            on product0_.account_id=account1_.account_id 
    left outer join
        product_url_list urllist2_ 
            on product0_.product_id=urllist2_.product_id 
    where
        product0_.product_id<? 
    order by
        product0_.created_at desc
Hibernate: 
    select                                           2번
        product0_.product_id as product_1_1_0_,
        account1_.account_id as account_1_0_1_,
        product0_.created_at as created_2_1_0_,
        product0_.updated_at as updated_3_1_0_,
        product0_.account_id as account10_1_0_,
        product0_.buyer_account_id as buyer_a11_1_0_,
        product0_.category as category4_1_0_,
        product0_.product_detail as product_5_1_0_,
        product0_.product_name as product_6_1_0_,
        product0_.product_price as product_7_1_0_,
        product0_.product_view_cnt as product_8_1_0_,
        product0_.seller_account_id as seller_12_1_0_,
        product0_.status as status9_1_0_,
        urllist2_.product_id as product_1_2_0__,
        urllist2_.url as url2_2_0__,
        account1_.created_at as created_2_0_1_,
        account1_.updated_at as updated_3_0_1_,
        account1_.access_token as access_t4_0_1_,
        account1_.account_email as account_5_0_1_,
        account1_.profile_image_url as profile_6_0_1_,
        account1_.profile_nickname as profile_7_0_1_,
        account1_.refresh_token as refresh_8_0_1_,
        account1_.user_name as user_nam9_0_1_,
        urllist2_.product_id as product_1_2_0__,
        urllist2_.url as url2_2_0__ 
    from
        product product0_ 
    left outer join
        account account1_ 
            on product0_.account_id=account1_.account_id 
    left outer join
        product_url_list urllist2_ 
            on product0_.product_id=urllist2_.product_id 
    where
        product0_.product_id<? 
    order by
        product0_.created_at desc
2022-01-18 22:05:00.065 TRACE 1008 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [5]
2022-01-18 22:05:00.069 DEBUG 1008 --- [nio-8080-exec-2] org.hibernate.SQL                        : 
    select                                           3번
        count(product0_.product_id) as col_0_0_ 
    from
        product product0_ 
    where
        product0_.product_id<?
Hibernate: 
    select                                           4번
        count(product0_.product_id) as col_0_0_ 
    from
        product product0_ 
    where
        product0_.product_id<?
2022-01-18 22:05:00.070 TRACE 1008 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [5]

총 4번의 쿼리문이 나가는것을 확인 할수 있었다. 

이런식으로 12번의 쿼리문을 4번의 쿼리문으로 줄이는 작업에 성공을 하였다. 

 

Lazy를 한번에 조회하는 방법이 한가지 더있어서 소개를 하겠습니다.

바로 Fetch Join 입니다. 

 

@Query("select m from Member m join fetch m.team ")

List<Member> findAllMembers();

이런식으로 @Query어노테이션을 쓰고 sql문을 적은다음 join뒤에 fetch를 적용해 줍니다.

 

이로써 오늘은 JPA를 사용하면서 만나는 N+1문제에 대해서 알아보았습니다. 

감사합니다.