게시물 목록 조회 API(단건, 페이징) 응답 데이터에 팔로우한 해시태그가 포함된 게시물도 추가하였습니다.
팔로우한 해시태그가 포함된 게시물이더라도, 본인이 올린 게시물은 포함되지 않습니다.
추가로 아래와 같이 쿼리도 개선하였습니다.
🔸변경 전 쿼리
select
distinct post0_.post_id as col_0_0_,
post0_.post_content as col_1_0_,
post0_.post_upload_date as col_2_0_,
post0_.member_id as col_3_0_,
(select
count(comments8_.post_id)
from
comments comments8_
where
post0_.post_id = comments8_.post_id) as col_4_0_,
(select
count(postlikes9_.post_id)
from
post_likes postlikes9_
where
post0_.post_id = postlikes9_.post_id) as col_5_0_,
exists (select
1
from
bookmarks bookmark3_
where
bookmark3_.post_id=post0_.post_id
and bookmark3_.member_id=?) as col_6_0_,
exists (select
1
from
post_likes postlike4_
where
postlike4_.post_id=post0_.post_id
and postlike4_.member_id=?) as col_7_0_,
post0_.post_comment_flag as col_8_0_,
post0_.post_like_flag as col_9_0_,
member1_.member_id as member_i1_13_,
member1_.member_email as member_e2_13_,
member1_.member_gender as member_g3_13_,
member1_.member_image_name as member_i4_13_,
member1_.member_image_type as member_i5_13_,
member1_.member_image_uuid as member_i6_13_,
member1_.member_image_url as member_i7_13_,
member1_.member_introduce as member_i8_13_,
member1_.member_name as member_n9_13_,
member1_.member_password as member_10_13_,
member1_.member_phone as member_11_13_,
member1_.member_role as member_12_13_,
member1_.member_username as member_13_13_,
member1_.member_website as member_14_13_
from
posts post0_
inner join
members member1_
on post0_.member_id=member1_.member_id
where
member1_.member_username in (
select
member7_.member_username
from
follows follow6_ cross
join
members member7_
where
follow6_.follow_member_id=member7_.member_id
and follow6_.member_id=?
)
order by
post0_.post_id desc limit ?
🔹변경 후 쿼리
```sql
select
distinct post0_.post_id as col_0_0_,
post0_.post_content as col_1_0_,
post0_.post_upload_date as col_2_0_,
post0_.member_id as col_3_0_,
(select
count(comments10_.post_id)
from
comments comments10_
where
post0_.post_id = comments10_.post_id) as col_4_0_,
(select
count(postlikes11_.post_id)
from
post_likes postlikes11_
where
post0_.post_id = postlikes11_.post_id) as col_5_0_,
exists (select
1
from
bookmarks bookmark3_
where
bookmark3_.post_id=post0_.post_id
and bookmark3_.member_id=?) as col_6_0_,
exists (select
1
from
post_likes postlike4_
where
postlike4_.post_id=post0_.post_id
and postlike4_.member_id=?) as col_7_0_,
post0_.post_comment_flag as col_8_0_,
post0_.post_like_flag as col_9_0_,
member1_.member_id as member_i1_13_,
member1_.member_email as member_e2_13_,
member1_.member_gender as member_g3_13_,
member1_.member_image_name as member_i4_13_,
member1_.member_image_type as member_i5_13_,
member1_.member_image_uuid as member_i6_13_,
member1_.member_image_url as member_i7_13_,
member1_.member_introduce as member_i8_13_,
member1_.member_name as member_n9_13_,
member1_.member_password as member_10_13_,
member1_.member_phone as member_11_13_,
member1_.member_role as member_12_13_,
member1_.member_username as member_13_13_,
member1_.member_website as member_14_13_
from
posts post0_
inner join
members member1_
on post0_.member_id=member1_.member_id
where
post0_.member_id in (
select
follow5_.follow_member_id
from
follows follow5_
where
follow5_.member_id=?
)
or post0_.post_id in (
select
hashtagpos6_.post_id
from
hashtag_posts hashtagpos6_
inner join
hashtag_follows hashtagfol7_
on (
hashtagfol7_.member_id=?
and hashtagfol7_.hashtag_id=hashtagpos6_.hashtag_id
)
inner join
posts post8_
on hashtagpos6_.post_id=post8_.post_id
and (
post8_.member_id<>?
)
)
order by
post0_.post_id desc limit ?
```
게시물 관련 쿼리 최적화
1. 각 게시물마다 content에 포함된 멘션 리스트를 파싱하고, 존재하는 username인지 db를 거쳐서 검증하는 쿼리 호출 횟수를 총 N회 -> 1회로 최적화하였습니다. (setMentionAndHashtagList)
각 게시물마다 content에 포함된 멘션 리스트를 한꺼번에 파싱한 후, 이들이 db에 존재하는 username인지 한 번의 쿼리를 날려서 검증하고, 메모리상에서 각 게시물에 해당하는 멘션된 username을 세팅하는 방식으로 변경되었습니다.
🔸변경 전 쿼리
```sql
select
member0_.member_id as member_i1_13_,
member0_.member_email as member_e2_13_,
member0_.member_gender as member_g3_13_,
member0_.member_image_name as member_i4_13_,
member0_.member_image_type as member_i5_13_,
member0_.member_image_uuid as member_i6_13_,
member0_.member_image_url as member_i7_13_,
member0_.member_introduce as member_i8_13_,
member0_.member_name as member_n9_13_,
member0_.member_password as member_10_13_,
member0_.member_phone as member_11_13_,
member0_.member_role as member_12_13_,
member0_.member_username as member_13_13_,
member0_.member_website as member_14_13_
from
members member0_
where
member0_.member_username in (
? , ? , ?
)
select
member0_.member_id as member_i1_13_,
member0_.member_email as member_e2_13_,
member0_.member_gender as member_g3_13_,
member0_.member_image_name as member_i4_13_,
member0_.member_image_type as member_i5_13_,
member0_.member_image_uuid as member_i6_13_,
member0_.member_image_url as member_i7_13_,
member0_.member_introduce as member_i8_13_,
member0_.member_name as member_n9_13_,
member0_.member_password as member_10_13_,
member0_.member_phone as member_11_13_,
member0_.member_role as member_12_13_,
member0_.member_username as member_13_13_,
member0_.member_website as member_14_13_
from
members member0_
where
member0_.member_username in (
? , ? , ?
)
select
member0_.member_id as member_i1_13_,
member0_.member_email as member_e2_13_,
member0_.member_gender as member_g3_13_,
member0_.member_image_name as member_i4_13_,
member0_.member_image_type as member_i5_13_,
member0_.member_image_uuid as member_i6_13_,
member0_.member_image_url as member_i7_13_,
member0_.member_introduce as member_i8_13_,
member0_.member_name as member_n9_13_,
member0_.member_password as member_10_13_,
member0_.member_phone as member_11_13_,
member0_.member_role as member_12_13_,
member0_.member_username as member_13_13_,
member0_.member_website as member_14_13_
from
members member0_
where
member0_.member_username in (
? , ? , ?
)
# (... 생략 ...)
```
🔹변경 후 쿼리
```sql
select
member0_.member_id as member_i1_13_,
member0_.member_email as member_e2_13_,
member0_.member_gender as member_g3_13_,
member0_.member_image_name as member_i4_13_,
member0_.member_image_type as member_i5_13_,
member0_.member_image_uuid as member_i6_13_,
member0_.member_image_url as member_i7_13_,
member0_.member_introduce as member_i8_13_,
member0_.member_name as member_n9_13_,
member0_.member_password as member_10_13_,
member0_.member_phone as member_11_13_,
member0_.member_role as member_12_13_,
member0_.member_username as member_13_13_,
member0_.member_website as member_14_13_
from
members member0_
where
member0_.member_username in (
? , ? , ?
)
```
2. 각 게시물마다 좋아요 숨김 옵션을 설정한 경우, 해당 게시물들의 id로 group by하여 옵션이 적용된 좋아요 횟수 조회 쿼리를 2N회 -> 1회로 최적화하였습니다. (hidePostLikesCountIfPostLikeFlagIsFalse)
🔸변경 전 쿼리
```sql
select
postlike0_.post_like_id as post_lik1_22_0_,
member1_.member_id as member_i1_13_1_,
postlike0_.member_id as member_i2_22_0_,
postlike0_.post_id as post_id3_22_0_,
member1_.member_email as member_e2_13_1_,
member1_.member_gender as member_g3_13_1_,
member1_.member_image_name as member_i4_13_1_,
member1_.member_image_type as member_i5_13_1_,
member1_.member_image_uuid as member_i6_13_1_,
member1_.member_image_url as member_i7_13_1_,
member1_.member_introduce as member_i8_13_1_,
member1_.member_name as member_n9_13_1_,
member1_.member_password as member_10_13_1_,
member1_.member_phone as member_11_13_1_,
member1_.member_role as member_12_13_1_,
member1_.member_username as member_13_13_1_,
member1_.member_website as member_14_13_1_
from
post_likes postlike0_
inner join
members member1_
on postlike0_.member_id=member1_.member_id
where
postlike0_.post_id=?
select
follow0_.follow_id as follow_i1_5_,
follow0_.follow_member_id as follow_m2_5_,
follow0_.member_id as member_i3_5_
from
follows follow0_
where
follow0_.member_id=?
select
postlike0_.post_like_id as post_lik1_22_0_,
member1_.member_id as member_i1_13_1_,
postlike0_.member_id as member_i2_22_0_,
postlike0_.post_id as post_id3_22_0_,
member1_.member_email as member_e2_13_1_,
member1_.member_gender as member_g3_13_1_,
member1_.member_image_name as member_i4_13_1_,
member1_.member_image_type as member_i5_13_1_,
member1_.member_image_uuid as member_i6_13_1_,
member1_.member_image_url as member_i7_13_1_,
member1_.member_introduce as member_i8_13_1_,
member1_.member_name as member_n9_13_1_,
member1_.member_password as member_10_13_1_,
member1_.member_phone as member_11_13_1_,
member1_.member_role as member_12_13_1_,
member1_.member_username as member_13_13_1_,
member1_.member_website as member_14_13_1_
from
post_likes postlike0_
inner join
members member1_
on postlike0_.member_id=member1_.member_id
where
postlike0_.post_id=?
select
follow0_.follow_id as follow_i1_5_,
follow0_.follow_member_id as follow_m2_5_,
follow0_.member_id as member_i3_5_
from
follows follow0_
where
follow0_.member_id=?
# (... 생략 ...)
```
🔹변경 후 쿼리
```sql
select
postlike0_.post_id as col_0_0_,
count(postlike0_.post_like_id) as col_1_0_
from
post_likes postlike0_
inner join
follows follow1_
on (
follow1_.follow_member_id=postlike0_.member_id
and follow1_.member_id=?
)
where
postlike0_.post_id=?
group by
postlike0_.post_id
```
3. 특정 게시물에 좋아요를 누른 회원 중 본인이 팔로우한 회원을 조회하는 로직에서 발생하는 2개의 쿼리를 하나의 쿼리로 최적화하였습니다. (setFollowingMemberUsernameLikedPost)
🔸변경 전 쿼리
```sql
select
follow0_.follow_id as follow_i1_5_,
follow0_.follow_member_id as follow_m2_5_,
follow0_.member_id as member_i3_5_
from
follows follow0_
where
follow0_.member_id=?
select
postlike0_.post_id as col_0_0_,
member1_.member_username as col_1_0_
from
post_likes postlike0_
inner join
members member1_
on postlike0_.member_id=member1_.member_id
where
(
postlike0_.post_id in (
? , ? , ? , ? , ? , ? , ? , ? , ? , ?
)
)
and postlike0_.member_id=?
```
🔹변경 후 쿼리
```sql
select
postlike0_.post_id as col_0_0_,
member1_.member_username as col_1_0_
from
post_likes postlike0_
inner join
members member1_
on postlike0_.member_id=member1_.member_id
where
(
postlike0_.post_id in (
? , ? , ? , ? , ? , ? , ? , ? , ? , ?
)
)
and (
postlike0_.member_id in (
select
follow3_.follow_member_id
from
follows follow3_
inner join
members member4_
on follow3_.follow_member_id=member4_.member_id
where
follow3_.member_id=?
)
)
```
💬Comment
Q파일 추가/변경된 게 있으니 빌드 전에 꼭 ./gradlew clean compileQuerydsl 한 번 실행해주세요.
이후 #205 구현과 서비스계층 리팩토링 및 쿼리 개선 작업 예정입니다.
📑References
✅Check List
[x] 추가한 기능에 대한 테스트는 모두 완료하셨나요?
[x] 코드 정렬(Ctrl + Alt + L), 불필요한 코드나 오타는 없는지 확인하셨나요?
📌Linked Issues
✏Change Details
기능 추가
팔로우한 해시태그가 포함된 게시물도 추가하였습니다.🔸변경 전 쿼리
🔹변경 후 쿼리
```sql select distinct post0_.post_id as col_0_0_, post0_.post_content as col_1_0_, post0_.post_upload_date as col_2_0_, post0_.member_id as col_3_0_, (select count(comments10_.post_id) from comments comments10_ where post0_.post_id = comments10_.post_id) as col_4_0_, (select count(postlikes11_.post_id) from post_likes postlikes11_ where post0_.post_id = postlikes11_.post_id) as col_5_0_, exists (select 1 from bookmarks bookmark3_ where bookmark3_.post_id=post0_.post_id and bookmark3_.member_id=?) as col_6_0_, exists (select 1 from post_likes postlike4_ where postlike4_.post_id=post0_.post_id and postlike4_.member_id=?) as col_7_0_, post0_.post_comment_flag as col_8_0_, post0_.post_like_flag as col_9_0_, member1_.member_id as member_i1_13_, member1_.member_email as member_e2_13_, member1_.member_gender as member_g3_13_, member1_.member_image_name as member_i4_13_, member1_.member_image_type as member_i5_13_, member1_.member_image_uuid as member_i6_13_, member1_.member_image_url as member_i7_13_, member1_.member_introduce as member_i8_13_, member1_.member_name as member_n9_13_, member1_.member_password as member_10_13_, member1_.member_phone as member_11_13_, member1_.member_role as member_12_13_, member1_.member_username as member_13_13_, member1_.member_website as member_14_13_ from posts post0_ inner join members member1_ on post0_.member_id=member1_.member_id where post0_.member_id in ( select follow5_.follow_member_id from follows follow5_ where follow5_.member_id=? ) or post0_.post_id in ( select hashtagpos6_.post_id from hashtag_posts hashtagpos6_ inner join hashtag_follows hashtagfol7_ on ( hashtagfol7_.member_id=? and hashtagfol7_.hashtag_id=hashtagpos6_.hashtag_id ) inner join posts post8_ on hashtagpos6_.post_id=post8_.post_id and ( post8_.member_id<>? ) ) order by post0_.post_id desc limit ? ```게시물 관련 쿼리 최적화
1. 각 게시물마다 content에 포함된 멘션 리스트를 파싱하고, 존재하는 username인지 db를 거쳐서 검증하는 쿼리 호출 횟수를 총
N회 -> 1회로 최적화하였습니다. (setMentionAndHashtagList)🔸변경 전 쿼리
```sql select member0_.member_id as member_i1_13_, member0_.member_email as member_e2_13_, member0_.member_gender as member_g3_13_, member0_.member_image_name as member_i4_13_, member0_.member_image_type as member_i5_13_, member0_.member_image_uuid as member_i6_13_, member0_.member_image_url as member_i7_13_, member0_.member_introduce as member_i8_13_, member0_.member_name as member_n9_13_, member0_.member_password as member_10_13_, member0_.member_phone as member_11_13_, member0_.member_role as member_12_13_, member0_.member_username as member_13_13_, member0_.member_website as member_14_13_ from members member0_ where member0_.member_username in ( ? , ? , ? ) select member0_.member_id as member_i1_13_, member0_.member_email as member_e2_13_, member0_.member_gender as member_g3_13_, member0_.member_image_name as member_i4_13_, member0_.member_image_type as member_i5_13_, member0_.member_image_uuid as member_i6_13_, member0_.member_image_url as member_i7_13_, member0_.member_introduce as member_i8_13_, member0_.member_name as member_n9_13_, member0_.member_password as member_10_13_, member0_.member_phone as member_11_13_, member0_.member_role as member_12_13_, member0_.member_username as member_13_13_, member0_.member_website as member_14_13_ from members member0_ where member0_.member_username in ( ? , ? , ? ) select member0_.member_id as member_i1_13_, member0_.member_email as member_e2_13_, member0_.member_gender as member_g3_13_, member0_.member_image_name as member_i4_13_, member0_.member_image_type as member_i5_13_, member0_.member_image_uuid as member_i6_13_, member0_.member_image_url as member_i7_13_, member0_.member_introduce as member_i8_13_, member0_.member_name as member_n9_13_, member0_.member_password as member_10_13_, member0_.member_phone as member_11_13_, member0_.member_role as member_12_13_, member0_.member_username as member_13_13_, member0_.member_website as member_14_13_ from members member0_ where member0_.member_username in ( ? , ? , ? ) # (... 생략 ...) ```🔹변경 후 쿼리
```sql select member0_.member_id as member_i1_13_, member0_.member_email as member_e2_13_, member0_.member_gender as member_g3_13_, member0_.member_image_name as member_i4_13_, member0_.member_image_type as member_i5_13_, member0_.member_image_uuid as member_i6_13_, member0_.member_image_url as member_i7_13_, member0_.member_introduce as member_i8_13_, member0_.member_name as member_n9_13_, member0_.member_password as member_10_13_, member0_.member_phone as member_11_13_, member0_.member_role as member_12_13_, member0_.member_username as member_13_13_, member0_.member_website as member_14_13_ from members member0_ where member0_.member_username in ( ? , ? , ? ) ```2. 각 게시물마다 좋아요 숨김 옵션을 설정한 경우, 해당 게시물들의 id로 group by하여 옵션이 적용된 좋아요 횟수 조회 쿼리를
2N회 -> 1회로 최적화하였습니다. (hidePostLikesCountIfPostLikeFlagIsFalse)🔸변경 전 쿼리
```sql select postlike0_.post_like_id as post_lik1_22_0_, member1_.member_id as member_i1_13_1_, postlike0_.member_id as member_i2_22_0_, postlike0_.post_id as post_id3_22_0_, member1_.member_email as member_e2_13_1_, member1_.member_gender as member_g3_13_1_, member1_.member_image_name as member_i4_13_1_, member1_.member_image_type as member_i5_13_1_, member1_.member_image_uuid as member_i6_13_1_, member1_.member_image_url as member_i7_13_1_, member1_.member_introduce as member_i8_13_1_, member1_.member_name as member_n9_13_1_, member1_.member_password as member_10_13_1_, member1_.member_phone as member_11_13_1_, member1_.member_role as member_12_13_1_, member1_.member_username as member_13_13_1_, member1_.member_website as member_14_13_1_ from post_likes postlike0_ inner join members member1_ on postlike0_.member_id=member1_.member_id where postlike0_.post_id=? select follow0_.follow_id as follow_i1_5_, follow0_.follow_member_id as follow_m2_5_, follow0_.member_id as member_i3_5_ from follows follow0_ where follow0_.member_id=? select postlike0_.post_like_id as post_lik1_22_0_, member1_.member_id as member_i1_13_1_, postlike0_.member_id as member_i2_22_0_, postlike0_.post_id as post_id3_22_0_, member1_.member_email as member_e2_13_1_, member1_.member_gender as member_g3_13_1_, member1_.member_image_name as member_i4_13_1_, member1_.member_image_type as member_i5_13_1_, member1_.member_image_uuid as member_i6_13_1_, member1_.member_image_url as member_i7_13_1_, member1_.member_introduce as member_i8_13_1_, member1_.member_name as member_n9_13_1_, member1_.member_password as member_10_13_1_, member1_.member_phone as member_11_13_1_, member1_.member_role as member_12_13_1_, member1_.member_username as member_13_13_1_, member1_.member_website as member_14_13_1_ from post_likes postlike0_ inner join members member1_ on postlike0_.member_id=member1_.member_id where postlike0_.post_id=? select follow0_.follow_id as follow_i1_5_, follow0_.follow_member_id as follow_m2_5_, follow0_.member_id as member_i3_5_ from follows follow0_ where follow0_.member_id=? # (... 생략 ...) ```🔹변경 후 쿼리
```sql select postlike0_.post_id as col_0_0_, count(postlike0_.post_like_id) as col_1_0_ from post_likes postlike0_ inner join follows follow1_ on ( follow1_.follow_member_id=postlike0_.member_id and follow1_.member_id=? ) where postlike0_.post_id=? group by postlike0_.post_id ```3. 특정 게시물에 좋아요를 누른 회원 중 본인이 팔로우한 회원을 조회하는 로직에서 발생하는 2개의 쿼리를 하나의 쿼리로 최적화하였습니다. (
setFollowingMemberUsernameLikedPost)🔸변경 전 쿼리
```sql select follow0_.follow_id as follow_i1_5_, follow0_.follow_member_id as follow_m2_5_, follow0_.member_id as member_i3_5_ from follows follow0_ where follow0_.member_id=? select postlike0_.post_id as col_0_0_, member1_.member_username as col_1_0_ from post_likes postlike0_ inner join members member1_ on postlike0_.member_id=member1_.member_id where ( postlike0_.post_id in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ) and postlike0_.member_id=? ```🔹변경 후 쿼리
```sql select postlike0_.post_id as col_0_0_, member1_.member_username as col_1_0_ from post_likes postlike0_ inner join members member1_ on postlike0_.member_id=member1_.member_id where ( postlike0_.post_id in ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) ) and ( postlike0_.member_id in ( select follow3_.follow_member_id from follows follow3_ inner join members member4_ on follow3_.follow_member_id=member4_.member_id where follow3_.member_id=? ) ) ```💬Comment
./gradlew clean compileQuerydsl한 번 실행해주세요.📑References
✅Check List