[척척학사] API 성능 튜닝: P6Spy 쿼리 분석부터 인덱싱 & 캐싱까지

들어가며

이번 글에서는 P6Spy를 활용해 API 쿼리 로그를 정밀 분석하고, 주요 API의 병목 지점을 찾아 리팩토링과 인덱싱, 캐싱을 통해 성능을 극적으로 개선한 과정을 정리했습니다.

테스트 환경

• 로컬 개발 환경 기준 (P6Spy는 운영 환경에서는 제외)
• 추후 더미 데이터를 이용한 대규모 부하 테스트 예정

---

📌 1. 학기 수강 정보 조회 (/api/academic/record)

이 API는 사용자의 학기별 수강 정보를 불러오는 핵심 기능입니다.
초기에는 학업 관련 테이블들이 분리되어 있어 중복 쿼리가 다수 발생했고, API 전체 처리 시간이 약 546ms에 달했습니다.

이를 개선하기 위해 복합 인덱스 추가와 Fetch Join 적용을 중심으로 리팩토링을 진행했습니다.

🔴 리팩토링 전 쿼리 로그

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	80ms	statement	users	사용자 정보 조회 (user_id → student_id 연결용)
2	86ms	statement	students, graduation_progress, academic_records	기본 정보 및 누적 학업 상태 조회
3	80ms	statement	semester_academic_records	특정 학기 성적 요약 조회
4	85ms	statement	student_courses, course_offerings, courses, professor	수강 강의 전체 조회
5	80ms	statement	graduation_progress	졸업 진척도 재조회
6	76ms	statement	academic_records	누적 학점/성적 재조회

총 6개의 쿼리가 실행되며,
API 처리 시간은 약 546ms, 쿼리 실행 시간은 564ms로 측정되었습니다.

🟢 리팩토링 후 쿼리 로그

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	80ms	statement	users	사용자 정보 조회
2	70ms	statement	students	기본 정보 조회
3	88ms	statement	semester_academic_records	특정 학기 성적 요약 (복합 인덱스 적용)
4	83ms	statement	student_courses, course_offerings, courses, professor	해당 학기 수강 강의 전체 조회

쿼리 수는 6개 → 4개로 줄었고,
API 처리 시간도 546ms → 321ms, 약 41% 단축되었습니다.
graduation_progress와 academic_records의 중복 조회가 제거되었으며, 연관 관계에 Fetch Join을 적용해 N+1 문제도 해결했습니다.

✅ 개선 요약

• 중복 조회 제거로 DB 접근 최소화
• (student_id, year, semester) 복합 인덱스 추가로 단건 조회 성능 향상
• 주요 연관 객체 Fetch Join 적용으로 N+1 문제 제거

CREATE INDEX CONCURRENTLY idx_semester_record_student_year_semester
ON semester_academic_records (student_id, year, semester);

---

📌 2. 전체 성적 요약 (/api/academic/summary)

이 API는 학생의 전체 성적과 졸업 요건 상태를 함께 조회합니다.
초기 버전은 academic_records와 graduation_progress를 매번 다시 불러와 총 5개의 SELECT 쿼리가 실행되는 구조였습니다.

🔴 리팩토링 전 쿼리 로그

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	115ms	statement	users	사용자 정보 조회
2	146ms	statement	students + graduation_progress + academic_records	학업 상태 JOIN
3	82ms	statement	academic_records	누적 성적 재조회
4	85ms	statement	students + graduation_progress + academic_records	재조회 (중복)
5	213ms	statement	department_area_requirements	학과별 이수 요건 조회

총 5개 SELECT 쿼리,
API 처리 시간은 약 556ms, 쿼리 시간 합계는 641ms였습니다.

🟡 1차 리팩토링 (중복 제거 + Redis 캐시 도입)

Redis를 도입하여 학과별 이수 요건(department_area_requirements)을 캐싱하고, 중복된 성적·진행도 조회를 제거했습니다.

순번	실행 시간	주요 테이블	설명 요약
1	81ms	users	사용자 조회
2	80ms	students	학번 및 학과 조회
3	83ms	student_academic_records	누적 성적 조회

쿼리 수는 5개 → 3개, API 처리 시간은 556ms → 207ms (약 63% 단축)되었습니다.

