인덱스 측정 결과

측정 조건

• DB 조건 : user 500명, 각 유저당 album 500개, 각 album에 picture 10개(총 2,500,000)
• 시나리오 조건 : 30초동안 2000VUs로 증가, 1분동안 2000VUs 유지, 30초동안 감소
• 측정도구 : k6

1. /api/album/monthly

api 요청 시 쿼리

Hibernate: 
    select
        u1_0.id,
        u1_0.created_at,
        u1_0.email,
        u1_0.nickname,
        u1_0.profile_image,
        u1_0.provider,
        u1_0.provider_id,
        u1_0.s3key,
        u1_0.updated_at,
        u1_0.user_status 
    from
        users u1_0 
    where
        u1_0.id=?
Hibernate: 
    select
        ua1_0.id,
        ua1_0.album_id,
        ua1_0.deleted_at,
        ua1_0.role,
        ua1_0.user_id 
    from
        user_album ua1_0 
    **where
        (
            ua1_0.deleted_at IS NULL
        ) 
        and ua1_0.user_id=?**
Hibernate: 
    select
        ua1_0.id 
    from
        user_album ua1_0 
    **left join
        album a1_0 
            on a1_0.id=ua1_0.album_id 
            and (a1_0.deleted_at IS NULL) 
    where
        (
            ua1_0.deleted_at IS NULL
        ) 
        and ua1_0.user_id=? 
        and a1_0.created_at<?** 
    limit
        ?

인덱스 정리

album

인덱스명	컬럼 목록	목적 (용도)
idx_album_created_deleted	created_at, deleted_at	생성일 기준 정렬/필터링, 삭제 여부 필터링

userAlbum

인덱스명	컬럼 목록	목적 (용도)
idx_user_album_albumid_deleted	album_id, deleted_at	특정 앨범에 속한 유저 목록 조회 최적화
idx_user_album_userid_deleted	user_id, deleted_at	특정 유저의 앨범 목록 조회 최적화

측정 결과

지표 구분	인덱스 전	인덱스 후	변화율	분석 요약
duration (avg)	40	7	🔻 -82.5%	평균 요청 응답 시간 크게 감소
duration (p90)	98	18	🔻 -81.6%	상위 10% 요청 응답 시간 개선
duration (p95)	202	30	🔻 -85.1%	상위 5% 요청에서도 병목 크게 해소
duration (p99)	749	63	🔻 -91.6%	최악의 응답도 10배 이상 빨라짐
waiting (avg)	31	6	🔻 -80.6%	DB 처리 시간도 평균적으로 빠름
waiting (p90)	69	15	🔻 -78.3%	대부분 요청에서 DB 응답 시간 안정화
waiting (p95)	145	24	🔻 -83.4%	상위 요청 응답도 DB 튜닝 효과 뚜렷
waiting (p99)	551	51	🔻 -90.7%	극단적인 병목도 거의 제거됨

인덱싱 전

인덱싱 후

2. /api/user/statistics/tag

api 요청 시 쿼리

```sql
Hibernate: 
    select
        u1_0.id,
        u1_0.created_at,
        u1_0.email,
        u1_0.nickname,
        u1_0.profile_image,
        u1_0.provider,
        u1_0.provider_id,
        u1_0.s3key,
        u1_0.updated_at,
        u1_0.user_status 
    from
        users u1_0 
    where
        u1_0.id=?
Hibernate: 
    select
        p1_0.id,
        p1_0.album_id,
        p1_0.created_at,
        p1_0.created_date,
        p1_0.deleted_at,
        p1_0.is_duplicated,
        p1_0.is_shaky,
        p1_0.latitude,
        p1_0.longitude,
        p1_0.pictureurl,
        p1_0.place_id,
        p1_0.quality_score,
        p1_0.s3key,
        p1_0.tag,
        p1_0.take_at,
        p1_0.user_id 
    from
        picture p1_0 
    **where
        (
            p1_0.deleted_at IS NULL
        ) 
        and p1_0.user_id=? 
        and p1_0.created_at between ? and ?**
```

