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

Транзакционность и implicit commit

-- DELETE — можно откатить
START TRANSACTION;
DELETE FROM orders WHERE created_at < '2024-01-01';
ROLLBACK;  -- строки вернутся

-- TRUNCATE вызывает implicit commit ПЕРЕД выполнением
START TRANSACTION;
INSERT INTO log VALUES ('important');
TRUNCATE TABLE staging_orders;  -- здесь MySQL неявно делает COMMIT!
-- INSERT уже зафиксирован, его не откатить
ROLLBACK;  -- ничего не делает

-- DROP — аналогично TRUNCATE, implicit commit
DROP TABLE old_reports;

Три разные операции

DELETE, TRUNCATE и DROP — принципиально разные по семантике, транзакционности и производительности.

• DELETE — DML-операция. Удаляет строки по условию (или все, если WHERE нет). Каждая удалённая строка пишется в undo log и binlog (row-формат — построчно). Работает внутри транзакции: можно сделать ROLLBACK. Триггеры BEFORE DELETE / AFTER DELETE срабатывают.
• TRUNCATE — DDL-операция (несмотря на «очистку таблицы»). В InnoDB пересоздаёт таблицу (drop + recreate), сбрасывает AUTO_INCREMENT. Вызывает implicit commit — нельзя откатить в рамках BEGIN...ROLLBACK. Триггеры не срабатывают. Значительно быстрее DELETE на больших таблицах.
• DROP — DDL-операция. Удаляет таблицу целиком (структуру + данные + индексы). Тоже вызывает implicit commit. Отменить нельзя (только восстановление из бэкапа).

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

-- DELETE миллиона строк — медленно: row-by-row undo log + binlog
DELETE FROM big_table WHERE status = 'archived';
-- Лучше батчами:
DELETE FROM big_table WHERE status = 'archived' LIMIT 10000;
-- Повторять до ROW_COUNT() = 0

-- TRUNCATE — мгновенно (пересоздаёт .ibd файл):
TRUNCATE TABLE big_table;

-- DROP — мгновенно, но необратимо:
DROP TABLE big_table;

AUTO_INCREMENT и FK

CREATE TABLE demo (id INT AUTO_INCREMENT PRIMARY KEY, val VARCHAR(50));
INSERT INTO demo VALUES (NULL, 'a'), (NULL, 'b');  -- id: 1, 2
DELETE FROM demo;                                    -- AUTO_INCREMENT остаётся 3
TRUNCATE TABLE demo;                                 -- AUTO_INCREMENT сбрасывается в 1

-- TRUNCATE и FK: если на таблицу есть FOREIGN KEY из другой таблицы,
-- TRUNCATE вернёт ошибку (в отличие от DELETE с каскадом)

Binlog и репликация

DELETE логируется построчно в row-based binlog или как SQL-запрос в statement-based. TRUNCATE и DROP пишутся как DDL-события и реплицируются как statement. На реплике TRUNCATE/DROP применяется немедленно и не может быть «отмотан» через binlog.

Подводные камни

• TRUNCATE не откатывается — implicit commit до и после операции. Случайный TRUNCATE в середине транзакции фиксирует всё, что было раньше.
• Триггеры игнорируются TRUNCATE — если в BEFORE/AFTER DELETE есть бизнес-логика (аудит, очистка связанных данных), TRUNCATE её обойдёт.
• FOREIGN KEY блокирует TRUNCATE — даже если дочерняя таблица пустая, наличие FK ограничения не позволяет TRUNCATE родительскую таблицу. Нужно SET FOREIGN_KEY_CHECKS=0 (опасно) или удалять в правильном порядке.
• DELETE без WHERE на большой таблице — эквивалентно TRUNCATE по результату, но генерирует огромный undo log и binlog, блокирует параллельные запросы на долгое время.
• DROP в скрипте миграции без IF EXISTS — если таблица уже удалена, скрипт упадёт. Всегда использовать DROP TABLE IF EXISTS в миграциях.
• innodb_file_per_table и DROP — при включённом innodb_file_per_table (дефолт MySQL 5.6+) DROP физически удаляет .ibd файл. Это может освободить место на диске, чего TRUNCATE тоже достигает (пересоздание).
• Репликация и TRUNCATE — на statement-based репликации TRUNCATE реплицируется как DDL-событие и применяется к реплике немедленно без возможности фильтрации по строкам.
• Параллельный SELECT во время DELETE — MVCC позволяет читателям видеть старые версии строк во время DELETE, но длинная транзакция DELETE удерживает undo log и замедляет всю систему.

• Не уточнять storage engine и говорить о delete, truncate и drop так, будто MyISAM и InnoDB ведут себя одинаково.
• Игнорировать implicit commit, autocommit, isolation level или binlog там, где они меняют ответ.
• Описывать PostgreSQL-поведение как MySQL-поведение.
• Забывать проверить план через EXPLAIN и фактическую версию MySQL.

• Кандидат объясняет delete, truncate и drop через реальный механизм MySQL, а не общими словами.
• Приводит корректный SQL или диагностический запрос для этой СУБД.
• Называет ограничения, версионные отличия или эксплуатационные последствия.
• Связывает ответ с проектированием приложения, производительностью, надежностью или безопасностью.