테스터는 새로운 테스트를 실행하기 전에 준비 테이블을 지우려고 합니다. DELETE, TRUNCATE 또는 DROP이어야 하나요? 잘못된 선택은 작업 속도 저하, 테이블 누락 또는 생산 과정에서 매우 나쁜 오후를 의미할 수 있습니다.

아래의 DELETE 및 DROP 예제를 SQLite 3.50에서 실행했으며 해당 실행에서 출력이 나왔습니다. SQLite에는 TRUNCATE 명령이 없으므로 TRUNCATE 참고 사항은 자체 테스트가 아닌 공식 PostgreSQL, MySQL 및 SQL Server 문서를 기반으로 합니다.

짧은 대답

• 일부 행을 제거해야 합니까? DELETE ... WHERE를 사용하세요.
• 테이블을 비워야 하지만 그대로 유지해야 합니까? 지원되는 경우 TRUNCATE을 사용하십시오.
• 테이블을 영원히 없애야 합니까? DROP TABLE을 사용하세요.

DELETE: 행 제거

DELETE은 데이터 변경(DML) 명령입니다. 한 번에 하나씩 행을 제거하고 행 수준 트리거를 실행하며 필터링할 수 있습니다. 감사 트리거가 포함된 작은 테이블을 설정합니다.

CREATE TABLE logs (id INTEGER PRIMARY KEY AUTOINCREMENT, level TEXT);
INSERT INTO logs (level) VALUES ('INFO'),('ERROR'),('INFO'),('DEBUG');

CREATE TABLE audit (msg TEXT);
CREATE TRIGGER logs_delete AFTER DELETE ON logs
BEGIN INSERT INTO audit VALUES ('deleted ' || OLD.id); END;

DELETE FROM logs WHERE level = 'DEBUG';

SELECT id, level FROM logs;  -- (1,'INFO'), (2,'ERROR'), (3,'INFO')
SELECT * FROM audit;         -- 'deleted 4'

일치하는 행만 이동하고 트리거가 이를 기록했습니다. WHERE 절 없이 DELETE FROM logs을 실행하면 나머지 3개 행이 제거되고 감사 테이블에는 삭제된 행당 하나씩 4개의 항목이 포함됩니다.

DELETE는 자동 증가 카운터를 재설정하지 않습니다. 테이블을 비운 후 다음 삽입에서는 1이 아닌 id 5을 받았습니다. SQLite에서는 AUTOINCREMENT 때문입니다. MySQL InnoDB 및 SQL Server ID 열은 DELETE 이후에도 계속됩니다.

TRUNCATE: 테이블을 비운다

TRUNCATE TABLE logs;은 모든 행을 한 번에 제거합니다. WHERE 절을 사용할 수 없습니다. 행을 하나씩 삭제하는 대신 데이터베이스는 일반적으로 테이블의 데이터 페이지를 해제하므로 대규모 테이블에서 훨씬 더 빠릅니다.

SQLite에서 실행했을 때 SQLite에는 다음 명령이 없기 때문에 구문 오류가 발생했습니다.

TRUNCATE TABLE logs;
-- near "TRUNCATE": syntax error

SQLite에서는 WHERE 절 없이 DELETE FROM logs;을 사용하세요. SQLite의 문서에는 테이블에 트리거가 없을 때 이를 빠르게 수행하는 “잘라내기 최적화”에 대해 설명되어 있습니다. 테이블에는 트리거가 있으므로 삭제는 행별로 실행되어 각 행에 대해 실행되었습니다.

TRUNCATE를 지원하는 데이터베이스의 경우 해당 설명서를 참조하세요.

• 일반적으로 행 수준 DELETE 트리거를 실행하지 않습니다.
• 예를 들어 MySQL 및 SQL Server에서 일반적으로 ID 또는 자동 증가 카운터를 재설정합니다. PostgreSQL에서는 RESTART IDENTITY을 추가합니다.
• 대부분의 시스템에서는 DELETE보다 더 강력한 권한이 필요합니다.

DROP: 테이블 자체를 제거합니다.

DROP TABLE은 스키마(DDL) 명령입니다. 데이터, 테이블 정의, 해당 인덱스, 트리거 및 제약 조건을 제거합니다. 뷰나 외래 키와 같이 테이블을 참조하는 모든 항목도 영향을 받습니다.

DROP TABLE logs;
SELECT * FROM logs;
-- no such table: logs

DROP TABLE IF EXISTS logs;  -- no error, even though it is gone

IF EXISTS은 두 번 이상 실행될 수 있는 설정 스크립트에 유용합니다. SQL 뷰 설명에 표시된 것처럼 삭제된 테이블의 뷰가 중단될 수 있습니다.

비교표

롤백할 수 있나요?

여기에서 많은 기사가 지나치게 단순화되었습니다. 정직한 대답은 데이터베이스에 따라 다르다는 것입니다.

DELETE는 트랜잭션 내에서 어디에서나 롤백될 수 있습니다. 우리는 그것을 테스트했습니다:

BEGIN;
DELETE FROM logs;
SELECT COUNT(*) FROM logs;  -- 0
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 3

DROP 또한 트랜잭션 DDL을 지원하는 SQLite에서 롤백되었습니다.

BEGIN;
DROP TABLE logs;
ROLLBACK;
SELECT COUNT(*) FROM logs;  -- 1 (the table is back)

해당 문서에 따르면 다른 데이터베이스의 TRUNCATE 및 DROP의 경우:

