스케줄 작업시 쿼리 최적화

Projection 관련 정리한 글

Projection 관련 학습을 진행하면서

해당 부분의 쿼리 최적화에도 관심을 가지게 되었다.

그러면서 문제를 발견하게 되었는데

@Transactional
public void processDormant() {
    // 기준 날짜 설정 (매월 1일 실행)
    YearMonth currentMonth = YearMonth.now();

    // 각 대상의 범위 조회 및 처리
    List<DormantAccountProjection> notifyCandidates = findCandidatesByMonthsAgo(11);
    List<DormantAccountProjection> lockCandidates = findCandidatesByMonthsAgo(12);
    List<DormantAccountProjection> deleteCandidates = findCandidatesByMonthsAgo(18);

    // 1. 휴면 안내 메일 전송
    sendDormantNotificationEmail(notifyCandidates, currentMonth.plusMonths(1));

    // 2. 계정 잠금 처리
    processUserAction(lockCandidates, SiteUser::lockAccount);

    // 3. 계정 소프트 삭제 처리
    processUserAction(deleteCandidates, SiteUser::delete);
}

기존 코드는 이렇게 각 안내 대상, 잠금 대상, 삭제 대상에 대해서

Projection 으로 조회를 진행하고 있다.

그런데 잠금과 삭제를 보면 엔티티 수정이 필요하기 때문에

조회한 내용의 id 를 바탕으로 다시 엔티티 조회를 진행하고 있었다.

private void processUserAction(List<DormantAccountProjection> candidates, java.util.function.Consumer<SiteUser> action) {
    candidates.forEach(candidate -> {
        userService.findById(candidate.getId()).ifPresent(action);
    });
}

엔티티를 찾기 위해 조회를 2번 진행하는 것에 더해서

각 대상에 대해서 findById 메서드를 호출하기 때문에

조회하는 엔티티 수 만큼의 쿼리가 나가고 있다는 생각이 들었다.

---

쿼리 수 줄이기!

1차 시도

일단은 엔티티를 조회하는데 쿼리가 2번씩 필요 없을 것 같아서

잠금과 삭제 대상은 엔티티를 바로 조회하는 것으로 수정하였다.

그리고 이 2가지도 한번의 쿼리로 조회하고 싶어서 고민을 해봤는데

1. 잠금 대상은 로그인한지 12개월이 지난 사용자이고
   휴면 계정 전환으로 계정 잠금을 진행한다.

2. 삭제 대상은 잠긴 후 6개월이 지나도 잠금을 해제하고 로그인하지 않은 사용자로
   개인정보 보호를 위해 계정 정보를 삭제한다.

보면 계정이 삭제되기까지의 과정이

계정 -> 1년 로그인하지 않음 -> 잠김 -> 6개월 더 로그인하지 않음 -> 삭제

이렇게 진행히 되고

그 상태는

1. 일반 계정
2. 잠긴 계정 (12개월)
3. 삭제 계정 (18개월)

이렇게로 삭제 계정은 잠긴 계정을 거치는, 포함되어지는 관계라는 것을 알아냈다.

정리하면 로그인한지 12개월이 지난 모든 회원을 조회 한 후,

잠긴 상태를 확인해서 잠겨있지 않으면 일반 계정이니 휴면 계정으로 잠금을 진행한다.

이미 잠겨있다면 잠긴 기간을 확인해서 6개월이 지났다면 삭제 대상으로 삭제를 진행한다.

이렇게 수행하면 되는 것이다.

그런데 계정이 잠긴지 6개월이 된 것을 확인하는 과정에서

새로운 필드를 만들어야 하나

로그인 기록 테이블과 Join 해야하나 고민했는데

생각해보니 계정을 잠그는 과정에서 엔티티를 수정하게 되고

그러면 SiteUser 의 modifyDate 가 변경되게 되는 것이다.

그렇기 때문에

잠긴 계정의 modifyDate 가 6개월이 지났다면

이 계정은 삭제 대상이라고 구분할 수 있게 되는 것으로 생각했다.

