기존에는...
프로젝트 기능 구현을 빨리 했어야 해서 성능 최적화에 대한 부분은 개발 과정에서 배재하였었다.
리팩토링 기간에 JPA 의 N+1 문제를 해결해서 성능 최적화를 달성해보자!!!
현재 상황
프로그램과 User 중간테이블로 Review가 존재하며(프로그램과 User 는 다대다)
Review 와 ReviewReviewTag 는 1대 다 관계이고
ReviewReviewTag 과 ReviewTag는 다 대 1 관계이다.
즉 Review 와 ReviewTag 중간테이블로 ReviewReviewTag가 존재한다(Review 와 ReviewTag 는 다대다)
기존 코드 설명
<4개의 리뷰를 보여주는 메서드>
@Override
public FourReviewResponseWithCounts show4Review(Long programId) {
Program program = programRepository.findById(programId)
.orElseThrow(() -> new NotFoundException(ErrorCode.PROGRAM_NOT_FOUND));
List<ReviewProgramResponseDto> reviewProgramResponseDtoList = reviewRepository.findTop4ByProgramOrderByLikeCountsDesc(
program).stream().map(this::makeReviewDto).collect(
Collectors.toList());
int leftReviewCounts = program.getReviewCount() - 4 > 0 ? program.getReviewCount() - 4 : 0;
return new FourReviewResponseWithCounts(
reviewProgramResponseDtoList, leftReviewCounts);
}<4개의 리뷰를 가지고 리뷰 dto 로 만드는 메서드>
private ReviewProgramResponseDto makeReviewDto(Review review) {
List<String> reviewTagNames = reviewReviewTagRepository.findByReviewWithReviewTagFetch(
review.getId())
.stream().map(reviewReviewTag -> reviewReviewTag.getReviewTag().getName())
.collect(Collectors.toList());
ReviewProgramResponseDto reviewProgramResponseDto = ReviewProgramResponseDto.builder()
.reviewTagNames(reviewTagNames)
.createdDateTime(review.getCreatedAt())
.like(review.getLikeCounts())
.ratings(review.getRating())
.contents(review.getContent())
.userNickName(review.getUser().getNickName())
.userPosterPath(review.getUser().getProfilePhoto())
.reviewId(review.getId())
.build();
return reviewProgramResponseDto;
}<중간 테이블 중심으로(ReviewReviewTag) ReviewTag를 함께 가져오는 fetch Join 상태
@Query("select rrt from ReviewReviewTag rrt join fetch rrt.reviewTag where rrt.review.id=:reviewId")
List<ReviewReviewTag> findByReviewWithReviewTagFetch(@Param("reviewId")Long reviewId);
테스트 데이터를 편하게 넣으려다 보니 리뷰에 대한 User가 같은 사람으로 존재하고 있고 ReviewTag 도 같은 3번과 5번으로 넣었다는 점만 유의한다.
코드에 대한 쿼리 분석
Hibernate:
select
program0_.program_id as program_1_7_0_,
program0_.average_rating as average_2_7_0_,
program0_.created_year as created_3_7_0_,
program0_.poster_path as poster_p4_7_0_,
program0_.review_count as review_c5_7_0_,
program0_.title as title6_7_0_,
program0_.tm_db_program_id as tm_db_pr7_7_0_,
program0_.type as type8_7_0_
from
program program0_
where
program0_.program_id=?
2024-02-07 03:29:15.516 INFO 2664 --- [nio-8080-exec-8] p6spy : #1707244155516 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=?
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=73;
Hibernate:
select
review0_.review_id as review_i1_10_,
review0_.created_at as created_2_10_,
review0_.updated_at as updated_3_10_,
review0_.content as content4_10_,
review0_.genre as genre5_10_,
review0_.like_counts as like_cou6_10_,
review0_.program_id as program_8_10_,
review0_.rating as rating7_10_,
review0_.user_id as user_id9_10_
from
review review0_
where
review0_.program_id=?
order by
review0_.like_counts desc limit ?
2024-02-07 03:29:15.520 INFO 2664 --- [nio-8080-exec-8] p6spy : #1707244155520 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select review0_.review_id as review_i1_10_, review0_.created_at as created_2_10_, review0_.updated_at as updated_3_10_, review0_.content as content4_10_, review0_.genre as genre5_10_, review0_.like_counts as like_cou6_10_, review0_.program_id as program_8_10_, review0_.rating as rating7_10_, review0_.user_id as user_id9_10_ from review review0_ where review0_.program_id=? order by review0_.like_counts desc limit ?
select review0_.review_id as review_i1_10_, review0_.created_at as created_2_10_, review0_.updated_at as updated_3_10_, review0_.content as content4_10_, review0_.genre as genre5_10_, review0_.like_counts as like_cou6_10_, review0_.program_id as program_8_10_, review0_.rating as rating7_10_, review0_.user_id as user_id9_10_ from review review0_ where review0_.program_id=73 order by review0_.like_counts desc limit 4;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 03:29:15.525 INFO 2664 --- [nio-8080-exec-8] p6spy : #1707244155525 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=21;
Hibernate:
select
user0_.user_id as user_id1_14_0_,
user0_.created_at as created_2_14_0_,
user0_.updated_at as updated_3_14_0_,
user0_.average_rating as average_4_14_0_,
user0_.code as code5_14_0_,
user0_.email as email6_14_0_,
user0_.grade as grade7_14_0_,
user0_.nick_name as nick_nam8_14_0_,
user0_.profile_photo as profile_9_14_0_,
user0_.role as role10_14_0_,
user0_.social_type as social_11_14_0_,
user0_.review_counts as review_12_14_0_
from
`user` user0_
where
user0_.user_id=?
2024-02-07 03:29:15.527 INFO 2664 --- [nio-8080-exec-8] p6spy : #1707244155527 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select user0_.user_id as user_id1_14_0_, user0_.created_at as created_2_14_0_, user0_.updated_at as updated_3_14_0_, user0_.average_rating as average_4_14_0_, user0_.code as code5_14_0_, user0_.email as email6_14_0_, user0_.grade as grade7_14_0_, user0_.nick_name as nick_nam8_14_0_, user0_.profile_photo as profile_9_14_0_, user0_.role as role10_14_0_, user0_.social_type as social_11_14_0_, user0_.review_counts as review_12_14_0_ from `user` user0_ where user0_.user_id=?
select user0_.user_id as user_id1_14_0_, user0_.created_at as created_2_14_0_, user0_.updated_at as updated_3_14_0_, user0_.average_rating as average_4_14_0_, user0_.code as code5_14_0_, user0_.email as email6_14_0_, user0_.grade as grade7_14_0_, user0_.nick_name as nick_nam8_14_0_, user0_.profile_photo as profile_9_14_0_, user0_.role as role10_14_0_, user0_.social_type as social_11_14_0_, user0_.review_counts as review_12_14_0_ from `user` user0_ where user0_.user_id=5;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 03:29:15.530 INFO 2664 --- [nio-8080-exec-8] p6spy : #1707244155530 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=22;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 03:29:15.531 INFO 2664 --- [nio-8080-exec-8] p6spy : #1707244155531 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=23;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 03:29:15.534 INFO 2664 --- [nio-8080-exec-8] p6spy : #1707244155534 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=24;
2024-02-07 03:29:15.535 INFO 2664 --- [nio-8080-exec-8] p6spy : #1707244155535 | took 0ms | commit | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
;성능 개선점 파악하기
program 과 Review 를 하나로 합쳐서 가져올 수 있을까?
즉 Program 과 Review 쿼리 총 2개 나가는 것을 하나로 줄이고 싶다.
하지만 애매한 이유가 존재한다.
1)가장 큰 이유:
program.getReviewCount() 에서 program 의 값을 직접적으로 사용한다. review를 List 형식으로 가져오는데 List 형식에서 아무 리뷰나 뽑아가지고 review.getProrgram().getReviewCount()으로 가지고 올 수는 있지만 review가 존재하지 않는다면 program 도 존재 하지 않는다. 따라서 우리 코드 상 리뷰가 존재하지 않을 때 review.getProgram.getReviewCount() 에 대한 NPE 를 처리해주어야 할 수도 있다.
2)내가 생각한 Review 에서 Program 을 Fetch JOIN 해서 가져올 때 비효율적인 부분:
게다가 Review 를 가지고 올 때 항상 Program 에 대한 정보를 항상 가지고 오는데(Review 쪽에서 fetch join 을 하면) 한 개의 프로그램에 대해 항상 리뷰를 가지고 올 때 마다 프로그램의 정보를 몽땅 가져오는 비효율적인 부분이 존재해 보인다. (어차피 같은 프로그램이니까)
3)Program 에서 Review 를 성능 최적화를 하려면?
3-1 => Batch Size 활용:
의미없다. 필요한 프로그램의 갯수가 1개이므로 의미가 아예 없다.
3-2 => 1대다 Fetch JOIN :
Pageable 이 붙어 있어 데이터 정합성 우려가 있다 .
또한 Pageable 이 존재하는데 1대다 fetch join 을 사용하면 메모리에서 페이징 처리를 하기 때문에 메모리의 부하가 증가 될 수 있다.
따라서 Program 과 Review 를 같이 가져오는 부분은 냅두기로 했다.
Review 와 User를 함께 가져온다
Review 를 가지고 올 때 User 에 대한 정보를 함께 가져와 User 에 대한 쿼리를 줄 일 수 있다.
프로그램 쪽은 항상 같은 프로그램이라서 한번만 조회하면 끝났는데
리뷰의 User 는 다른 사람일 가능성이 매우 높아서
Review 를 조회할 때 User 를 같이 가져오는 Fetch Join을 활용하는 것이 좋을 것 같다!
Review 와 ReviewTagTag 및 ReviewTag를 같이 가져오고 싶다
현재 Pageable 즉 페이징 처리를 하고 있으므로 데이터 정합성 및 메모리 관련 이슈 때문에
BatchSize 를 활용해서 성능을 더욱 최적화 하는 방법을 강구할 수 있다.
성능 개선 및 알게 된점
Review 와 User를 함께 가져오도록 성능 최적화
@Query("select r from Review r join fetch r.user where r.program.id=:programId order by r.likeCounts desc")
List<Review> findTop4ByProgramOrderByLikeCountsDescWithFetchUser(
@Param("programId") Long programId, Pageable pageable);를 통해 Review 와 User를 함께 가지고 올 수 있다.
이를 통해 기존 Review 에 대해서 Review 의 User가 새로운 User가 나타날때마다 발생했던 User 의 쿼리는 발생하지 않게 되었다.
Hibernate:
select
program0_.program_id as program_1_7_0_,
program0_.average_rating as average_2_7_0_,
program0_.created_year as created_3_7_0_,
program0_.poster_path as poster_p4_7_0_,
program0_.review_count as review_c5_7_0_,
program0_.title as title6_7_0_,
program0_.tm_db_program_id as tm_db_pr7_7_0_,
program0_.type as type8_7_0_
from
program program0_
where
program0_.program_id=?
2024-02-07 04:06:58.592 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418592 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=?
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=73;
Hibernate:
select
review0_.review_id as review_i1_10_0_,
user1_.user_id as user_id1_14_1_,
review0_.created_at as created_2_10_0_,
review0_.updated_at as updated_3_10_0_,
review0_.content as content4_10_0_,
review0_.genre as genre5_10_0_,
review0_.like_counts as like_cou6_10_0_,
review0_.program_id as program_8_10_0_,
review0_.rating as rating7_10_0_,
review0_.user_id as user_id9_10_0_,
user1_.created_at as created_2_14_1_,
user1_.updated_at as updated_3_14_1_,
user1_.average_rating as average_4_14_1_,
user1_.code as code5_14_1_,
user1_.email as email6_14_1_,
user1_.grade as grade7_14_1_,
user1_.nick_name as nick_nam8_14_1_,
user1_.profile_photo as profile_9_14_1_,
user1_.role as role10_14_1_,
user1_.social_type as social_11_14_1_,
user1_.review_counts as review_12_14_1_
from
review review0_
inner join
`user` user1_
on review0_.user_id=user1_.user_id
where
review0_.program_id=?
order by
review0_.like_counts desc limit ?
2024-02-07 04:06:58.594 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418594 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=? order by review0_.like_counts desc limit ?
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=73 order by review0_.like_counts desc limit 4;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 04:06:58.596 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418596 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=40;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 04:06:58.598 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418598 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=39;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 04:06:58.600 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418600 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=38;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 04:06:58.601 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418601 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=37;
2024-02-07 04:06:58.602 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418602 | took 0ms | commit | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
;즉 Review 와 User가 함께 가지고 와서 1+N 문제를 하나 해결했다.
Review 와 ReviewTag에 대한 실험
만약 페이징 처리를 하고 있지만 Fetch Join 을 해본다는 가정하에 어떤 문제점이 발생하는지 파악하는 실험이다.
@Query("select r from Review r join fetch r.user join fetch r.reviewReviewTags rrt join fetch rrt.reviewTag where r.program.id=:programId order by r.likeCounts desc")
List<Review> findTop4ByProgramOrderByLikeCountsDescWithFetchUser(
@Param("programId") Long programId, Pageable pageable);이렇게 쿼리를 작성했다.
private ReviewProgramResponseDto makeReviewDto(Review review) {
/*List<String> reviewTagNames = reviewReviewTagRepository.findByReviewWithReviewTagFetch(
review.getId())
.stream().map(reviewReviewTag -> reviewReviewTag.getReviewTag().getName())
.collect(Collectors.toList());*/
List<String> reviewTagNames = review.getReviewReviewTags().stream().map(reviewReviewTag -> reviewReviewTag.getReviewTag().getName()).collect(
Collectors.toList());
ReviewProgramResponseDto reviewProgramResponseDto = ReviewProgramResponseDto.builder()
.reviewTagNames(reviewTagNames)
.createdDateTime(review.getCreatedAt())
.like(review.getLikeCounts())
.ratings(review.getRating())
.contents(review.getContent())
.userNickName(review.getUser().getNickName())
.userPosterPath(review.getUser().getProfilePhoto())
.reviewId(review.getId())
.build();
return reviewProgramResponseDto;
} 그리고 이렇게 원래 중간테이블 기준으로 Fetch Join 을 하던 부분을 주석처리하고 reviewReviewTag.getReviewTag().getName() 으로 전환했다.
이렇게 한다면
Hibernate:
select
program0_.program_id as program_1_7_0_,
program0_.average_rating as average_2_7_0_,
program0_.created_year as created_3_7_0_,
program0_.poster_path as poster_p4_7_0_,
program0_.review_count as review_c5_7_0_,
program0_.title as title6_7_0_,
program0_.tm_db_program_id as tm_db_pr7_7_0_,
program0_.type as type8_7_0_
from
program program0_
where
program0_.program_id=?
2024-02-07 04:16:51.604 INFO 6868 --- [nio-8080-exec-1] p6spy : #1707247011604 | took 0ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=?
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=73;
2024-02-07 04:16:51.619 WARN 6868 --- [nio-8080-exec-1] o.h.h.internal.ast.QueryTranslatorImpl : HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!
Hibernate:
select
review0_.review_id as review_i1_10_0_,
user1_.user_id as user_id1_14_1_,
reviewrevi2_.review_review_tag_id as review_r1_11_2_,
reviewtag3_.review_tag_id as review_t1_12_3_,
review0_.created_at as created_2_10_0_,
review0_.updated_at as updated_3_10_0_,
review0_.content as content4_10_0_,
review0_.genre as genre5_10_0_,
review0_.like_counts as like_cou6_10_0_,
review0_.program_id as program_8_10_0_,
review0_.rating as rating7_10_0_,
review0_.user_id as user_id9_10_0_,
user1_.created_at as created_2_14_1_,
user1_.updated_at as updated_3_14_1_,
user1_.average_rating as average_4_14_1_,
user1_.code as code5_14_1_,
user1_.email as email6_14_1_,
user1_.grade as grade7_14_1_,
user1_.nick_name as nick_nam8_14_1_,
user1_.profile_photo as profile_9_14_1_,
user1_.role as role10_14_1_,
user1_.social_type as social_11_14_1_,
user1_.review_counts as review_12_14_1_,
reviewrevi2_.review_id as review_i2_11_2_,
reviewrevi2_.review_tag_id as review_t3_11_2_,
reviewrevi2_.review_id as review_i2_11_0__,
reviewrevi2_.review_review_tag_id as review_r1_11_0__,
reviewtag3_.name as name2_12_3_
from
review review0_
inner join
`user` user1_
on review0_.user_id=user1_.user_id
inner join
review_review_tag reviewrevi2_
on review0_.review_id=reviewrevi2_.review_id
inner join
review_tag reviewtag3_
on reviewrevi2_.review_tag_id=reviewtag3_.review_tag_id
where
review0_.program_id=?
order by
review0_.like_counts desc
2024-02-07 04:16:51.622 INFO 6868 --- [nio-8080-exec-1] p6spy : #1707247011622 | took 1ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, reviewrevi2_.review_review_tag_id as review_r1_11_2_, reviewtag3_.review_tag_id as review_t1_12_3_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_, reviewrevi2_.review_id as review_i2_11_2_, reviewrevi2_.review_tag_id as review_t3_11_2_, reviewrevi2_.review_id as review_i2_11_0__, reviewrevi2_.review_review_tag_id as review_r1_11_0__, reviewtag3_.name as name2_12_3_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id inner join review_review_tag reviewrevi2_ on review0_.review_id=reviewrevi2_.review_id inner join review_tag reviewtag3_ on reviewrevi2_.review_tag_id=reviewtag3_.review_tag_id where review0_.program_id=? order by review0_.like_counts desc
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, reviewrevi2_.review_review_tag_id as review_r1_11_2_, reviewtag3_.review_tag_id as review_t1_12_3_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_, reviewrevi2_.review_id as review_i2_11_2_, reviewrevi2_.review_tag_id as review_t3_11_2_, reviewrevi2_.review_id as review_i2_11_0__, reviewrevi2_.review_review_tag_id as review_r1_11_0__, reviewtag3_.name as name2_12_3_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id inner join review_review_tag reviewrevi2_ on review0_.review_id=reviewrevi2_.review_id inner join review_tag reviewtag3_ on reviewrevi2_.review_tag_id=reviewtag3_.review_tag_id where review0_.program_id=73 order by review0_.like_counts desc;
2024-02-07 04:16:51.634 INFO 6868 --- [nio-8080-exec-1] p6spy : #1707247011634 | took 0ms | commit | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
;발생하는 쿼리 양 자체가 줄었지만
2024-02-07 04:16:51.619 WARN 6868 --- [nio-8080-exec-1] o.h.h.internal.ast.QueryTranslatorImpl : HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!페이징 처리를 메모리에서 한다는 Warning 을 발생시킨다.
JSON 데이터는 어떻게 나가는지 보자!
여기서 내가 헷갈렸던 지점이다.
나는 Review 와 ReviewReviewTag 이 1대다 이다 보니까 같은 Review 에 대한 Row 의 수가 ReviewReviewTag 의 개수만큼 테이블 Row 가 늘어날 줄 알았지만
{
"data": {
"reviewProgramResponseDtoList": [
{
"reviewId": 40,
"like": 0,
"contents": "너무 좋았습니다19",
"userPosterPath": null,
"userNickName": null,
"localDateTime": "2024-02-04T03:40:19.049805",
"ratings": "0.5",
"reviewTagNames": [
"돈이 아까워요",
"극장에서 또 보고 싶어요"
]
},
{
"reviewId": 39,
"like": 0,
"contents": "너무 좋았습니다18",
"userPosterPath": null,
"userNickName": null,
"localDateTime": "2024-02-04T03:40:19.044206",
"ratings": "1.0",
"reviewTagNames": [
"돈이 아까워요",
"극장에서 또 보고 싶어요"
]
},
{
"reviewId": 38,
"like": 0,
"contents": "너무 좋았습니다17",
"userPosterPath": null,
"userNickName": null,
"localDateTime": "2024-02-04T03:40:19.039263",
"ratings": "0.5",
"reviewTagNames": [
"돈이 아까워요",
"극장에서 또 보고 싶어요"
]
},
{
"reviewId": 37,
"like": 0,
"contents": "너무 좋았습니다16",
"userPosterPath": null,
"userNickName": null,
"localDateTime": "2024-02-04T03:40:19.032038",
"ratings": "3.0",
"reviewTagNames": [
"돈이 아까워요",
"극장에서 또 보고 싶어요"
]
}
],
"leftReviewCounts": 0
}
}JSON 데이터는 정상적이였다.
ReviewTagNames 가 두번씩 나왔기 때문에 데이터 자체가 두번씩 중복되는 것이 옳다고 생각했다.
실제로 위에서 발생한 쿼리를 찍어보면 (아래)
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, reviewrevi2_.review_review_tag_id as review_r1_11_2_, reviewtag3_.review_tag_id as review_t1_12_3_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_, reviewrevi2_.review_id as review_i2_11_2_, reviewrevi2_.review_tag_id as review_t3_11_2_, reviewrevi2_.review_id as review_i2_11_0__, reviewrevi2_.review_review_tag_id as review_r1_11_0__, reviewtag3_.name as name2_12_3_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id inner join review_review_tag reviewrevi2_ on review0_.review_id=reviewrevi2_.review_id inner join review_tag reviewtag3_ on reviewrevi2_.review_tag_id=reviewtag3_.review_tag_id where review0_.program_id=73 order by review0_.like_counts desc; 
이렇게 기존 Review 에 대한 테이블이 ReviewTagTag 의 개수만큼 늘어나 있는 것을 볼 수 있다.
만약에 Pageable을 빼서 Paging 처리를 하지 않으면 어떻게 될까???
{
"data": {
"reviewProgramResponseDtoList": [
{
"reviewId": 40,
"like": 0,
"contents": "너무 좋았습니다19",
"userPosterPath": null,
"userNickName": null,
"localDateTime": "2024-02-04T03:40:19.049805",
"ratings": "0.5",
"reviewTagNames": [
"돈이 아까워요",
"극장에서 또 보고 싶어요"
]
},
{
"reviewId": 40,
"like": 0,
"contents": "너무 좋았습니다19",
"userPosterPath": null,
"userNickName": null,
"localDateTime": "2024-02-04T03:40:19.049805",
"ratings": "0.5",
"reviewTagNames": [
"돈이 아까워요",
"극장에서 또 보고 싶어요"
]
},
{
"reviewId": 39,
"like": 0,
"contents": "너무 좋았습니다18",
"userPosterPath": null,
"userNickName": null,
"localDateTime": "2024-02-04T03:40:19.044206",
"ratings": "1.0",
"reviewTagNames": [
"돈이 아까워요",
"극장에서 또 보고 싶어요"
]
},
{
"reviewId": 39,
"like": 0,
"contents": "너무 좋았습니다18",
"userPosterPath": null,
"userNickName": null,
"localDateTime": "2024-02-04T03:40:19.044206",
"ratings": "1.0",
"reviewTagNames": [
"돈이 아까워요",
"극장에서 또 보고 싶어요"
]
},그럼 이렇게 데이터가 중복되는 것을 확인할 수 있다.
이렇게 데이터가 중복되는 것을 해결하려면 select 뒤에 distinct 를 붙이면 중복이 해결된다.
즉 페이징 처리를 하지 않고 1대다 fetch join 을 하고 싶을 때는 select distinct ... 를 활용해서 중복을 처리하는 것이 필수이다.
근데 왜 Pageable을 붙였을 때는 데이터가 중복이 해결되는지 잘 모르겠다.
하지만 확실한 것은 페이징처리 와 1대다 Fetch Join 을 사용하면 메모리에서 페이징 처리를 하기 때문에 위험하다는 것이다.
Review 와 ReviewReviewTag에 대한 성능 최적화
만일
@OneToMany(mappedBy = "review", cascade = CascadeType.ALL, orphanRemoval = true)
@BatchSize(size = 4)
private List<ReviewReviewTag> reviewReviewTags = new ArrayList<>();이렇게 BatchSize를 붙이면
4개의 Review 에 대한 ReviewReviewTag를 몽땅 가지고 온다.
쿼리를 확인하면
2024-02-07 04:56:04.986 INFO 15828 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2024-02-07 04:56:04.987 INFO 15828 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2024-02-07 04:56:04.988 INFO 15828 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Completed initialization in 1 ms
2024-02-07 04:56:05.003 INFO 15828 --- [nio-8080-exec-1] t.OTTify.oauth.jwt.JwtAuthFilter : checkAccessTokenAndAuthentication() 호출
2024-02-07 04:56:05.004 INFO 15828 --- [nio-8080-exec-1] tavebalak.OTTify.oauth.jwt.JwtService : extractAccessToken() 호출
Hibernate:
select
program0_.program_id as program_1_7_0_,
program0_.average_rating as average_2_7_0_,
program0_.created_year as created_3_7_0_,
program0_.poster_path as poster_p4_7_0_,
program0_.review_count as review_c5_7_0_,
program0_.title as title6_7_0_,
program0_.tm_db_program_id as tm_db_pr7_7_0_,
program0_.type as type8_7_0_
from
program program0_
where
program0_.program_id=?
2024-02-07 04:56:05.048 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365048 | took 1ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=?
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=73;
Hibernate:
select
review0_.review_id as review_i1_10_0_,
user1_.user_id as user_id1_14_1_,
review0_.created_at as created_2_10_0_,
review0_.updated_at as updated_3_10_0_,
review0_.content as content4_10_0_,
review0_.genre as genre5_10_0_,
review0_.like_counts as like_cou6_10_0_,
review0_.program_id as program_8_10_0_,
review0_.rating as rating7_10_0_,
review0_.user_id as user_id9_10_0_,
user1_.created_at as created_2_14_1_,
user1_.updated_at as updated_3_14_1_,
user1_.average_rating as average_4_14_1_,
user1_.code as code5_14_1_,
user1_.email as email6_14_1_,
user1_.grade as grade7_14_1_,
user1_.nick_name as nick_nam8_14_1_,
user1_.profile_photo as profile_9_14_1_,
user1_.role as role10_14_1_,
user1_.social_type as social_11_14_1_,
user1_.review_counts as review_12_14_1_
from
review review0_
inner join
`user` user1_
on review0_.user_id=user1_.user_id
where
review0_.program_id=?
order by
review0_.like_counts desc limit ?
2024-02-07 04:56:05.076 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365076 | took 1ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=? order by review0_.like_counts desc limit ?
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=73 order by review0_.like_counts desc limit 4;
Hibernate:
select
reviewrevi0_.review_id as review_i2_11_1_,
reviewrevi0_.review_review_tag_id as review_r1_11_1_,
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_
from
review_review_tag reviewrevi0_
where
reviewrevi0_.review_id in (
?, ?, ?, ?
)
2024-02-07 04:56:05.085 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365085 | took 0ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_id as review_i2_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_ from review_review_tag reviewrevi0_ where reviewrevi0_.review_id in (?, ?, ?, ?)
select reviewrevi0_.review_id as review_i2_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_ from review_review_tag reviewrevi0_ where reviewrevi0_.review_id in (40, 39, 38, 37);
Hibernate:
select
reviewtag0_.review_tag_id as review_t1_12_0_,
reviewtag0_.name as name2_12_0_
from
review_tag reviewtag0_
where
reviewtag0_.review_tag_id=?
2024-02-07 04:56:05.096 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365096 | took 0ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id=?
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id=3;
Hibernate:
select
reviewtag0_.review_tag_id as review_t1_12_0_,
reviewtag0_.name as name2_12_0_
from
review_tag reviewtag0_
where
reviewtag0_.review_tag_id=?
2024-02-07 04:56:05.098 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365098 | took 0ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id=?
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id=5;
2024-02-07 04:56:05.100 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365099 | took 0ms | commit | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
;Review 에서 ReviewReviewTag 에 대한 성능 최적화 부분은 해결되었지만
ReviewReviewTag 에서 ReviewTag 에 대한 성능 최적화 부분은 해결되지 못했다.
ReviewTag 에 대한 select 쿼리가 2개만 나온 이유는 모든 Review 의 ReviewTag를 같은 2개로 통일 시켰기 때문이다. 만약 일반적인 상황처럼 ReviewTag가 서로다른 여러개라면 그만큼의 쿼리가 또 나갈 것이다. 이 문제를 해결하려면 대편 다 쪽에도 역시 BatchSize를 설정해야 했다 .
Review 와 ReviewReviewTag 와 ReviewTag 에 대한 성능 최적화
아래와 같이 설정하면 된다.@BatchSize(size = 4)
@Entity
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class ReviewTag {Hibernate:
select
program0_.program_id as program_1_7_0_,
program0_.average_rating as average_2_7_0_,
program0_.created_year as created_3_7_0_,
program0_.poster_path as poster_p4_7_0_,
program0_.review_count as review_c5_7_0_,
program0_.title as title6_7_0_,
program0_.tm_db_program_id as tm_db_pr7_7_0_,
program0_.type as type8_7_0_
from
program program0_
where
program0_.program_id=?
2024-02-08 00:54:29.672 INFO 20988 --- [nio-8080-exec-8] p6spy : #1707321269672 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=?
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=73;
Hibernate:
select
review0_.review_id as review_i1_10_0_,
user1_.user_id as user_id1_14_1_,
review0_.created_at as created_2_10_0_,
review0_.updated_at as updated_3_10_0_,
review0_.content as content4_10_0_,
review0_.genre as genre5_10_0_,
review0_.like_counts as like_cou6_10_0_,
review0_.program_id as program_8_10_0_,
review0_.rating as rating7_10_0_,
review0_.user_id as user_id9_10_0_,
user1_.created_at as created_2_14_1_,
user1_.updated_at as updated_3_14_1_,
user1_.average_rating as average_4_14_1_,
user1_.code as code5_14_1_,
user1_.email as email6_14_1_,
user1_.grade as grade7_14_1_,
user1_.nick_name as nick_nam8_14_1_,
user1_.profile_photo as profile_9_14_1_,
user1_.role as role10_14_1_,
user1_.social_type as social_11_14_1_,
user1_.review_counts as review_12_14_1_
from
review review0_
inner join
`user` user1_
on review0_.user_id=user1_.user_id
where
review0_.program_id=?
order by
review0_.like_counts desc limit ?
2024-02-08 00:54:29.673 INFO 20988 --- [nio-8080-exec-8] p6spy : #1707321269673 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=? order by review0_.like_counts desc limit ?
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=73 order by review0_.like_counts desc limit 4;
Hibernate:
select
reviewrevi0_.review_id as review_i2_11_1_,
reviewrevi0_.review_review_tag_id as review_r1_11_1_,
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_
from
review_review_tag reviewrevi0_
where
reviewrevi0_.review_id in (
?, ?, ?, ?
)
2024-02-08 00:54:29.675 INFO 20988 --- [nio-8080-exec-8] p6spy : #1707321269675 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_id as review_i2_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_ from review_review_tag reviewrevi0_ where reviewrevi0_.review_id in (?, ?, ?, ?)
select reviewrevi0_.review_id as review_i2_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_ from review_review_tag reviewrevi0_ where reviewrevi0_.review_id in (40, 39, 38, 37);
Hibernate:
select
reviewtag0_.review_tag_id as review_t1_12_0_,
reviewtag0_.name as name2_12_0_
from
review_tag reviewtag0_
where
reviewtag0_.review_tag_id in (
?, ?
)
2024-02-08 00:54:29.679 INFO 20988 --- [nio-8080-exec-8] p6spy : #1707321269679 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id in (?, ?)
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id in (3, 5);
2024-02-08 00:54:29.680 INFO 20988 --- [nio-8080-exec-8] p6spy : #1707321269680 | took 0ms | commit | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
;이렇게 하면 최종적으로 1+N+N 이였던 쿼리를
최대 1+1+1까지 성능을 최적화할 수 있다.
최종 비교
Review 에서 User를 같이 Fetch Join 해서 가져오는 쿼리는 픽스하고
Review 와 ReviewTag 의 성능 최적화를 쿼리 위주로 비교하고 정리해보겠다.
중간 테이블 중심으로 성능 최적화 (기존 코드를 사용하였을 경우)
Hibernate:
select
program0_.program_id as program_1_7_0_,
program0_.average_rating as average_2_7_0_,
program0_.created_year as created_3_7_0_,
program0_.poster_path as poster_p4_7_0_,
program0_.review_count as review_c5_7_0_,
program0_.title as title6_7_0_,
program0_.tm_db_program_id as tm_db_pr7_7_0_,
program0_.type as type8_7_0_
from
program program0_
where
program0_.program_id=?
2024-02-07 04:06:58.592 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418592 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=?
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=73;
Hibernate:
select
review0_.review_id as review_i1_10_0_,
user1_.user_id as user_id1_14_1_,
review0_.created_at as created_2_10_0_,
review0_.updated_at as updated_3_10_0_,
review0_.content as content4_10_0_,
review0_.genre as genre5_10_0_,
review0_.like_counts as like_cou6_10_0_,
review0_.program_id as program_8_10_0_,
review0_.rating as rating7_10_0_,
review0_.user_id as user_id9_10_0_,
user1_.created_at as created_2_14_1_,
user1_.updated_at as updated_3_14_1_,
user1_.average_rating as average_4_14_1_,
user1_.code as code5_14_1_,
user1_.email as email6_14_1_,
user1_.grade as grade7_14_1_,
user1_.nick_name as nick_nam8_14_1_,
user1_.profile_photo as profile_9_14_1_,
user1_.role as role10_14_1_,
user1_.social_type as social_11_14_1_,
user1_.review_counts as review_12_14_1_
from
review review0_
inner join
`user` user1_
on review0_.user_id=user1_.user_id
where
review0_.program_id=?
order by
review0_.like_counts desc limit ?
2024-02-07 04:06:58.594 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418594 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=? order by review0_.like_counts desc limit ?
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=73 order by review0_.like_counts desc limit 4;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 04:06:58.596 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418596 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=40;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 04:06:58.598 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418598 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=39;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 04:06:58.600 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418600 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=38;
Hibernate:
select
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewtag1_.review_tag_id as review_t1_12_1_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_,
reviewtag1_.name as name2_12_1_
from
review_review_tag reviewrevi0_
inner join
review_tag reviewtag1_
on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id
where
reviewrevi0_.review_id=?
2024-02-07 04:06:58.601 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418601 | took 0ms | statement | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=?
select reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewtag1_.review_tag_id as review_t1_12_1_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_, reviewtag1_.name as name2_12_1_ from review_review_tag reviewrevi0_ inner join review_tag reviewtag1_ on reviewrevi0_.review_tag_id=reviewtag1_.review_tag_id where reviewrevi0_.review_id=37;
2024-02-07 04:06:58.602 INFO 17180 --- [nio-8080-exec-2] p6spy : #1707246418602 | took 0ms | commit | connection 6| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
;쿼리 설명 :
1) 프로그램 찾아오는 쿼리
2) User 와 Review 를 fetch Join 으로 함께 가져오는 쿼리
3) 4개의 Review 의 각각에 대해서 ReviewReviewTag 를 ReviewTag 와 fetch join 으로 함께 가져온다.
결론은
(프로그램 찾아오는 쿼리 + User 와 Review 를 fetch Join 으로 함께 가져오는 쿼리 + 가져올 Review 의 개수만큼 ReviewReivewTag 과 ReviewTag를 가져온다)
⇒ 1+ 1+ 가져올 Review 개수만큼의 쿼리
Review 에서 @OneToMany 에만 @BatchSize 붙인다면?
2024-02-07 04:56:04.986 INFO 15828 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring DispatcherServlet 'dispatcherServlet'
2024-02-07 04:56:04.987 INFO 15828 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Initializing Servlet 'dispatcherServlet'
2024-02-07 04:56:04.988 INFO 15828 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet : Completed initialization in 1 ms
2024-02-07 04:56:05.003 INFO 15828 --- [nio-8080-exec-1] t.OTTify.oauth.jwt.JwtAuthFilter : checkAccessTokenAndAuthentication() 호출
2024-02-07 04:56:05.004 INFO 15828 --- [nio-8080-exec-1] tavebalak.OTTify.oauth.jwt.JwtService : extractAccessToken() 호출
Hibernate:
select
program0_.program_id as program_1_7_0_,
program0_.average_rating as average_2_7_0_,
program0_.created_year as created_3_7_0_,
program0_.poster_path as poster_p4_7_0_,
program0_.review_count as review_c5_7_0_,
program0_.title as title6_7_0_,
program0_.tm_db_program_id as tm_db_pr7_7_0_,
program0_.type as type8_7_0_
from
program program0_
where
program0_.program_id=?
2024-02-07 04:56:05.048 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365048 | took 1ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=?
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=73;
Hibernate:
select
review0_.review_id as review_i1_10_0_,
user1_.user_id as user_id1_14_1_,
review0_.created_at as created_2_10_0_,
review0_.updated_at as updated_3_10_0_,
review0_.content as content4_10_0_,
review0_.genre as genre5_10_0_,
review0_.like_counts as like_cou6_10_0_,
review0_.program_id as program_8_10_0_,
review0_.rating as rating7_10_0_,
review0_.user_id as user_id9_10_0_,
user1_.created_at as created_2_14_1_,
user1_.updated_at as updated_3_14_1_,
user1_.average_rating as average_4_14_1_,
user1_.code as code5_14_1_,
user1_.email as email6_14_1_,
user1_.grade as grade7_14_1_,
user1_.nick_name as nick_nam8_14_1_,
user1_.profile_photo as profile_9_14_1_,
user1_.role as role10_14_1_,
user1_.social_type as social_11_14_1_,
user1_.review_counts as review_12_14_1_
from
review review0_
inner join
`user` user1_
on review0_.user_id=user1_.user_id
where
review0_.program_id=?
order by
review0_.like_counts desc limit ?
2024-02-07 04:56:05.076 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365076 | took 1ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=? order by review0_.like_counts desc limit ?
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=73 order by review0_.like_counts desc limit 4;
Hibernate:
select
reviewrevi0_.review_id as review_i2_11_1_,
reviewrevi0_.review_review_tag_id as review_r1_11_1_,
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_
from
review_review_tag reviewrevi0_
where
reviewrevi0_.review_id in (
?, ?, ?, ?
)
2024-02-07 04:56:05.085 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365085 | took 0ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_id as review_i2_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_ from review_review_tag reviewrevi0_ where reviewrevi0_.review_id in (?, ?, ?, ?)
select reviewrevi0_.review_id as review_i2_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_ from review_review_tag reviewrevi0_ where reviewrevi0_.review_id in (40, 39, 38, 37);
Hibernate:
select
reviewtag0_.review_tag_id as review_t1_12_0_,
reviewtag0_.name as name2_12_0_
from
review_tag reviewtag0_
where
reviewtag0_.review_tag_id=?
2024-02-07 04:56:05.096 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365096 | took 0ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id=?
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id=3;
Hibernate:
select
reviewtag0_.review_tag_id as review_t1_12_0_,
reviewtag0_.name as name2_12_0_
from
review_tag reviewtag0_
where
reviewtag0_.review_tag_id=?
2024-02-07 04:56:05.098 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365098 | took 0ms | statement | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id=?
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id=5;
2024-02-07 04:56:05.100 INFO 15828 --- [nio-8080-exec-1] p6spy : #1707249365099 | took 0ms | commit | connection 4| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
;1) 프로그램 찾아오는 쿼리
2) User 와 Review 를 fetch Join 으로 함께 가져오는 쿼리
3) BatchSize 만큼의 Review 에 대해서 그만큼의 ReviewReviewTag를 몽땅 가져온다 .
4) 각각의 Review_review_tag에서 get 을 통해 Review_Tag 의 이름을 호출하면 1차 캐시에 없는 것을 기준으로 select 쿼리로 review_tag 을 가져온다. 새로운 review_tag 가 많을 수록 그 만큼 Review_tag에 대한 쿼리는 더 나간다 .
(프로그램 찾아오는 쿼리 + User 와 Review 를 fetch Join 으로 함께 가져오는 쿼리 + Review 에 대해서 ReviewReviewTag 를 가져오는 쿼리 + ReviewReviewTag 로 부터 ReviewTag 의 이름을 호출할 때 새로운 것에 대한 select 쿼리 )
Review 에서 @OneToMany 에 @BatchSize + 반대편에도 Entity에도 @BatchSize 작성해 주기
Hibernate:
select
program0_.program_id as program_1_7_0_,
program0_.average_rating as average_2_7_0_,
program0_.created_year as created_3_7_0_,
program0_.poster_path as poster_p4_7_0_,
program0_.review_count as review_c5_7_0_,
program0_.title as title6_7_0_,
program0_.tm_db_program_id as tm_db_pr7_7_0_,
program0_.type as type8_7_0_
from
program program0_
where
program0_.program_id=?
2024-02-08 00:54:29.672 INFO 20988 --- [nio-8080-exec-8] p6spy : #1707321269672 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=?
select program0_.program_id as program_1_7_0_, program0_.average_rating as average_2_7_0_, program0_.created_year as created_3_7_0_, program0_.poster_path as poster_p4_7_0_, program0_.review_count as review_c5_7_0_, program0_.title as title6_7_0_, program0_.tm_db_program_id as tm_db_pr7_7_0_, program0_.type as type8_7_0_ from program program0_ where program0_.program_id=73;
Hibernate:
select
review0_.review_id as review_i1_10_0_,
user1_.user_id as user_id1_14_1_,
review0_.created_at as created_2_10_0_,
review0_.updated_at as updated_3_10_0_,
review0_.content as content4_10_0_,
review0_.genre as genre5_10_0_,
review0_.like_counts as like_cou6_10_0_,
review0_.program_id as program_8_10_0_,
review0_.rating as rating7_10_0_,
review0_.user_id as user_id9_10_0_,
user1_.created_at as created_2_14_1_,
user1_.updated_at as updated_3_14_1_,
user1_.average_rating as average_4_14_1_,
user1_.code as code5_14_1_,
user1_.email as email6_14_1_,
user1_.grade as grade7_14_1_,
user1_.nick_name as nick_nam8_14_1_,
user1_.profile_photo as profile_9_14_1_,
user1_.role as role10_14_1_,
user1_.social_type as social_11_14_1_,
user1_.review_counts as review_12_14_1_
from
review review0_
inner join
`user` user1_
on review0_.user_id=user1_.user_id
where
review0_.program_id=?
order by
review0_.like_counts desc limit ?
2024-02-08 00:54:29.673 INFO 20988 --- [nio-8080-exec-8] p6spy : #1707321269673 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=? order by review0_.like_counts desc limit ?
select review0_.review_id as review_i1_10_0_, user1_.user_id as user_id1_14_1_, review0_.created_at as created_2_10_0_, review0_.updated_at as updated_3_10_0_, review0_.content as content4_10_0_, review0_.genre as genre5_10_0_, review0_.like_counts as like_cou6_10_0_, review0_.program_id as program_8_10_0_, review0_.rating as rating7_10_0_, review0_.user_id as user_id9_10_0_, user1_.created_at as created_2_14_1_, user1_.updated_at as updated_3_14_1_, user1_.average_rating as average_4_14_1_, user1_.code as code5_14_1_, user1_.email as email6_14_1_, user1_.grade as grade7_14_1_, user1_.nick_name as nick_nam8_14_1_, user1_.profile_photo as profile_9_14_1_, user1_.role as role10_14_1_, user1_.social_type as social_11_14_1_, user1_.review_counts as review_12_14_1_ from review review0_ inner join `user` user1_ on review0_.user_id=user1_.user_id where review0_.program_id=73 order by review0_.like_counts desc limit 4;
Hibernate:
select
reviewrevi0_.review_id as review_i2_11_1_,
reviewrevi0_.review_review_tag_id as review_r1_11_1_,
reviewrevi0_.review_review_tag_id as review_r1_11_0_,
reviewrevi0_.review_id as review_i2_11_0_,
reviewrevi0_.review_tag_id as review_t3_11_0_
from
review_review_tag reviewrevi0_
where
reviewrevi0_.review_id in (
?, ?, ?, ?
)
2024-02-08 00:54:29.675 INFO 20988 --- [nio-8080-exec-8] p6spy : #1707321269675 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewrevi0_.review_id as review_i2_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_ from review_review_tag reviewrevi0_ where reviewrevi0_.review_id in (?, ?, ?, ?)
select reviewrevi0_.review_id as review_i2_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_1_, reviewrevi0_.review_review_tag_id as review_r1_11_0_, reviewrevi0_.review_id as review_i2_11_0_, reviewrevi0_.review_tag_id as review_t3_11_0_ from review_review_tag reviewrevi0_ where reviewrevi0_.review_id in (40, 39, 38, 37);
Hibernate:
select
reviewtag0_.review_tag_id as review_t1_12_0_,
reviewtag0_.name as name2_12_0_
from
review_tag reviewtag0_
where
reviewtag0_.review_tag_id in (
?, ?
)
2024-02-08 00:54:29.679 INFO 20988 --- [nio-8080-exec-8] p6spy : #1707321269679 | took 0ms | statement | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id in (?, ?)
select reviewtag0_.review_tag_id as review_t1_12_0_, reviewtag0_.name as name2_12_0_ from review_tag reviewtag0_ where reviewtag0_.review_tag_id in (3, 5);
2024-02-08 00:54:29.680 INFO 20988 --- [nio-8080-exec-8] p6spy : #1707321269680 | took 0ms | commit | connection 5| url jdbc:mysql://localhost:3306/ottifyjinu?serverTimezone=Asia/Seoul&characterEncoding=UTF-8
;프로그램에 대한 쿼리+ (User + Review 에 대한 쿼리) + ReviewReviewTag 에 대한 쿼리 + 그에 맞춘 ReviewTag 에 대한 쿼리
=⇒성능이게 무조건 낫다.
결국...
위와 같은 과정을 통해서
Program 과 Review는 함께 가져오지 않는 것으로 했고
Review 와 User 는 함께 가져오는 것으로 정했으며
Review 와 ReviewReviewTag ,ReviewTag 를 BatchSize를 활용해서 성능 최적화 하기로 결정했다.
기록을 통해 실력을 쌓아가자