🟢 2차 리팩토링 (성적 요약 응답 DTO 캐싱)

최종적으로 계산된 성적 요약 DTO 자체를 Redis에 캐싱하여 DB 조회를 완전히 제거했습니다.

순번	실행 시간	주요 테이블	설명 요약
1	84ms	users	사용자 정보 조회
2	83ms	students	학생 엔티티 조회
3	0ms	redis	✅ 캐시에서 최종 DTO 조회

쿼리 수는 2개 (DB 조회 0), API 처리 시간은 17ms로 단축되었습니다.

✅ 개선 요약

• Redis 캐시를 통해 학과별 졸업 요건 및 성적 정보를 즉시 반환
• 계산된 DTO 캐싱으로 DB I/O 제거
• API 처리 시간 약 96.9% 단축 (556ms → 17ms)

---

📌 3. 수강 학기 정보 조회 (/api/semester)

이 API는 학생의 학기별 성적 요약 데이터를 반환합니다.
초기 버전에서는 매 요청마다 DB에서 모든 데이터를 불러와 불필요한 중복 조회와 Stream 정렬로 인한 오버헤드가 존재했습니다.

🔴 리팩토링 전 쿼리 로그

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	83ms	statement	users	사용자 정보 조회
2	90ms	statement	students + graduation_progress + academic_records	학생 상태 조회
3	88ms	statement	semester_academic_records	전체 학기 성적 요약 조회

총 3개의 SELECT 쿼리가 실행되었으며, API 처리 시간은 198ms였습니다.
Java Stream을 이용해 응답 직전에 정렬을 수행했습니다.

🟡 1차 리팩토링 (Redis + DTO 캐싱 적용)

Redis에 학기별 성적 요약 데이터를 캐싱하고, 최종 응답 DTO까지 캐싱하는 2단계 구조를 도입했습니다. 또한, DB 정렬 후 반환하는 방식으로 변경했습니다.

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	82ms	statement	users	사용자 정보 조회
2	79ms	statement	students	studentId 조회
3	0ms	redis	✅ 학기 성적 요약 정보 + DTO 캐시 조회

쿼리 수는 3개 → 2개 (SELECT) + 1개 Redis 조회로 줄었고, API 처리 시간은 198ms → 62ms로 개선되었습니다.

🟢 2차 리팩토링 (DTO 캐싱 제거 → 단일 캐시 구조)

DTO 캐싱은 빠르지만 Redis 메모리 점유율이 높다는 단점이 있었습니다.
따라서 캐싱 단위를 “DTO 전체”에서 “요약 데이터 리스트”로 축소하고, 서비스 레이어에서 DTO 변환을 매번 수행하도록 구조를 변경했습니다.

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	89ms	statement	users	사용자 정보 조회
2	121ms	statement	students	studentId 조회
3	0ms	redis	✅ SemesterSummaryResponse 리스트 캐시 조회

쿼리 수는 동일하지만, API 전체 처리 시간은 62ms → 7ms로 대폭 단축되었고, Redis 메모리 효율 및 캐시 재사용성이 크게 향상되었습니다.

---

📌 4. 전체 학기 성적 조회 (/api/semester/grades)

이 API는 학생의 모든 학기 성적 요약 데이터를 조회합니다.
초기 버전에서는 매 요청마다 전체 학기 데이터를 DB에서 직접 조회했기 때문에 쿼리 부하와 정렬 비용이 컸습니다.

🔴 리팩토링 전 쿼리 로그

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	81ms	statement	users	사용자 정보 조회 (userId → studentId 매핑)
2	82ms	statement	students + graduation_progress + academic_records	학생 상태 및 학업 식별 조회
3	82ms	statement	semester_academic_records	전체 학기 성적 요약 및 정렬 포함 조회

총 3개의 SELECT 쿼리가 실행되었으며, API 처리 시간은 178ms였습니다.

🟢 리팩토링 후 쿼리 로그 (Redis 캐시 적용)

Redis 캐시를 도입하여 전체 학기 성적 요약 데이터를 semester-summaries 키로 캐싱했습니다. 앞서 개선한 /api/semester와 캐시를 공유합니다.

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	76ms	statement	users	사용자 정보 조회
2	77ms	statement	students	studentId 조회 (userId 매핑)
3	0ms	redis	✅ 전체 학기 성적 요약 정보 캐시 조회 (semester-summaries)