인덱스 정리

picture

인덱스명	컬럼 목록	목적 (용도)
idx_picture_user_deleted_created	user_id, deleted_at , created_at	soft deleted 필터링 조건에서 특정 사용자와 사진 생성 시점으로 필터링

• 측정 결과

  지표 구분	인덱스 전 (ms)	인덱스 후 (ms)	변화율	분석 요약
  duration (avg)	959	783	🔻 -18.4%	평균 응답 시간 개선으로 전반적인 응답 속도 향상
  duration (p90)	1000	1000	➖ 0.0%	상위 10% 응답 시간은 유지됨 (여전히 1초)
  duration (p95)	2000	1000	🔻 -50.0%	상위 5% 요청에서 병목 절반으로 완화
  duration (p99)	2000	2000	➖ 0.0%	극단적인 응답 속도는 그대로 (개선 없음)
  waiting (avg)	958	779	🔻 -18.7%	DB 처리 시간 평균적으로 감소
  waiting (p90)	1000	1000	➖ 0.0%	대다수 요청에서는 변화 없음
  waiting (p95)	2000	1000	🔻 -50.0%	병목이 있던 상위 요청군에서 크게 개선
  waiting (p99)	2000	2000	➖ 0.0%	최악의 케이스는 여전히 남아 있음

인덱스 전

인덱스 후

3. /api/user/statistics/picture

api 요청 시 쿼리

```sql
Hibernate: 
    select
        u1_0.id,
        u1_0.created_at,
        u1_0.email,
        u1_0.nickname,
        u1_0.profile_image,
        u1_0.provider,
        u1_0.provider_id,
        u1_0.s3key,
        u1_0.updated_at,
        u1_0.user_status 
    from
        users u1_0 
    where
        u1_0.id=?
Hibernate: 
    select
        date(p1_0.created_date),
        count(p1_0.id) 
    from
        picture p1_0 
    where
        (
            p1_0.deleted_at IS NULL
        ) 
        and p1_0.user_id=? 
        and p1_0.created_date between ? and ? 
    group by
        date(p1_0.created_date) 
    order by
        date(p1_0.created_date)
```

인덱스 정리

picture

인덱스명	컬럼 목록	목적 (용도)
idx_picture_user_deleted_created_date	user_id, deleted_at , created_date	soft deleted 필터링 조건에서 특정 사용자와 사진 생성 시점으로 필터링

컬럼 추가

• /api/user/statistics/picture는 yearMonth를 받아 각 Day의 사진 수를 반환하고, 로직 중에 LocalDate를 key로 사진 수를 매핑하는 Map이 있음.
• LocalDateTime로 저장되는 created_at보다 통계용 컬럼인 created_date(type:LocalDate)를 설정하여 인덱싱을 하는 것이 조회 시 유리할 것으로 판단하여 picture 엔티티에 컬럼 추가

측정 결과

지표 구분	인덱스 전 (ms)	인덱스 후 (ms)	변화율	분석 요약
duration (avg)	21	6	🔻 -71.4%	평균 응답 시간이 크게 개선됨 – 전체 시스템 성능 향상
duration (p90)	57	11	🔻 -80.7%	상위 10% 요청까지도 응답 지연이 거의 사라짐
duration (p95)	115	18	🔻 -84.3%	병목 구간이었던 구간에서 응답 시간 대폭 단축
duration (p99)	245	43	🔻 -82.4%	극단적 지연 요청도 대부분 해결됨
waiting (avg)	16	6	🔻 -62.5%	DB 처리 시간도 확실히 감소 – 인덱스 효과 직결
waiting (p90)	42	11	🔻 -73.8%	병목 제거로 고부하 구간까지 안정화됨
waiting (p95)	82	17	🔻 -79.3%	대기 시간 길었던 요청군이 거의 해소됨
waiting (p99)	190	41	🔻 -78.4%	최악의 요청들도 크게 빨라짐

인덱스 전

인덱스 후

결론