그래서 수정된 코드는 아래와 같다.

@Transactional
public void processDormant() {
    // 기준 날짜 설정 (매월 1일 실행)
    YearMonth currentMonth = YearMonth.now();

    // 1. 휴면 안내 메일 전송
    List<DormantAccountProjection> notifyCandidates = findCandidatesByMonthsAgo(11);
    sendDormantNotificationEmail(notifyCandidates, currentMonth.plusMonths(1));

    List<SiteUser> lockAndDeleteCandidates = findUsersByMonthsAgo(12);  // 기간으로 사용자 리스트 반환하는 메서드

    for(SiteUser user : lockAndDeleteCandidates) {
        if(!user.isLocked()) {
            // 2. 계정 잠금 처리
            user.lockAccount();
        } else {
            // 3. 계정 소프트 삭제 처리
            if(user.getModifyDate().isBefore(LocalDateTime.now().minusMonths(6))) {
                user.delete();
            }
        }
    }
}

이렇게 수정하여 관리 대상을 조회할 때

기존의 3 + n(잠금) + m(삭제) 번에서

2번으로 줄이게 되었다.

---

2차 시도

그런데 생각해보니

SELECT 쿼리는 확 줄였지만

UPDATE 쿼리는 여전히 대상 수 만큼 수행되는 것 같았다.

이것도 줄여보고 싶어서

한번에 업데이트 할 수 있는 Bulk 메서드를 구현하기로 하였다.

엔티티를 조회하지 않고 대상 id 들만 조회하여

이 id 의 리스트로 bulk 쿼리를 수행하였다.

public interface UserRepository extends JpaRepository<SiteUser, Long> {
    @Modifying
    @Query("UPDATE SiteUser u SET u.locked = true WHERE u.id IN :ids")
    void bulkLockAccounts(@Param("ids") List<Long> ids);

    @Modifying
    @Query("""
        UPDATE SiteUser u
        SET u.username = CONCAT('deleted_', UUID()), 
            u.email = CONCAT('deleted_', UUID(), '@deleted.com'), 
            u.nickname = CONCAT('탈퇴한 사용자_', u.username), 
            u.isDeleted = true, 
            u.deletedDate = :deletedDate
        WHERE u.id IN :userIds
        """)
    void bulkDeleteAccounts(@Param("userIds") List<Long> userIds, @Param("deletedDate") LocalDateTime deletedDate);
}

이렇게 챗GPT 의 도움을 받아

한번에 작업을 수행할 수 있게 되었는데

만약 WHERE u.id IN :userIds 이 부분의,

대상이 너무 많아져 리스트가 길어지게 되면

성능에 문제가 생길 수 있을 것 같아서

서비스에서 한번에 100명씩 배치로 처리하는 것으로 수정했다.

최종 코드는 아래와 같다.

@Service
@RequiredArgsConstructor
public class UserDormantService {

    private final AuthenticationRepository authenticationRepository;
    private final GoogleMailService emailService;
    private final UserService userService;
    private final UserRepository userRepository;

    @Transactional
    public void processDormant() {
        // 기준 날짜 설정 (매월 1일 실행)
        YearMonth currentMonth = YearMonth.now();

        // 1. 휴면 안내 메일 전송 (닉네임과 이메일만 조회)
        List<DormantAccountProjection> notifyCandidates = findCandidatesByMonthsAgo(11);
        sendDormantNotificationEmail(notifyCandidates, currentMonth.plusMonths(1));

        // 2. 계정 잠금 (id 만 조회 후 한번에 100명씩 처리)
        List<Long> lockIds = findUserIdsByMonthsAgo(12);
        processInBatches(lockIds, 100, userRepository::bulkLockAccounts);

        // 3. 삭제 처리 (id 만 조회 후 한번에 100명씩 처리)
        List<Long> deleteIds = findUserIdsByMonthsAgo(18);
        processInBatches(deleteIds, 100,
                ids -> userRepository.bulkDeleteAccounts(ids, LocalDateTime.now()));
    }

