7장-3. 일대다 연관관계의 N+1 문제와 @BatchSize

요구사항

• @BatchSize : N+1 문제의 해결
• Tuple 이용 : 중첩된 엔티티 객체를 DTO로 변환하는 방식

---

N+1 문제의 발생

충분히 많은 데이터가 존재하는 경우 목록을 처리하는 과정에서 'N+1'문제가 발생할 수 있다.

 

(1) 더미 데이터 추가

-Board, BoardImage, Reply에 더미 데이터 추가

//N+1문제 발생을 위해 더미데이터 추가
@Test
public void testInsertAll() {
    for (int i = 1; i <= 100; i++) {
        Board board = Board.builder()
                .title("Title..." + i)
                .content("Content..." + i)
                .writer("writer..." + i)
                .build();

        for (int j = 0; j < 3; j++) {
            if (i % 5 == 0) {
                continue;
            }
            board.addImage(UUID.randomUUID().toString(), i+"file"+j+".jpg");
        }
        boardRepository.save(board);
    }
}

 

(2) 동적쿼리를 이용해 목록 데이터를 처리하는 메서드 작성

-Querydsl를 이용해 BoardSearch 인터페이스에 searchWithAll() 메서드 작성 후 구현

-모든 게시물과 댓글을 출력하는 메서드

 

//검색 조건 없이 페이징 처리한 게시물과 댓글 외부 조인
    @Override
    public Page<BoardListReplyDTO> searchWithAll(String[] types, String keyword, Pageable pageable) {
        QBoard board=QBoard.board;
        QReply reply = QReply.reply;

        //from board
        JPQLQuery<Board> boardJPQLQuery=from(board);
        //left join reply on board=reply.board
        boardJPQLQuery.leftJoin(reply).on(reply.board.eq(board));

        //paging
        getQuerydsl().applyPagination(pageable,boardJPQLQuery);

        //쿼리 결과 저장
        List<Board> boardList=boardJPQLQuery.fetch();

        boardList.forEach(board1 ->{
            System.out.println(board1.getBno());
            System.out.println(board1.getImageSet());
            System.out.println("----------");
        });

        return null;
    }

 

(3) 테스트 코드 작성

//N+1문제 발생을 위한 동적쿼리를 이용한 조인 수행
@Transactional
@Test
public void testSearchWithAll(){
    Pageable pageable=PageRequest.of(0,10,Sort.by("bno").descending());
    //검색 조건 없이 페이징 처리한 게시물과 댓글 외부 조인
    boardRepository.searchWithAll(null, null, pageable);
}

