기존 소스

@EntityGraph(attributePaths = {"responseEntitySet","responseEntitySet.questionOptionEntity","fileEntitySet"})
Page<UserEntity> findAllByRole(Pageable pageable, UserRole role);

• @EntityGraph를 이용하여 한번에 fetch해 오고 있었다.
• 이렇게 해도 작동에 이상이 없어 그대로 사용하던 중, 다른 오류때문에 검색하다가 이 방식이 efficient 하지 않다는 것을 알게 되었다.

Hibernate: 
    select
        userentity0_.id as id1_7_0_,
        responseen1_.id as id1_4_1_,
        questionop2_.id as id1_3_2_,
        fileentity3_.id as id1_0_3_,
        userentity0_.created_at as created_2_7_0_,
        userentity0_.modified_at as modified3_7_0_,
        userentity0_.age as age4_7_0_,
        userentity0_.alcohol as alcohol5_7_0_,
        userentity0_.alcohol_intake as alcohol_6_7_0_,
        userentity0_.alcohol_per_week as alcohol_7_7_0_,
        userentity0_.cigarette as cigarett8_7_0_,
        userentity0_.cigarette_per_day as cigarett9_7_0_,
        userentity0_.education_type as educati10_7_0_,
        userentity0_.education_year as educati11_7_0_,
        userentity0_.finished_at as finishe12_7_0_,
        userentity0_.gender as gender13_7_0_,
        userentity0_.job as job14_7_0_,
        userentity0_.location as locatio15_7_0_,
        userentity0_.login_id as login_i16_7_0_,
        userentity0_.marital_status as marital17_7_0_,
        userentity0_.name as name18_7_0_,
        userentity0_.password as passwor19_7_0_,
        userentity0_.religion as religio20_7_0_,
        userentity0_.role as role21_7_0_,
        userentity0_.token as token22_7_0_,
        userentity0_.zoom_url as zoom_ur23_7_0_,
        responseen1_.created_at as created_2_4_1_,
        responseen1_.modified_at as modified3_4_1_,
        responseen1_.question_option_id as question5_4_1_,
        responseen1_.user_id as user_id4_4_1_,
        responseen1_.user_id as user_id4_4_0__,
        responseen1_.id as id1_4_0__,
        questionop2_.created_at as created_2_3_2_,
        questionop2_.modified_at as modified3_3_2_,
        questionop2_.degree as degree4_3_2_,
        questionop2_.description as descript5_3_2_,
        questionop2_.question_id as question6_3_2_,
        fileentity3_.created_at as created_2_0_3_,
        fileentity3_.modified_at as modified3_0_3_,
        fileentity3_.fancy_size as fancy_si4_0_3_,
        fileentity3_.mime as mime5_0_3_,
        fileentity3_.name as name6_0_3_,
        fileentity3_.size as size7_0_3_,
        fileentity3_.task as task8_0_3_,
        fileentity3_.user_id as user_id9_0_3_,
        fileentity3_.user_id as user_id9_0_1__,
        fileentity3_.id as id1_0_1__ 
    from
        user userentity0_ 
    left outer join
        response responseen1_ 
            on userentity0_.id=responseen1_.user_id 
    left outer join
        question_option questionop2_ 
            on responseen1_.question_option_id=questionop2_.id 
    left outer join
        file fileentity3_ 
            on userentity0_.id=fileentity3_.user_id 
    where
        userentity0_.role=? 
    order by
        userentity0_.login_id asc

• application.properties에서 다음 설정을 적용하면 위의 쿼리를 볼 수 있다
  • spring.jpa.show-sql=true
  • spring.jpa.properties.hibernate.format_sql=true
• 쿼리가 excute될 때, 요청한 페이지만큼 조회하는 것이 아닌 전체조회를 하고있다.
• 전체조회 결과를 in-memory에 두고 나중에 paging처리를 해서 반환하기때문에 페이징된 결과를 받아볼 수 있었던 것이다.

수정한 소스

1단계: @EntityGraph 어노테이션 제거

user 조회 쿼리

Hibernate: 
    select
        userentity0_.id as id1_7_,
        userentity0_.created_at as created_2_7_,
        userentity0_.modified_at as modified3_7_,
        userentity0_.age as age4_7_,
        userentity0_.alcohol as alcohol5_7_,
        userentity0_.alcohol_intake as alcohol_6_7_,
        userentity0_.alcohol_per_week as alcohol_7_7_,
        userentity0_.cigarette as cigarett8_7_,
        userentity0_.cigarette_per_day as cigarett9_7_,
        userentity0_.education_type as educati10_7_,
        userentity0_.education_year as educati11_7_,
        userentity0_.finished_at as finishe12_7_,
        userentity0_.gender as gender13_7_,
        userentity0_.job as job14_7_,
        userentity0_.location as locatio15_7_,
        userentity0_.login_id as login_i16_7_,
        userentity0_.marital_status as marital17_7_,
        userentity0_.name as name18_7_,
        userentity0_.password as passwor19_7_,
        userentity0_.religion as religio20_7_,
        userentity0_.role as role21_7_,
        userentity0_.token as token22_7_,
        userentity0_.zoom_url as zoom_ur23_7_ 
    from
        user userentity0_ 
    where
        userentity0_.role=? 
    order by
        userentity0_.login_id asc limit ?

