현재 상황
스프링 강의를 보기 전에 만들어놓은 개인 프로젝트 리팩토링
문제 상황
Post 하나 조회하는데 엄청난 N...😫
일부 Map으로 구현 된 곳 DTO로 변경하기
구현 목표
- Post 조회 시 내가 팔로우한 멤버의 게시글만 나온다.
- 게시글이 나올 땐 댓글수과 좋아요수가 항상 포함된다.
- 게시글을 작성한 멤버의 정보도 나와야한다.
- 페이지네이션이 적용되어있다.
N+1
Post 조회 한 번에 나오는 쿼리! 딱 봐도 엄청난 양!
줄여보기로 했다!
Hibernate:
select
member0_.member_id as member_i1_5_,
member0_.created_at as created_2_5_,
member0_.updated_at as updated_3_5_,
member0_.email as email4_5_,
member0_.email_confirmation as email_co5_5_,
member0_.nickname as nickname6_5_,
member0_.notification as notifica7_5_,
member0_.password as password8_5_,
member0_.phone as phone9_5_,
member0_.profile_path as profile10_5_,
member0_.role as role11_5_,
member0_.social as social12_5_
from
member member0_
where
member0_.email=?
Hibernate:
select
member0_.member_id as member_i1_5_,
member0_.created_at as created_2_5_,
member0_.updated_at as updated_3_5_,
member0_.email as email4_5_,
member0_.email_confirmation as email_co5_5_,
member0_.nickname as nickname6_5_,
member0_.notification as notifica7_5_,
member0_.password as password8_5_,
member0_.phone as phone9_5_,
member0_.profile_path as profile10_5_,
member0_.role as role11_5_,
member0_.social as social12_5_
from
member member0_
where
member0_.email=?
Hibernate:
select
member0_.member_id as member_i1_5_,
member0_.created_at as created_2_5_,
member0_.updated_at as updated_3_5_,
member0_.email as email4_5_,
member0_.email_confirmation as email_co5_5_,
member0_.nickname as nickname6_5_,
member0_.notification as notifica7_5_,
member0_.password as password8_5_,
member0_.phone as phone9_5_,
member0_.profile_path as profile10_5_,
member0_.role as role11_5_,
member0_.social as social12_5_
from
member member0_
where
member0_.email=?
Hibernate:
select
post0_.post_id as post_id1_10_0_,
post0_.created_at as created_2_10_0_,
post0_.updated_at as updated_3_10_0_,
post0_.content as content4_10_0_,
post0_.member_id as member_i5_10_0_
from
post post0_
where
post0_.post_id=?
Hibernate:
select
comment0_.comment_id as comment_1_1_,
comment0_.created_at as created_2_1_,
comment0_.updated_at as updated_3_1_,
comment0_.content as content4_1_,
comment0_.deleted_at as deleted_5_1_,
comment0_.depth as depth6_1_,
comment0_.member_id as member_i7_1_,
comment0_.parent_id as parent_i8_1_,
comment0_.post_id as post_id9_1_
from
comment comment0_
where
(
comment0_.deleted_at IS NULL
)
and comment0_.post_id=?
and comment0_.depth=?
order by
comment0_.comment_id desc limit ?
Hibernate:
select
member0_.member_id as member_i1_5_0_,
member0_.created_at as created_2_5_0_,
member0_.updated_at as updated_3_5_0_,
member0_.email as email4_5_0_,
member0_.email_confirmation as email_co5_5_0_,
member0_.nickname as nickname6_5_0_,
member0_.notification as notifica7_5_0_,
member0_.password as password8_5_0_,
member0_.phone as phone9_5_0_,
member0_.profile_path as profile10_5_0_,
member0_.role as role11_5_0_,
member0_.social as social12_5_0_
from
member member0_
where
member0_.member_id=?
Hibernate:
select
postfileli0_.post_id as post_id9_11_0_,
postfileli0_.post_file_id as post_fil1_11_0_,
postfileli0_.post_file_id as post_fil1_11_1_,
postfileli0_.created_at as created_2_11_1_,
postfileli0_.updated_at as updated_3_11_1_,
postfileli0_.file_path as file_pat4_11_1_,
postfileli0_.file_size as file_siz5_11_1_,
postfileli0_.file_type as file_typ6_11_1_,
postfileli0_.file_name as file_nam7_11_1_,
postfileli0_.original_file_name as original8_11_1_,
postfileli0_.post_id as post_id9_11_1_
from
post_file postfileli0_
where
postfileli0_.post_id=?
Hibernate:
select
member0_.member_id as member_i1_5_0_,
member0_.created_at as created_2_5_0_,
member0_.updated_at as updated_3_5_0_,
member0_.email as email4_5_0_,
member0_.email_confirmation as email_co5_5_0_,
member0_.nickname as nickname6_5_0_,
member0_.notification as notifica7_5_0_,
member0_.password as password8_5_0_,
member0_.phone as phone9_5_0_,
member0_.profile_path as profile10_5_0_,
member0_.role as role11_5_0_,
member0_.social as social12_5_0_
from
member member0_
where
member0_.member_id=?
Hibernate:
select
children0_.parent_id as parent_i8_1_0_,
children0_.comment_id as comment_1_1_0_,
children0_.comment_id as comment_1_1_1_,
children0_.created_at as created_2_1_1_,
children0_.updated_at as updated_3_1_1_,
children0_.content as content4_1_1_,
children0_.deleted_at as deleted_5_1_1_,
children0_.depth as depth6_1_1_,
children0_.member_id as member_i7_1_1_,
children0_.parent_id as parent_i8_1_1_,
children0_.post_id as post_id9_1_1_
from
comment children0_
where
(
children0_.deleted_at IS NULL
)
and children0_.parent_id=?
Hibernate:
select
children0_.parent_id as parent_i8_1_0_,
children0_.comment_id as comment_1_1_0_,
children0_.comment_id as comment_1_1_1_,
children0_.created_at as created_2_1_1_,
children0_.updated_at as updated_3_1_1_,
children0_.content as content4_1_1_,
children0_.deleted_at as deleted_5_1_1_,
children0_.depth as depth6_1_1_,
children0_.member_id as member_i7_1_1_,
children0_.parent_id as parent_i8_1_1_,
children0_.post_id as post_id9_1_1_
from
comment children0_
where
(
children0_.deleted_at IS NULL
)
and children0_.parent_id=?
Hibernate:
select
children0_.parent_id as parent_i8_1_0_,
children0_.comment_id as comment_1_1_0_,
children0_.comment_id as comment_1_1_1_,
children0_.created_at as created_2_1_1_,
children0_.updated_at as updated_3_1_1_,
children0_.content as content4_1_1_,
children0_.deleted_at as deleted_5_1_1_,
children0_.depth as depth6_1_1_,
children0_.member_id as member_i7_1_1_,
children0_.parent_id as parent_i8_1_1_,
children0_.post_id as post_id9_1_1_
from
comment children0_
where
(
children0_.deleted_at IS NULL
)
and children0_.parent_id=?
Hibernate:
select
count(favorite0_.favorite_id) as col_0_0_
from
favorite favorite0_
where
favorite0_.post_id=?
Hibernate:
select
count(comment0_.comment_id) as col_0_0_
from
comment comment0_
where
(
comment0_.deleted_at IS NULL
)
and comment0_.post_id=?
Hibernate:
select
favorite0_.favorite_id as favorite1_3_,
favorite0_.member_id as member_i2_3_,
favorite0_.post_id as post_id3_3_
from
favorite favorite0_
where
favorite0_.member_id=?
and favorite0_.post_id=?기존의 PostService list 메서드
강의를 보고 나니 여기는 통으로 날려줘야할 것 같다...
public Map<String, Object> list(Member member, String nickname, Pageable pageable) {
List<FollowResponseDto> follow = followRepository.findByMemberOrderByCreatedAtDesc(member);
List<Map<String,Object>> listmap = new ArrayList<Map<String, Object>>();
List<Member> memberList = new ArrayList<>();
Map<String, Object> result = new HashMap<String, Object>();
Page<Post> posts;
// role이 star일 경우 자기 자신 게시글 보여주고 글 작성 권한이 있음
if (member.getRole().toString().equals("ROLE_STAR") || member.getRole().toString().equals("ROLE_STAR_TEST")) {
result.put("role", "star");
nickname = member.getNickname();
}
if (nickname == null || nickname == "") {
/* 팔로잉 목록 */
// 팔로잉한 member 찾은 후 nickname, profile만 보여줌
for (FollowResponseDto following : follow) {
Map<String, Object> followingList = new HashMap<String, Object>();
followingList.put("nickname", following.getFollowing().getNickname());
followingList.put("profilePath", following.getFollowing().getProfilePath());
listmap.add(followingList);
memberList.add(following.getFollowing());
}
/* 팔로잉한 게시글 목록 */
posts = postRepository.findByMemberOrderByCreatedAtDesc(memberList, pageable);
} else {
/* 특정 회원 게시글 목록 */
Member following = memberRepository.findByNickname(nickname).orElse(null);
posts = postRepository.findByMemberOrderByCreatedAtDesc(following, pageable);
}
result.put("following", listmap);
List<PostResponseDto> postList = new ArrayList<>();
for (Post post : posts) {
PostResponseDto postResponseDto = new PostResponseDto(post);
postResponseDto.setFavoriteCount(favoriteRepository.countByPost(post)); // 좋아요 수
postResponseDto.setCommentCount(commentRepository.countByPost(post)); // 댓글 수
// 좋아요 여부
Favorite favorite = favoriteRepository.findByMemberAndPost(member, post);
if (favorite == null) {
postResponseDto.setFavorite(false);
} else {
postResponseDto.setFavorite(true);
}
postList.add(postResponseDto);
}
result.put("postList", postList);
// page
Map<String, Integer> pageList = new HashMap<>();
pageList.put("page", posts.getNumber());
pageList.put("totalPages", posts.getTotalPages());
pageList.put("nextPage", pageable.next().getPageNumber());
result.put("pageList", pageList);
return result;
}1. QueryDSL 도입!
동적 쿼리를 사용하기 때문에 QueryDSL을 도입하기로 했다. JPQL로 구현해놓았던 쿼리를 QueryDSL로 변경하면서 쿼리를 약간 바꿨다.
기존의 쿼리
Post를 whereIn memberList로 조회하였다.
memberList는 Service에서 FollowRepository 조회를 통해 만들었다.
-> postService 에서 postRepository와 FollowRepository를 접근하는 문제!
책임을 분리하자
@Query("SELECT p FROM Post p WHERE p.member in :memberList ORDER BY CREATED_AT DESC")
Page<Post> findByMemberOrderByCreatedAtDesc(@Param("memberList") List<Member> memberList, Pageable pageable);변경한 쿼리
QueryDSL을 추가하여 post와 follow를 join 하였다.
public List<Post> findByPostAndFollowing(Member member, Pageable pageable) {
QPost post = QPost.post;
QFollow follow = QFollow.follow;
List<Post> list = queryFactory
.select(post)
.from(post)
.where(post.member.eq(follow.following))
.join(follow)
.on(follow.member.eq(member))
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
return list;
}2. Post 도메인에 countComment, countFavorite 추가
기존의 나의 코드에서는 favoriteRepository.countByPost(post)로 post를 조회할 때마다 repository에 접근하여 count 해왔다.
아래 코드와 같이 Post domain에 추가해주었다.
public class Post extends BaseTimeEntity {
// 생략
@OneToMany(mappedBy = "post", cascade = CascadeType.ALL)
@ToString.Exclude
private List<Comment> commentList = new ArrayList<>();
@OneToMany(mappedBy = "post", cascade = CascadeType.ALL)
@ToString.Exclude
private List<Favorite> favoriteList = new ArrayList<>();
public int countComment() {
return commentList.size();
}
public int countFavorite() {
return favoriteList.size();
}
// 생략
}3. Map -> ResponseDto
Map으로 절대 return 하지 말기!
Map으로 request, response를 할 경우 어떤 값을 주고 어떤 값을 받는지 알 수가 없다!
(Map을 써놓은 곳은 몇 개월 지나서 보니까 나도 모르겠다😭.. 그래도 Map 보다는 ResponseDto와 RequestDto 쓴 곳이 훨씬 많아서 다행이었음)
PostListResponseDto 추가
public class PostListResponseDto {
private Member member;
private String content;
private int commentCount;
private int favoriteCount;
public PostListResponseDto(Post post) {
this.member = post.getMember();
this.content = post.getContent();
this.commentCount = post.countComment();
this.favoriteCount = post.countFavorite();
}
}4. fetch join 사용하기 -> 해결X
안된다 난 안된다!!!! fetch join을 2개를 사용할 수 없다!!
💡 JPA에서 Fetch Join의 특징
OneToOne, ManyToOne는 1개 이상 사용 가능
ManyToMany, OneToMany는 1개만 사용 가능
나의 코드에서는 OneToMany 관계...
public List<Post> findByPostAndFollowing(Member member, Pageable pageable) {
QPost post = QPost.post;
QFollow follow = QFollow.follow;
QComment comment = QComment.comment;
QFavorite favorite = QFavorite.favorite;
List<Post> list = queryFactory
.select(post)
.from(post)
.where(post.member.eq(follow.following))
.join(follow)
.on(follow.member.eq(member))
.leftJoin(post.commentList, comment).fetchJoin()
.leftJoin(post.favoriteList, favorite).fetchJoin()
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
return list;
}Error
org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [com.realtimechat.client.domain.Post.commentList, com.realtimechat.client.domain.Post.favoriteList]; nested exception is java.lang.IllegalArgumentException: org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags: [com.realtimechat.client.domain.Post.commentList, com.realtimechat.client.domain.Post.favoriteList]
나는 countComment와 countFavorite를 한꺼번에 가져와야하는데 fetch join은 하나만 사용할 수 있다..
저기서 comment나 favorite를 지워주면 아주 잘 된다. 해결 완료가 될 뻔 했는데..😭
댓글 수나 좋아요 수 중에 하나를 뺄까 잠깐 나쁜 생각이 들었지만
다른 방법을 찾아보자
5. 서브 쿼리
@EntityGraph 도 MultipleBagFetchException 에러가 나기 때문에 사용 할 수 없었다.
난 개인적으로 서브 쿼리를 안좋아한다. 하지만 여기서는 서브 쿼리를 쓰는 것이 가장 나은 방법이라고 판단되었다. (MySQL 에서 count() 연산은 매우 빠르기 때문)
서브 쿼리를 이용해서 count()를 하였다.
JPAQuery<PostListResponseDto> list = queryFactory
.select(Projections.constructor(
PostListResponseDto.class,
qPost.member,
qPost.content,
JPAExpressions.select(subComment.count())
.from(subComment)
.where(subComment.post.eq(qPost)),
JPAExpressions.select(subFavorite.count())
.from(subFavorite)
.where(subFavorite.post.eq(qPost))
))
.from(qPost);select
post0_.member_id as col_0_0_,
post0_.content as col_1_0_,
(select
count(comment3_.comment_id)
from
comment comment3_
where
(
comment3_.deleted_at IS NULL
)
and comment3_.post_id=post0_.post_id) as col_2_0_,
(select
count(favorite4_.favorite_id)
from
favorite favorite4_
where
favorite4_.post_id=post0_.post_id) as col_3_0_,
member2_.member_id as member_i1_5_,
member2_.created_at as created_2_5_,
member2_.updated_at as updated_3_5_,
member2_.email as email4_5_,
member2_.email_confirmation as email_co5_5_,
member2_.nickname as nickname6_5_,
member2_.notification as notifica7_5_,
member2_.password as password8_5_,
member2_.phone as phone9_5_,
member2_.profile_path as profile10_5_,
member2_.role as role11_5_,
member2_.social as social12_5_
from
post post0_
inner join
follow follow1_
on (
follow1_.member_id=?
)
inner join
member member2_
on post0_.member_id=member2_.member_id
where
post0_.member_id=follow1_.following_member_id
order by
post0_.created_at desc limit ?6. 최종
서브 쿼리로 해결 하였기 때문에 2.Post 도메인에 countComment, countFavorite 추가했던 부분은 삭제를 하였다. 또한 @OneToMany로 되어있던 commentList, favoriteList 모두 지웠다.
동적 쿼리를 사용하므로 BooleanBuilder를 추가하였다. 필요한 데이터만 조회하도록 Projections를 추가하였다.
public Page<PostListResponseDto> findByPostAndFollowingAndFavoriteCountAndCommentCount(Member member, String nickname, Pageable pageable) {
QPost qPost = QPost.post;
QFollow qFollow = QFollow.follow;
QComment subComment = new QComment("subComment");
QFavorite subFavorite = new QFavorite("subFavorite");
QMember qMember = QMember.member;
BooleanBuilder builder = new BooleanBuilder();
JPAQuery<PostListResponseDto> list = queryFactory
.select(Projections.constructor(
PostListResponseDto.class,
qPost.member.nickname,
qPost.member.profilePath,
qPost.content,
JPAExpressions.select(subComment.count())
.from(subComment)
.where(subComment.post.eq(qPost)),
JPAExpressions.select(subFavorite.count())
.from(subFavorite)
.where(subFavorite.post.eq(qPost))
))
.from(qPost);
if (nickname == null || nickname.equals("")) { // 팔로우한 모든 멤버 조회
builder.and(qPost.member.eq(qFollow.following));
list.join(qFollow).on(qFollow.member.eq(member));
} else { // 선택한 멤버 조회
builder.and(qPost.member.eq(qMember));
list.join(qMember).on(qMember.nickname.eq(nickname));
}
list.where(builder).orderBy(qPost.createdAt.desc());
List<PostListResponseDto> content = list
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
long total = list.fetch().size();
return new PageImpl<>(content, pageable, total);
}그리고 맨 처음 PostService list 메서드는 아래와 같이 변경 되었다.
public Page<PostListResponseDto> list(Member member, String nickname, Pageable pageable) {
return postRepository.findByPostAndFollowingAndFavoriteCountAndCommentCount(member, nickname, pageable);
}뭔가 내가 원하던 해결 방향은 아니였지만 쿼리로 해결하는 방법이 가장 나아보였다. fetch join, EntityGraph, Batch size 등 검색하면 바로 나오는 방법을 써서 해결하려고 했는데 쿼리로 마무리 해버렸다! 또 다른 곳에서 나는 N+1 문제는 fetch join, EntityGraph, Batch size 방법으로 해결해봐야겠다.
(더 좋은 방법을 알려주신다면 감사할 것 같습니다😭)
항상 좋은 글 감사합니다.