프로젝트/SpringBoot Side Project
트러블 슈팅 : JPA leftjoin N+1 문제
amungstudy
2024. 1. 24. 16:26
원래 Article 조회 시 모든 join을 fetchjoin으로 하고 있었어서 문제가 없었는데
Article의 Hashtag가 없는 경우 fetchjoin으로 조회할 수 없어서 쿼리를 아래와 같이 변경했다.
@Query("select a from Article a " +
"join fetch a.category c " +
"join fetch a.member m " +
"left join a.articleHashtags ah " +
"left join ah.hashtag h " +
"where a.id =:id")
Article findArticleByArticleId(@Param("id") long id);
이랬더니 N+1문제가 발생했다.
해시태그가 만약 2개가 있는경우 조회쿼리1번, ah조회쿼리 1번 해시태그조회쿼리가 2번 나가고,,,4개면 4번,,,발생한다.
2024-01-24T16:14:17.216+09:00 DEBUG 24832 --- [nio-8080-exec-8] org.hibernate.SQL :
select
a1_0.article_id,
c1_0.category_id,
c1_0.created_by,
c1_0.created_date,
c1_0.last_modified_by,
c1_0.last_modified_date,
c1_0.name,
a1_0.content,
a1_0.created_by,
a1_0.created_date,
a1_0.last_modified_by,
a1_0.last_modified_date,
m1_0.member_id,
m1_0.city,
m1_0.full_address,
m1_0.lat,
m1_0.lon,
m1_0.created_date,
m1_0.email,
m1_0.last_modified_date,
m1_0.nickname,
m1_0.password,
m1_0.profile_img,
m1_0.status,
a1_0.title
from
article a1_0
join
category c1_0
on c1_0.category_id=a1_0.category_id
join
member m1_0
on m1_0.member_id=a1_0.member_id
left join
article_hashtag ah1_0
on a1_0.article_id=ah1_0.article_id
where
a1_0.article_id=?
2024-01-24T16:14:17.217+09:00 INFO 24832 --- [nio-8080-exec-8] p6spy : #1706080457217 | took 0ms | statement | connection 13| url jdbc:h2:tcp://localhost/~/blog
select a1_0.article_id,c1_0.category_id,c1_0.created_by,c1_0.created_date,c1_0.last_modified_by,c1_0.last_modified_date,c1_0.name,a1_0.content,a1_0.created_by,a1_0.created_date,a1_0.last_modified_by,a1_0.last_modified_date,m1_0.member_id,m1_0.city,m1_0.full_address,m1_0.lat,m1_0.lon,m1_0.created_date,m1_0.email,m1_0.last_modified_date,m1_0.nickname,m1_0.password,m1_0.profile_img,m1_0.status,a1_0.title from article a1_0 join category c1_0 on c1_0.category_id=a1_0.category_id join member m1_0 on m1_0.member_id=a1_0.member_id left join article_hashtag ah1_0 on a1_0.article_id=ah1_0.article_id where a1_0.article_id=?
select a1_0.article_id,c1_0.category_id,c1_0.created_by,c1_0.created_date,c1_0.last_modified_by,c1_0.last_modified_date,c1_0.name,a1_0.content,a1_0.created_by,a1_0.created_date,a1_0.last_modified_by,a1_0.last_modified_date,m1_0.member_id,m1_0.city,m1_0.full_address,m1_0.lat,m1_0.lon,m1_0.created_date,m1_0.email,m1_0.last_modified_date,m1_0.nickname,m1_0.password,m1_0.profile_img,m1_0.status,a1_0.title from article a1_0 join category c1_0 on c1_0.category_id=a1_0.category_id join member m1_0 on m1_0.member_id=a1_0.member_id left join article_hashtag ah1_0 on a1_0.article_id=ah1_0.article_id where a1_0.article_id=1;
2024-01-24T16:14:17.218+09:00 INFO 24832 --- [nio-8080-exec-8] p6spy : #1706080457218 | took 0ms | commit | connection 13| url jdbc:h2:tcp://localhost/~/blog
;
2024-01-24T16:14:17.223+09:00 DEBUG 24832 --- [nio-8080-exec-8] org.hibernate.SQL :
select
ah1_0.article_id,
ah1_0.article_hashtag_id,
ah1_0.created_date,
ah1_0.hashtag_id,
ah1_0.last_modified_date
from
article_hashtag ah1_0
where
ah1_0.article_id=?
2024-01-24T16:14:17.224+09:00 INFO 24832 --- [nio-8080-exec-8] p6spy : #1706080457224 | took 0ms | statement | connection 13| url jdbc:h2:tcp://localhost/~/blog
select ah1_0.article_id,ah1_0.article_hashtag_id,ah1_0.created_date,ah1_0.hashtag_id,ah1_0.last_modified_date from article_hashtag ah1_0 where ah1_0.article_id=?
select ah1_0.article_id,ah1_0.article_hashtag_id,ah1_0.created_date,ah1_0.hashtag_id,ah1_0.last_modified_date from article_hashtag ah1_0 where ah1_0.article_id=1;
2024-01-24T16:14:17.225+09:00 DEBUG 24832 --- [nio-8080-exec-8] org.hibernate.SQL :
select
h1_0.hashtag_id,
h1_0.created_by,
h1_0.created_date,
h1_0.last_modified_by,
h1_0.last_modified_date,
h1_0.name
from
hashtag h1_0
where
h1_0.hashtag_id=?
2024-01-24T16:14:17.226+09:00 INFO 24832 --- [nio-8080-exec-8] p6spy : #1706080457226 | took 0ms | statement | connection 13| url jdbc:h2:tcp://localhost/~/blog
select h1_0.hashtag_id,h1_0.created_by,h1_0.created_date,h1_0.last_modified_by,h1_0.last_modified_date,h1_0.name from hashtag h1_0 where h1_0.hashtag_id=?
select h1_0.hashtag_id,h1_0.created_by,h1_0.created_date,h1_0.last_modified_by,h1_0.last_modified_date,h1_0.name from hashtag h1_0 where h1_0.hashtag_id=1;
2024-01-24T16:14:17.226+09:00 DEBUG 24832 --- [nio-8080-exec-8] org.hibernate.SQL :
select
h1_0.hashtag_id,
h1_0.created_by,
h1_0.created_date,
h1_0.last_modified_by,
h1_0.last_modified_date,
h1_0.name
from
hashtag h1_0
where
h1_0.hashtag_id=?
2024-01-24T16:14:17.226+09:00 INFO 24832 --- [nio-8080-exec-8] p6spy : #1706080457226 | took 0ms | statement | connection 13| url jdbc:h2:tcp://localhost/~/blog
select h1_0.hashtag_id,h1_0.created_by,h1_0.created_date,h1_0.last_modified_by,h1_0.last_modified_date,h1_0.name from hashtag h1_0 where h1_0.hashtag_id=?
select h1_0.hashtag_id,h1_0.created_by,h1_0.created_date,h1_0.last_modified_by,h1_0.last_modified_date,h1_0.name from hashtag h1_0 where h1_0.hashtag_id=2;
혹시 배치사이즈 글로벌 설정으로 해결이 되나 했는데 그대로였다.
그래서 다른 방법을 채택했다.
Service layer에서 해시태그가 있는 경우와 없는 경우를 분리해서 쿼리 작성
public Article findById(long id){
List<ArticleHashtag> articleHashtagList = articleHashtagRepository.findAllByArticleId(id);
System.out.println("****************조회시작***************");
if(articleHashtagList.isEmpty()){
return articleRepository.findArticleWithoutLeftJoinById(id);
}else{
return articleRepository.findArticleById(id);
}
}@Query("select a from Article a " +
"join fetch a.category c " +
"join fetch a.member m " +
"where a.id =:id")
Article findArticleWithoutLeftJoinById(@Param("id") long id);
@Query("select a from Article a " +
"join fetch a.category c " +
"join fetch a.member m " +
"join fetch a.articleHashtags ah " +
"join fetch ah.hashtag h " +
"where a.id =:id")
Article findArticleById(@Param("id") long id);
이렇게 하면 해시태그가 있는 경우 ah테이블 쿼리 1번 조회쿼리 1번 실행
해시태그가 없는 경우 ah테이블 쿼리1번 조회쿼리 2번 실행
N+1문제는 발생하지 않는다.
대신 더 좋은 방법이 없을까 고민해보고싶다.