회원가입   로그인
IMG-LOGO
공지사항 :

SQL  DELETE

lmkfox - 2025-09-29 06:40:31 50 Views 0 Comment

1. 기본 문법 (표준 형태)

DELETE FROM table_name
WHERE condition;

  • WHERE를 생략하면 테이블의 모든 행을 삭제합니다(치명적 실수 방지 주의).

  • 대부분 DB에서 단일 DELETE 문은 문장 단위로 원자적(문장 실패시 롤백)입니다. 트랜잭션 안에서는 여러 문장을 묶어서 원자성 보장.

2. DB별 추가 구문 / 반환 지원

  • PostgreSQL: DELETE ... RETURNING * 처럼 삭제된 행을 바로 반환 가능.

  • SQL Server: DELETE ... OUTPUT deleted.*로 삭제 전/후 값을 출력 가능.

  • MySQL: 전통적으로 DELETE ... RETURNING는 지원하지 않았음(확인 필요). MySQL 환경에서는 삭제 전에 SELECT로 행을 확인하거나 트랜잭션 내에서 SELECT 후 DELETE를 사용합니다. (사용 중인 DB의 버전 문서를 확인하세요.)

3. 다중-테이블 삭제 (MySQL 예시)

  • MySQL은 JOIN을 사용해 다른 테이블 연관행을 참조하여 삭제할 수 있습니다.

-- t1만 삭제
DELETE t1
FROM t1
JOIN t2 ON t1.ref = t2.id
WHERE t2.flag = 1;

-- t1, t2 둘 다 삭제
DELETE t1, t2
FROM t1
JOIN t2 ON t1.ref = t2.id
WHERE t2.flag = 1;

  • PostgreSQL은 USING 절을 사용:

DELETE FROM t1 USING t2 WHERE t1.ref = t2.id AND t2.flag = 1;

4. 외래키(FOREIGN KEY)와 

ON DELETE

 옵션

  • 참조 무결성 제약이 있으면 삭제 동작은 외래키 규칙에 따릅니다:

    • ON DELETE CASCADE : 부모 삭제 시 자식 자동 삭제

    • ON DELETE SET NULL : 참조 컬럼을 NULL로 설정

    • ON DELETE RESTRICT/NO ACTION : 참조가 있으면 삭제 불가

  • 설계 시 자동 삭제(CASCADE)의 편의성과 위험(의도치 않은 연쇄 삭제)을 반드시 고려.

5. 트랜잭션·락·동시성

  • DELETE는 대상 행에 배타적 락을 건다(엔진·격리 수준에 따라 gap lock 등 추가 가능).

  • 긴 트랜잭션(대량 삭제)은 락 대기, 데드락, 리플리케이션 지연, undo/redo 로그 폭증을 유발함.

  • 권장: 짧은 트랜잭션, 필요 시 낙관적 동시성(버전 컬럼) 또는 일괄 배치로 처리.

  • 데드락 발생 시 애플리케이션은 재시도 로직을 구현.

6. 삭제 방식 비교: DELETE vs TRUNCATE vs DROP

  • DELETE FROM table WHERE … : 조건 기반 삭제, 트랜잭션으로 롤백 가능(엔진에 따라 다름), 각 행에 대해 로그 기록(비용 큼).

  • TRUNCATE TABLE table : 테이블의 모든 행을 빠르게 제거(대부분 DDL 수준, 롤백 불가하거나 제한), auto_increment 리셋, 외래키 제약에 의해 제한될 수 있음.

  • DROP TABLE : 테이블 자체를 제거(스키마 구조 삭제).

    선택은 목적(모든 데이터 제거 vs 구조 유지)·복구 가능성·퍼포먼스에 따라 달라짐.

7. 성능/운영 이슈와 해결책

문제 원인

  • 조건에 인덱스 없음 → 전체 테이블 스캔

  • 대규모 행 삭제 → 긴 트랜잭션, 많은 undo/redo, binlog 증가, replication lag

  • 인덱스가 많으면 삭제 비용 증가

해결 전략

  1. 인덱스 활용: WHERE 조건이 인덱스를 타도록 쿼리 설계.

  2. 청크(Chunk) 삭제: 한 번에 많은 행을 지우지 말고 적당한 묶음으로 나눠 반복 처리. 예: DELETE FROM t WHERE condition LIMIT 1000; (MySQL에서 LIMIT 사용 가능).

  3. 키 범위 방식: WHERE id BETWEEN x AND y 식으로 범위 단위 처리.

  4. 파티셔닝 사용: 날짜 기준 같은 경우 파티셔닝 후 오래된 파티션을 DROP PARTITION 하면 매우 빠르게 데이터 제거 가능.

  5. 아카이브 + 삭제: 중요한 데이터는 다른 테이블/스토리지로 옮긴 뒤 삭제. pt-archiver 같은 도구 사용 권장.

  6. 비활성화 옵션(주의): MySQL에서 SET FOREIGN_KEY_CHECKS=0;로 외래키 검사를 끄고 삭제하면 속도 향상되나 무결성 위험. 신중히 사용.

  7. OPTIMIZE / 테이블 리빌드: 많은 행 삭제 후 디스크 공간을 회수하려면 OPTIMIZE TABLE 또는 테이블 재생성 필요(특히 innodb_file_per_table 설정에 따라 결과가 다름).

