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

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

Board에 대해 limit을 이용해 페이질 처리
Board의 bno값 출력
Board 객체의 bno에 해당하는 board_image테이블을 조회해 imageSet을 가져오는 쿼리 실행
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<>();

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

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

저작자표시 비영리

'Server Programming > Spring Boot Backend Programming' 카테고리의 다른 글

7장-5. 이미지 추가를 위한 컨트롤러와 화면 처리 (+ 파일명에 언더바가 들어간 경우 에러 발생) (0)	2022.12.11
7장-4. 이전 프로젝트에 이미지 추가 (0)	2022.12.10
7장-2. 일대다 연관관계인 게시물과 첨부파일 (+ 매핑테이블, @Transactional, @EntityGraph) (0)	2022.12.09
7장-1. 다중 파일 업로드 처리 (+MultipartFile) (0)	2022.12.09
6장-3. 댓글의 자바스크립트 처리 (+Axios, @JsonFormat, @JsonIgnore) (0)	2022.12.08

Just do it, and then some

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

요구사항

N+1 문제의 발생

N+1 문제의 해결

'Server Programming > Spring Boot Backend Programming' 카테고리의 다른 글

티스토리툴바

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

요구사항

N+1 문제의 발생

N+1 문제의 해결

'Server Programming > Spring Boot Backend Programming' 카테고리의 다른 글

'Server Programming/Spring Boot Backend Programming' Related Articles

티스토리툴바