Delete vs Truncate

목적

오늘 업무를 하다가 삭제 쿼리 개선에 관한 내용이 나와서 정리하고자 글을 작성한다.
두 쿼리의 차이점을 이해하고 효율적인 쿼리를 작성하자

 

공통점

두 쿼리 모두 데이터를 삭제한다는 관점에서는 같다.

 

차이점

삭제를 하는 방식과 결과

- DELETE : DELETE FROM ${테이블 명} WHERE 조건

조건에 맞는 모든 행을 삭제한다. 

- TRUNCATE TABLE ${테이블 명}

테이블을 초기화(모든 행을 삭제) 시키고, 관련된 FK와 PK, 인덱스를 삭제한다. 

 

위 과정만 본다면 단순 모든 것을 삭제하는 TRUNCATE 연산이 좀 더 효율적인 것으로 알 수 있지만, 모든 데이터가 날라간다는 단점이 있다.

하지만 많은 양의 데이터를 제거해야한다면 TRUNCATE 연산을 고려해 볼 수 있다.
남겨야 할 데이터를 임시 테이블에 넣고, TRUNCATE 연산을 통해 데이터를 다 날리고, 다시 PK와 FK, 인덱스를 걸어주는 것도 방법의 하나이다.

이 방법이 빠를 수 있지만, 고려해야할 점이 있다.
디스크가 충분히 남았는가? 인덱스와 PK, FK 설정하는데 소요되는 비용이 DELETE를 사용하는 비용보다 적은가?
등 충분히 고려하고 사용해야 한다.

회사에서 1600만건의 데이터를 삭제해야하는 이슈가 있었는데, 저 방법으로 30분까지 최적화됐다고 한다.

 

 

예제. 우리가 흔히 잘 사용하는 employees 예제를 사용한다.

* MySQL은 TRUNCATE 구문이 없다.

임시 테이블 생성

CREATE TABLE employees_temp AS
SELECT *
FROM employees
WHERE salary < 5000;

TRUNCATE 사용

TRUNCATE TABLE employees;

임시테이블 데이터 이전

INSERT INTO employees
SELECT *
FROM employees_temp;

인덱스, PK, FK 설정

ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

ALTER TABLE employees
ADD CONSTRAINT fk_dept_id
FOREIGN KEY (department_id) REFERENCES departments(department_id);

CREATE INDEX idx_salary
ON employees(salary);

 

하나의 트랜잭션으로 구성

중간에 작업이 실패할 경우를 생각해서 하나의 트랜잭션으로 구성해야 위험요소를 줄일 수 있다.

BEGIN;

CREATE TABLE employees_temp AS
SELECT *
FROM employees
WHERE salary < 5000;

TRUNCATE TABLE employees;

INSERT INTO employees
SELECT *
FROM employees_temp;

ALTER TABLE employees
ADD PRIMARY KEY (employee_id);

ALTER TABLE employees
ADD CONSTRAINT fk_dept_id
FOREIGN KEY (department_id) REFERENCES departments(department_id);

CREATE INDEX idx_salary
ON employees(salary);

COMMIT;