[JPA] N+1 문제? 이렇게 많은 쿼리문이 수행될 줄 몰랐지

N+1 문제에 대해 알지 못했을 때, 한 번의 작업으로 필요 이상의 쿼리문을 작성하는 문제에 대해 인지하지 못하고 있었다. JPA를 다루며 수많은 문제에 직면했고, 조금씩 에러를 해결해 나가면서 N+1 문제를 알게 되자 이번에 처음으로 콘솔 창이 보여준 수많은 쿼리문을 보게 됐다. 

 List<PlanVO> p=new ArrayList();
 p=this.planService.allUserPlan();

@Override
   public List<PlanVO> allUserPlan() {
      return this.planRepo.findAll();
   }

 

DB에 저장된 모든 계획을 가져오기 위해 findAll() 을 이용했다. 그러자.

Hibernate: select planvo0_.plan_no as plan_no1_10_, planvo0_.arrival_date as arrival_date2_10_, planvo0_.departure_date as departure_date3_10_, planvo0_.member_id as member_id5_10_, planvo0_.plan_date as plan_date4_10_ from plan planvo0_
Hibernate: select membervo0_.member_id as member_id1_6_0_, membervo0_.chat_no as chat_no17_6_0_, membervo0_.mail_domain as mail_domain2_6_0_, membervo0_.mail_id as mail_id3_6_0_, membervo0_.member_join_date as member_join_date4_6_0_, membervo0_.member_name as member_name5_6_0_, membervo0_.member_phone01 as member_phone6_6_0_, membervo0_.member_phone02 as member_phone7_6_0_, membervo0_.member_phone03 as member_phone8_6_0_, membervo0_.member_pwd as member_pwd9_6_0_, membervo0_.resident_id as resident_id10_6_0_, membervo0_.resident_id2 as resident_id11_6_0_, membervo0_.role as role12_6_0_, membervo0_.sample6_address as sample13_6_0_, membervo0_.sample6_detail_address as sample14_6_0_, membervo0_.sample6_extra_address as sample15_6_0_, membervo0_.sample6_postcode as sample16_6_0_ from member membervo0_ where membervo0_.member_id=?
Hibernate: select cities0_.plan_no as plan_no1_11_0_, cities0_.city_code as city_code2_11_0_, cityvo1_.city_code as city_code1_1_1_, cityvo1_.capital_city as capital_city2_1_1_, cityvo1_.city_name as city_name3_1_1_, cityvo1_.latitude as latitude4_1_1_, cityvo1_.longitude as longitude5_1_1_, cityvo1_.national_code as national_code6_1_1_, nationalvo2_.national_code as national_code1_8_2_, nationalvo2_.flag_path as flag_path2_8_2_, nationalvo2_.national_name as national_name3_8_2_, nationalvo2_.time_difference as time_difference4_8_2_ from plan_city cities0_ inner join city cityvo1_ on cities0_.city_code=cityvo1_.city_code left outer join national nationalvo2_ on cityvo1_.national_code=nationalvo2_.national_code where cities0_.plan_no=?
Hibernate: select cities0_.plan_no as plan_no1_11_0_, cities0_.city_code as city_code2_11_0_, cityvo1_.city_code as city_code1_1_1_, cityvo1_.capital_city as capital_city2_1_1_, cityvo1_.city_name as city_name3_1_1_, cityvo1_.latitude as latitude4_1_1_, cityvo1_.longitude as longitude5_1_1_, cityvo1_.national_code as national_code6_1_1_, nationalvo2_.national_code as national_code1_8_2_, nationalvo2_.flag_path as flag_path2_8_2_, nationalvo2_.national_name as national_name3_8_2_, nationalvo2_.time_difference as time_difference4_8_2_ from plan_city cities0_ inner join city cityvo1_ on cities0_.city_code=cityvo1_.city_code left outer join national nationalvo2_ on cityvo1_.national_code=nationalvo2_.national_code where cities0_.plan_no=?
Hibernate: select cities0_.plan_no as plan_no1_11_0_, cities0_.city_code as city_code2_11_0_, cityvo1_.city_code as city_code1_1_1_, cityvo1_.capital_city as capital_city2_1_1_, cityvo1_.city_name as city_name3_1_1_, cityvo1_.latitude as latitude4_1_1_, cityvo1_.longitude as longitude5_1_1_, cityvo1_.national_code as national_code6_1_1_, nationalvo2_.national_code as national_code1_8_2_, nationalvo2_.flag_path as flag_path2_8_2_, nationalvo2_.national_name as national_name3_8_2_, nationalvo2_.time_difference as time_difference4_8_2_ from plan_city cities0_ inner join city cityvo1_ on cities0_.city_code=cityvo1_.city_code left outer join national nationalvo2_ on cityvo1_.national_code=nationalvo2_.national_code where cities0_.plan_no=?
Hibernate: select cities0_.plan_no as plan_no1_11_0_, cities0_.city_code as city_code2_11_0_, cityvo1_.city_code as city_code1_1_1_, cityvo1_.capital_city as capital_city2_1_1_, cityvo1_.city_name as city_name3_1_1_, cityvo1_.latitude as latitude4_1_1_, cityvo1_.longitude as longitude5_1_1_, cityvo1_.national_code as national_code6_1_1_, nationalvo2_.national_code as national_code1_8_2_, nationalvo2_.flag_path as flag_path2_8_2_, nationalvo2_.national_name as national_name3_8_2_, nationalvo2_.time_difference as time_difference4_8_2_ from plan_city cities0_ inner join city cityvo1_ on cities0_.city_code=cityvo1_.city_code left outer join national nationalvo2_ on cityvo1_.national_code=nationalvo2_.national_code where cities0_.plan_no=?
Hibernate: select cities0_.plan_no as plan_no1_11_0_, cities0_.city_code as city_code2_11_0_, cityvo1_.city_code as city_code1_1_1_, cityvo1_.capital_city as capital_city2_1_1_, cityvo1_.city_name as city_name3_1_1_, cityvo1_.latitude as latitude4_1_1_, cityvo1_.longitude as longitude5_1_1_, cityvo1_.national_code as national_code6_1_1_, nationalvo2_.national_code as national_code1_8_2_, nationalvo2_.flag_path as flag_path2_8_2_, nationalvo2_.national_name as national_name3_8_2_, nationalvo2_.time_difference as time_difference4_8_2_ from plan_city cities0_ inner join city cityvo1_ on cities0_.city_code=cityvo1_.city_code left outer join national nationalvo2_ on cityvo1_.national_code=nationalvo2_.national_code where cities0_.plan_no=?
Hibernate: select cities0_.plan_no as plan_no1_11_0_, cities0_.city_code as city_code2_11_0_, cityvo1_.city_code as city_code1_1_1_, cityvo1_.capital_city as capital_city2_1_1_, cityvo1_.city_name as city_name3_1_1_, cityvo1_.latitude as latitude4_1_1_, cityvo1_.longitude as longitude5_1_1_, cityvo1_.national_code as national_code6_1_1_, nationalvo2_.national_code as national_code1_8_2_, nationalvo2_.flag_path as flag_path2_8_2_, nationalvo2_.national_name as national_name3_8_2_, nationalvo2_.time_difference as time_difference4_8_2_ from plan_city cities0_ inner join city cityvo1_ on cities0_.city_code=cityvo1_.city_code left outer join national nationalvo2_ on cityvo1_.national_code=nationalvo2_.national_code where cities0_.plan_no=?
Hibernate: select cities0_.plan_no as plan_no1_11_0_, cities0_.city_code as city_code2_11_0_, cityvo1_.city_code as city_code1_1_1_, cityvo1_.capital_city as capital_city2_1_1_, cityvo1_.city_name as city_name3_1_1_, cityvo1_.latitude as latitude4_1_1_, cityvo1_.longitude as longitude5_1_1_, cityvo1_.national_code as national_code6_1_1_, nationalvo2_.national_code as national_code1_8_2_, nationalvo2_.flag_path as flag_path2_8_2_, nationalvo2_.national_name as national_name3_8_2_, nationalvo2_.time_difference as time_difference4_8_2_ from plan_city cities0_ inner join city cityvo1_ on cities0_.city_code=cityvo1_.city_code left outer join national nationalvo2_ on cityvo1_.national_code=nationalvo2_.national_code where cities0_.plan_no=?
Hibernate: select cities0_.plan_no as plan_no1_11_0_, cities0_.city_code as city_code2_11_0_, cityvo1_.city_code as city_code1_1_1_, cityvo1_.capital_city as capital_city2_1_1_, cityvo1_.city_name as city_name3_1_1_, cityvo1_.latitude as latitude4_1_1_, cityvo1_.longitude as longitude5_1_1_, cityvo1_.national_code as national_code6_1_1_, nationalvo2_.national_code as national_code1_8_2_, nationalvo2_.flag_path as flag_path2_8_2_, nationalvo2_.national_name as national_name3_8_2_, nationalvo2_.time_difference as time_difference4_8_2_ from plan_city cities0_ inner join city cityvo1_ on cities0_.city_code=cityvo1_.city_code left outer join national nationalvo2_ on cityvo1_.national_code=nationalvo2_.national_code where cities0_.plan_no=?
Hibernate: select cities0_.plan_no as plan_no1_11_0_, cities0_.city_code as city_code2_11_0_, cityvo1_.city_code as city_code1_1_1_, cityvo1_.capital_city as capital_city2_1_1_, cityvo1_.city_name as city_name3_1_1_, cityvo1_.latitude as latitude4_1_1_, cityvo1_.longitude as longitude5_1_1_, cityvo1_.national_code as national_code6_1_1_, nationalvo2_.national_code as national_code1_8_2_, nationalvo2_.flag_path as flag_path2_8_2_, nationalvo2_.national_name as national_name3_8_2_, nationalvo2_.time_difference as time_difference4_8_2_ from plan_city cities0_ inner join city cityvo1_ on cities0_.city_code=cityvo1_.city_code left outer join national nationalvo2_ on cityvo1_.national_code=nationalvo2_.national_code where cities0_.plan_no=?
Hibernate: select cities0_.plan_no as plan_no1_11_0_, cities0_.city_code as city_code2_11_0_, cityvo1_.city_code as city_code1_1_1_, cityvo1_.capital_city as capital_city2_1_1_, cityvo1_.city_name as city_name3_1_1_, cityvo1_.latitude as latitude4_1_1_, cityvo1_.longitude as longitude5_1_1_, cityvo1_.national_code as national_code6_1_1_, nationalvo2_.national_code as national_code1_8_2_, nationalvo2_.flag_path as flag_path2_8_2_, nationalvo2_.national_name as national_name3_8_2_, nationalvo2_.time_difference as time_difference4_8_2_ from plan_city cities0_ inner join city cityvo1_ on cities0_.city_code=cityvo1_.city_code left outer join national nationalvo2_ on cityvo1_.national_code=nationalvo2_.national_code where cities0_.plan_no=?
Hibernate: select cities0_.plan_no as plan_no1_11_0_, cities0_.city_code as city_code2_11_0_, cityvo1_.city_code as city_code1_1_1_, cityvo1_.capital_city as capital_city2_1_1_, cityvo1_.city_name as city_name3_1_1_, cityvo1_.latitude as latitude4_1_1_, cityvo1_.longitude as longitude5_1_1_, cityvo1_.national_code as national_code6_1_1_, nationalvo2_.national_code as national_code1_8_2_, nationalvo2_.flag_path as flag_path2_8_2_, nationalvo2_.national_name as national_name3_8_2_, nationalvo2_.time_difference as time_difference4_8_2_ from plan_city cities0_ inner join city cityvo1_ on cities0_.city_code=cityvo1_.city_code left outer join national nationalvo2_ on cityvo1_.national_code=nationalvo2_.national_code where cities0_.plan_no=?

 

