DELETE 与 TRUNCATE 在事务回滚与性能上的真实差距

TRUNCATE无法回滚是铁律，因其属DDL操作且隐式提交；delete可回滚但性能差、开销大；TRUNCATE快如闪电但受外键、触发器、自增ID等限制。

TRUNCATE 无法回滚是铁律，别信“加了 BEGIN 就能撤”

在 mysql 和 oracle 中，TRUNCATE table 是 DDL 操作，执行即隐式提交——哪怕你手动写了 BEGIN TRANSACTION，它也会立刻生效，ROLLBACK 完全无效。实测中，以下操作后数据永远丢失：

START TRANSACTION;
TRUNCATE TABLE logs;
ROLLBACK;

而 DELETE FROM logs 在同一事务里可以随时 ROLLBACK 恢复。唯一例外是 SQL Server（支持 DDL 回滚），但跨数据库迁移时绝不能依赖这点。

删 100 万行，DELETE 耗时 82 秒，TRUNCATE 只要 0.01 秒

性能差距不是线性，而是数量级碾压：

• DELETE 逐行标记、写 undo/redo 日志、触发器校验、行锁排队——100 万行产生约 200MB 日志，CPU 占用峰值超 70%
• TRUNCATE 直接释放数据页，只记一条元数据变更，日志几乎为零，全程表级锁但毫秒级完成
• 中等规模（10 万行）已见分晓：DELETE 平均 8.2 秒 vs TRUNCATE 0.01 秒

这意味着：线上清理大表时，用 DELETE 不仅慢，还可能拖垮主库 I/O 和连接池。

外键、触发器、自增 ID —— 这些地方一踩就报错

看似只是“删数据”，但底层行为差异极大：

• 有子表外键引用？TRUNCATE 直接报错；DELETE 可配 ON DELETE CAScadE 或先禁用约束
• 表上有 AFTER DELETE 触发器？TRUNCATE 完全不触发，业务逻辑会断掉
• auto_increment 值：DELETE 后下一条插入仍是原最大值+1；TRUNCATE 后重置为 1（MySQL 默认）
• 权限要求也不同：TRUNCATE 需要 DROP 权限，不是只有 DELETE 权限就够

生产环境执行前，务必查清 select CONSTRaiNT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'your_table'。

该用哪个？看这三点就足够判断

不用背区别，直接按场景选：

• 要删全部数据 + 不需要回滚 + 没外键 + 不依赖触发器 → 无条件选 TRUNCATE
• 要删部分数据（比如 WHERE created_at ）→ 只能用 DELETE
• 要删全部但担心误操作 → 先 CREATE TABLE backup AS SELECT * FROM t，再 TRUNCATE，别指望 rollback

最常被忽略的是：InnoDB 下 DELETE FROM t 不释放磁盘空间，后续必须 OPTIMIZE TABLE t 才能回收——而 TRUNCATE 一步到位。