refactor: DB Repository 계층 쿼리 성능 개선 (5건 - 쿼리 구조 개선 + 인덱스 추가) #845

어떤 부분을 리팩터링하려 하나요?

로컬 환경에서 Repository 계층 전체를 대상으로 쿼리 플랜(EXPLAIN ANALYZE) 조사를 진행해, 비효율이 확인된 5개 쿼리에 대해 구조 개선 및 인덱스 추가를 검증했습니다. 로컬 MySQL(합성 데이터)에서 검증만 완료된 상태이고, 실제 코드/DB에는 아직 반영되지 않았습니다.

AS-IS

• 게시판 목록 조회(PostRepository): category 필터를 애플리케이션 레이어(Java 스트림)에서 처리 — DB에서 board 전체를 가져온 뒤 대부분 버림. (board_code, created_at) 복합 인덱스 없음
• 채팅 메시지 페이징(ChatMessageRepository.findByRoomIdWithPaging): LEFT JOIN FETCH cm.chatAttachments + Pageable 조합 때문에 Hibernate가 SQL LIMIT을 포기하고 방의 전체 메시지를 매 요청마다 로드
• 성적 검수 대기 목록(GpaScoreFilterRepositoryImpl/LanguageTestScoreFilterRepositoryImpl): verify_status/created_at 인덱스가 없어 매번 테이블 전체 스캔
• 대학 검색 / 개인 추천(UnivApplyInfoFilterRepositoryImpl/UnivApplyInfoRepository): languageRequirements(1:N) fetch join으로 행이 폭증(검색 쿼리는 .distinct() 누락으로 결과 List에 중복 엔티티가 들어가는 정합성 버그도 있음)
• 관리자 제재 유저 목록(SiteUserFilterRepositoryImpl.searchRestrictedUsers): 상관 서브쿼리(MAX(report.id))가 결과 페이지 20건마다 report 테이블 전체(6만 건)를 재스캔

TO-BE

• 게시판 목록: category 필터를 SQL WHERE로 이동 + (board_code, category, created_at) 인덱스
• 채팅 메시지 페이징: fetch join 제거(+ChatMessage.chatAttachments에 @BatchSize) + (chat_room_id, created_at) 인덱스
• 성적 검수 목록: (verify_status, created_at) 인덱스 (gpa_score, language_test_score 동일 적용)
• 대학 검색/개인 추천: languageRequirements fetch join 제거 + @BatchSize로 지연 로딩 전환(중복 반환 버그도 함께 해결)
• 관리자 제재 유저 목록: 상관 서브쿼리 → site_user 페이징 + report/user_ban 배치조회(IN절) 3단계로 재작성 + (user_status, created_at), (reported_id) 인덱스

작업 상세 내용

• PostRepository에 category 조건 포함 쿼리 추가, PostQueryService의 인메모리 필터링(getPostListByPostCategory) 제거
• ChatMessageRepository.findByRoomIdWithPaging에서 LEFT JOIN FETCH cm.chatAttachments 제거, ChatMessage.chatAttachments에 @BatchSize 적용
• SiteUserFilterRepositoryImpl.searchRestrictedUsers를 배치조회 방식으로 재작성
• UnivApplyInfoRepository, UnivApplyInfoFilterRepositoryImpl에서 languageRequirements fetch join 제거, UnivApplyInfo.languageRequirements에 @BatchSize 적용
• Flyway 마이그레이션(V60__...)으로 인덱스 8종 추가: post, post_image, post_like, chat_message, gpa_score, language_test_score, site_user, report
• (선택) 성적 검수 count 쿼리에 인덱스 힌트(IGNORE INDEX) 적용 검토 — 선택도가 낮아 인덱스 사용 시 오히려 느려지는 케이스 확인됨
• 관련 테스트 코드 확인/보강

참고할만한 자료(선택)

로컬 MySQL 8.0(합성 데이터: post 8만 건, chat_message 14.9만 건, site_user 1.5만 건, report 6만 건 등) 기준 EXPLAIN ANALYZE 개선 효과:

상세 조사/검증 과정(적용 SQL, EXPLAIN ANALYZE 로그, 반복측정 결과)은 Notion "쿼리 플랜 결과" DB에 기록되어 있습니다.

🤖 Generated with Claude Code