【架构】-- Mysql delete vs truncate 深度解析

对比项	DELETE	TRUNCATE
类型	DML	DDL
是否逐行删除	是	否
是否记录每行日志	是	否
是否可带 WHERE	支持	不支持
是否可回滚	一般可回滚	大多数情况不可回滚
是否重置自增 ID	不会	会
执行速度	慢	快
是否触发 DELETE Trigger	会	不会
锁类型	行锁	表锁
是否释放空间	不一定	通常会
binlog 量	大	小

四、DELETE 底层原理

1、DELETE 是逐行删除

DELETE 本质属于：

DML（Data Manipulation Language）

执行过程：

找到符合条件的数据
一行一行删除
记录 undo log
记录 redo log
记录 binlog
更新索引
更新 MVCC 信息

例如：

DELETE FROM order_info WHERE create_time < '2025-01-01';

数据库需要：

扫描数据
找到符合条件的行
更新聚簇索引
更新二级索引
写入事务日志

因此：

数据量越大，DELETE 越慢。

2、DELETE 支持事务回滚

例如：

BEGIN;

DELETE FROM user_info WHERE id = 1;

ROLLBACK;

数据会恢复。

原因：

DELETE 会写 undo log。

3、DELETE 不会重置 AUTO_INCREMENT

例如：

DELETE FROM user_info;

再插入：

INSERT INTO user_info(name) VALUES('Tom');

ID 可能继续增长：

不会重新从 1 开始。

五、TRUNCATE 底层原理

1、TRUNCATE 本质是 DDL

TRUNCATE 属于：

DDL（Data Definition Language）

很多数据库内部实现：

DROP + CREATE

或者：

快速重建数据文件

因此：

TRUNCATE 不需要逐行删除。

2、TRUNCATE 执行速度非常快

例如：

TRUNCATE TABLE log_info;

数据库通常：

直接清空数据页
重置元数据
重建表空间

不扫描每一行数据。

因此：

即使亿级数据：

TRUNCATE 也可能秒级完成。

3、TRUNCATE 会重置 AUTO_INCREMENT

例如：

TRUNCATE TABLE user_info;

再次插入：

INSERT INTO user_info(name) VALUES('Tom');

ID 会重新从：

开始。

4、TRUNCATE 通常不可回滚

在 MySQL 中：

TRUNCATE TABLE user_info;

会隐式提交事务。

即使：

BEGIN;
TRUNCATE TABLE user_info;
ROLLBACK;

通常也无法恢复。

六、MySQL InnoDB 深度分析

1、DELETE 的 InnoDB 行为

DELETE 会：

写 undo log
写 redo log
更新聚簇索引
更新二级索引
保留 MVCC 版本链

因此：

大量 DELETE 容易：

产生大量碎片
导致 purge lag
导致 undo 膨胀
导致 IO 飙升

2、TRUNCATE 的 InnoDB 行为

TRUNCATE：

不逐行删除
不生成大量 undo
不维护旧版本链

通常直接：

重建表

因此性能极高。

七、锁机制区别

DELETE

DELETE 使用：

行锁（Row Lock）

例如：

DELETE FROM user_info WHERE id = 1;

只锁部分数据。

并发性能较好。

TRUNCATE

TRUNCATE 使用：

表锁（Table Lock）

整个表不可读写。

因此：

线上高并发环境需要谨慎。

八、大表 DELETE 的问题

1、为什么大表 DELETE 很危险

例如：

DELETE FROM order_info;

可能导致：

主从延迟
binlog 暴涨
undo 暴涨
IO 飙升
锁等待
CPU 飙升

尤其：

千万级数据表。

2、正确做法

分批删除

DELETE FROM order_info
WHERE create_time < '2024-01-01'
LIMIT 1000;

循环执行。

使用分区表

ALTER TABLE order_info DROP PARTITION p202401;

速度远超 DELETE。

使用归档表

先归档：

INSERT INTO order_history
SELECT * FROM order_info
WHERE create_time < '2024-01-01';

再删除。

九、binlog 区别

DELETE

DELETE 会记录：

每一行变更

binlog 非常大。

TRUNCATE

TRUNCATE 只记录：

DDL 操作

binlog 很小。

十、PostgreSQL 中的区别

1、DELETE

PostgreSQL DELETE：

采用 MVCC
不会立即删除数据
只是标记为 dead tuple

因此：

需要：

VACUUM

回收空间。

2、TRUNCATE

PostgreSQL TRUNCATE：

速度极快
支持 RESTART IDENTITY
支持 CASCADE

例如：

TRUNCATE TABLE user_info RESTART IDENTITY;

3、PostgreSQL 特点

PostgreSQL 的 TRUNCATE：

居然支持事务回滚。

例如：

BEGIN;

TRUNCATE TABLE user_info;

ROLLBACK;

数据可以恢复。

这一点与 MySQL 不同。

十一、StarRocks 中的区别

1、DELETE 特点

StarRocks 属于：

MPP OLAP 数据库

DELETE 不适合频繁使用。

因为：

OLAP 引擎更适合：

批量写入
批量覆盖
分区替换

2、StarRocks DELETE 原理

DELETE 通常：

标记删除
后台 Compaction 清理

因此：

频繁 DELETE 会影响查询性能。

3、TRUNCATE TABLE

StarRocks：

TRUNCATE TABLE table_name;

会快速清空数据。

速度远高于 DELETE。

4、StarRocks 更推荐的方案

实际生产中：

更推荐：

DROP PARTITION

ALTER TABLE order_info DROP PARTITION p202501;

或者：

INSERT OVERWRITE

因为：

OLAP 引擎更适合：

批量替换

而不是逐行删除。

十二、多数据库横向对比

数据库	DELETE	TRUNCATE
MySQL	DML，逐行删除	DDL，快速清空
PostgreSQL	MVCC 标记删除	支持事务回滚
StarRocks	不适合高频 DELETE	推荐快速清空
Oracle	支持回滚机制复杂	高性能 DDL
SQL Server	逐行记录日志	页级快速释放