• limit 절이 적용됨

response, question_option, file 조회 쿼리 (N+1)

• response, file: 조회된 user의 수만큼 n+1 발생
• question_option: response의 수만큼 n+1 발생

response

Hibernate: 
    select
        responseen0_.user_id as user_id4_4_0_,
        responseen0_.id as id1_4_0_,
        responseen0_.id as id1_4_1_,
        responseen0_.created_at as created_2_4_1_,
        responseen0_.modified_at as modified3_4_1_,
        responseen0_.question_option_id as question5_4_1_,
        responseen0_.user_id as user_id4_4_1_ 
    from
        response responseen0_ 
    where
        responseen0_.user_id=?

question_option

Hibernate: 
    select
        questionop0_.id as id1_3_0_,
        questionop0_.created_at as created_2_3_0_,
        questionop0_.modified_at as modified3_3_0_,
        questionop0_.degree as degree4_3_0_,
        questionop0_.description as descript5_3_0_,
        questionop0_.question_id as question6_3_0_ 
    from
        question_option questionop0_ 
    where
        questionop0_.id=?

file

Hibernate: 
    select
        fileentity0_.user_id as user_id9_0_0_,
        fileentity0_.id as id1_0_0_,
        fileentity0_.id as id1_0_1_,
        fileentity0_.created_at as created_2_0_1_,
        fileentity0_.modified_at as modified3_0_1_,
        fileentity0_.fancy_size as fancy_si4_0_1_,
        fileentity0_.mime as mime5_0_1_,
        fileentity0_.name as name6_0_1_,
        fileentity0_.size as size7_0_1_,
        fileentity0_.task as task8_0_1_,
        fileentity0_.user_id as user_id9_0_1_ 
    from
        file fileentity0_ 
    where
        fileentity0_.user_id=?

2단계(1): application.properties 수정

• spring.jpa.properties.hibernate.default_batch_fetch_size=1000
• response, file와 같이 Many 테이블을 in으로 조회해온다.

response

Hibernate:
    select
        responseen0_.user_id as user_id4_4_1_,
        responseen0_.id as id1_4_1_,
        responseen0_.id as id1_4_0_,
        responseen0_.created_at as created_2_4_0_,
        responseen0_.modified_at as modified3_4_0_,
        responseen0_.question_option_id as question5_4_0_,
        responseen0_.user_id as user_id4_4_0_ 
    from
        response responseen0_ 
    where
        responseen0_.user_id in (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )

file

Hibernate: 
    select
        fileentity0_.user_id as user_id9_0_1_,
        fileentity0_.id as id1_0_1_,
        fileentity0_.id as id1_0_0_,
        fileentity0_.created_at as created_2_0_0_,
        fileentity0_.modified_at as modified3_0_0_,
        fileentity0_.fancy_size as fancy_si4_0_0_,
        fileentity0_.mime as mime5_0_0_,
        fileentity0_.name as name6_0_0_,
        fileentity0_.size as size7_0_0_,
        fileentity0_.task as task8_0_0_,
        fileentity0_.user_id as user_id9_0_0_ 
    from
        file fileentity0_ 
    where
        fileentity0_.user_id in (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )

2단계(2): @BatchSize 어노테이션 적용

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name="user_id")
@BatchSize(size=100)
private Set<ResponseEntity> responseEntitySet = new LinkedHashSet<>();

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name="user_id")
@BatchSize(size=100)
private Set<FileEntity> fileEntitySet = new LinkedHashSet<>();

결론

• @EntityGraph를 이용한 페이징
  • page 값과 상관 없이 전체결과를 조회한 후, 나중에 페이징 처리를 한다
• @EntityGraph 없이 페이징
  • limit을 이용해 페이지만큼 조회하지만, n+1이 발생한다
• @EntityGraph 없이 페이징 + default_batch_fetch_size 혹ㅡㄴ @BatchSize 설정
  • limit을 이용해 조회하고, n+1 없이 in 절을 활용해 조회한다.

참고

• https://coco-log.tistory.com/127
• https://stackoverflow.com/questions/21162172/default-batch-fetch-size-recommended-values