Разбираем разницу между TRUNCATE и DELETE в SQL

Быстрый ответ

Команда TRUNCATE выполняет очистку таблицы в массовом порядке и отличается высокой скоростью выполнения. Эта команда удаляет все строки, не позволяя указывать условия, и при этом не регистрирует каждое изменение в журнале. Помимо этого, TRUNCATE сбрасывает автоинкрементные счётчики, если таковые присутствуют в таблице. Используйте TRUNCATE, если вам необходимо быстро и полностью очистить таблицу:

TRUNCATE TABLE table_name;  -- Таблица пуста.

В отличие от TRUNCATE, команда DELETE предоставляет гораздо большую гибкость: она позволяет выставлять ряд условий (WHERE) для удаления определенных строк, использовать откат операций и отслеживать каждое удаление. Этот вариант подходит для случаев, когда требуется контроль над процессом удаления:

DELETE FROM table_name WHERE condition; -- Удаление с условиями.

В сравнении TRUNCATE равносилен крупному инструменту, как молотку, а DELETE — это уже скорее точечный инструмент, аналогичный скальпелю.

Когда использовать TRUNCATE, а когда DELETE

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

Особенности TRUNCATE:

• TRUNCATE — это команда DDL, быстро и целиком очищающая таблицу.

• Это команда освобождает пространство, занимаемое таблицей, возвращая его сразу же обратно.

• Команда не учитывает отдельные строки и не регистрирует лог операций, тем самым, не занимая пространство журналирования.

• TRUNCATE не активирует триггеры на уровне строк, так как не взаимодействует с ними по отдельности.

• Работает с эксклюзивной блокировкой на протяжении всей операции.

• В случае автоинкрементных полей – обнуляет регистры.

• Обновляет неиспользуемые ранее индексы, делая их готовыми к дальнейшему использованию.

TRUNCATE TABLE Employee; -- Полный кадровый перезапуск.

Детали DELETE:

• DELETE — это команда DML, позволяющая удалять строки по одной или все сразу, в зависимости от заданных условий.

• Команда регистрирует лог операций для каждой удаленной строки, обеспечивая полноценное логирование.

• DELETE не затрагивает индексы, они остаются в прежнем виде.

• Команда применяет блокировку на уровне строк, обеспечивая возможность соревновательного выполнения операций.

• Поле с автоинкрементом остается без изменений.

• DELETE активирует DML триггеры, если они имеются.

DELETE FROM Employee WHERE name='John Doe'; -- Увольнение конкретного сотрудника.

В ролях: DELETE и TRUNCATE

Посмотрим более тщательно на их характеристики:

Журналы транзакций и обработка данных

TRUNCATE действует более экономично по отношению к журналированию — создает меньше данных для отката и не оказывает существенного влияния на журналы.

TRUNCATE TABLE logs; -- Удаление всей истории событий.

Команда DELETE документирует каждое удаление, generируя при этом большие объемы данных для отката и повторного выполнения.

DELETE FROM logs WHERE log_date < CURRENT_DATE; -- Удаление данных за период до актуальной даты.

Триггеры и возможность отката

TRUNCATE не активирует DML триггеры, в то время как DELETE вызывает их для каждой удаленной строки.

TRUNCATE TABLE tries;  -- Триггеры не активизируются.
DELETE FROM tries;  -- Активизация триггеров.

После применения TRUNCATE отсутствует возможность восстановить данные до состояния до операции.

Права доступа и ограничения

TRUNCATE можно выполнить, имея только соответствующие привилегии, даже без прав на удаление данных.

GRANT SELECT, TRUNCATE ON employees TO user;  -- Очистка таблицы возможна, не требующая прав на удаление.

Если таблица участвует во внешнем ключевом ограничении, выполнять TRUNCATE не получится, в отличие от DELETE, которое возможно при соблюдении ключевого условия.

DELETE FROM parent_table WHERE id NOT IN (SELECT FOREIGN_ID FROM child_table);  -- Удаление с учетом внешних ключей.
TRUNCATE TABLE parent_table;  -- Запрет на выполнение из-за внешних ключей.

Особенности SQL Server и таблиц, оптимизированных для работы в памяти

В SQL Server TRUNCATE может сбросить счетчики в столбцах Identity. Такого не произойдет в таблицах, оптимизированных для работы в памяти.

Визуализация

-- Диалог TRUNCATE и DELETE
Truncate: Не помню, что удалял 🧠.
Delete: Я тоже забываю после COMMIT или ROLLBACK.
Truncate: Не могу отменить свои действия 😎.
Delete: Я отменяю все до COMMIT 😉.
Truncate: Надо очистить эту таблицу 🏓.
Delete: Пока ты занят, я активизирую ТРИГГЕРЫ 🔫.

Практические соображения

Производительность 💹

TRUNCATE выполняется значительно быстрее DELETE, когда нужно удалить все строки из таблицы, так как он совершает массовую операцию.

Подробнее об этом расскажет наш спикер на видео

Масштабируемость 📈

TRUNCATE предпочтительнее при обработке больших объемов данных из-за его минимальных оверхедов на логирование. Использование DELETE без добавления условий может снижать производительность на больших таблицах.

Конкурентность 🧩

При необходимости минимизации блокировок и обеспечении параллелизма, DELETE получает преимущество за счет его меньшего влияния на работу базы данных.

Восстановление данных и аудит 📚

При строгих требованиях к восстановлению данных и аудиту в пользу команды DELETE говорит ее возможность отслеживания и отката транзакций.

Полезные материалы

1. Stack Overflow — В чем разница между TRUNCATE и DELETE в SQL

2. Microsoft Learn — TRUNCATE TABLE (Transact-SQL)

3. Oracle Docs — Использование Flashback Drop и управление Корзиной

4. MySQL 8.0 Reference Manual — TRUNCATE TABLE Statement

5. PostgreSQL Documentation — TRUNCATE

6. IBM DB2 9.7 for Linux, UNIX, and Windows — CREATE TRIGGER

7. TechOnTheNet — SQL: TRUNCATE TABLE Statement