Что работает быстрее, TRUNCATE или DELETE?

Развернутый ответ: TRUNCATE vs DELETE

Если коротко, TRUNCATE работает значительно быстрее, чем DELETE во всех основных СУБД (Oracle, PostgreSQL, MySQL, SQL Server). Разница может достигать сотен раз и более при работе с большими таблицами.

Основные причины скорости TRUNCATE

• Минимальное журналирование: TRUNCATE обычно фиксируется в журнале транзакций как операция освобождения целых экстентов (блоков данных, выделенных таблице), а не как удаление каждой отдельной строки. Например, вместо записи "удалил строки с id 1, 2, 3... 1 000 000", пишется "освободил экстенты 5, 7, 9 таблицы X". Это радикально уменьшает объем данных в логе.
• Отсутствие проверок ограничений (Constraints): TRUNCATE не активирует триггеры ON DELETE и не проверяет внешние ключи (FOREIGN KEY) (если нет каскадного удаления, а в некоторых СУБД проверяется всегда, что является ограничением). DELETE же для каждой строки должен убедиться, что ее удаление не нарушает целостность данных.
• Сброс идентификаторов (Identity/Sequences): В большинстве СУБД TRUNCATE сбрасывает счетчик автоинкрементного поля (AUTO_INCREMENT, SEQUENCE) до начального значения. Это побочный эффект, но он показывает, что операция работает с метаданными таблицы, а не с данными.
• Блокировка на уровне таблицы: TRUNCATE почти всегда требует и получает эксклюзивную блокировку таблицы (LOCK TABLE). Это позволяет ей работать максимально быстро, но полностью блокирует любые другие операции с таблицей на время своего выполнения. DELETE может использовать более мелкие блокировки (строк, страниц), что позволяет параллельным операциям читать или изменять другие строки, но добавляет накладные расходы на управление блокировками.

Технические отличия и примеры

1. Уровень операции

• DELETE — операция DML (Data Manipulation Language). Она работает с данными внутри таблицы.
• TRUNCATE — операция DDL (Data Definition Language) в Oracle, PostgreSQL, SQL Server (с некоторыми оговорками) или DDL-подобная в MySQL. Она работает с метаданными и структурой хранения таблицы.

2. Возможность отката (Rollback)

• DELETE — полностью участвует в транзакции. Ее можно откатить командой ROLLBACK (если транзакция не завершена COMMIT).
• TRUNCATE — также может быть откачена в Oracle, PostgreSQL и SQL Server (но не во всех старых версиях). В MySQL (с движками InnoDB, MariaDB) TRUNCATE также транзакционна и откатываема. Важный миф: утверждение, что TRUNCATE нельзя откатить, устарело для современных СУБД, но откат все равно будет быстрым, так как восстанавливаются лишь метаданные.

3. Синтаксис и WHERE-условие

• DELETE — позволяет использовать условие WHERE для выборочного удаления.

  DELETE FROM orders WHERE status = 'cancelled';
  

• TRUNCATE — не поддерживает WHERE. Она всегда удаляет все строки из таблицы.

  TRUNCATE TABLE orders;
  -- или в некоторых СУБД просто TRUNCATE orders;
  

Практический вывод и рекомендации

Используйте TRUNCATE, когда вам нужно:

• Быстро удалить все строки из большой таблицы.
• Полностью очистить таблицу (включая сброс счетчика автоинкремента).
• Выполнить операцию в рамках ETL-процесса или перед загрузкой нового дампа данных.

Используйте DELETE, когда вам нужно:

• Удалить только часть данных (с условием WHERE).
• Чтобы сработали триггеры ON DELETE (например, для каскадного удаления или аудита).
• Удалить строки, на которые ссылаются внешние ключи без ON DELETE CASCADE.
• Выполнить операцию с более детальными блокировками, позволяющими параллельный доступ к другим частям таблицы.

Пример производительности (гипотетический, PostgreSQL)

Предположим, таблица events содержит 10 миллионов записей.

-- Медленно: каждая строка журналируется, проверяются триггеры.
-- Время: может занимать минуты.
BEGIN;
DELETE FROM events;
COMMIT; -- или ROLLBACK для отмены.

-- Быстро: только освобождение пространства в журнале.
-- Время: доли секунды.
BEGIN;
TRUNCATE events;
COMMIT; -- или ROLLBACK для отмены.

Итог: Для полной очистки таблицы TRUNCATE — безусловный чемпион по скорости. Однако его "тяжелая" блокировка и негибкость требуют взвешенного подхода в высоконагруженных продакшен-средах, где предпочтительнее может оказаться DELETE небольшими порциями (пагинацией) или с использованием partitioning.