• 인덱스 결과 duration과 waiting 모두 유의미한 차이를 보임
• 특히 p95, p99와 같은 상위 지연에 대한 개선폭이 크며, 이는 대부분의 요청에 대한 응답속도가 개선됨을 의미함
• 결과적으로 사용자 응답 속도 개선, 서버 부하 감소, 처리 안정성 향상에 기여함.

인덱스, 스키마 변경 dev 반영

현 상황

• dev, prod 서버의 환경설정은 ddl-auto:none 으로 설정되어 있어, JPA 단위의 코드 변화로는 스키마 변경을 감지하지 못한다.

• 그렇다고 설정을 update나 create로 둔다면 하이버네이트가 자동으로 스키마를 변경하여 데이터베이스 부분에서 오류가 발생할 수도 있고, 컬럼 삭제나 스키마 변경 시 데이터가 의도치 않게 소실되는 등 개발자의 의도와 다르게 작동할 가능성이 높다.

• 가장 쉬운 방법은 db를 초기화하는 방법이겠지만, 실제 운영되는 서비스이기 때문에 최대한 ‘운영중인 db를 어떻게 안전하게 수정하지’라고 생각하던 중 DB 마이그레이션 툴을 알게 되서 이를 적용하게 되었다.

• 대안

• DB 마이그레이션 툴을 사용하면 의도치 않은 변경을 방지하고 개발자가 직접 스크립트를 통해 마이그레이션을 할 수 있다. 또한 깃처럼 형상관리를 할 수 있다는 것이 큰 장점이다.

• prod/dev 서버에 직접 sql 쿼리를 적용하는 것보다 훨씬 안전하고 추적관리가 용이하다는 장점이 있다.

Flyway

• 스크립트를 통해 배포와 동시에 스키마를 변경할 수 있다.
• 버저닝을 할 수 있고, 스크립트 성공/실패 여부와 버저닝 적용 여부를 flyway_schema_history를 통해 확인할 수 있다.

에러로그

• 깃액션으로 도커 빌드하면 자꾸 다음 에러가 발생

Detected failed migration to version 2 (add created date column and index).
Please remove any half-completed changes then run repair to fix the schema history.

• 이전 마이그레이션 시 flyway가 version 2의 스크립트가 실패했음을 기록했고, 그 기록이 남아있기 때문에 flyway는 V2 스크립트는 실패한 스크립트라고 인식하고 다음 마이그레이션에서 에러를 반환함
• 중요한건, V2 스크립트를 올바르게 수정해도 발생하는 에러이다. 파일의 checksum이 달라져 ‘이 스크립트는 오염되었다’라고 판단하기 때문이다. 상세한 원인은 flyway_schema_history 테이블을 확인하면 된다.

installed_rank	version	description	script	success
1	1	<< Flyway Baseline >>	<< Flyway Baseline >>	1
2	2	add created date column and index	V2__add_created_date_column_and_index.sql	0

• success가 0으로 기록된 버전이 있으면 flyway는 어떠한 마이그레이션도 실시하지 않는다.

해결책

• flywayRepair를 통해 flyway_schema_history 테이블을 정리해야 함.

  • 내부 정리 로직

    DELETE FROM flyway_schema_history WHERE success = 0;
    

  • 현재 마이그레이션 파일과 DB의 checksum이 다른 경우, DB의 checksum을 현재 파일 기준으로 덮어씀

    • 즉, checksum mismatch가 더 이상 오류를 발생시키지 않음.

  • 정상적으로 완료된 마이그레이션(=success=1)은 유지함

    • 데이터는 건드리지 않음
    • 실제 SQL은 다시 실행하지 않음

  • flywayRepair 유의사항

    • checksum을 덮어쓰는 것이기 때문에 만약 스크립트를 수정했다면 해당 스크립트는 반영이 되지 않음
    • 스키마를 수정해야 한다면 스크립트를 수정하지 말고 새로운 버전의 스크립트를 작성하는 것이 데이터 일관성에 더 좋다.

결과

• dev서버에 인덱스 반영 성공

• 추가 커럼(created_date) 반영 성공