답변.

수천만 행을 대량 삭제하는 것은 전형적인 작업 중 하나이며 특히 고부하 데이터베이스에서 가장 위험한 작업 중 하나입니다. 역사적으로 많은 사람들이 단순히 DELETE FROM을 작성했는데, 이는 테이블 잠금과 트랜잭션 로그의 초과로 이어졌습니다. 주요 문제는 트랜잭션이 너무 커지고, 서비스 프로세스가 지연되며, 롤백의 결과가 예측하기 어려울 수 있다는 것입니다.

해결책 — 삭제를 "배치"로 구현하여 짧은 트랜잭션에서 적은 수의 행을 처리하여 잠금과 시스템에 미치는 영향을 최소화합니다:

코드 예제 (SQL Server):

WHILE 1=1
BEGIN
   DELETE TOP (10000)
   FROM YourHugeTable
   WHERE CreatedAt < DATEADD(year,-2,GETDATE());

   IF @@ROWCOUNT = 0 BREAK;
   WAITFOR DELAY '00:00:01'; -- 부하를 줄이기 위한 작은 지연
END

주요 특징:

잠금 크기와 트랜잭션 로그 기록을 최소화합니다.
작은 배치로 처리됩니다: 시스템이 반응성을 유지합니다.
진행 상황을 표시하거나 외부 모니터링 로직과 결합할 수 있습니다.

함정 질문.

DELETE 대신 TRUNCATE를 하면 항상 더 빠르고 안전합니까?

아니요. TRUNCATE는 훨씬 빠르지만 :

TRUNCATE는 외래 키가 참조하는 경우 적용할 수 없습니다.
TRUNCATE는 트리거를 호출하지 않습니다.
TRUNCATE는 조건에 따라 행을 삭제하는 것이 아니라 모든 행을 완전히 삭제합니다.

대량 DELETE에서 필터 필드에 인덱스를 사용하는 것이 중요합니까?

네, 필터 열(예: CreatedAt)에 적절한 인덱스가 있으면 삭제할 행을 검색하는 속도를 높이고 테이블에 대한 부하를 줄입니다. 인덱스가 없으면 쿼리가 전체 테이블에 영향을 미치며, 각 배치에서 적은 수의 행이 삭제되더라도 마찬가지입니다.

CREATE INDEX idx_createdat ON YourHugeTable(CreatedAt);

여러 스레드에서 대량 DELETE를 동시에 실행하면 어떻게 됩니까?

잠금에 대한 경쟁이 발생하게 되며, 잠금 에스컬레이션, 대기 시간 증가 및 교착 상태 가능성이 생깁니다. 동일한 테이블에 대한 대량 삭제는 하나의 프로세스에서 진행되어야 하며, 매우 신중하게 범위를 나누어야 합니다.

일반적인 실수와 안티패턴

하나의 트랜잭션에서 대량 삭제 (테이블 잠금, 트랜잭션 로그 초과).
진행 상황 확인 및 실행 시간 제어 부족.
인덱스 부족 — 매번 전체 테이블을 스캔합니다.

실제 사례

부정적인 사례

DBA가 DELETE FROM Log WHERE dt < '2021-01-01'라는 단일 쿼리로 6천만 행의 테이블을 청소하기로 결정했습니다. 서버가 거의 "중단" 상태에 이르렀고, 다른 프로세스는 수행을 기다리게 되었으며, 로그 파일이 급격히 증가하고 복구가 긴 시간이 걸렸습니다.

장점:

구현이 간단합니다.

단점:

전체 서버 성능이 크게 저하되며, 사고 발생시 데이터 손실 가능성이 있으며, 복구가 긴 시간이 걸린다.

긍정적인 사례

삭제를 10,000행 단위로 나누어 프로세스를 제어하며, 각 배치 후에 대기합니다. 서버가 안정적으로 작동하고 나머지 작업이 수행되며 관리자가 진행 상황을 모니터링 합니다.