황당했다. 너무나 많은 쿼리문이 작성된 걸 보고 이게 많은 사람들이 성능 저하로 이어진다는 N+1 문제임을 직감했다.

 

@Query("select distinct m from PlanVO m join fetch m.cities")

 

join fetch를 이용해 기존 findAll()을 대신했다. 그러자.

 

Hibernate: select distinct planvo0_.plan_no as plan_no1_10_0_, cityvo2_.city_code as city_code1_1_1_, planvo0_.arrival_date as arrival_date2_10_0_, planvo0_.departure_date as departure_date3_10_0_, planvo0_.member_id as member_id5_10_0_, planvo0_.plan_date as plan_date4_10_0_, cityvo2_.capital_city as capital_city2_1_1_, cityvo2_.city_name as city_name3_1_1_, cityvo2_.latitude as latitude4_1_1_, cityvo2_.longitude as longitude5_1_1_, cityvo2_.national_code as national_code6_1_1_, cities1_.plan_no as plan_no1_11_0__, cities1_.city_code as city_code2_11_0__ from plan planvo0_ inner join plan_city cities1_ on planvo0_.plan_no=cities1_.plan_no inner join city cityvo2_ on cities1_.city_code=cityvo2_.city_code
Hibernate: select membervo0_.member_id as member_id1_6_0_, membervo0_.chat_no as chat_no17_6_0_, membervo0_.mail_domain as mail_domain2_6_0_, membervo0_.mail_id as mail_id3_6_0_, membervo0_.member_join_date as member_join_date4_6_0_, membervo0_.member_name as member_name5_6_0_, membervo0_.member_phone01 as member_phone6_6_0_, membervo0_.member_phone02 as member_phone7_6_0_, membervo0_.member_phone03 as member_phone8_6_0_, membervo0_.member_pwd as member_pwd9_6_0_, membervo0_.resident_id as resident_id10_6_0_, membervo0_.resident_id2 as resident_id11_6_0_, membervo0_.role as role12_6_0_, membervo0_.sample6_address as sample13_6_0_, membervo0_.sample6_detail_address as sample14_6_0_, membervo0_.sample6_extra_address as sample15_6_0_, membervo0_.sample6_postcode as sample16_6_0_ from member membervo0_ where membervo0_.member_id=?
Hibernate: select nationalvo0_.national_code as national_code1_8_0_, nationalvo0_.flag_path as flag_path2_8_0_, nationalvo0_.national_name as national_name3_8_0_, nationalvo0_.time_difference as time_difference4_8_0_ from national nationalvo0_ where nationalvo0_.national_code=?
Hibernate: select nationalvo0_.national_code as national_code1_8_0_, nationalvo0_.flag_path as flag_path2_8_0_, nationalvo0_.national_name as national_name3_8_0_, nationalvo0_.time_difference as time_difference4_8_0_ from national nationalvo0_ where nationalvo0_.national_code=?

 

