truncate、delete、drop 的区别？

结论：DELETE 是 DML，逐行删除、可回滚、可带条件、会写 undo 与 binlog；TRUNCATE 与 DROP 是 DDL，隐式提交、不可回滚。TRUNCATE 保留表结构并重置自增，DROP 连表结构一起删。三者恢复成本与锁行为完全不同。

一、逐项对照

二、执行过程差异

DELETE FROM t WHERE ctime < '2025-01-01';
  ┌──────────────────────────────────────────────┐
  │ 逐行定位 → 标记删除 → 写 undo（可回滚）        │
  │ → 写 redo → 写 binlog（row 格式含每行前镜像） │
  │ 100 万行 ≈ 100 万次行操作 + 海量日志           │
  └──────────────────────────────────────────────┘

TRUNCATE TABLE t;
  ┌──────────────────────────────────────────────┐
  │ 取 MDL 排他锁 → 丢弃数据页、重建表空间        │
  │ → 重置 AUTO_INCREMENT → 提交（DDL 隐式提交）  │
  └──────────────────────────────────────────────┘

DELETE 的代价不只是删数据，还有 undo 膨胀与 binlog 体积；TRUNCATE 的代价是拿不到 MDL 时会一直等，把后续请求全部堵住。

三、实践中的写法

分批删除，避免大事务与主从延迟：

-- 每批 2000 行，循环执行直到影响行数为 0
DELETE FROM t_log
WHERE ctime < '2025-01-01'
ORDER BY id
LIMIT 2000;

-- 用主键区间推进，效率更高（避免每批都从头扫）
DELETE FROM t_log
WHERE id BETWEEN 1000000 AND 1001999
  AND ctime < '2025-01-01';

清空表并重置自增：

TRUNCATE TABLE t_log;
-- 等价于 DROP + CREATE，但权限要求更低（需要 DROP 权限）

防误删：

SET sql_safe_updates = 1;   -- 无 WHERE 的 UPDATE/DELETE 直接报错
DELETE FROM t_log;          -- ERROR 1175: You are using safe update mode

删除后回收空间：

-- DELETE 全表后表文件不会变小，需要重建
OPTIMIZE TABLE t_log;
-- 或（8.0 支持在线重建）
ALTER TABLE t_log ENGINE = InnoDB, ALGORITHM = INPLACE, LOCK = NONE;

四、选型判断

• 需要按条件删一部分：只能 DELETE，且必须分批。
• 需要清空整表且保留结构：TRUNCATE，注意它会重置自增、不可回滚。
• 表不再需要：DROP，但先确认没有外键引用、没有视图/存储过程依赖。
• 要保留数据以备回溯：先 RENAME TABLE 成备份表，再新建空表，比 DELETE 安全得多。

RENAME TABLE t_log TO t_log_bak_20261103, t_log_new TO t_log;

常见追问

• TRUNCATE 真的不能回滚吗？ 不能。它是 DDL，执行前隐式提交当前事务，InnoDB 也无法用 undo 恢复。
• 为什么 DELETE 全表后磁盘没变小？ 删除只把页标记为可复用，空间留在表空间里；需要 OPTIMIZE TABLE 重建。
• TRUNCATE 会锁表吗？ 会取 MDL 排他锁，期间所有读写阻塞；被长事务挡住时会一直等。
• DELETE 能触发触发器吗？ 能，逐行触发；这也意味着批量删除时触发器会把开销放大。
• 三者的权限要求？ DELETE 需要 DELETE 权限；TRUNCATE 需要 DROP 权限；DROP 需要 DROP 权限。
• TRUNCATE 与 DELETE 在主从复制中的差异？ row 格式下 DELETE 会产生大量行事件，TRUNCATE 只是一条 DDL，从库执行更快但风险同样不可逆。

小结

把三者记成一句话：DELETE 是「一行一行地删，能反悔但很慢」，TRUNCATE 是「把数据页整体丢弃，快但不可逆」，DROP 是「连表一起消失」。生产环境的红线是：大表不直接 DELETE 全表，TRUNCATE/DROP 前必须有备份与二次确认。