Many To One N+1
문제 상황
Dog 엔티티를 전체 조회하는 api를 호출하면 Dog 엔티티와 ManyToOne으로 매핑된 Party엔티티도 조회되는 N+1이 발생한다.
아래의 실행 로그에 대한 내용은 다음과 같다.
- 한 유저는 반려견을 3마리 등록했다.
- 반려견 한마리는 무조건 Party에 소속되어 있다.
- 유저는 자신이 등록한 반려견들을 조회하려고 한다.
- 반려견 조회시 Party에 대한 조회 쿼리도 생성
Hibernate:
select
dog0_.dog_id as dog_id1_3_,
dog0_.created_at as created_2_3_,
dog0_.deleted_at as deleted_3_3_,
dog0_.last_modified_at as last_mod4_3_,
dog0_.affection_temperature as affectio5_3_,
dog0_.birth as birth6_3_,
dog0_.breed as breed7_3_,
dog0_.dog_size as dog_size8_3_,
dog0_.gender as gender9_3_,
dog0_.isbn as isbn10_3_,
dog0_.name as name11_3_,
dog0_.neutralization as neutral12_3_,
dog0_.group_id as group_i17_3_,
dog0_.profile_img as profile13_3_,
dog0_.socialization_degree as sociali14_3_,
dog0_.socialization_temperature as sociali15_3_,
dog0_.weight as weight16_3_
from
dog dog0_ cross
join
user_party userparty1_ cross
join
users user2_
where
userparty1_.user_id=user2_.user_id
and dog0_.group_id=userparty1_.party_id
and user2_.id=?
Hibernate:
select
party0_.party_id as party_id1_5_0_,
party0_.created_at as created_2_5_0_,
party0_.deleted_at as deleted_3_5_0_,
party0_.last_modified_at as last_mod4_5_0_,
party0_.name as name5_5_0_,
party0_.party_isbn as party_is6_5_0_,
party0_.user_id as user_id7_5_0_,
user1_.user_id as user_id1_15_1_,
user1_.created_at as created_2_15_1_,
user1_.deleted_at as deleted_3_15_1_,
user1_.last_modified_at as last_mod4_15_1_,
user1_.detail_adr as detail_a5_15_1_,
user1_.street_adr as street_a6_15_1_,
user1_.zipcode as zipcode7_15_1_,
user1_.animal_career as animal_c8_15_1_,
user1_.applicant_count as applican9_15_1_,
user1_.applicant_status as applica10_15_1_,
user1_.care_experience as care_ex11_15_1_,
user1_.email as email12_15_1_,
user1_.having_with_pet as having_13_15_1_,
user1_.id as id14_15_1_,
user1_.identification as identif15_15_1_,
user1_.is_smoking as is_smok16_15_1_,
user1_.license_img as license17_15_1_,
user1_.motivate as motivat18_15_1_,
user1_.name as name19_15_1_,
user1_.password as passwor20_15_1_,
user1_.pet_sitter_career as pet_sit21_15_1_,
user1_.phone as phone22_15_1_,
user1_.profile_img as profile23_15_1_,
user1_.role as role24_15_1_
from
party party0_
inner join
users user1_
on party0_.user_id=user1_.user_id
where
party0_.party_id=?
Hibernate:
select
party0_.party_id as party_id1_5_0_,
party0_.created_at as created_2_5_0_,
party0_.deleted_at as deleted_3_5_0_,
party0_.last_modified_at as last_mod4_5_0_,
party0_.name as name5_5_0_,
party0_.party_isbn as party_is6_5_0_,
party0_.user_id as user_id7_5_0_,
user1_.user_id as user_id1_15_1_,
user1_.created_at as created_2_15_1_,
user1_.deleted_at as deleted_3_15_1_,
user1_.last_modified_at as last_mod4_15_1_,
user1_.detail_adr as detail_a5_15_1_,
user1_.street_adr as street_a6_15_1_,
user1_.zipcode as zipcode7_15_1_,
user1_.animal_career as animal_c8_15_1_,
user1_.applicant_count as applican9_15_1_,
user1_.applicant_status as applica10_15_1_,
user1_.care_experience as care_ex11_15_1_,
user1_.email as email12_15_1_,
user1_.having_with_pet as having_13_15_1_,
user1_.id as id14_15_1_,
user1_.identification as identif15_15_1_,
user1_.is_smoking as is_smok16_15_1_,
user1_.license_img as license17_15_1_,
user1_.motivate as motivat18_15_1_,
user1_.name as name19_15_1_,
user1_.password as passwor20_15_1_,
user1_.pet_sitter_career as pet_sit21_15_1_,
user1_.phone as phone22_15_1_,
user1_.profile_img as profile23_15_1_,
user1_.role as role24_15_1_
from
party party0_
inner join
users user1_
on party0_.user_id=user1_.user_id
where
party0_.party_id=?
Hibernate:
select
party0_.party_id as party_id1_5_0_,
party0_.created_at as created_2_5_0_,
party0_.deleted_at as deleted_3_5_0_,
party0_.last_modified_at as last_mod4_5_0_,
party0_.name as name5_5_0_,
party0_.party_isbn as party_is6_5_0_,
party0_.user_id as user_id7_5_0_,
user1_.user_id as user_id1_15_1_,
user1_.created_at as created_2_15_1_,
user1_.deleted_at as deleted_3_15_1_,
user1_.last_modified_at as last_mod4_15_1_,
user1_.detail_adr as detail_a5_15_1_,
user1_.street_adr as street_a6_15_1_,
user1_.zipcode as zipcode7_15_1_,
user1_.animal_career as animal_c8_15_1_,
user1_.applicant_count as applican9_15_1_,
user1_.applicant_status as applica10_15_1_,
user1_.care_experience as care_ex11_15_1_,
user1_.email as email12_15_1_,
user1_.having_with_pet as having_13_15_1_,
user1_.id as id14_15_1_,
user1_.identification as identif15_15_1_,
user1_.is_smoking as is_smok16_15_1_,
user1_.license_img as license17_15_1_,
user1_.motivate as motivat18_15_1_,
user1_.name as name19_15_1_,
user1_.password as passwor20_15_1_,
user1_.pet_sitter_career as pet_sit21_15_1_,
user1_.phone as phone22_15_1_,
user1_.profile_img as profile23_15_1_,
user1_.role as role24_15_1_
from
party party0_
inner join
users user1_
on party0_.user_id=user1_.user_id
where
party0_.party_id=?원인
Party
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Entity
@Getter
public class Party extends BaseEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long partyId;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "userId", nullable = false)
private User user;
private String name;
private String partyIsbn;
@OneToMany(fetch = FetchType.EAGER, mappedBy = "party")
private List<UserParty> userPartyList = new ArrayList<>();
public Party(User user) {
this.user = user;
}
public void updateParty(String name, String isbn) {
this.name = name;
this.partyIsbn = isbn;
}
}Dog 엔티티
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Getter
@Builder
public class Dog extends BaseEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long dogId;
@NotNull
private String name;
@ManyToOne
@JoinColumn(name = "groupId", nullable = false)
private Party party;
@NotNull
private String gender;
@NotNull
private Boolean neutralization;
@NotNull
private LocalDate birth;
@NotNull
private Float weight;
@Lob
private String profile_img;
@NotNull
private String breed;
private String isbn;
@NotNull
private Double socializationTemperature; //펫시터가 작성한 반려견 온도
@NotNull
private Integer socializationDegree; //반려인이 작성한 반려견 사회화 정도 %로 표현
@NotNull
private Double affectionTemperature;
@Enumerated(EnumType.STRING)
private DogSize dogSize;
//.. Dog 엔티티 관련 메소드
}- ManyToOne 기본 FetchType은 EAGER → 관계에 필요한 데이터는 바로 조회
해결 방법
public interface DogRepository extends JpaRepository<Dog, Long>, DogRepositoryCustom {
@Query("select distinct d from Dog d join fetch d.party where d.party.partyId in (:userPartyList) order by d.party.partyId asc ")
List<Dog> findAllUserDogs(@Param("userPartyList") List<Long> userPartyList);
List<Dog> findAllByParty(Party party);
}- Fetch Join을 사용한다.
- FetchType을 Lazy로 변경해도 Dog의 Party에 접근하는 순간 추가 쿼리가 발생한다.
- Lazy는 Party에 대해 가짜 (proxy) 객체를 가지고 있고 데이터가 필요한 시점에 쿼리를 호출하기 때문에 Lazy를 사용한다고 해도 N+1 문제가 없어지는 것이 아니다.
- 따라서 Fetch Join을 사용하여 Dog의 참조 Party 엔티티를 한꺼번에 불러온다.
결과
Hibernate:
select
userparty0_.party_id as col_0_0_
from
user_party userparty0_ cross
join
users user1_
where
userparty0_.user_id=user1_.user_id
and user1_.id=?
2023-05-28 19:16:56.437 INFO 35680 --- [nio-8080-exec-5] c.ajou_nice.with_pet.service.DogService : ==================================== findAllUserDogs Start ==============================================
Hibernate:
select
distinct dog0_.dog_id as dog_id1_3_0_,
party1_.party_id as party_id1_5_1_,
dog0_.created_at as created_2_3_0_,
dog0_.deleted_at as deleted_3_3_0_,
dog0_.last_modified_at as last_mod4_3_0_,
dog0_.affection_temperature as affectio5_3_0_,
dog0_.birth as birth6_3_0_,
dog0_.breed as breed7_3_0_,
dog0_.dog_size as dog_size8_3_0_,
dog0_.gender as gender9_3_0_,
dog0_.isbn as isbn10_3_0_,
dog0_.name as name11_3_0_,
dog0_.neutralization as neutral12_3_0_,
dog0_.group_id as group_i17_3_0_,
dog0_.profile_img as profile13_3_0_,
dog0_.socialization_degree as sociali14_3_0_,
dog0_.socialization_temperature as sociali15_3_0_,
dog0_.weight as weight16_3_0_,
party1_.created_at as created_2_5_1_,
party1_.deleted_at as deleted_3_5_1_,
party1_.last_modified_at as last_mod4_5_1_,
party1_.name as name5_5_1_,
party1_.party_isbn as party_is6_5_1_,
party1_.user_id as user_id7_5_1_
from
dog dog0_
inner join
party party1_
on dog0_.group_id=party1_.party_id
where
dog0_.group_id in (
? , ? , ?
)
order by
dog0_.group_id asc
Hibernate:
select
userpartyl0_.party_id as party_id5_14_0_,
userpartyl0_.user_party_id as user_par1_14_0_,
userpartyl0_.user_party_id as user_par1_14_1_,
userpartyl0_.created_at as created_2_14_1_,
userpartyl0_.deleted_at as deleted_3_14_1_,
userpartyl0_.last_modified_at as last_mod4_14_1_,
userpartyl0_.party_id as party_id5_14_1_,
userpartyl0_.user_id as user_id6_14_1_,
user1_.user_id as user_id1_15_2_,
user1_.created_at as created_2_15_2_,
user1_.deleted_at as deleted_3_15_2_,
user1_.last_modified_at as last_mod4_15_2_,
user1_.detail_adr as detail_a5_15_2_,
user1_.street_adr as street_a6_15_2_,
user1_.zipcode as zipcode7_15_2_,
user1_.animal_career as animal_c8_15_2_,
user1_.applicant_count as applican9_15_2_,
user1_.applicant_status as applica10_15_2_,
user1_.care_experience as care_ex11_15_2_,
user1_.email as email12_15_2_,
user1_.having_with_pet as having_13_15_2_,
user1_.id as id14_15_2_,
user1_.identification as identif15_15_2_,
user1_.is_smoking as is_smok16_15_2_,
user1_.license_img as license17_15_2_,
user1_.motivate as motivat18_15_2_,
user1_.name as name19_15_2_,
user1_.password as passwor20_15_2_,
user1_.pet_sitter_career as pet_sit21_15_2_,
user1_.phone as phone22_15_2_,
user1_.profile_img as profile23_15_2_,
user1_.role as role24_15_2_
from
user_party userpartyl0_
inner join
users user1_
on userpartyl0_.user_id=user1_.user_id
where
userpartyl0_.party_id=?
Hibernate:
select
userpartyl0_.party_id as party_id5_14_0_,
userpartyl0_.user_party_id as user_par1_14_0_,
userpartyl0_.user_party_id as user_par1_14_1_,
userpartyl0_.created_at as created_2_14_1_,
userpartyl0_.deleted_at as deleted_3_14_1_,
userpartyl0_.last_modified_at as last_mod4_14_1_,
userpartyl0_.party_id as party_id5_14_1_,
userpartyl0_.user_id as user_id6_14_1_,
user1_.user_id as user_id1_15_2_,
user1_.created_at as created_2_15_2_,
user1_.deleted_at as deleted_3_15_2_,
user1_.last_modified_at as last_mod4_15_2_,
user1_.detail_adr as detail_a5_15_2_,
user1_.street_adr as street_a6_15_2_,
user1_.zipcode as zipcode7_15_2_,
user1_.animal_career as animal_c8_15_2_,
user1_.applicant_count as applican9_15_2_,
user1_.applicant_status as applica10_15_2_,
user1_.care_experience as care_ex11_15_2_,
user1_.email as email12_15_2_,
user1_.having_with_pet as having_13_15_2_,
user1_.id as id14_15_2_,
user1_.identification as identif15_15_2_,
user1_.is_smoking as is_smok16_15_2_,
user1_.license_img as license17_15_2_,
user1_.motivate as motivat18_15_2_,
user1_.name as name19_15_2_,
user1_.password as passwor20_15_2_,
user1_.pet_sitter_career as pet_sit21_15_2_,
user1_.phone as phone22_15_2_,
user1_.profile_img as profile23_15_2_,
user1_.role as role24_15_2_
from
user_party userpartyl0_
inner join
users user1_
on userpartyl0_.user_id=user1_.user_id
where
userpartyl0_.party_id=?
Hibernate:
select
userpartyl0_.party_id as party_id5_14_0_,
userpartyl0_.user_party_id as user_par1_14_0_,
userpartyl0_.user_party_id as user_par1_14_1_,
userpartyl0_.created_at as created_2_14_1_,
userpartyl0_.deleted_at as deleted_3_14_1_,
userpartyl0_.last_modified_at as last_mod4_14_1_,
userpartyl0_.party_id as party_id5_14_1_,
userpartyl0_.user_id as user_id6_14_1_,
user1_.user_id as user_id1_15_2_,
user1_.created_at as created_2_15_2_,
user1_.deleted_at as deleted_3_15_2_,
user1_.last_modified_at as last_mod4_15_2_,
user1_.detail_adr as detail_a5_15_2_,
user1_.street_adr as street_a6_15_2_,
user1_.zipcode as zipcode7_15_2_,
user1_.animal_career as animal_c8_15_2_,
user1_.applicant_count as applican9_15_2_,
user1_.applicant_status as applica10_15_2_,
user1_.care_experience as care_ex11_15_2_,
user1_.email as email12_15_2_,
user1_.having_with_pet as having_13_15_2_,
user1_.id as id14_15_2_,
user1_.identification as identif15_15_2_,
user1_.is_smoking as is_smok16_15_2_,
user1_.license_img as license17_15_2_,
user1_.motivate as motivat18_15_2_,
user1_.name as name19_15_2_,
user1_.password as passwor20_15_2_,
user1_.pet_sitter_career as pet_sit21_15_2_,
user1_.phone as phone22_15_2_,
user1_.profile_img as profile23_15_2_,
user1_.role as role24_15_2_
from
user_party userpartyl0_
inner join
users user1_
on userpartyl0_.user_id=user1_.user_id
where
userpartyl0_.party_id=?
2023-05-28 19:16:56.546 INFO 35680 --- [nio-8080-exec-5] c.ajou_nice.with_pet.service.DogService : ==================================== findAllUserDogs End ==============================================
2023-05-28 19:16:56.548 INFO 35680 --- [nio-8080-exec-5] c.a.with_pet.controller.DogController : ================================= 반려견 상세정보 목록 끝 =================================- dog 엔티티 조회시 연관매핑된 Party 엔티티도 조회한다.
- 하지만 Party 엔티티와 연관된 UserParty 엔티티는 n+1문제가 발생한다.
- 해당 문제에 대한 해결책이 필요하다.
주니어 개발자를 향해서..