Hibernate:      select         board0_.bno as bno1_0_,         board0_.moddate as moddate2_0_,         board0_.regdate as regdate3_0_,         board0_.content as content4_0_,         board0_.title as title5_0_,         board0_.writer as writer6_0_      from         board board0_      left outer join         reply reply1_              on (                 reply1_.board_bno=board0_.bno             )      order by         board0_.bno desc limit ? 100 Hibernate:      select         imageset0_.board_bno as board_bn4_1_0_,         imageset0_.uuid as uuid1_1_0_,         imageset0_.uuid as uuid1_1_1_,         imageset0_.board_bno as board_bn4_1_1_,         imageset0_.file_name as file_nam2_1_1_,         imageset0_.ord as ord3_1_1_      from         board_image imageset0_      where         imageset0_.board_bno=? [] ---------- 99 Hibernate:      select         imageset0_.board_bno as board_bn4_1_0_,         imageset0_.uuid as uuid1_1_0_,         imageset0_.uuid as uuid1_1_1_,         imageset0_.board_bno as board_bn4_1_1_,         imageset0_.file_name as file_nam2_1_1_,         imageset0_.ord as ord3_1_1_      from         board_image imageset0_      where         imageset0_.board_bno=? [BoardImage(uuid=0ac861db-3d1f-454c-81e0-2841dbdd9224, fileName=99file1.jpg, ord=1), BoardImage(uuid=166bc341-d418-409f-9cc1-a1c0a7fd34e1, fileName=99file0.jpg, ord=0), BoardImage(uuid=2c7cbc1f-4e64-4c19-94cb-169fc0fd9d0c, fileName=99file2.jpg, ord=2)] ---------- 98 Hibernate:      select         imageset0_.board_bno as board_bn4_1_0_,         imageset0_.uuid as uuid1_1_0_,         imageset0_.uuid as uuid1_1_1_,         imageset0_.board_bno as board_bn4_1_1_,         imageset0_.file_name as file_nam2_1_1_,         imageset0_.ord as ord3_1_1_      from         board_image imageset0_      where         imageset0_.board_bno=? [BoardImage(uuid=052b64ef-6cfd-4d4f-a520-9eb4e8f0c3e2, fileName=98file1.jpg, ord=1), BoardImage(uuid=663faf16-dd3b-4ef0-bfb0-4b0af5a3f7da, fileName=98file0.jpg, ord=0), BoardImage(uuid=5871f779-f79c-4f51-90a9-5f05c03b00b4, fileName=98file2.jpg, ord=2)] ---------- 97 Hibernate:      select         imageset0_.board_bno as board_bn4_1_0_,         imageset0_.uuid as uuid1_1_0_,         imageset0_.uuid as uuid1_1_1_,         imageset0_.board_bno as board_bn4_1_1_,         imageset0_.file_name as file_nam2_1_1_,         imageset0_.ord as ord3_1_1_      from         board_image imageset0_      where         imageset0_.board_bno=? [BoardImage(uuid=70394339-a843-4422-b168-159b84feb0bc, fileName=97file1.jpg, ord=1), BoardImage(uuid=b836f543-2512-4837-8347-394d3b8046e8, fileName=97file2.jpg, ord=2), BoardImage(uuid=ec2bbfc1-ebb3-4574-99f3-82f51fc19894, fileName=97file0.jpg, ord=0)] ---------- 96 Hibernate:      select         imageset0_.board_bno as board_bn4_1_0_,         imageset0_.uuid as uuid1_1_0_,         imageset0_.uuid as uuid1_1_1_,         imageset0_.board_bno as board_bn4_1_1_,         imageset0_.file_name as file_nam2_1_1_,         imageset0_.ord as ord3_1_1_      from         board_image imageset0_      where         imageset0_.board_bno=? [BoardImage(uuid=3c5d8331-879e-46ef-a151-d5349ede6826, fileName=96file1.jpg, ord=1), BoardImage(uuid=5c7bf4d8-527a-4028-befc-9a4dd21508e5, fileName=96file0.jpg, ord=0), BoardImage(uuid=9ed6cfdb-c03e-42a9-95a5-307b2441a6f8, fileName=96file2.jpg, ord=2)] ---------- 95 Hibernate:      select         imageset0_.board_bno as board_bn4_1_0_,         imageset0_.uuid as uuid1_1_0_,         imageset0_.uuid as uuid1_1_1_,         imageset0_.board_bno as board_bn4_1_1_,         imageset0_.file_name as file_nam2_1_1_,         imageset0_.ord as ord3_1_1_      from         board_image imageset0_      where         imageset0_.board_bno=? [] ---------- 94 Hibernate:      select         imageset0_.board_bno as board_bn4_1_0_,         imageset0_.uuid as uuid1_1_0_,         imageset0_.uuid as uuid1_1_1_,         imageset0_.board_bno as board_bn4_1_1_,         imageset0_.file_name as file_nam2_1_1_,         imageset0_.ord as ord3_1_1_      from         board_image imageset0_      where         imageset0_.board_bno=? [BoardImage(uuid=b9cc38c1-e38e-4694-ae6c-8c1ab964740b, fileName=94file1.jpg, ord=1), BoardImage(uuid=1738f919-898f-49eb-b3d1-6f7cc3dae3f4, fileName=94file0.jpg, ord=0), BoardImage(uuid=295d6e74-9c26-4343-b981-39e89d0694fa, fileName=94file2.jpg, ord=2)] ---------- 93 Hibernate:      select         imageset0_.board_bno as board_bn4_1_0_,         imageset0_.uuid as uuid1_1_0_,         imageset0_.uuid as uuid1_1_1_,         imageset0_.board_bno as board_bn4_1_1_,         imageset0_.file_name as file_nam2_1_1_,         imageset0_.ord as ord3_1_1_      from         board_image imageset0_      where         imageset0_.board_bno=? [BoardImage(uuid=2fdd3551-795e-460b-bd23-282281afc960, fileName=93file0.jpg, ord=0), BoardImage(uuid=f49faa68-81d1-435a-aef0-51be407f0f1c, fileName=93file2.jpg, ord=2), BoardImage(uuid=aeafada7-74fe-4bb7-bf9c-2614b229d0ad, fileName=93file1.jpg, ord=1)] ---------- 92 Hibernate:      select         imageset0_.board_bno as board_bn4_1_0_,         imageset0_.uuid as uuid1_1_0_,         imageset0_.uuid as uuid1_1_1_,         imageset0_.board_bno as board_bn4_1_1_,         imageset0_.file_name as file_nam2_1_1_,         imageset0_.ord as ord3_1_1_      from         board_image imageset0_      where         imageset0_.board_bno=? [BoardImage(uuid=2e5dc656-fb82-4996-9985-c799c2f579dc, fileName=92file2.jpg, ord=2), BoardImage(uuid=36ca866a-dd2d-4f9e-af3b-844ff68a4cad, fileName=92file1.jpg, ord=1), BoardImage(uuid=d265c0d5-4ccc-4cf3-82fb-9f466f32d43a, fileName=92file0.jpg, ord=0)] ---------- 91 Hibernate:      select         imageset0_.board_bno as board_bn4_1_0_,         imageset0_.uuid as uuid1_1_0_,         imageset0_.uuid as uuid1_1_1_,         imageset0_.board_bno as board_bn4_1_1_,         imageset0_.file_name as file_nam2_1_1_,         imageset0_.ord as ord3_1_1_      from         board_image imageset0_      where         imageset0_.board_bno=?

