eagerJpqlNplus1

JPQL 에서 즉시로딩시 N+1 문제 이슈

JPA 를 사용하다 보면 예기치 않는 문제 N+1 이슈가 발생 한다.

대표적으로 원인이 JPQL 를 사용하면서 발생되는 경우가 많은데

이것을 알아 보고자 한다.

우선

부모 DB 테이블
member_group

자식 DB 테이블
member

구성되어 있고

member_group(1) vs member(n)

해당 회원 그룹이 여러 회원을 갖는다.

1. member_group, member ENTITY 구성

2.PNG
1.PNG

2. member_group Repository 에서 JPQL 방식 select 메소드 구성

3.PNG

3. member_group, member 더미 데이터 구성 및 확인

4.PNG

member_group JOIN member 테이블의 매핑된 데이터가 총 4 ROW 이다.

4. 구성은 다 되었다. 이제 TEST 해보자

5.PNG

일단 TEST 하기 전 예상 해보자면

1
2
3
//OneToMany
@OneToMany(mappedBy = "memberGroup", fetch = FetchType.EAGER)
Set<Member> members = new HashSet<>();

MemberGroup Entity 테이블 확인 하자면
fetch = FetchType.EAGER 로 설정 되어 있어서
분명 member 테이블와 member_group 테이블 JOIN 해서 데이터를 가져올 것이다.

SQL문 결과

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
2020-11-08 16:00:08.059 DEBUG 15784 --- [           main] org.hibernate.SQL                        : 
select
member0_.member_no as member_n1_5_,
member0_.created_at as created_2_5_,
member0_.updated_at as updated_3_5_,
member0_.use_yn as use_yn4_5_,
member0_.email as email5_5_,
member0_.id as id6_5_,
member0_.member_group_no as member_14_5_,
member0_.name as name7_5_,
member0_.oauth_id as oauth_id8_5_,
member0_.oauth_type as oauth_ty9_5_,
member0_.password as passwor10_5_,
member0_.point as point11_5_,
member0_.sns_yn as sns_yn12_5_,
member0_.today_login as today_l13_5_
from
member member0_
2020-11-08 16:00:08.255 DEBUG 15784 --- [ main] org.hibernate.SQL :
select
membergrou0_.member_group_no as member_g1_7_0_,
membergrou0_.created_at as created_2_7_0_,
membergrou0_.updated_at as updated_3_7_0_,
membergrou0_.use_yn as use_yn4_7_0_,
membergrou0_.description as descript5_7_0_,
membergrou0_.name as name6_7_0_,
members1_.member_group_no as member_14_5_1_,
members1_.member_no as member_n1_5_1_,
members1_.member_no as member_n1_5_2_,
members1_.created_at as created_2_5_2_,
members1_.updated_at as updated_3_5_2_,
members1_.use_yn as use_yn4_5_2_,
members1_.email as email5_5_2_,
members1_.id as id6_5_2_,
members1_.member_group_no as member_14_5_2_,
members1_.name as name7_5_2_,
members1_.oauth_id as oauth_id8_5_2_,
members1_.oauth_type as oauth_ty9_5_2_,
members1_.password as passwor10_5_2_,
members1_.point as point11_5_2_,
members1_.sns_yn as sns_yn12_5_2_,
members1_.today_login as today_l13_5_2_
from
member_group membergrou0_
left outer join
member members1_
on membergrou0_.member_group_no=members1_.member_group_no
where
membergrou0_.member_group_no=?
2020-11-08 16:00:08.338 DEBUG 15784 --- [ main] org.hibernate.SQL :
select
membergrou0_.member_group_no as member_g1_7_0_,
membergrou0_.created_at as created_2_7_0_,
membergrou0_.updated_at as updated_3_7_0_,
membergrou0_.use_yn as use_yn4_7_0_,
membergrou0_.description as descript5_7_0_,
membergrou0_.name as name6_7_0_,
members1_.member_group_no as member_14_5_1_,
members1_.member_no as member_n1_5_1_,
members1_.member_no as member_n1_5_2_,
members1_.created_at as created_2_5_2_,
members1_.updated_at as updated_3_5_2_,
members1_.use_yn as use_yn4_5_2_,
members1_.email as email5_5_2_,
members1_.id as id6_5_2_,
members1_.member_group_no as member_14_5_2_,
members1_.name as name7_5_2_,
members1_.oauth_id as oauth_id8_5_2_,
members1_.oauth_type as oauth_ty9_5_2_,
members1_.password as passwor10_5_2_,
members1_.point as point11_5_2_,
members1_.sns_yn as sns_yn12_5_2_,
members1_.today_login as today_l13_5_2_
from
member_group membergrou0_
left outer join
member members1_
on membergrou0_.member_group_no=members1_.member_group_no
where
membergrou0_.member_group_no=?