쿼리 수는 3개 → 2개 (SELECT) + 1개 Redis 조회로 줄었으며,
API 전체 처리 시간은 178ms → 36ms (80% 단축)로 개선되었습니다.

---

📌 5. 졸업 요건 조회 (/api/graduation/progress)

이 API는 학생의 이수 과목과 학과별 졸업 요건을 비교하여 현재 졸업 진행 상태를 계산하는 기능입니다.

초기에는 복잡한 졸업 요건을 처리하기 위해 CTE(Common Table Expression) 방식을 사용했습니다. 하지만 이는 유지보수가 어렵고 성능 저하의 주원인이 되었습니다.

💡 설계 개선에 대한 자세한 내용

CTE 쿼리를 비즈니스 로직 단위로 분해하고 아키텍처를 개선한 과정은 아래 글에서 상세히 다루고 있습니다. 이번 글에서는 '성능 수치 변화'에 집중합니다.

👉 [척척학사] 복잡한 졸업 요건 검증 로직: 거대 SQL(CTE)에서 애플리케이션 조합으로의 전환

🔴 리팩토링 전 쿼리 로그 (CTE + 무캐싱)

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	118ms	statement	users	사용자 정보 조회
2	213ms	statement	students + graduation_progress + academic_records	학생 상태 조회
3	83ms	statement	students + graduation_progress + academic_records	중복 재조회
4	479ms	statement	student_courses + course_offerings + department_area_requirements	졸업요건 CTE 쿼리 수행

총 4개의 SELECT 쿼리가 실행되었으며, API 처리 시간은 891ms로 가장 느렸습니다.

🟡 1차 리팩토링 (CTE 분해 + 졸업 요건 캐싱)

복잡한 CTE 쿼리를 단계별로 분해하고, 입학년도 및 학과별 졸업 요건 데이터를 Redis에 캐싱했습니다.

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	81ms	statement	users	사용자 정보 조회
2	97ms	statement	students	userId → studentId 매핑
3	93ms	statement	students	studentId → 상태 조회
4	98ms	statement	student_courses + course_offerings + courses	이수 과목 최신 버전 필터링
5	82ms	statement	department_area_requirements	✅ 졸업 요건 Redis 캐시 활용

쿼리 수는 5개로 소폭 증가했지만, 중복 계산이 제거되어 API 처리 시간은 891ms → 431ms로 개선되었습니다.

🟢 2차 리팩토링 (최종 응답 DTO 캐싱)

최종 계산된 졸업 진행 정보를 DTO 단위로 Redis에 캐싱했습니다.

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	173ms	statement	users	사용자 정보 조회
2	97ms	statement	students	userId → studentId 매핑
3	0ms	redis	✅ 최종 졸업 진행 정보 DTO 캐시 조회 (graduation-progress:{studentId})

쿼리 수는 2개 SELECT + 1개 Redis 조회, API 처리 시간은 891ms → 32ms로 극적으로 단축되었습니다.

✅ 개선 요약

• 복잡한 CTE 쿼리를 분해하여 각 단계별 캐싱 포인트 확보
• API 처리 시간 약 96.4% 단축 (891ms → 32ms)

---

📌 6. 목표 학점 저장 (/api/student/target-gpa)

이 API는 학생의 목표 학점을 수정하는 기능입니다.
초기 버전에서는 단순한 필드 업데이트임에도 불구하고 save() 메서드를 사용해 엔티티 전체를 로딩했기 때문에 불필요한 쿼리가 발생했습니다.

🔴 리팩토링 전 쿼리 로그 (불필요한 재조회 포함)

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	80ms	statement	users	사용자 정보 조회
2	81ms	statement	students + graduation_progress + academic_records	상태 조회
3	81ms	statement	students + graduation_progress + academic_records	중복 재조회
4	80ms	statement	students	목표 학점 업데이트 수행

총 4개의 쿼리(SELECT 3 + UPDATE 1)가 실행되었으며, API 처리 시간은 256ms였습니다.

🟢 리팩토링 후 쿼리 로그 (중복 제거 + JPQL 경량 업데이트 적용)