목록 출력 쿼리 1 : 하나의 게시물마다 게시물의 이미지 쿼리 N

즉, 목록 출력할 때마다 모든 게시물 한번씩 접근해 이미지를 가져온다.

1. Board에 대해 limit을 이용해 페이질 처리
2. Board의 bno값 출력
3. Board 객체의 bno에 해당하는 board_image테이블을 조회해 imageSet을 가져오는 쿼리 실행
4. bno값 출력 -> bno에 해당하는 imageSet 가져오는 쿼리 실행 반복

-> 엄청난 성능 저하

---

N+1 문제의 해결

@BatchSize : size라는 속성을 지정해 'N번'에 해당하는 쿼리를 한번에 수행

즉, 게시물마다 게시물의 이미지를 가져오는 쿼리를 묶어서 한번에 수행

 

1. 상위 엔티티인 Board 클래스의 하위 엔티티에 해당하는 필드 ImageSet에 @BatchSize 적용

-20개의 게시물에 해당하는 이미지를 한번에 쿼리

//하위 엔티티 필드 생성
//: 중복된 이미지를 제거하기 위한 HashSet 자료구조 사용
//mappedBy 속성으로 연관관계의 주인 명시함으로써, 매핑테이블 생성하지 않고 양방향 참조관계 설정
//cascade 속성으로 영속성 전이 단계 설정
//fetch 속성으로 언제 fetch할지 설정
@OneToMany(mappedBy = "board", cascade = {CascadeType.ALL}, fetch = FetchType.LAZY, orphanRemoval = true)
@Builder.Default
@BatchSize(size=20)
private Set<BoardImage> imageSet=new HashSet<>();

 

