[졸프] Spring Boot + MySQL 기반 ‘매주 월요일 1권 추천 도서’ 시스템 설계 & 구현 기록

개요

이번 프로젝트에서는 개인별 추천 도서 풀에서 매주 월요일마다 1권을 확정하여 노출하는 기능을 설계하고 구현했다.
주요 목표는 안정성 확보와 운영 편의성이며, 이를 위해 DB 구조를 개선하고 스케줄러 쿼리를 작성했으며, 필요 없는 컬럼(reason)을 제거했다.

1. 시스템 요구사항

• 매주 월요일 개인별 추천 도서 1권 선정
• 선정된 도서는 주 내내 고정
• 이미 읽은 책(user_book_read에 존재하는 데이터)은 제외
• 추천 도서 풀은 ML 서버에서 제공
• 향후 점수(score) 기반 Top-1로 확장 가능
• 불필요한 컬럼(reason) 제거

2. DB 설계

2.1 추천 도서 풀 (기존)

user_recommended_book
: ML이 생성한 추천 도서를 저장하는 테이블.

변경사항: reason 컬럼 삭제

2.2 주간 확정 도서 (신규)

user_weekly_featured_book
: 월요일마다 확정된 1권의 추천 도서를 저장하는 테이블.

3. 스케줄러 쿼리 (Top-1, score 없음)

MySQL 8+ (윈도 함수 사용)

SET @week_start := DATE_SUB(CURDATE(), INTERVAL (WEEKDAY(CURDATE())) DAY);

INSERT INTO user_weekly_featured_book (user_id, book_id, week_start)
SELECT user_id, book_id, @week_start
FROM (
    SELECT
        ur.user_id,
        ur.book_id,
        ROW_NUMBER() OVER (
            PARTITION BY ur.user_id
            ORDER BY ur.recommended_at DESC, ur.recommend_id DESC
        ) AS rn
    FROM user_recommended_book ur
    WHERE NOT EXISTS (
        SELECT 1
        FROM user_book_read r
        WHERE r.user_id = ur.user_id
          AND r.book_id = ur.book_id
    )
) t
WHERE t.rn = 1
ON DUPLICATE KEY UPDATE book_id = VALUES(book_id);

4. 성능 최적화 인덱스

-- 추천 풀 정렬 가속
CREATE INDEX idx_urb_user_at_id
ON user_recommended_book (user_id, recommended_at DESC, recommend_id DESC);

-- 읽은 책 제외 조건 가속
CREATE INDEX idx_ubr_user_book
ON user_book_read (user_id, book_id);

• idx_urb_user_at_id: 추천 풀에서 유저별 최신 도서를 뽑을 때 정렬 비용을 줄여 Top-1 조회 속도를 크게 향상.

• idx_ubr_user_book: 이미 읽은 책 제외 조건을 인덱스로 처리해 존재 여부 확인 속도를 최적화.

5. 불필요 컬럼(reason) 제거 작업

5.1 DB

ALTER TABLE user_recommended_book DROP COLUMN reason;

5.2 백엔드 코드

• Entity: reason 필드 삭제
• DTO: 응답/요청 DTO에서 reason 삭제
• Repository: SELECT/INSERT/UPDATE 쿼리에서 reason 제거
• Service/Controller: reason 세팅/사용 로직 삭제
• 테스트 코드: JSON 응답 기대값 수정

6. 스케줄러 — 월요일 09:00 확정 저장

@Scheduled(cron = "0 0 9 ? * MON", zone = "Asia/Seoul")
@Transactional
public void publishWeeklyFeatured() {
    LocalDate weekStart = thisMondayKST();
    var userIds = recRepo.findDistinctUserIds();
    var top1 = PageRequest.of(0, 1);

    for (Long userId : userIds) {
        var candidate = recRepo.findTopCandidateForWeekly(userId, top1)
                               .stream().findFirst();
        if (candidate.isEmpty()) continue;

        Integer bookId = candidate.get().getBook().getBookId();

        jdbc.update("""
            INSERT INTO user_weekly_featured_book (user_id, book_id, week_start)
            VALUES (?, ?, ?)
            ON DUPLICATE KEY UPDATE book_id = VALUES(book_id)
        """, userId, bookId, weekStart);
    }
}

7. 향후 확장 계획

• score 컬럼 추가

ALTER TABLE user_recommended_book ADD COLUMN score DECIMAL(6,3) NULL;

• ML 서버에서 점수 함께 전달
• 스케줄러 쿼리 정렬 기준 변경:

ORDER BY score DESC, recommended_at DESC, recommend_id DESC

• 주차 기반 시드 방식 적용 가능(다양성 확보 목적)
• 관리자 페이지에서 특정 주 추천 교체/롤백 기능 추가

이 부분은 나중에 ML에서 점수까지 반환 가능하다고 하면 해볼 예정이다.

8. 이번 구현의 장점

• 안정성: 주간 확정 테이블에 저장 → 주 내 절대 변경 없음
• 운영 편의: UPSERT & UNIQUE KEY로 중복/재실행 안전
• 성능 최적화: 인덱스 설계로 배치 쿼리 속도 향상
• 유연성: score 추가, 추천 정책 변경 가능

지금 만든 쿼리 자체는 DB에서 즉시 실행하는 배치 SQL이라, “월요일 몇 시”라는 시간은 아직 정해져 있지 않다. 그래서 코드를 통해서 월요일 아침 09:00에 되도록 스케줄링 확정 코드를 구현했다. 이제는 ML 연동을 해야한다. ML 연동은 다음 글에!