쿼리 N + 1 문제

1. 문제 상황

• 조회하지도 않은 쿼리가 조회되는 문제
• 쿼리가 조회한 갯수만큼 발생하는 문제
  
  

2. 원인 분석

• FetchType을 Lazy로 설정했음에도 불구하고 해당 설정이 적용되지 않음
• 지연로딩이 적용되지 않은 도메인의 연관관계를 보니 OneToOne 중 주인이 아닌 쪽임을 확인

Board Entity

@OneToOne(mappedby = "board", fetch = FetchType.LAZY)
private Vote vote;

Vote Entity

@OneToOne(fetch = FetchType.Lazy)
@JoinColumn(name = "board_id")
private Board board;

@OneToOne(mappedby = "board") : Board와 Vote를 OneToOne으로 양방향 연관관계를 맺음(Vote에 Board의 FK가 들어있음)

fetch = FetchType.LAZY : Board에서 지연로딩으로 설정을 했음에도 불구하고, board를 5개 조회할 때 vote도 동일하게 5번 조회가 됨. 대신 Vote는 주인이기 때문에 Vote에서 설정한 지연로딩은 그대로 적용되어 Board가 조회되지는 않음

Query X 5

Hibernate: 
    select
        vote0_.id as id1_15_1_,
        vote0_.board_id as board_id8_15_1_,
        vote0_.chat_room_id as chat_roo9_15_1_,
        vote0_.top_pointa as top_poin2_15_1_,
        vote0_.top_pointb as top_poin3_15_1_,
        vote0_.topicacnt as topicacn4_15_1_,
        vote0_.topicbcnt as topicbcn5_15_1_,
        vote0_.total_pointa as total_po6_15_1_,
        vote0_.total_pointb as total_po7_15_1_,
    from
        vote vote0_ 
    where
        vote0_.board_id=?

3. 조치 방안

• OneToOne이어도 주인인 쪽은 지연로딩이 적용될 수 있다는 점을 이용
• getter를 쓸 수 밖에 없는 entity를 주인으로 설정한 후 단방향 연관관계를 맺음

Board Entity

@OneToOne(cascade = CascadeType.REMOVE, fetch = FetchType.LAZY)
@JoinColumn(name = "vote")
private Vote vote;

@JoinColumn(name = "vote") : Board에서 Vote를 get 요청하는 로직이 필요하기 때문에 Board를 주인으로 설정
fetch = FetchType.Lazy : Board가 주인이기 때문에 OneToOne 임에도 지연로딩 설정이 적용됨

Query

Hibernate: 
    select
        board0_.id as id1_1_,
        board0_.created_at as created_2_1_,
        board0_.modified_at as modified3_1_,
        board0_.converted_file_name as converte4_1_,
        board0_.file_path as file_pat5_1_,
        board0_.topic_a as topic_a6_1_,
        board0_.topic_b as topic_b7_1_,
        board0_.user_id as user_id9_1_,
        board0_.vote as vote10_1_,
        board0_.winner as winner8_1_ 
    from
        board board0_ 
    order by
        board0_.created_at desc limit ?

4. 추가 문제

• 5개의 Board에서 Vote를 get 요청 했을 시 Vote가 5개 조회되는 문제가 발생
• page 처리가 되어있어서 fetch join으로 해결 불가능

Query X 5

Hibernate: 
    select
        vote0_.id as id1_15_0_,
        vote0_.chat_room_id as chat_roo8_15_0_,
        vote0_.top_pointa as top_poin2_15_0_,
        vote0_.top_pointb as top_poin3_15_0_,
        vote0_.topicacnt as topicacn4_15_0_,
        vote0_.topicbcnt as topicbcn5_15_0_,
        vote0_.total_pointa as total_po6_15_0_,
        vote0_.total_pointb as total_po7_15_0_ 
    from
        vote vote0_ 
    where
        vote0_.id=?

5. 추가 문제 해결

• global로 batchsize를 설정하여 복수로 조회를 해도 쿼리는 한 번만 발생하게끔 개선

application.yml

default_batch_fetch_size: 10

Query X 1

Hibernate: 
    select
        vote0_.id as id1_15_0_,
        vote0_.chat_room_id as chat_roo8_15_0_,
        vote0_.top_pointa as top_poin2_15_0_,
        vote0_.top_pointb as top_poin3_15_0_,
        vote0_.topicacnt as topicacn4_15_0_,
        vote0_.topicbcnt as topicbcn5_15_0_,
        vote0_.total_pointa as total_po6_15_0_,
        vote0_.total_pointb as total_po7_15_0_ 
    from
        vote vote0_ 
    where
        vote0_.id in (
            ?, ?, ?
        )