데이터 정합성 확보(4) - 인덱스로 성능 개선

최근 아웃박스(Outbox) 패턴을 구현하면서 메시지 폴링(Polling) 로직에서 발생할 수 있는 성능 이슈를 튜닝한 과정을 정리한다.

문제 상황 및 배경

아웃박스 패턴을 통해 외부로 발행할 메시지를 DB에 저장하고, 스케줄러가 주기적으로 이 테이블을 조회하여 처리하는 구조다. 여기서 중요한 점은 메시지가 처리된 후에도 삭제되지 않고 계속 쌓인다는 점이다. 데이터가 누적될수록 조회 성능 저하가 우려되는 상황이었다.

튜닝 전 성능 분석

문제가 된 쿼리

status가 'PENDING'인 메시지 중 생성된 지 가장 오래된 10건을 조회하는 쿼리다.

SELECT * FROM outbox 
WHERE status = 'PENDING' 
ORDER BY created_at ASC 
LIMIT 10;

테스트 환경 및 결과

• 데이터 규모: 100만 건
• 데이터 분포: PENDING과 DONE의 비율 1:1000
• 평균 수행 시간: 0.389초

실행 계획(Execution Plan) 분석

인덱스 설정 전

실행 계획을 확인해 본 결과, 비효율적인 처리가 발생하고 있었다.

1. Full Table Scan: 테이블 전체를 스캔하여 데이터를 찾고 있었다.
2. Using filesort: 100만 건 중 조건에 맞는 행을 메모리로 가져와 정렬 작업을 별도로 수행하고 있었다. 겨우 10건을 조회하기 위해 불필요한 리소스가 낭비되는 상황이었다.

해결 방안: 복합 인덱스 적용

문제 해결을 위해 인덱스를 적용했다. 핵심은 WHERE 절의 조건 컬럼과 ORDER BY 절의 정렬 컬럼을 모두 커버하는 것이다.

CREATE INDEX idx_status_createdat ON outbox (status, created_at);

• status: 필터링 조건을 위해 인덱스의 선두 컬럼으로 배치.
• created_at: 이미 필터링된 데이터 내에서 정렬 없이 데이터를 가져오기 위해 후행 컬럼으로 배치.

튜닝 결과

인덱스 적용 후 동일한 쿼리를 실행한 결과는 다음과 같다.

• 수행 시간: 0.389초 → 0.001초
• 성능 개선율: 약 99.74% 단축

개선된 실행 계획

인덱스가 설정 된 후 실행 계획

• Sorting 제거: 인덱스가 이미 created_at 순으로 정렬되어 생성되므로, DB 엔진이 별도의 정렬(Filesort) 과정을 생략할 수 있게 되었다.
• Index Scan: Full Table Scan 대신 인덱스를 타고 필요한 데이터 10건만 빠르게 조회했다.

결론

쿼리 튜닝의 핵심은 불필요한 IO와 연산을 줄이는 데 있다. 이번 사례는 WHERE 조건 컬럼 + ORDER BY 정렬 컬럼 순서로 복합 인덱스를 구성하는 것만으로도, 대용량 데이터 환경에서 극적인 성능 개선이 가능함을 보여준다. 쿼리 속도가 느리다면 실행 계획을 확인하고 적절한 인덱스 전략을 세우는 것이 우선이다.