Dirty Checking 대신 @Modifying JPQL 쿼리를 사용해 엔티티 전체 로딩 없이 특정 필드만 직접 수정하도록 변경했습니다.

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	80ms	statement	users	사용자 정보 조회
2	80ms	statement	students	userId → studentId 조회
3	98ms	statement	students	✅ JPQL 기반 목표 학점 업데이트 수행 (@Modifying)

쿼리 수는 4개 → 3개 (SELECT 2 + UPDATE 1)로 줄었고, API 처리 시간은 256ms → 250ms로 개선되었습니다. 수치보다는 DB I/O 부하 감소에 의의가 있습니다.

@Modifying
@Query("UPDATE Student s SET s.targetGpa = :targetGpa WHERE s.id = :studentId")
void updateTargetGpa(@Param("studentId") UUID studentId, @Param("targetGpa") Double targetGpa);

---

📌 7. 학생 프로필 조회 (/api/student/profile)

이 API는 학생의 기본 정보, 학과, 사용자 계정 정보를 통합하여 조회합니다.
초기 버전에서는 학생과 사용자 엔티티를 여러 번 중복 조회하면서 불필요한 쿼리가 다수 발생했습니다.

🔴 리팩토링 전 쿼리 로그 (중복 조회 다수)

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	97ms	statement	users	사용자 정보 1차 조회
2	91ms	statement	students + graduation_progress + academic_records	학생 정보 조회 (1차)
3	89ms	statement	students + graduation_progress + academic_records	학생 정보 재조회 (2차)
4	100ms	statement	departments	학과 정보 조회
5	81ms	statement	users	사용자 재조회 (2차)
6	80ms	statement	students + graduation_progress + academic_records	학생 정보 재조회 (3차)

총 6개의 SELECT 쿼리가 실행되었으며, API 처리 시간은 453ms였습니다.

🟢 리팩토링 후 쿼리 로그 (Fetch Join + 단일 조회 최적화)

Fetch Join을 통해 학과, 전공, 사용자 정보를 한 번에 조회하도록 변경했습니다.

순번	실행 시간	카테고리	주요 테이블	설명 요약
1	90ms	statement	users	사용자 정보 1회 조회
2	87ms	statement	students	userId → student 매핑 조회
3	100ms	statement	students + departments + users	✅ Fetch Join으로 학과/전공/사용자 통합 조회

쿼리 수는 6개 → 3개로 감소했고, API 처리 시간은 453ms → 292ms (약 48.5% 단축)되었습니다.

---

💡 고민: CustomUserDetails에 엔티티를 담을까?

인증된 사용자 정보를 다루는 CustomUserDetails에서 매번 studentId로 DB를 조회하는 비용을 줄이고자 Student 엔티티를 직접 담는 방안을 고려했습니다.

[아이디어]
studentId (UUID) 대신 Student (Entity) 객체 자체를 UserDetails에 포함

[결론: 보류]
성능 이점은 확실하나(조회 쿼리 0회), 다음과 같은 리스크로 인해 적용하지 않기로 했습니다.
1. 세션 비대화: 세션이나 토큰 크기가 커져 네트워크 오버헤드 발생 가능
2. 데이터 정합성: 로그인 후 DB 데이터가 변경되어도 세션 정보는 갱신되지 않음 (치명적)
3. OSIV 이슈: 세션에 저장된 엔티티는 Detached 상태라 Lazy Loading 시 예외 발생

대신, 자주 조회되는 데이터는 짧은 TTL의 Redis 캐시를 활용하는 방향으로 결정했습니다.

---

🚀 마무리

이번 리팩토링을 통해 중복 조회 제거, Fetch Join 도입, 인덱스 튜닝, 그리고 Redis 캐시 전략을 단계적으로 적용하며 주요 API에서 최소 30%에서 최대 96%의 성능 개선을 달성했습니다.

특히 졸업 요건 조회 API의 경우, 복잡한 쿼리를 억지로 튜닝하려 하지 않고 로직을 분해하여 캐싱 포인트를 만들어낸 전략이 유효했습니다.

앞으로는 캐시 무효화(Invalidation) 전략을 더 정교하게 다듬어, '빠르면서도 정확한' 데이터를 제공하는 데 집중할 계획입니다.