Hibernate:      select         board0_.bno as bno1_0_,         board0_.moddate as moddate2_0_,         board0_.regdate as regdate3_0_,         board0_.content as content4_0_,         board0_.title as title5_0_,         board0_.writer as writer6_0_      from         board board0_      left outer join         reply reply1_              on (                 reply1_.board_bno=board0_.bno             )      order by         board0_.bno desc limit ? 100 Hibernate:      select         imageset0_.board_bno as board_bn4_1_1_,         imageset0_.uuid as uuid1_1_1_,         imageset0_.uuid as uuid1_1_0_,         imageset0_.board_bno as board_bn4_1_0_,         imageset0_.file_name as file_nam2_1_0_,         imageset0_.ord as ord3_1_0_      from         board_image imageset0_      where         imageset0_.board_bno in (             ?, ?, ?, ?, ?, ?, ?, ?, ?, ?         ) [] ---------- 99 [BoardImage(uuid=166bc341-d418-409f-9cc1-a1c0a7fd34e1, fileName=99file0.jpg, ord=0), BoardImage(uuid=2c7cbc1f-4e64-4c19-94cb-169fc0fd9d0c, fileName=99file2.jpg, ord=2), BoardImage(uuid=0ac861db-3d1f-454c-81e0-2841dbdd9224, fileName=99file1.jpg, ord=1)] ---------- 98 [BoardImage(uuid=5871f779-f79c-4f51-90a9-5f05c03b00b4, fileName=98file2.jpg, ord=2), BoardImage(uuid=052b64ef-6cfd-4d4f-a520-9eb4e8f0c3e2, fileName=98file1.jpg, ord=1), BoardImage(uuid=663faf16-dd3b-4ef0-bfb0-4b0af5a3f7da, fileName=98file0.jpg, ord=0)] ---------- 97 [BoardImage(uuid=b836f543-2512-4837-8347-394d3b8046e8, fileName=97file2.jpg, ord=2), BoardImage(uuid=ec2bbfc1-ebb3-4574-99f3-82f51fc19894, fileName=97file0.jpg, ord=0), BoardImage(uuid=70394339-a843-4422-b168-159b84feb0bc, fileName=97file1.jpg, ord=1)] ---------- 96 [BoardImage(uuid=5c7bf4d8-527a-4028-befc-9a4dd21508e5, fileName=96file0.jpg, ord=0), BoardImage(uuid=9ed6cfdb-c03e-42a9-95a5-307b2441a6f8, fileName=96file2.jpg, ord=2), BoardImage(uuid=3c5d8331-879e-46ef-a151-d5349ede6826, fileName=96file1.jpg, ord=1)] ---------- 95 [] ---------- 94 [BoardImage(uuid=1738f919-898f-49eb-b3d1-6f7cc3dae3f4, fileName=94file0.jpg, ord=0), BoardImage(uuid=295d6e74-9c26-4343-b981-39e89d0694fa, fileName=94file2.jpg, ord=2), BoardImage(uuid=b9cc38c1-e38e-4694-ae6c-8c1ab964740b, fileName=94file1.jpg, ord=1)] ---------- 93 [BoardImage(uuid=2fdd3551-795e-460b-bd23-282281afc960, fileName=93file0.jpg, ord=0), BoardImage(uuid=aeafada7-74fe-4bb7-bf9c-2614b229d0ad, fileName=93file1.jpg, ord=1), BoardImage(uuid=f49faa68-81d1-435a-aef0-51be407f0f1c, fileName=93file2.jpg, ord=2)] ---------- 92 [BoardImage(uuid=36ca866a-dd2d-4f9e-af3b-844ff68a4cad, fileName=92file1.jpg, ord=1), BoardImage(uuid=2e5dc656-fb82-4996-9985-c799c2f579dc, fileName=92file2.jpg, ord=2), BoardImage(uuid=d265c0d5-4ccc-4cf3-82fb-9f466f32d43a, fileName=92file0.jpg, ord=0)] ---------- 91 [BoardImage(uuid=08c691e7-616a-4b2a-82a5-3bc8293ebc89, fileName=91file2.jpg, ord=2), BoardImage(uuid=8cf72165-71cc-4612-94c7-650adc80b702, fileName=91file0.jpg, ord=0), BoardImage(uuid=06be21fb-fb88-45a7-9e9a-b4dc8234a07b, fileName=91file1.jpg, ord=1)] ----------

-> @BatchSize에 지정된 수만큼 BoardImage 조회시 bno의 조건의 범위가 in으로 지정된다.

where
    imageset0_.board_bno in (
        ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
    )