게시물 목록 조회 N+1 문제(getPosts)

게시글 조회시 N + 1 문제 발생

Hibernate: select distinct p1_0.id,p1_0.board_type,p1_0.content,p1_0.created_at,p1_0.image_url,p1_0.is_hidden,p1_0.like_count,p1_0.member_id,m1_0.id,m1_0.created_at,m1_0.email,m1_0.last_login_at,m1_0.nickname,m1_0.oauth_access_token,m1_0.oauth_provider,m1_0.oauth_refresh_token,m1_0.onboarded,m1_0.password,m1_0.profile_image_url,m1_0.role,m1_0.shop_name,m1_0.shop_url,m1_0.sns_urls,m1_0.status,m1_0.username,p1_0.title,p1_0.view_count from post p1_0 left join comment c1_0 on c1_0.post_id=p1_0.id join member m1_0 on m1_0.id=p1_0.member_id where p1_0.board_type in (?,?,?,?) and p1_0.is_hidden=0 and (? is null or ?='' or lower(p1_0.title) like replace(lower(concat('%',?,'%')),'\\','\\\\') or lower(p1_0.content) like replace(lower(concat('%',?,'%')),'\\','\\\\') or lower(c1_0.content) like replace(lower(concat('%',?,'%')),'\\','\\\\')) order by p1_0.created_at desc limit ?
Hibernate: select count(distinct p1_0.id) from post p1_0 left join comment c1_0 on c1_0.post_id=p1_0.id where p1_0.board_type in (?,?,?,?) and p1_0.is_hidden=0 and (? is null or ?='' or lower(p1_0.title) like replace(lower(concat('%',?,'%')),'\\','\\\\') or lower(p1_0.content) like replace(lower(concat('%',?,'%')),'\\','\\\\') or lower(c1_0.content) like replace(lower(concat('%',?,'%')),'\\','\\\\'))
Hibernate: select m1_0.id,m1_0.created_at,m1_0.email,m1_0.last_login_at,m1_0.nickname,m1_0.oauth_access_token,m1_0.oauth_provider,m1_0.oauth_refresh_token,m1_0.onboarded,m1_0.password,m1_0.profile_image_url,m1_0.role,m1_0.shop_name,m1_0.shop_url,m1_0.sns_urls,m1_0.status,m1_0.username from member m1_0 where m1_0.username=?
Hibernate: select pl1_0.id,pl1_0.member_id,pl1_0.post_id from post_like pl1_0 where pl1_0.member_id=? and pl1_0.post_id in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)
Hibernate: select count(c1_0.id) from comment c1_0 where c1_0.post_id=? and not(c1_0.is_hidden)

• 24번의 쿼리가 실행.

• 1.게시글 목록 조회 (1번): select distinct p1_0.id ... from post p1_0 ... limit ?

• 2.전체 게시글 수 카운트 (1번): select count(distinct p1_0.id) from post p1_0 ... (페이징 처리를 위해 전체 개수를 세는 쿼리)

• 3.로그인 회원 정보 조회 (1번): select ... from member m1_0 where m1_0.username=?

• 4.좋아요 누른 목록 조회 (1번): select ... from post_like pl1_0 where pl1_0.member_id=? and pl1_0.post_id in (...)

• 5.개별 댓글 개수 조회 (20번): select count(c1_0.id) from comment c1_0 where c1_0.post_id=? ... (이 부분이 게시글 개수만큼 반복되어 총 20번 실행됨)
  ---> N+1문제 발생. 여기도 1번발 발생해서 총 5번만 발생되게 해야한다.
  총계: 1 + 1 + 1 + 1 + 20 = 총 24번

'''
commentRepository.countByPostIdAndIsHiddenFalse(p.getId())에서 문제 발생.

int countByPostIdAndIsHiddenFalse(Long postId); //게시글 하나당 댓글 개수를 조회하는 메서드 ---> 게시글이 20개면 20번 호출하니까 N+1이 발생하는 것
'''

• posts.map()이 게시글 개수만큼 반복 실행되면서 게시글마다 댓글 개수를 조회하는 SQL이 추가로 발생하였다.

