디비 성능 개선

[yeeuniii]

작업 요약

디비 성능 개선

• 달력형 페이지 조회 시, 너무 많은 데이터 쿼리 날리는 문제
  • 아래와 같은 데이터일 때, 16번

• 위 데이터보다 그룹원 한 명 더 있었을 때, 22번 발생

  
  2024-10-30 16:08:12.932 [http-nio-8080-exec-1] [DEBUG] j.sqlonly -  com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
  1. select rt1_0."refresh_token_id",rt1_0."created_at",rt1_0."member_id",rt1_0."token",rt1_0."updated_at" from "refresh_token" rt1_0 where rt1_0."member_id"=3

2024-10-30 16:08:12.999 [http-nio-8080-exec-1] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."member_id",m1_0."created_at",m1_0."group_id",m1_0."group_role",m1_0."kakao_id",m1_0."last_viewable_diary_date",m1_0."nickname",m1_0."order_in_group",m1_0."profile_image",m1_0."updated_at" from "member" m1_0 where m1_0."member_id"=3

2024-10-30 16:08:13.009 [http-nio-8080-exec-1] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."group_id" from "member" m1_0 where m1_0."member_id"=3

2024-10-30 16:08:13.014 [http-nio-8080-exec-1] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."group_id" from "member" m1_0 where m1_0."member_id"=3

2024-10-30 16:08:13.039 [http-nio-8080-exec-1] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select g1_0."group_id",g1_0."code",g1_0."created_at",g1_0."current_order",g1_0."last_skip_order_date",g1_0."name",g1_0."updated_at" from "group" g1_0 where g1_0."group_id"=100

2024-10-30 16:08:14.253 [http-nio-8080-exec-5] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."member_id",m1_0."created_at",m1_0."group_id",m1_0."group_role",m1_0."kakao_id",m1_0."last_viewable_diary_date",m1_0."nickname",m1_0."order_in_group",m1_0."profile_image",m1_0."updated_at" from "member" m1_0 where m1_0."member_id"=3

2024-10-30 16:08:14.261 [http-nio-8080-exec-5] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."group_id" from "member" m1_0 where m1_0."member_id"=3

2024-10-30 16:08:14.283 [http-nio-8080-exec-5] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select d1_0."diary_id",d1_0."content",d1_0."created_at",d1_0."group_id",d1_0."member_id",d1_0."mood_location",d1_0."updated_at" from "diary" d1_0 where d1_0."group_id"=100 and extract(year from d1_0."created_at")=2024 and extract(month from d1_0."created_at")=10 order by d1_0."created_at"

2024-10-30 16:08:14.292 [http-nio-8080-exec-5] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."last_viewable_diary_date" from "member" m1_0 where m1_0."member_id"=3

2024-10-30 16:08:14.296 [http-nio-8080-exec-5] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."member_id",m1_0."created_at",m1_0."group_id",m1_0."group_role",m1_0."kakao_id",m1_0."last_viewable_diary_date",m1_0."nickname",m1_0."order_in_group",m1_0."profile_image",m1_0."updated_at" from "member" m1_0 where m1_0."member_id"=1000

2024-10-30 16:08:14.302 [http-nio-8080-exec-5] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."member_id",m1_0."created_at",m1_0."group_id",m1_0."group_role",m1_0."kakao_id",m1_0."last_viewable_diary_date",m1_0."nickname",m1_0."order_in_group",m1_0."profile_image",m1_0."updated_at" from "member" m1_0 where m1_0."member_id"=1001

2024-10-30 16:08:14.305 [http-nio-8080-exec-5] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."member_id",m1_0."created_at",m1_0."group_id",m1_0."group_role",m1_0."kakao_id",m1_0."last_viewable_diary_date",m1_0."nickname",m1_0."order_in_group",m1_0."profile_image",m1_0."updated_at" from "member" m1_0 where m1_0."member_id"=1002

2024-10-30 16:08:14.309 [http-nio-8080-exec-5] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."member_id",m1_0."created_at",m1_0."group_id",m1_0."group_role",m1_0."kakao_id",m1_0."last_viewable_diary_date",m1_0."nickname",m1_0."order_in_group",m1_0."profile_image",m1_0."updated_at" from "member" m1_0 where m1_0."member_id"=1003

2024-10-30 16:08:14.312 [http-nio-8080-exec-5] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."member_id",m1_0."created_at",m1_0."group_id",m1_0."group_role",m1_0."kakao_id",m1_0."last_viewable_diary_date",m1_0."nickname",m1_0."order_in_group",m1_0."profile_image",m1_0."updated_at" from "member" m1_0 where m1_0."member_id"=1004

2024-10-30 16:08:14.314 [http-nio-8080-exec-5] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."member_id",m1_0."created_at",m1_0."group_id",m1_0."group_role",m1_0."kakao_id",m1_0."last_viewable_diary_date",m1_0."nickname",m1_0."order_in_group",m1_0."profile_image",m1_0."updated_at" from "member" m1_0 where m1_0."member_id"=3

2024-10-30 16:08:14.374 [http-nio-8080-exec-9] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."member_id",m1_0."created_at",m1_0."group_id",m1_0."group_role",m1_0."kakao_id",m1_0."last_viewable_diary_date",m1_0."nickname",m1_0."order_in_group",m1_0."profile_image",m1_0."updated_at" from "member" m1_0 where m1_0."member_id"=3

2024-10-30 16:08:14.379 [http-nio-8080-exec-9] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select m1_0."group_id" from "member" m1_0 where m1_0."member_id"=3

2024-10-30 16:08:14.396 [http-nio-8080-exec-9] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select case when m1_0."order_in_group"=g1_0."current_order" then true else false end from "group" g1_0 join "member" m1_0 on g1_0."group_id"=m1_0."group_id" and m1_0."member_id"=3

2024-10-30 16:08:14.407 [http-nio-8080-exec-9] [DEBUG] j.sqlonly - com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)

1. select d1_0."diary_id",d1_0."content",d1_0."created_at",d1_0."group_id",d1_0."member_id",d1_0."mood_location",d1_0."updated_at" from "diary" d1_0 where d1_0."group_id"=100 and cast(d1_0."created_at" as date)=current_date

### 작업 세부 사항
- [x] 달력형 조회 API에서 일기 조회 시 사용자와 join 사용
- [x] 레코드 존재하는지 확인할 때는 exists 사용