    private List<Long> findUserIdsByMonthsAgo(int monthsAgo) {
        LocalDateTime[] dateRange = calculateDateRange(monthsAgo);
        return userRepository.findUserIdsInDateRange(dateRange[0], dateRange[1]);
    }

    private LocalDateTime[] calculateDateRange(int monthsAgo) {
        YearMonth targetMonth = YearMonth.now().minusMonths(monthsAgo);
        LocalDateTime startDate = targetMonth.atDay(1).atStartOfDay();
        LocalDateTime endDate = targetMonth.atEndOfMonth().atTime(LocalTime.MAX);
        return new LocalDateTime[] { startDate, endDate };
    }

    private <T> void processInBatches(List<T> ids, int batchSize, Consumer<List<T>> processor) {
        for (int i = 0; i < ids.size(); i += batchSize) {
            int end = Math.min(i + batchSize, ids.size());
            List<T> batch = ids.subList(i, end);
            processor.accept(batch);
        }
    }
}

public interface UserRepository extends JpaRepository<SiteUser, Long> {
    @Query("""
        SELECT a.user.id
        FROM Authentication a
        JOIN a.user u
        WHERE a.lastLogin BETWEEN :startDate AND :endDate
        AND u.isDeleted = false
    """)
    List<Long> findUserIdsInDateRange(@Param("startDate") LocalDateTime startDate,
                                        @Param("endDate") LocalDateTime endDate);

    @Modifying
    @Query("UPDATE SiteUser u SET u.locked = true WHERE u.id IN :ids")
    void bulkLockAccounts(@Param("ids") List<Long> ids);

    @Modifying
    @Query("""
    UPDATE SiteUser u
    SET u.username = CONCAT('deleted_', UUID()), 
        u.email = CONCAT('deleted_', UUID(), '@deleted.com'), 
        u.nickname = CONCAT('탈퇴한 사용자_', u.username), 
        u.isDeleted = true, 
        u.deletedDate = :deletedDate
    WHERE u.id IN :userIds
""")
    void bulkDeleteAccounts(@Param("userIds") List<Long> userIds, @Param("deletedDate") LocalDateTime deletedDate);
}

public interface AuthenticationRepository extends JpaRepository<Authentication, Long> {
    @Query("""
        SELECT
            u.nickname AS nickname,
            u.email AS email
        FROM Authentication a
        JOIN a.user u
        WHERE a.lastLogin BETWEEN :startDate AND :endDate
        AND u.isDeleted = false
    """)
    List<DormantAccountProjection> findDormantAccountsInDateRange(
            @Param("startDate") LocalDateTime startDate,
            @Param("endDate") LocalDateTime endDate
    );
}

---

발생한 효과

1. 기존 쿼리 수 :

Projection 쿼리 3번 (SELECT)

+ 잠금 대상 엔티티 조회 n 번 (SELECT)

+ 잠금 작업 n 번 (UPDATE)

+ 삭제 대상 엔티티 조회 m 번 (SELECT)

+ 삭제 작업 m 번 (UPDATE)

== 총 3n + 3m + 3 번

( SELECT 2n + 2m + 3 번, UPDATE n + m 번 )

예를들어, 잠금 대상과 삭제 대상이 각 50명이라면,

SELECT 203 번, UPDATE 100번으로

총 303번의 쿼리 발생.

2. 변경 쿼리 수 :

Projection 쿼리 3번 (SELECT)

+ 잠금 작업 n / 100 + 1 번 (UPDATE)

+ 삭제 작업 m / 100 + 1 번 (UPDATE)

== 총 n / 100 + m / 100 + 5 번

( == 잠금, 삭제 대상이 100명 이하일 경우 5번 )

( SELECT 3 번, UPDATE n / 100 + m / 100 + 2번 )

예를들어, 잠금 대상과 삭제 대상이 각 50명이라면,

SELECT 3 번, UPDATE 2번으로

총 5번의 쿼리 발생.