절반 성공이다. 하지만 아직도 필요 이상의 쿼리문이다.

 

spring.jpa.properties.hibernate.default_batch_fetch_size=100

 

application.properties에 추가한다.

여러 개의 연관된 엔티티를 한 번에 묶어서 로드하는 방법. 데이터베이스 왕복 횟수가 줄어들어 성능이 향상된다. 한 번에 최대 100개의 연관된 엔티티를 로드할 수 있게 했다. 그러자.

 

Hibernate: select distinct planvo0_.plan_no as plan_no1_10_0_, cityvo2_.city_code as city_code1_1_1_, planvo0_.arrival_date as arrival_date2_10_0_, planvo0_.departure_date as departure_date3_10_0_, planvo0_.member_id as member_id5_10_0_, planvo0_.plan_date as plan_date4_10_0_, cityvo2_.capital_city as capital_city2_1_1_, cityvo2_.city_name as city_name3_1_1_, cityvo2_.latitude as latitude4_1_1_, cityvo2_.longitude as longitude5_1_1_, cityvo2_.national_code as national_code6_1_1_, cities1_.plan_no as plan_no1_11_0__, cities1_.city_code as city_code2_11_0__ from plan planvo0_ inner join plan_city cities1_ on planvo0_.plan_no=cities1_.plan_no inner join city cityvo2_ on cities1_.city_code=cityvo2_.city_code
Hibernate: select membervo0_.member_id as member_id1_6_0_, membervo0_.chat_no as chat_no17_6_0_, membervo0_.mail_domain as mail_domain2_6_0_, membervo0_.mail_id as mail_id3_6_0_, membervo0_.member_join_date as member_join_date4_6_0_, membervo0_.member_name as member_name5_6_0_, membervo0_.member_phone01 as member_phone6_6_0_, membervo0_.member_phone02 as member_phone7_6_0_, membervo0_.member_phone03 as member_phone8_6_0_, membervo0_.member_pwd as member_pwd9_6_0_, membervo0_.resident_id as resident_id10_6_0_, membervo0_.resident_id2 as resident_id11_6_0_, membervo0_.role as role12_6_0_, membervo0_.sample6_address as sample13_6_0_, membervo0_.sample6_detail_address as sample14_6_0_, membervo0_.sample6_extra_address as sample15_6_0_, membervo0_.sample6_postcode as sample16_6_0_ from member membervo0_ where membervo0_.member_id=?
Hibernate: select nationalvo0_.national_code as national_code1_8_0_, nationalvo0_.flag_path as flag_path2_8_0_, nationalvo0_.national_name as national_name3_8_0_, nationalvo0_.time_difference as time_difference4_8_0_ from national nationalvo0_ where nationalvo0_.national_code in (?, ?)

 

