[Spring Boot] DB 인덱스를 활용한 스케줄링 작업 개선하기

개요

	@Scheduled(cron = "0 0 * * * ?", zone = "Asia/Seoul")
	@Transactional
	void changePlanetReadyToInProgress() {
		...
		planetRepositoryCustom.updateStatusReadyToInProgress();
		planetRepositoryCustom.updateStatusInProgressToUnderReview();
		...
	}
    
	public void updateStatusReadyToInProgress() {
		queryFactory.update(planet)
			.set(planet.status, PlanetStatus.IN_PROGRESS)
			.where(
				isReadyStatus(),
				eqStartDateAndToday()
			)
			.execute();
	}

플래닛러쉬 프로젝트에서는 자정마다 한 번씩 아래와 같은 배치 작업을 수행하고 있습니다.

start_date가 오늘이고 planetStatus가 'READY'인 행성의 상태를 'IN_PROGRESS'로 변경한다.
end_date가 오늘이고 planetStatus가 'IN_PROGRESS'인 행성의 상태를 'UNDER_REVIEW'로 변경한다.

이 요구사항을 Spring 스케줄러로 구현했었습니다. 최근 Real MySQL 책을 읽으며 인덱스와 잠금에 대해 공부했고 배치 작업에 개선이 필요함을 느꼈습니다. 이를 위해 더미 데이터가 필요했고 데이터양을 선정한 기준은 다음과 같습니다.

1. 사용자는 최대 9개의 행성만 가입할 수 있다. (생성하는 것도 가입으로 간주)
2. 사용자 1000명이 서비스를 이요한다고 가정하면, 하루에 최대로 만들어질 수 있는 행성의 개수는 9000개. 약 1만 개로 가정(변경 대상)
   실행 환경: 로컬에서 Spring Boot, MySQL 사용

변경 조건에 부합하는 1만 개 데이터만 저장 - 96ms

실행 시간은 충분히 짧았고 큰 문제가 될 것 같지는 않아 보였습니다. 이번에는 총 10만 개의 데이터 중, 1만 개만 조건에 부합하는 상황에서 시간을 측정해봤습니다.

10만 개 중 1만 개만 변경 조건에 부합 - 141ms

1.5배 조금 안되는 만큼 쿼리 실행 시간이 늘어났습니다.

update 쿼리 실행 계획 분석(실행 시간: 약 100ms)

실행 계획을 분석하면 1만 개 데이터를 변경하기 위해 실제로는 약 9.8만개, 즉 풀 테이블 스캔을 하고 있습니다. 클러스터드 인덱스를 타고 조건에 부합하는 레코드를 찾아 변경한 것입니다. 문제는 MySQL은 데이터 쓰기 작업을 수행할 때 쓰기 락을 점유하게 되며, 잠금은 인덱스부터 걸리게 됩니다. 즉 1만 개 데이터를 변경하기 위해 불필요하게 9만 개의 레코드에도 잠금을 걸게 되는 것입니다.

시점	Session A	Session B
1	BEGIN;
2	update planet set planet_status = 'IN_PROGRESS' where start_date = current_date and planet_status = 'READY';
3		BEGIN;
4		UPDATE planet SET planet_status='UNDER_REVIEW' WHERE start_date = current_date and planet_status = 'READY' (대기)
5	COMMIT;
6		COMMIT;

다행히 현재 서비스에서는 배치 작업이 진행되는 동안 동일한 planet 레코드의 값을 변경하거나 수정하는 기능이 없기 때문에 잠금으로 인한 경합 상황은 발생하지 않았습니다. 하지만 작업의 효율을 위해 꼭 필요한 레코드만 조회할 수 있도록 탐색 범위를 줄일 방법을 고민했습니다.

복합 인덱스 생성 - (start_date, planet_status), (end_date, planet_status)

planet 테이블에 대해 (start_date, planet_status), (end_date, planet_status) 각각 두 개의 칼럼으로 복합 인덱스를 만들었습니다. 날짜 기준으로 우선 정렬되고 상태 값으로 한 번 더 정렬되어 인덱스 트리가 생성되기 때문에 필요한 레코드만 효율적으로 탐색하고 수정할 것이라 생각했습니다. 따라서 start_date 또는 end_date가 오늘과 일치하는 레코드만 우선적으로 접근하고 쓰기 락을 걸기 때문에 탐색 범위를 줄일 수 있다고 기대했습니다.

ALTER TABLE planet
ADD INDEX idx_start_date_status (start_date, planet_status);

ALTER TABLE planet
ADD INDEX idx_end_date_status (end_date, planet_status);

update 쿼리 실행 계획 분석(실행 시간: 약 360ms)

실제 결과를 보면 복합 인덱스를 range 스캔하였으며 약 1.8만 개의 레코드를 탐색한 것을 알 수 있습니다. 탐색의 범위는 줄었지만 오히려 쿼리 실행 시간은 3.5배 늘어났습니다. 이미 인덱스로 만들어진 planet_status 칼럼의 값을 변경하기 때문에 B-Tree 노드도 1만개의 엔트리에 대해 삭제, 삽입이 필요하기 때문입니다. 인덱스로 인해 실행 시간은 늘어났지만, 탐색할 레코드의 범위는 10배 가까이 줄어들었습니다.

정리

Secondary Index를 생성한 결과, 탐색해야 할 레코드 범위는 크게 줄어들었지만 업데이트 성능은 저하되는 것을 확인했습니다. 이는 인덱스에 포함된 planet_status 값을 변경하면서 B-Tree 구조에 대해 삭제와 삽입 작업이 동시에 발생했기 때문입니다. 즉, 읽기 성능 최적화에는 유리하지만 쓰기 연산에는 추가 비용이 발생한 것입니다.

결국 인덱스 설계는 단순히 쿼리 속도만이 아니라 읽기/쓰기 패턴과 서비스 정책을 함께 고려해야 합니다. 이번 작업을 통해 배운 점은 다음과 같습니다.

1. 풀 스캔을 줄이는 효과는 분명 존재한다. ➡️ 불필요하게 잠금이 걸리는 레코드 수가 줄어든다.
2. 쓰기 비용은 늘어난다. ➡️ 인덱스 컬럼을 수정해야 하므로 B-Tree 노드 재구성이 발생한다.
3. 따라서, 데이터 변경이 빈번한 칼럼에 인덱스를 추가할 경우 읽기 효율과 쓰기 비용 간의 트레이드오프를 반드시 검토해야 한다.
4. 현재 서비스 특성상 자정에만 배치 작업이 수행되고, 동시에 해당 레코드를 수정하는 기능이 없으므로 잠금 경합은 발생하지 않는다.

즉, 인덱스를 추가하는 것이 항상 성능 최적화를 보장하는 것은 아니며, 서비스의 쿼리 패턴과 데이터 변경 특성에 맞는 전략적 선택이 필요하다는 점을 확인할 수 있었습니다.