로그 결과를 보면 member 테이블 SELECT 한 후 각각 매핑된 member_group 테이블의 데이터를 가져 오는것을 볼 수 있다.

5. 원인

JPQL 은 SQL문을 그대로 번역해서 가져오기 때문에

1
2
@Query(value = "select m from Member m")
List<Member> selectAll();

보시다싶이 우선 member 테이블에 있는 데이터를 가져온다.
member 테이블의 데이터를 가져오고 매칭된 member_group 을 확인 하니
패치 전략이 EAGER 로 되어 있으니 이제서야 member_group 데이터를 가져오게 된다.

1
2
3
4
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "member_group_no")
@JsonIgnore
private MemberGroup memberGroup;

6. 해결 방법

이러한 문제를 해결하기 위해서는 여러가지 방법이 있다.

우선 협업에서는 EAGER 는 비추천 한다.
이와 같이 예상치 못한 SQL 문이 SELECT 하기 때문에
모두 LAZY 로 셋팅 한다.

예시로는 간단하게 구성해서 그렇지 협업에서는 이보다 더 복잡한 테이블와 연결 구조를 되어 있을 것이고
데이터 양도 방대 할 것이다.

그럼 member 데이터 하나 가져 올때마다 매핑된 member_group 테이블 해당 데이터를 각각 SELECT 하는 N+1 문제 발생 된다.
100개 데이터 매핑되어 있다면 100개 + 호출 된다는 것이다.

이를 방지 하기 위해

7. fetch 전략을 모두 LAZY 로 한다.

1
2
3
4
5
6
7
8
9
// Member
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "member_group_no")
@JsonIgnore
private MemberGroup memberGroup;

// MemberGroup
@OneToMany(mappedBy = "memberGroup", fetch = FetchType.LAZY)
Set<Member> members = new HashSet<>();

8. fetch join 를 사용 한다.

6.PNG

이렇게 하고 다시 TEST 해보자!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
2020-11-08 16:24:27.943 DEBUG 5224 --- [           main] o.s.b.f.s.DefaultListableBeanFactory     : Returning cached instance of singleton bean 'encryptAspect'
2020-11-08 16:24:28.009 DEBUG 5224 --- [ main] org.hibernate.SQL :
select
member0_.member_no as member_n1_5_0_,
membergrou1_.member_group_no as member_g1_7_1_,
member0_.created_at as created_2_5_0_,
member0_.updated_at as updated_3_5_0_,
member0_.use_yn as use_yn4_5_0_,
member0_.email as email5_5_0_,
member0_.id as id6_5_0_,
member0_.member_group_no as member_14_5_0_,
member0_.name as name7_5_0_,
member0_.oauth_id as oauth_id8_5_0_,
member0_.oauth_type as oauth_ty9_5_0_,
member0_.password as passwor10_5_0_,
member0_.point as point11_5_0_,
member0_.sns_yn as sns_yn12_5_0_,
member0_.today_login as today_l13_5_0_,
membergrou1_.created_at as created_2_7_1_,
membergrou1_.updated_at as updated_3_7_1_,
membergrou1_.use_yn as use_yn4_7_1_,
membergrou1_.description as descript5_7_1_,
membergrou1_.name as name6_7_1_
from
member member0_
inner join
member_group membergrou1_
on member0_.member_group_no=membergrou1_.member_group_no

성공적으로 한개 Query 문으로 가져 왔다.

9. QueryDsl 를 사용 한다.

QueryDsl 는 동적 쿼리를 사용 할때 유용하게 이용 할 수 있다.
7.PNG

10. 마치면서

JPA 는 편리하지만 잘못 사용하면 오히려 속도 이슈 문제로 발생 할 수 있다.
유용하게 사용 할려면 일단 패치 전략을 모두 LAZY 로 설정 하는 것이 가장 중요하다.

이렇게 설정 후 그에 맞게 로직을 구상 하는 것이 좋다.


Copyright 201- syh8088. 무단 전재 및 재배포 금지. 출처 표기 시 인용 가능.

💰

×

Help us with donation