В чем разница между DELETE и TRUNCATE?

Разница между DELETE и TRUNCATE

Оба оператора удаляют данные из таблицы, но они работают совершенно по-разному. Для Data Scientist это важно знать при работе с SQL и очисткой данных.

Основные отличия

DELETE — операция DML

DELETE удаляет строки одну за одной, логирует каждое удаление и может иметь условие WHERE.

-- Удалить все строки (эквивалент TRUNCATE по результату)
DELETE FROM users;

-- Удалить с условием
DELETE FROM users WHERE age < 18;

-- Удалить с JOIN
DELETE FROM orders
WHERE customer_id IN (
    SELECT id FROM customers WHERE status = 'inactive'
);

Как работает DELETE:

1. Находит строки, соответствующие условию WHERE
2. Удаляет каждую строку
3. Логирует каждую операцию удаления
4. Срабатывают триггеры DELETE
5. Занимает место в журнале транзакций (transaction log)

Пример с логированием:

BEGIN TRANSACTION;
DELETE FROM large_table WHERE id > 1000000;
-- Если таблица содержит 10 млн строк, то будут залогированы все 9 млн удалений
COMMIT;  -- или ROLLBACK

TRUNCATE — операция DDL

TRUNCATE освобождает страницы памяти таблицы, не логируя отдельные удаления. Это намного быстрее.

-- Очистить всю таблицу
TRUNCATE TABLE users;

-- TRUNCATE не поддерживает WHERE
-- Это вызовет ошибку:
TRUNCATE TABLE users WHERE age < 18;  -- ОШИБКА!

Как работает TRUNCATE:

1. Освобождает страницы памяти, выделенные таблице
2. Логирует только выделение страниц (очень экономно)
3. Не срабатывают триггеры DELETE
4. Сбрасывает счётчик IDENTITY (в SQL Server, PostgreSQL)
5. Намного меньше использует transaction log

Практические примеры

Пример 1: Очистка лога активности (очень большая таблица)

-- Медленно и дорого
DELETE FROM activity_log;

-- Быстро и дешево
TRUNCATE TABLE activity_log;

Для таблицы с 100 млн строк разница может быть в 100 раз!

Пример 2: Очистка с условием

-- Нужно DELETE, потому что нужно условие
DELETE FROM orders WHERE created_at < '2020-01-01';

-- TRUNCATE не поддерживает WHERE, придётся DELETE
-- или создать новую таблицу:
CREATE TABLE orders_new AS
SELECT * FROM orders WHERE created_at >= '2020-01-01';

DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;

Пример 3: Сброс Identity (SQL Server)

TRUNCATE TABLE users;  -- Сбросит IDENTITY на 1

DELETE FROM users;     -- IDENTITY сохранится

-- Если нужно сбросить IDENTITY после DELETE:
DBCC CHECKIDENT ('users', RESEED, 0);

Откаты в транзакциях

PostgreSQL:

BEGIN;
TRUNCATE TABLE users;  -- Можно откатить
ROLLBACK;  -- Данные восстановлены

MySQL:

START TRANSACTION;
TRUNCATE TABLE users;  -- Некоторые версии могут вызвать неявный COMMIT
ROLLBACK;  -- Может не сработать!

Триггеры

CREATE TRIGGER user_delete_trigger
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO audit_log VALUES (OLD.id, 'deleted', NOW());
END;

DELETE FROM users WHERE id = 1;  -- Триггер СРАБОТАЕТ

TRUNCATE TABLE users;  -- Триггер НЕ сработает

Когда что использовать

Используйте TRUNCATE:

• Нужно очистить всю таблицу (без условия)
• Таблица большая (> 1 млн строк)
• Не нужны триггеры
• Нужна максимальная скорость
• Пример: очистка временных таблиц, логов

Используйте DELETE:

• Нужно условие WHERE
• Нужны триггеры
• Таблица небольшая
• Нужна гибкость
• Пример: удаление неактивных пользователей

Безопасность

-- ОПАСНО: можно случайно очистить всю таблицу
DELETE FROM users;  -- Нет WHERE!

-- Безопаснее:
DELETE FROM users WHERE 1=0;  -- Не удалит ничего (условие FALSE)

-- Лучше всегда указывать WHERE:
DELETE FROM users WHERE status = 'deleted';

-- ОЧЕНЬ опасно:
TRUNCATE TABLE users;  -- Моментально очищает, откатить может быть сложно

Итого: TRUNCATE быстрее и экономнее для полной очистки таблицы, DELETE гибче и безопаснее для частичного удаления. Выбирайте в зависимости от задачи.