[Field-Passer 프로젝트] 쿼리 최적화 (N + 1 문제 해결하기)

뇌 뺴고 코딩 하다가 N + 1 문제가 발생하는 걸 알아차려서 해결하는 방법을 써보겠다.

회원 명단을 가져오는 API를 만들고 있었는데 쿼리를 보니까 N + 1 문제가 발생하고 있는 것을 깨달았다.

================= 1 : 회원 명단 가져오기 =================   
Hibernate: 
    select
        member0_.id as id1_4_,
        member0_.authority as authorit2_4_,
        member0_.delete_check as delete_c3_4_,
        member0_.email as email4_4_,
        member0_.membername as memberna5_4_,
        member0_.password as password6_4_,
        member0_.privilege as privileg7_4_,
        member0_.profile_img as profile_8_4_,
        member0_.signup_date as signup_d9_4_,
        member0_.visit_count as visit_c10_4_ 
    from
        member member0_ 
    where
        member0_.delete_check=0 
    order by
        member0_.id asc limit ?
        
================= N-(1) : 관리자 조회 =================     

Hibernate: 
    select
        admin0_.admin_id as admin_id1_0_0_,
        admin0_.id as id3_0_0_,
        admin0_.promote_date as promote_2_0_0_ 
    from
        adminlist admin0_ 
    where
        admin0_.id=?
        
================= N-(2) : 관리자 조회 =================   
        
Hibernate: 
    select
        admin0_.admin_id as admin_id1_0_0_,
        admin0_.id as id3_0_0_,
        admin0_.promote_date as promote_2_0_0_ 
    from
        adminlist admin0_ 
    where
        admin0_.id=?
        
================= N-(3) : 관리자 조회 =================   
        
Hibernate: 
    select
        admin0_.admin_id as admin_id1_0_0_,
        admin0_.id as id3_0_0_,
        admin0_.promote_date as promote_2_0_0_ 
    from
        adminlist admin0_ 
    where
        admin0_.id=?

 DB에 회원 수가 3명이라 다행이지 만약 100명 1000명이었으면 admin 조회하는 쿼리가 미친듯 나갔을 거다.

 

@Entity
@Table(name = "MEMBER")
public class Member {
    
    .
    .
    .

    @OneToOne(mappedBy = "member", fetch = FetchType.LAZY)
    private Admin admin;

분명  Entity에 Admin에 대한 FetchType을 LAZY로 해뒀는데 왜 N + 1 문제가 발생한 걸까?

 

 

public interface MemberRepositoryJPA extends JpaRepository<Member, Integer> {
    .
    .
    .
    //전체 회원 조회
    @Query("select m from Member m where m.delete = 0")
    Page<Member> findAllMembers(Pageable pageable);
}

짜잔~!! 그 이유는 JPQL로 데이터를 조회했기 때문입니다. JPQL을 사용하면  엔티티에 글로벌 로딩 전약으로 지연 로딩으로 설정했어도 JPQL의 우선 순위가 더 높기 때문에 무시하는 거였습니다.

 

public interface MemberRepositoryJPA extends JpaRepository<Member, Integer> {
    .
    .
    .
    //전체 회원 조회
    @EntityGraph(attributePaths = {"admin"})
    @Query("select m from Member m where m.delete = 0")
    Page<Member> findAllMembers(Pageable pageable);
}

그래서 해당 부분을 @EntityGraph를 통해 처리했더니

Hibernate: 
    select
        member0_.id as id1_4_0_,
        admin1_.admin_id as admin_id1_0_1_,
        member0_.authority as authorit2_4_0_,
        member0_.delete_check as delete_c3_4_0_,
        member0_.email as email4_4_0_,
        member0_.membername as memberna5_4_0_,
        member0_.password as password6_4_0_,
        member0_.privilege as privileg7_4_0_,
        member0_.profile_img as profile_8_4_0_,
        member0_.signup_date as signup_d9_4_0_,
        member0_.visit_count as visit_c10_4_0_,
        admin1_.id as id3_0_1_,
        admin1_.promote_date as promote_2_0_1_ 
    from
        member member0_ 
    left outer join
        adminlist admin1_ 
            on member0_.id=admin1_.id 
    where
        member0_.delete_check=0 
    order by
        member0_.id asc limit ?

 위와 같이 쿼리가 outer join되어 한번만 나가는 기적을 보았습니다.

 

앞으로 최적화에 좀 더 신경써서 개발을 해야겠다는 생각을 했습니다.