해결방법

• GROUP BY로 한 번에 조회
• 댓글 개수를 게시글마다 조회하지 않고, GROUP BY와 IN을 이용하여 모든 게시글의 댓글 개수를 한 번의 집계 쿼리로 조회하도록 변경.

'''
SELECT
post_id,
COUNT(*)
FROM comment
WHERE post_id IN (...)
AND is_hidden = false
GROUP BY post_id;
'''

• 조회한 결과를 Map<PostId, CommentCount> 형태로 저장한 뒤, PostResponse 생성 시 Map에서 댓글 개수를 조회하도록 수정

'''
List postIds = posts.stream()
.map(Post::getId)
.toList();

Map<Long, Integer> commentCountMap =
commentRepository.countCommentsByPostIds(postIds)
.stream()
.collect(Collectors.toMap(
row -> (Long) row[0],
row -> ((Long) row[1]).intValue()
));

final List finalLikedPostIds = likedPostIds;

return posts.map(p -> new PostResponse(
p.getId(),
p.getTitle(),
p.getContent(),
p.getBoardType().name(),
p.getImageUrl(),
p.getMember().getNickname(),
p.getViewCount() + postRedisService.getCachedViewCount(p.getId()),
p.getLikeCount(),
p.getCreatedAt(),
p.getMember().getProfileImageUrl(),
finalLikedPostIds.contains(p.getId()),
commentCountMap.getOrDefault(p.getId(), 0)
));
'''
Hibernate: select distinct p1_0.id,p1_0.board_type,p1_0.content,p1_0.created_at,p1_0.image_url,p1_0.is_hidden,p1_0.like_count,p1_0.member_id,m1_0.id,m1_0.created_at,m1_0.email,m1_0.last_login_at,m1_0.nickname,m1_0.oauth_access_token,m1_0.oauth_provider,m1_0.oauth_refresh_token,m1_0.onboarded,m1_0.password,m1_0.profile_image_url,m1_0.role,m1_0.shop_name,m1_0.shop_url,m1_0.sns_urls,m1_0.status,m1_0.username,p1_0.title,p1_0.view_count from post p1_0 left join comment c1_0 on c1_0.post_id=p1_0.id join member m1_0 on m1_0.id=p1_0.member_id where p1_0.board_type in (?,?,?,?) and p1_0.is_hidden=0 and (? is null or ?='' or lower(p1_0.title) like replace(lower(concat('%',?,'%')),'\','\\') or lower(p1_0.content) like replace(lower(concat('%',?,'%')),'\','\\') or lower(c1_0.content) like replace(lower(concat('%',?,'%')),'\','\\')) order by p1_0.created_at desc limit ?
Hibernate: select count(distinct p1_0.id) from post p1_0 left join comment c1_0 on c1_0.post_id=p1_0.id where p1_0.board_type in (?,?,?,?) and p1_0.is_hidden=0 and (? is null or ?='' or lower(p1_0.title) like replace(lower(concat('%',?,'%')),'\','\\') or lower(p1_0.content) like replace(lower(concat('%',?,'%')),'\','\\') or lower(c1_0.content) like replace(lower(concat('%',?,'%')),'\','\\'))
Hibernate: select m1_0.id,m1_0.created_at,m1_0.email,m1_0.last_login_at,m1_0.nickname,m1_0.oauth_access_token,m1_0.oauth_provider,m1_0.oauth_refresh_token,m1_0.onboarded,m1_0.password,m1_0.profile_image_url,m1_0.role,m1_0.shop_name,m1_0.shop_url,m1_0.sns_urls,m1_0.status,m1_0.username from member m1_0 where m1_0.username=?
Hibernate: select pl1_0.id,pl1_0.member_id,pl1_0.post_id from post_like pl1_0 where pl1_0.member_id=? and pl1_0.post_id in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
Hibernate: select c1_0.post_id,count(c1_0.id) from comment c1_0 where c1_0.post_id in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) and c1_0.is_hidden=0 group by c1_0.post_id
''' --> sql문이 5번만 실행.