따라서 “TRUNCATE는 롤백할 수 없습니다”는 PostgreSQL 및 SQL Server에서는 잘못된 것이고 “항상 롤백할 수 있습니다”는 MySQL에서는 잘못된 것입니다. 트랜잭션 외부에서는 백업 없이는 그 어떤 것도 취소할 수 없습니다.

TRUNCATE가 빠른 이유

DELETE는 변경 사항을 실행 취소하고 복제할 수 있도록 제거된 각 행을 기록하고, 트리거를 실행하고 행별로 제약 조건을 확인합니다. 수백만 개의 행이 있는 테이블에서는 많은 작업과 로그 볼륨이 생성됩니다. TRUNCATE는 일반적으로 한 번의 작업으로 스토리지 할당을 취소하여 훨씬 적은 양의 로깅을 수행합니다. 전체가 아닌 대규모 하위 집합을 제거하려면 일괄 삭제하여 긴 잠금을 방지하세요. 잠금 및 쿼리 비용은 SQL 쿼리 최적화에서 다룹니다.

외래 키 및 권한

• 외래 키. SQL Server는 외래 키가 참조하는 테이블 TRUNCATE를 거부합니다. PostgreSQL에서는 참조 테이블도 자르거나 CASCADE를 사용해야 합니다. DELETE는 대신 제약 조건에 대해 각 행을 확인합니다.
• 권한. DELETE에는 삭제 권한이 필요합니다. TRUNCATE에는 ALTER 또는 전용 TRUNCATE 권한이 필요한 경우가 많으며 DROP에는 스키마에 대한 소유권 또는 ALTER가 필요합니다.
• 복제 및 감사. 다른 시스템이 행 수준 변경 로그 또는 트리거에 의존하는 경우 TRUNCATE는 이를 건너뛸 수 있습니다. 해당 시스템을 소유한 사람에게 먼저 확인하십시오.

어느 것을 사용할 것인가

테스트 데이터의 경우 TRUNCATE는 ID를 재설정하여 테스트를 반복할 수 있기 때문에 널리 사용됩니다. 롤백되는 트랜잭션에서 각 테스트를 래핑하는 것은 SQL 트랜잭션 및 ACID에 설명된 또 다른 옵션입니다.

안전 체크리스트

1. 연결된 데이터베이스와 환경을 확인하세요.
2. 먼저 WHERE 절을 SELECT로 실행합니다.
3. 데이터베이스가 지원하는 트랜잭션에서 변경 사항을 래핑합니다.
4. 프로덕션에서 TRUNCATE 또는 DROP 전에 최근에 테스트된 백업을 확보하십시오.
5. 임시 콘솔이 아닌 검토된 마이그레이션 스크립트에 DROP 문을 넣습니다.
6. 뷰, 외래 키, 보고서 등 테이블에 종속된 항목을 삭제하기 전에 확인하세요.

대용량 데이터를 안전하게 삭제하기

한 번의 DELETE로 수백만 개의 행을 제거하면 테이블이 오랫동안 잠기고 트랜잭션 로그가 채워지며 복제 속도가 느려질 수 있습니다. 일반적인 패턴은 작은 배치로 삭제하고 아무것도 남지 않을 때까지 반복하는 것입니다.

-- SQL Server style (not run for this article)
DELETE TOP (5000) FROM logs WHERE created_at < '2026-01-01';

-- PostgreSQL style (not run for this article)
DELETE FROM logs
WHERE id IN (SELECT id FROM logs WHERE created_at < '2026-01-01' LIMIT 5000);

0개 행이 보고될 때까지 일괄 처리 사이에 짧은 일시 중지를 사용하여 루프에서 문을 실행합니다. 각 배치는 작은 트랜잭션이므로 잠금이 짧고 다른 쿼리가 계속 실행됩니다. 필터 열의 인덱스(여기 created_at)는 모든 배치 속도를 빠르게 유지합니다.

로그, 이벤트 등 날짜별로 늘어나는 데이터의 경우 테이블 파티셔닝이 더 좋습니다. 매달 자체 파티션에 존재하며 오래된 데이터를 제거하면 행을 하나씩 삭제하는 대신 하나의 파티션을 삭제하거나 자르는 빠른 작업이 됩니다.

일시 삭제

많은 애플리케이션은 비즈니스 기록을 물리적으로 삭제하지 않습니다. deleted_at과 같은 열을 추가하고 대신 설정한 다음 쿼리는 deleted_at IS NULL를 필터링합니다. 일시 삭제를 사용하면 복구가 쉬워지고 감사 기록이 유지되지만 모든 쿼리는 필터를 기억해야 합니다. 이는 보기에 적합한 작업입니다. 일시 삭제된 행에는 여전히 해당 값이 유지되므로 고유 제약 조건에도 주의가 필요합니다. 개인 정보 보호법에 따라 일정 기간이 지나면 개인 데이터를 실제로 삭제해야 할 수 있으므로 일시 삭제는 보존 정책을 대체할 수 없습니다.

마무리

DELETE는 정확하고 신중한 도구입니다. TRUNCATE는 빠른 재설정 버튼이고 DROP은 테이블을 완전히 제거합니다. 가장 큰 위험은 특히 롤백과 관련하여 한 데이터베이스가 다른 데이터베이스처럼 작동한다고 가정하는 것입니다. 확실하지 않은 경우 먼저 자신의 데이터베이스에 있는 테이블 복사본에서 명령을 테스트하세요.

자주 묻는 질문

계속 학습하세요