줄어들긴 했으나 뭔가 아직도 부족하다. 쿼리문을 잘 읽어봐야 한다. 이 경우(내 작업에선)엔 membervo가 필요치 않은 상황이니 아직도 불필요한 쿼리문을 수행중인 것.

 

@ManyToOne(cascade=CascadeType.ALL, fetch=FetchType.LAZY)

 

 

기존에는 @ManyToOne에 fetch에 관한 속성을 명시해주지 않았다.  

기본적으로 @ManyToOne과 @OneToOne 관계는 FetchType.EAGER

기본적으로 @OneToMany와 @ManyToMany 관계는 FetchType.LAZY

따라서 내 경우엔 EAGER 속성이 적용되어 있던 상태... 결국 명시적으로 지정해주자.

Hibernate: select distinct planvo0_.plan_no as plan_no1_10_0_, cityvo2_.city_code as city_code1_1_1_, planvo0_.arrival_date as arrival_date2_10_0_, planvo0_.departure_date as departure_date3_10_0_, planvo0_.member_id as member_id5_10_0_, planvo0_.plan_date as plan_date4_10_0_, cityvo2_.capital_city as capital_city2_1_1_, cityvo2_.city_name as city_name3_1_1_, cityvo2_.latitude as latitude4_1_1_, cityvo2_.longitude as longitude5_1_1_, cityvo2_.national_code as national_code6_1_1_, cities1_.plan_no as plan_no1_11_0__, cities1_.city_code as city_code2_11_0__ from plan planvo0_ inner join plan_city cities1_ on planvo0_.plan_no=cities1_.plan_no inner join city cityvo2_ on cities1_.city_code=cityvo2_.city_code

 

결국 하나의 쿼리문만 실행됐다.

에러를 발견했을 때 우리는 다음 과정으로 넘어가기 위해 에러를 해결하지만, 불필요한 성능 저하를 일으키는 문제에 대해선 인지하지 못할 수 있다. N+1에 대해 들어보지 못했다면 수많은 쿼리문을 보고서도 왜 저렇게 쿼리문이 많은 거지? 라고 궁금해하지 않았을 것이다. 아는 만큼 보인다는 것. 무식한 게 문제인 거겠지만 조금씩 무식에서 앎으로 넘어가는 중인 것이다.