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

DELETE vs TRUNCATE в SQL: критические отличия

Быстрое сравнение

DELETE: более гибкий, медленнее

-- Синтаксис DELETE
DELETE FROM users WHERE age > 70;
DELETE FROM orders WHERE created_at < '2020-01-01';
DELETE FROM products WHERE stock = 0;
DELETE FROM users;  -- удалить все

-- DELETE логирует каждую строку
-- Генерирует row-level логи для отката
-- Может срабатывать FK constraints

Под капотом DELETE:

Для каждой строки:
  1. Проверить FK constraints
  2. Выполнить DELETE триггеры
  3. Удалить строку
  4. Залогировать операцию
  5. Обновить индексы

TRUNCATE: быстро, но все или ничего

-- Синтаксис TRUNCATE
TRUNCATE TABLE users;
TRUNCATE TABLE orders;
TRUNCATE TABLE products;

-- TRUNCATE НЕ поддерживает WHERE
TRUNCATE TABLE users WHERE age > 70;  -- ERROR!

-- TRUNCATE сбрасывает счетчик identity
TRUNCATE TABLE users;
-- Следующий вставленный id будет 1 (не следующий по порядку)

Под капотом TRUNCATE:

1. Деаллоцировать все страницы таблицы (кроме 1-й)
2. Обнулить счетчик identity
3. Залогировать одну операцию (не каждую строку)

Примеры из реальной жизни

Сценарий 1: Удалить старые логи

-- ПЛОХО: слишком медленно
DELETE FROM event_logs WHERE created_at < '2023-01-01';
-- Если логов 1M+ это может заблокировать таблицу на часы

-- ХОРОШО: использовать TRUNCATE после copy
CREATE TABLE event_logs_backup AS
SELECT * FROM event_logs WHERE created_at >= '2023-01-01';

TRUNCATE TABLE event_logs;  -- быстро

INSERT INTO event_logs
SELECT * FROM event_logs_backup;

DROP TABLE event_logs_backup;

Сценарий 2: Удалить только определённых пользователей

-- ТОЛЬКО DELETE подходит (TRUNCATE не может с WHERE)
DELETE FROM users WHERE status = 'inactive' AND last_login < '2022-01-01';

Сценарий 3: Очистить тестовую таблицу

-- Быстрая очистка для тестирования
TRUNCATE TABLE test_data;

-- Или с переинициализацией identity
TRUNCATE TABLE test_data;
DBCC CHECKIDENT ('test_data', RESEED, 0);  -- SQL Server
-- ALTER SEQUENCE test_data_seq RESTART;  -- PostgreSQL

Identity/Sequence поведение

DELETE сохраняет счетчик

CREATE TABLE users (
    user_id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(100)
);

INSERT INTO users VALUES ('Alice');
INSERT INTO users VALUES ('Bob');
INSERT INTO users VALUES ('Charlie');
-- user_id будут: 1, 2, 3

DELETE FROM users WHERE user_id = 3;
-- Следующий INSERT будет иметь user_id = 4

INSERT INTO users VALUES ('David');
-- user_id = 4 (не переиспользует 3)

TRUNCATE обнуляет счетчик

TRUNCATE TABLE users;
-- Identity счетчик обнулен

INSERT INTO users VALUES ('Eve');
-- user_id = 1 (начинается с нуля)

Производительность: DELETE vs TRUNCATE

-- Тест: удалить 1 млн строк

-- DELETE (МЕДЛЕННО)
DELETE FROM big_table WHERE id <= 1000000;
-- Время: ~2-5 минут
-- Лог растет: Каждая строка логируется
-- Таблица заблокирована

-- TRUNCATE (БЫСТРО)
TRUNCATE TABLE big_table;
-- Время: < 1 секунда
-- Лог минимален: одна операция
-- Таблица быстро освобождается

Foreign Key constraints

DELETE проверяет FK

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- DELETE проверит есть ли ссылки
DELETE FROM users WHERE user_id = 1;
-- Ошибка: foreign key constraint violation (если есть заказы)