8. 안전 장치 / 베스트 프랙티스

  • 항상 SELECT로 대상 확인: SELECT COUNT(*) 혹은 SELECT id ... LIMIT 10으로 삭제 대상 확인 후 DELETE 실행.

  • 트랜잭션 사용: 가능하면 트랜잭션으로 묶어 잘못된 경우 롤백.

  • 백업: 중요한 삭제 전 백업(또는 스냅샷).

  • 권한 최소화: 프로덕션 계정에 불필요한 DELETE 권한 부여 금지.

  • 감사/로그: 삭제 감사가 필요하면 트리거로 audit 테이블에 기록하거나 애플리케이션 레벨 로그를 유지.

  • Soft delete(권장 옵션): 복구/감사 목적이라면 deleted_at 타임스탬프 사용(즉시 물리 삭제 하지 않고 표시).

  • 자동화 시 안전장치: 운영 스크립트에 ‘dry-run’(SELECT), 확인 단계, 재시도 로직 포함.

9. 소프트 삭제 vs 하드 삭제

  • Soft delete: UPDATE table SET deleted_at = NOW() WHERE id = ...;

    • 장점: 복구 용이, 감사 가능, 외래키 문제 회피

    • 단점: 조회시 WHERE deleted_at IS NULL 추가, 인덱스 관리 필요, 저장공간 증가

  • 하드 삭제: 물리적 삭제. 규정(개인정보 파기, GDPR 등)이나 저장공간 문제시 필요.

10. 대량 삭제(예시 패턴)

A. MySQL: LIMIT 반복(간단한 스크립트 방식)

-- 반복(애플리케이션 or shell)
DELETE FROM big_table WHERE created_at < '2023-01-01' LIMIT 10000;
-- 반복해서 영향 행수가 0이 될 때까지 수행

B. MySQL: Stored procedure(예시 개념)

-- 단순 예시(실제 환경에선 에러 핸들링 추가)
DELIMITER //
CREATE PROCEDURE purge_old()
BEGIN
  DECLARE done INT DEFAULT 0;
  REPEAT
    DELETE FROM big_table WHERE created_at < '2023-01-01' LIMIT 10000;
    SET @cnt = ROW_COUNT();
    IF @cnt = 0 THEN SET done = 1; END IF;
  UNTIL done END REPEAT;
END//
DELIMITER ;
CALL purge_old();

C. Python 예제 (안전한 반복 삭제)

import mysql.connector

conn = mysql.connector.connect(...)
cur = conn.cursor()
batch = 1000
while True:
    cur.execute("DELETE FROM big_table WHERE created_at < %s LIMIT %s", ('2023-01-01', batch))
    deleted = cur.rowcount
    conn.commit()
    if deleted == 0:
        break
cur.close()
conn.close()

D. 파티션 드롭 (대용량 날짜 삭제 최적)

-- 파티셔닝된 테이블 예
ALTER TABLE logs PARTITION BY RANGE (TO_DAYS(log_date)) (
  PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
  PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
  ...
);

-- 오래된 파티션 빠르게 제거
ALTER TABLE logs DROP PARTITION p202301;
파티션 드롭은 내부적으로 매우 빠르며, 대량 데이터 제거에 가장 권장되는 방법 중 하나(파티셔닝이 설계상 가능할 때).

11. 트리거와 삭제

  • BEFORE DELETE / AFTER DELETE 트리거로 삭제 전/후 로직(감사, 연관 cleanup 등)을 구현 가능.

  • 트리거는 성능/복잡성 영향을 줄 수 있으므로 간단하고 빠르게 유지.

12. 복제·로그 관점

  • 대규모 DELETE는 binary log(binlog)를 크게 만들고 복제 슬레이브에 부하를 줄 수 있음.

  • 슬레이브 지연(replication lag) 및 binlog 보존 정책 고려.

  • DELETE 많을 때는 binlog 사이즈·전송·디스크 영향 주의.

13. 기타 유용한 예제

삭제 대상 행 미리 보기 (안전)

SELECT id, created_at FROM orders WHERE created_at < '2023-01-01' LIMIT 20;

중복 행 삭제 (MySQL 예)

DELETE t1 FROM users t1
INNER JOIN users t2 
  ON t1.email = t2.email
WHERE t1.id > t2.id;

조건부 삭제 (CASE 기반 체크는 SELECT로 검증 후)

DELETE 문 자체에서 CASE로 조건부 삭제하지 않음(WHERE로 필터링). 복잡한 로직은 서브쿼리 또는 애플리케이션 로직으로 처리.

14. 요약 체크리스트 (실무용)

  • 삭제 대상 반드시 확인(SELECT)

  • 트랜잭션/백업/복구 절차 준비

  • 인덱스가 WHERE를 지원하는지 확인(성능)

  • 대량 삭제는 청크/배치로 처리(또는 파티션 드롭)

  • 외래키 규칙과 트리거 영향 점검

  • 삭제 로그/감사 또는 soft-delete 전략 결정

  • 운영 스크립트에 재시도·모니터링·알람 포함


댓글