n+1 문제 발생 체크 및 Jpa 최적화
findOneDiabetesDiaryOfOwnerWithRelation
GET http://localhost:8084/api/diary/owner/1/diabetes-diary/1/with/relations
작성자의 일지 “하나”를 조회하는 메서드이다. 일지 “하나”를 조회할 때는 연관된 식단, 음식을 모두 가져오는 게 좋을 것 같아서 만들었다.
DTO는 다음과 같다.
public class DiaryFetchResponseDTO{
private final Long diaryId;
private final Long writerId;
private final int fastingPlasmaGlucose;
private final String remark;
private final LocalDateTime writtenTime;
private final List<DietListFindResponseDTO> dietList;
private final List<FoodListFindResponseDTO> foodList;
}원하는 리스폰스는 다음과 같다.
{
"success": true,
"response": {
"diaryId": 1,
"writerId": 1,
"fastingPlasmaGlucose": 100,
"remark": "me_1",
"writtenTime": "2021-12-20T00:00:00",
"dietList": [
{
"writerId": 1,
"diaryId": 1,
"dietId": 1,
"eatTime": "BreakFast",
"bloodSugar": 100
},
{
"writerId": 1,
"diaryId": 1,
"dietId": 2,
"eatTime": "Lunch",
"bloodSugar": 150
},
{
"writerId": 1,
"diaryId": 1,
"dietId": 3,
"eatTime": "Dinner",
"bloodSugar": 250
}
],
"foodList": [
{
"writerId": 1,
"diaryId": 1,
"dietId": 1,
"foodId": 1,
"foodName": "food111"
},
{
"writerId": 1,
"diaryId": 1,
"dietId": 1,
"foodId": 2,
"foodName": "food112"
},
{
"writerId": 1,
"diaryId": 1,
"dietId": 2,
"foodId": 3,
"foodName": "food121"
},
{
"writerId": 1,
"diaryId": 1,
"dietId": 2,
"foodId": 4,
"foodName": "food122"
},
{
"writerId": 1,
"diaryId": 1,
"dietId": 3,
"foodId": 5,
"foodName": "food131"
},
{
"writerId": 1,
"diaryId": 1,
"dietId": 3,
"foodId": 6,
"foodName": "food132"
}
]
},
"error": null
}로그는 다음과 같다. 예상대로 N+1문제가 발생한다.
Hibernate: select diabetesdi0_.diary_id as diary_id1_0_, diabetesdi0_.writer_id as writer_i2_0_, diabetesdi0_.fpg as fpg3_0_, diabetesdi0_.remark as remark4_0_, diabetesdi0_.written_time as written_5_0_ from diabetes_diary diabetesdi0_ where diabetesdi0_.writer_id=? and diabetesdi0_.diary_id=?
Hibernate: select writer0_.writer_id as writer_i1_3_0_, writer0_.email as email2_3_0_, writer0_.name as name3_3_0_, writer0_.role as role4_3_0_ from writer writer0_ where writer0_.writer_id=?
Hibernate: select dietlist0_.diary_id as diary_id4_1_0_, dietlist0_.writer_id as writer_i5_1_0_, dietlist0_.diary_id as diary_id0_1_0_, dietlist0_.writer_id as writer_i0_1_0_, dietlist0_.diet_id as diet_id1_1_0_, dietlist0_.diary_id as diary_id0_1_1_, dietlist0_.writer_id as writer_i0_1_1_, dietlist0_.diet_id as diet_id1_1_1_, dietlist0_.diary_id as diary_id4_1_1_, dietlist0_.writer_id as writer_i5_1_1_, dietlist0_.blood_sugar as blood_su2_1_1_, dietlist0_.eat_time as eat_time3_1_1_ from diet dietlist0_ where dietlist0_.diary_id=? and dietlist0_.writer_id=?
Hibernate: select foodlist0_.diary_id as diary_id3_2_0_, foodlist0_.writer_id as writer_i4_2_0_, foodlist0_.diet_id as diet_id5_2_0_, foodlist0_.diary_id as diary_id0_2_0_, foodlist0_.writer_id as writer_i0_2_0_, foodlist0_.diet_id as diet_id0_2_0_, foodlist0_.food_id as food_id1_2_0_, foodlist0_.diary_id as diary_id0_2_1_, foodlist0_.writer_id as writer_i0_2_1_, foodlist0_.diet_id as diet_id0_2_1_, foodlist0_.food_id as food_id1_2_1_, foodlist0_.diary_id as diary_id3_2_1_, foodlist0_.writer_id as writer_i4_2_1_, foodlist0_.diet_id as diet_id5_2_1_, foodlist0_.food_name as food_nam2_2_1_ from food foodlist0_ where foodlist0_.diary_id=? and foodlist0_.writer_id=? and foodlist0_.diet_id=?
Hibernate: select foodlist0_.diary_id as diary_id3_2_0_, foodlist0_.writer_id as writer_i4_2_0_, foodlist0_.diet_id as diet_id5_2_0_, foodlist0_.diary_id as diary_id0_2_0_, foodlist0_.writer_id as writer_i0_2_0_, foodlist0_.diet_id as diet_id0_2_0_, foodlist0_.food_id as food_id1_2_0_, foodlist0_.diary_id as diary_id0_2_1_, foodlist0_.writer_id as writer_i0_2_1_, foodlist0_.diet_id as diet_id0_2_1_, foodlist0_.food_id as food_id1_2_1_, foodlist0_.diary_id as diary_id3_2_1_, foodlist0_.writer_id as writer_i4_2_1_, foodlist0_.diet_id as diet_id5_2_1_, foodlist0_.food_name as food_nam2_2_1_ from food foodlist0_ where foodlist0_.diary_id=? and foodlist0_.writer_id=? and foodlist0_.diet_id=?
Hibernate: select foodlist0_.diary_id as diary_id3_2_0_, foodlist0_.writer_id as writer_i4_2_0_, foodlist0_.diet_id as diet_id5_2_0_, foodlist0_.diary_id as diary_id0_2_0_, foodlist0_.writer_id as writer_i0_2_0_, foodlist0_.diet_id as diet_id0_2_0_, foodlist0_.food_id as food_id1_2_0_, foodlist0_.diary_id as diary_id0_2_1_, foodlist0_.writer_id as writer_i0_2_1_, foodlist0_.diet_id as diet_id0_2_1_, foodlist0_.food_id as food_id1_2_1_, foodlist0_.diary_id as diary_id3_2_1_, foodlist0_.writer_id as writer_i4_2_1_, foodlist0_.diet_id as diet_id5_2_1_, foodlist0_.food_name as food_nam2_2_1_ from food foodlist0_ where foodlist0_.diary_id=? and foodlist0_.writer_id=? and foodlist0_.diet_id=?일지 하나를 조회할 때는 연관된 것을 한꺼번에 조회하도록 페치 조인하자. 하지만 문제가 발생하였다.
@Override
public Optional findDiabetesDiaryOfWriterWithRelation(Long writerId, Long diaryId) {
return Optional.ofNullable(jpaQueryFactory.selectFrom(QDiabetesDiary.diabetesDiary)
.innerJoin(QDiabetesDiary.diabetesDiary.writer, QWriter.writer)
.fetchJoin()
.innerJoin(QDiabetesDiary.diabetesDiary.dietList, QDiet.diet)
.fetchJoin()
.innerJoin(QDiet.diet.foodList, QFood.food)
.fetchJoin()
.where(QDiabetesDiary.diabetesDiary.writer.writerId.eq(writerId).and(QDiabetesDiary.diabetesDiary.diaryId.eq(diaryId)))
.fetchOne());
}
또 다른 문제 발생
일대다 관계에서 여러번 페치 조인할 때 발생하는 문제다. (multiple bag fetch Exception)
페치조인은 xxToMany 에서 단 하나만 적용할 수 있기 때문이다. (반면, xxToOne은 여러개 사용해도 된다.)
org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: 해결
가장 간단한 방법은 xxToMany 에서 사용된 컬렉션의 자료형을 Set으로 바꾸는 것이다.
private final Set<Diet> dietList = new HashSet<>(); <- 일지 내 컬렉션
private final Set<Food> foodList = new HashSet<>(); <-식단 내 컬렉션로그를 보면 조인을 활용해서 한 번에 결과를 조회하였다.
Hibernate: select diabetesdi0_.diary_id as diary_id1_0_0_, diabetesdi0_.writer_id as writer_i2_0_0_, writer1_.writer_id as writer_i1_3_1_, dietlist2_.diary_id as diary_id0_1_2_, dietlist2_.writer_id as writer_i0_1_2_, dietlist2_.diet_id as diet_id1_1_2_, foodlist3_.diary_id as diary_id0_2_3_, foodlist3_.writer_id as writer_i0_2_3_, foodlist3_.diet_id as diet_id0_2_3_, foodlist3_.food_id as food_id1_2_3_, diabetesdi0_.fpg as fpg3_0_0_, diabetesdi0_.remark as remark4_0_0_, diabetesdi0_.written_time as written_5_0_0_, writer1_.email as email2_3_1_, writer1_.name as name3_3_1_, writer1_.role as role4_3_1_, dietlist2_.diary_id as diary_id4_1_2_, dietlist2_.writer_id as writer_i5_1_2_, dietlist2_.blood_sugar as blood_su2_1_2_, dietlist2_.eat_time as eat_time3_1_2_, dietlist2_.diary_id as diary_id4_1_0__, dietlist2_.writer_id as writer_i5_1_0__, dietlist2_.diary_id as diary_id0_1_0__, dietlist2_.writer_id as writer_i0_1_0__, dietlist2_.diet_id as diet_id1_1_0__, foodlist3_.diary_id as diary_id3_2_3_, foodlist3_.writer_id as writer_i4_2_3_, foodlist3_.diet_id as diet_id5_2_3_, foodlist3_.food_name as food_nam2_2_3_, foodlist3_.diary_id as diary_id3_2_1__, foodlist3_.writer_id as writer_i4_2_1__, foodlist3_.diet_id as diet_id5_2_1__, foodlist3_.diary_id as diary_id0_2_1__, foodlist3_.writer_id as writer_i0_2_1__, foodlist3_.diet_id as diet_id0_2_1__, foodlist3_.food_id as food_id1_2_1__ from diabetes_diary diabetesdi0_ inner join writer writer1_ on diabetesdi0_.writer_id=writer1_.writer_id inner join diet dietlist2_ on diabetesdi0_.diary_id=dietlist2_.diary_id and diabetesdi0_.writer_id=dietlist2_.writer_id inner join food foodlist3_ on dietlist2_.diary_id=foodlist3_.diary_id and dietlist2_.writer_id=foodlist3_.writer_id and dietlist2_.diet_id=foodlist3_.diet_id where diabetesdi0_.writer_id=? and diabetesdi0_.diary_id=? DTO에서 발생하는 성능상 이슈
다음 DTO 코드를 보면, getOOO()를 사용한다.
public class DietFindResponseDTO {
private final Long writerId;
private final Long diaryId;
private final Long dietId;
private final EatTime eatTime;
private final int bloodSugar;
public DietFindResponseDTO(Diet diet) {
this.writerId = diet.getDiary().getWriter().getId();
this.diaryId = diet.getDiary().getId();
this.dietId = diet.getDietId();
this.eatTime = diet.getEatTime();
this.bloodSugar = diet.getBloodSugar();
}
}이 DTO를 사용한다면, 연관 관계 엔티티인 작성자와 일지를 추가 SELECT 해야 한다.
음식 DTO의 경우 최대 3개까지 연관 엔티티를 조회하기 때문에 개수가 별로 없어보인다.
하지만, DTO의 경우 굉장히 빈번하게 요청될 수 있기 때문에 추가적인 SELECT는 성능상 문제를 야기할 것이다.
따라서 DTO는 자신의 엔티티의 ID만 반환하는 것이 나을 것이다.
public class DietFindResponseDTO {
private final Long dietId;
private final EatTime eatTime;
private final int bloodSugar;
public DietFindResponseDTO(Diet diet) {
this.dietId = diet.getDietId();
this.eatTime = diet.getEatTime();
this.bloodSugar = diet.getBloodSugar();
}
}
삭제 벌크 쿼리 적용 (orphanRemoval 제거)
일지 하나를 삭제할 때 생기는 로그이다.
Hibernate: select writer0_.writer_id as writer_i1_3_0_, writer0_.email as email2_3_0_, writer0_.name as name3_3_0_, writer0_.role as role4_3_0_ from writer writer0_ where writer0_.writer_id=?
Hibernate: select diabetesdi0_.diary_id as diary_id1_0_, diabetesdi0_.writer_id as writer_i2_0_, diabetesdi0_.fpg as fpg3_0_, diabetesdi0_.remark as remark4_0_, diabetesdi0_.written_time as written_5_0_ from diabetes_diary diabetesdi0_ where diabetesdi0_.writer_id=? and diabetesdi0_.diary_id=?
Hibernate: select diaries0_.writer_id as writer_i2_0_0_, diaries0_.diary_id as diary_id1_0_0_, diaries0_.diary_id as diary_id1_0_1_, diaries0_.writer_id as writer_i2_0_1_, diaries0_.fpg as fpg3_0_1_, diaries0_.remark as remark4_0_1_, diaries0_.written_time as written_5_0_1_ from diabetes_diary diaries0_ where diaries0_.writer_id=?
Hibernate: select dietlist0_.diary_id as diary_id4_1_0_, dietlist0_.writer_id as writer_i5_1_0_, dietlist0_.diary_id as diary_id0_1_0_, dietlist0_.writer_id as writer_i0_1_0_, dietlist0_.diet_id as diet_id1_1_0_, dietlist0_.diary_id as diary_id0_1_1_, dietlist0_.writer_id as writer_i0_1_1_, dietlist0_.diet_id as diet_id1_1_1_, dietlist0_.diary_id as diary_id4_1_1_, dietlist0_.writer_id as writer_i5_1_1_, dietlist0_.blood_sugar as blood_su2_1_1_, dietlist0_.eat_time as eat_time3_1_1_ from diet dietlist0_ where dietlist0_.diary_id=? and dietlist0_.writer_id=?
Hibernate: select foodlist0_.diary_id as diary_id3_2_0_, foodlist0_.writer_id as writer_i4_2_0_, foodlist0_.diet_id as diet_id5_2_0_, foodlist0_.diary_id as diary_id0_2_0_, foodlist0_.writer_id as writer_i0_2_0_, foodlist0_.diet_id as diet_id0_2_0_, foodlist0_.food_id as food_id1_2_0_, foodlist0_.diary_id as diary_id0_2_1_, foodlist0_.writer_id as writer_i0_2_1_, foodlist0_.diet_id as diet_id0_2_1_, foodlist0_.food_id as food_id1_2_1_, foodlist0_.diary_id as diary_id3_2_1_, foodlist0_.writer_id as writer_i4_2_1_, foodlist0_.diet_id as diet_id5_2_1_, foodlist0_.food_name as food_nam2_2_1_ from food foodlist0_ where foodlist0_.diary_id=? and foodlist0_.writer_id=? and foodlist0_.diet_id=?
Hibernate: select foodlist0_.diary_id as diary_id3_2_0_, foodlist0_.writer_id as writer_i4_2_0_, foodlist0_.diet_id as diet_id5_2_0_, foodlist0_.diary_id as diary_id0_2_0_, foodlist0_.writer_id as writer_i0_2_0_, foodlist0_.diet_id as diet_id0_2_0_, foodlist0_.food_id as food_id1_2_0_, foodlist0_.diary_id as diary_id0_2_1_, foodlist0_.writer_id as writer_i0_2_1_, foodlist0_.diet_id as diet_id0_2_1_, foodlist0_.food_id as food_id1_2_1_, foodlist0_.diary_id as diary_id3_2_1_, foodlist0_.writer_id as writer_i4_2_1_, foodlist0_.diet_id as diet_id5_2_1_, foodlist0_.food_name as food_nam2_2_1_ from food foodlist0_ where foodlist0_.diary_id=? and foodlist0_.writer_id=? and foodlist0_.diet_id=?
Hibernate: select foodlist0_.diary_id as diary_id3_2_0_, foodlist0_.writer_id as writer_i4_2_0_, foodlist0_.diet_id as diet_id5_2_0_, foodlist0_.diary_id as diary_id0_2_0_, foodlist0_.writer_id as writer_i0_2_0_, foodlist0_.diet_id as diet_id0_2_0_, foodlist0_.food_id as food_id1_2_0_, foodlist0_.diary_id as diary_id0_2_1_, foodlist0_.writer_id as writer_i0_2_1_, foodlist0_.diet_id as diet_id0_2_1_, foodlist0_.food_id as food_id1_2_1_, foodlist0_.diary_id as diary_id3_2_1_, foodlist0_.writer_id as writer_i4_2_1_, foodlist0_.diet_id as diet_id5_2_1_, foodlist0_.food_name as food_nam2_2_1_ from food foodlist0_ where foodlist0_.diary_id=? and foodlist0_.writer_id=? and foodlist0_.diet_id=?
Hibernate: delete from food where diary_id=? and writer_id=? and diet_id=? and food_id=?
Hibernate: delete from food where diary_id=? and writer_id=? and diet_id=? and food_id=?
Hibernate: delete from diet where diary_id=? and writer_id=? and diet_id=?
Hibernate: delete from food where diary_id=? and writer_id=? and diet_id=? and food_id=?
Hibernate: delete from food where diary_id=? and writer_id=? and diet_id=? and food_id=?
Hibernate: delete from diet where diary_id=? and writer_id=? and diet_id=?
Hibernate: delete from food where diary_id=? and writer_id=? and diet_id=? and food_id=?
Hibernate: delete from food where diary_id=? and writer_id=? and diet_id=? and food_id=?
Hibernate: delete from diet where diary_id=? and writer_id=? and diet_id=?
Hibernate: delete from diabetes_diary where diary_id=? and writer_id=? 일지 하나에 식단 3개, 식단 1개에 음식 2개씩 있는 상황이었다.
cascadeType.all 이므로 삭제가 전파되어 연관된 식단이랑 음식이 전부 제거되야 한다.
위 로그는 정상적으로 작동하는 것은 맞지만, 성능 상으로는 좋지 않다.
연관된 엔티티를 하나씩 select 후 delete하는 것보단, 한 꺼번에 delete하는 것이 성능 상 더 훌륭하다.
원인
orphanRemoval=true 가 원인이었다.
public class Writer {
@OneToMany(mappedBy = "writer", orphanRemoval = true, cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private final Set<DiabetesDiary> diaries = new HashSet<>();
} @Transactional
public void deleteDiary(EntityId<Writer, Long> writerEntityId, EntityId<DiabetesDiary, Long> diaryEntityId) {
logger.info("delete diary");
checkNotNull(writerEntityId, "writerId must be provided");
checkNotNull(diaryEntityId, "diaryId must be provided");
Writer writer = writerRepository.findById(writerEntityId.getId()).orElseThrow(() -> new NoResultException("해당 작성자가 없습니다."));
DiabetesDiary targetDiary = diaryRepository.findOneDiabetesDiaryByIdInWriter(writerEntityId.getId(), diaryEntityId.getId())
.orElseThrow(() -> new NoResultException("해당 혈당일지가 존재하지 않습니다."));
//orphanRemoval = true 로 해놓았기 때문에 부모의 컬렉션에서 자식이 null 되면 알아서 delete 쿼리가 나간다.
writer.removeDiary(targetDiary);
} 해결
orphanRemoval=true를 컬렉션에서 제거하고, 벌크 삭제용 메서드를 따로 만들었다.
@Override
public void bulkDeleteDiary(Long diaryId) {
//select diet id
logger.info("select diet id");
List<Long> dietIdList = jpaQueryFactory.selectFrom(QDiet.diet)
.innerJoin(QDiet.diet.diary, QDiabetesDiary.diabetesDiary)
.on(QDiet.diet.diary.diaryId.eq(diaryId))
.fetch()
.stream().map(
Diet::getDietId
).collect(Collectors.toList());
//select food id
logger.info("select food id");
List<Long> foodIdList = jpaQueryFactory.selectFrom(QFood.food)
.innerJoin(QFood.food.diet, QDiet.diet)
.on(QDiet.diet.dietId.in(dietIdList))
.fetch()
.stream().map(
Food::getId
).collect(Collectors.toList());
//bulk delete food
logger.info("bulk delete food");
jpaQueryFactory.delete(QFood.food)
.where(QFood.food.foodId.in(foodIdList))
.execute();
//bulk delete diet
logger.info("bulk delete diet");
jpaQueryFactory.delete(QDiet.diet)
.where(QDiet.diet.dietId.in(dietIdList))
.execute();
logger.info("select diet id");
jpaQueryFactory.delete(QDiabetesDiary.diabetesDiary)
.where(QDiabetesDiary.diabetesDiary.diaryId.eq(diaryId))
.execute();
} 로그 결과는 다음과 같다. in으로 “단 한 번에” 삭제하기 때문에 성능이 향상되었다.
Hibernate: select writer0_.writer_id as writer_i1_3_0_, writer0_.email as email2_3_0_, writer0_.name as name3_3_0_, writer0_.role as role4_3_0_ from writer writer0_ where writer0_.writer_id=?
Hibernate: select diabetesdi0_.diary_id as diary_id1_0_, diabetesdi0_.writer_id as writer_i2_0_, diabetesdi0_.fpg as fpg3_0_, diabetesdi0_.remark as remark4_0_, diabetesdi0_.written_time as written_5_0_ from diabetes_diary diabetesdi0_ where diabetesdi0_.writer_id=? and diabetesdi0_.diary_id=?
2021-12-26 13:58:38.690 INFO 1564 --- [io-8084-exec-10] c.d.r.a.s.UpdateDeleteDiaryService : association detached
Hibernate: select diaries0_.writer_id as writer_i2_0_0_, diaries0_.diary_id as diary_id1_0_0_, diaries0_.diary_id as diary_id1_0_1_, diaries0_.writer_id as writer_i2_0_1_, diaries0_.fpg as fpg3_0_1_, diaries0_.remark as remark4_0_1_, diaries0_.written_time as written_5_0_1_ from diabetes_diary diaries0_ where diaries0_.writer_id=?
2021-12-26 13:58:38.691 INFO 1564 --- [io-8084-exec-10] c.d.r.a.s.UpdateDeleteDiaryService : bulk delete
2021-12-26 13:58:38.691 INFO 1564 --- [io-8084-exec-10] c.d.r.a.d.d.d.DiaryRepositoryImpl : select diet id
Hibernate: select diet0_.diary_id as diary_id0_1_, diet0_.writer_id as writer_i0_1_, diet0_.diet_id as diet_id1_1_, diet0_.diary_id as diary_id4_1_, diet0_.writer_id as writer_i5_1_, diet0_.blood_sugar as blood_su2_1_, diet0_.eat_time as eat_time3_1_ from diet diet0_ inner join diabetes_diary diabetesdi1_ on diet0_.diary_id=diabetesdi1_.diary_id and diet0_.writer_id=diabetesdi1_.writer_id and (diabetesdi1_.diary_id=?)
2021-12-26 13:58:38.696 INFO 1564 --- [io-8084-exec-10] c.d.r.a.d.d.d.DiaryRepositoryImpl : select food id
Hibernate: select food0_.diary_id as diary_id0_2_, food0_.writer_id as writer_i0_2_, food0_.diet_id as diet_id0_2_, food0_.food_id as food_id1_2_, food0_.diary_id as diary_id3_2_, food0_.writer_id as writer_i4_2_, food0_.diet_id as diet_id5_2_, food0_.food_name as food_nam2_2_ from food food0_ inner join diet diet1_ on food0_.diary_id=diet1_.diary_id and food0_.writer_id=diet1_.writer_id and food0_.diet_id=diet1_.diet_id and (diet1_.diet_id in (? , ? , ?))
2021-12-26 13:58:38.704 INFO 1564 --- [io-8084-exec-10] c.d.r.a.d.d.d.DiaryRepositoryImpl : bulk delete food
Hibernate: delete from food where food_id in (? , ? , ? , ? , ? , ?)
2021-12-26 13:58:38.711 INFO 1564 --- [io-8084-exec-10] c.d.r.a.d.d.d.DiaryRepositoryImpl : bulk delete diet
Hibernate: delete from diet where diet_id in (? , ? , ?)
2021-12-26 13:58:38.713 INFO 1564 --- [io-8084-exec-10] c.d.r.a.d.d.d.DiaryRepositoryImpl : select diet id
Hibernate: delete from diabetes_diary where diary_id=?
아키텍쳐 설계와 테스트 코드에 관심이 많음.