SQL Performance Tuning Report: 인덱스 제거를 통한 대량 업데이트 성능 개선¶
본 보고서는 사원출입기록 테이블의 대량 데이터 업데이트 과정에서 발생하는 인덱스 부하를 진단하고, 이력 테이블의 특성을 고려한 최적화 전략을 제안합니다.
1. Analysis Overview¶
대상 쿼리¶
실행 계획(EXPLAIN) 및 현황 분석¶
- 실행 계획:
type: index,key: PRIMARY,rows: 658,764 - 문제점:
출입문열에 인덱스(I_출입문)가 있음에도 불구하고, 옵티마이저는 전체 데이터를 스캔하는PRIMARY인덱스 풀 스캔을 선택함. - 원인:
출입문열의 카디널리티(Cardinality)가 3으로 매우 낮아, 인덱스를 통한 랜덤 액세스보다 전체 스캔이 효율적이라고 판단됨.
2. Performance Diagnosis¶
- 불필요한 인덱스 유지 비용: 이력용 테이블(Logging/History)은 데이터의 삽입(INSERT)이 빈번하게 일어납니다. 활용도가 낮은 인덱스는 데이터 삽입 시마다 인덱스 정렬 및 관리를 위한 오버헤드를 발생시킵니다.
- 업데이트 성능 저하:
UPDATE작업 시 변경되는 열에 인덱스가 걸려 있다면, 데이터 자체의 변경뿐만 아니라 인덱스 페이지의 수정 및 정렬 작업이 동반되어 쓰기 성능이 급격히 저하됩니다. - 옵티마이저의 혼선: 카디널리티가 낮은 열에 인덱스가 존재할 경우, 옵티마이저가 부적절한 실행 계획을 세우거나 인덱스를 탐색하는 비용을 계산하는 과정에서 불필요한 리소스를 소모합니다.
3. Optimization Strategy¶
전략 1: 인덱스 활용도 검토 및 상시 삭제 (Index Removal)¶
사원출입기록과 같은 이력용 테이블에서 I_출입문 인덱스가 조회 조건으로 거의 사용되지 않는다면, 인덱스를 영구적으로 삭제하는 것이 가장 효과적입니다.
- 판단 근거:
출입문열의 종류가 3개뿐이라면, 해당 조건으로 검색 시 전체 데이터의 약 33%를 읽어야 하므로 인덱스 효율이 극히 낮습니다. - 조치: 활용도가 낮은 인덱스를 삭제하여
INSERT및UPDATE성능을 전반적으로 향상시킵니다.
전략 2: 대량 업데이트를 위한 일시적 삭제 후 재구성 (Drop & Recreate)¶
서비스 영향도가 적은 시간대에 수행되는 대량 배치 작업의 경우, 인덱스를 일시적으로 제거한 뒤 작업을 수행하고 다시 생성하는 것이 총 소요 시간을 단축시킵니다.
- 인덱스 삭제: 업데이트 작업 전 인덱스 관리 부하를 제거합니다.
- 데이터 업데이트: 인덱스 정렬 과정 없이 고속으로 데이터를 갱신합니다.
- 인덱스 재생성: 작업 완료 후 인덱스를 다시 생성하여 일괄 정렬(Bulk Build) 방식으로 인덱스를 구축합니다.
-- 1. 인덱스 삭제
ALTER TABLE 사원출입기록 DROP INDEX I_출입문;
-- 2. 대량 업데이트 수행
UPDATE 사원출입기록 SET 출입문 = 'X' WHERE 출입문 = 'B';
-- 3. 인덱스 재생성 (필요 시)
CREATE INDEX I_출입문 ON 사원출입기록(출입문);
4. Expected Impact¶
- 쓰기 성능 극대화:
UPDATE시 인덱스 갱신 부하가 사라져 전체 작업 시간이 대폭 단축됩니다. - 스토리지 효율성: 불필요한 인덱스 페이지가 점유하던 디스크 공간을 확보할 수 있습니다.
- 관리 비용 감소: 이력 데이터 적재(INSERT) 시의 성능 지연(Latency)을 방지하여 데이터 파이프라인의 안정성을 높입니다.