TRUNCATE может нарушить FK

TRUNCATE TABLE users;
-- УСПЕШНО, но НАРУШАЕТ целостность!
-- orders.user_id теперь ссылаются на несуществующих пользователей

-- Решение: очистить в правильном порядке
TRUNCATE TABLE orders;  -- сначала зависимые
TRUNCATE TABLE users;   -- потом основные

Триггеры

DELETE срабатывает триггеры

CREATE TABLE users_audit (
    user_id INT,
    action VARCHAR(20),
    deleted_at TIMESTAMP
);

CREATE TRIGGER trg_delete_user
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO users_audit VALUES (OLD.user_id, 'DELETED', NOW());
END;

DELETE FROM users WHERE user_id = 1;
-- Триггер СРАБОТАЕТ, в audit будет запись

TRUNCATE не срабатывает триггеры

TRUNCATE TABLE users;
-- Триггер НЕ срабатывает!
-- В audit таблице НЕ будет записей

-- Если нужна аудит, используй DELETE
DELETE FROM users;  -- триггер сработает

Откат (ROLLBACK) в транзакции

-- Оба можно откатить в транзакции

BEGIN TRANSACTION;
  DELETE FROM users WHERE id = 1;
  ROLLBACK;  -- Отменяет DELETE

BEGIN TRANSACTION;
  TRUNCATE TABLE users;
  ROLLBACK;  -- Отменяет TRUNCATE

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

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

• ✅ Нужно удалить выборочно (WHERE clause)
• ✅ Критичны FK constraints
• ✅ Нужны триггеры
• ✅ Нужно сохранить identity값
• ✅ Аудит важен

DELETE FROM users WHERE status = 'inactive';

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

• ✅ Нужно удалить ВСЕ строки
• ✅ Нужна максимальная скорость
• ✅ Тестовая данные (нет critical constraints)
• ✅ Не нужны триггеры
• ✅ Identity может начать с нуля

TRUNCATE TABLE test_table;

Потребление диска

ДО:
┌─────────────────────────────────────┐
│ Таблица users (1GB): 10M строк      │
├─────────────────────────────────────┤
│ Индекс idx_user_id: 100MB           │
└─────────────────────────────────────┘

ПОСЛЕ DELETE:
┌─────────────────────────────────────┐
│ Таблица users: 800MB (не все место  │
│ освобождается, есть "дыры")          │
└─────────────────────────────────────┘

ПОСЛЕ TRUNCATE:
┌─────────────────────────────────────┐
│ Таблица users: по умолчанию         │
│ резервирует одну страницу (8KB)     │
└─────────────────────────────────────┘

Лучшие практики

-- 1. Для большого удаления, используй DELETE в batches
DELETE FROM events WHERE created_at < '2020-01-01' LIMIT 100000;
DELETE FROM events WHERE created_at < '2020-01-01' LIMIT 100000;
-- Повторяй пока есть строки
-- Это не блокирует таблицу

-- 2. Очищение таблицы для тестирования
TRUNCATE TABLE test_orders;

-- 3. Перезагрузить данные
TRUNCATE TABLE products;
INSERT INTO products SELECT * FROM products_backup;

-- 4. История и backup перед большим DELETE
CREATE TABLE products_deleted AS
SELECT * FROM products WHERE expiry_date < '2024-01-01';

DELETE FROM products WHERE expiry_date < '2024-01-01';
-- Теперь данные в backup, если что-то пошло не так

Памятка для собеседования

DELETE:

• Удаляет строки одну за одной
• Поддерживает WHERE
• Медленнее
• Логирует каждую операцию
• Срабатывают триггеры
• Проверяются FK
• Сохраняет identity

TRUNCATE:

• Очищает все сразу
• Очень быстро
• Нет WHERE
• Минимальное логирование
• Не срабатывают триггеры
• Может нарушить FK
• Обнуляет identity

Выбор:

• Нужна гибкость? → DELETE
• Нужна скорость и нет constraints? → TRUNCATE