DELETE、TRUNCATE、DROP的区别，别再搞混了

重生之我在二本学院拿offer当牌打
76 阅读8分钟

摘要：从一次"想清空表却等了半小时"的乌龙事件出发，深度剖析DELETE、TRUNCATE、DROP三种删除方式的本质区别。通过事务回滚测试、性能压测对比、以及误删数据的恢复方案，揭秘为什么DELETE慢、TRUNCATE快、DROP危险，以及各自的适用场景。配合时序图展示执行流程，给出生产环境的最佳实践。

💥 翻车现场

周五下午4点，哈吉米准备清空测试环境的日志表。

-- 想快速清空表
DELETE FROM access_log;

10分钟过去了……还在执行中……

哈吉米："卧槽，这表才500万数据，怎么这么慢？"

20分钟过去了……

哈吉米："不会是卡住了吧？"

30分钟后，终于执行完成。

Query OK, 5000000 rows affected (30 min 15.23 sec)

哈吉米："30分钟！清空个表而已！"

旁边的南北绿豆看不下去了。

南北绿豆："清空表用DELETE？你应该用TRUNCATE啊！"
哈吉米："有区别吗？"
阿西噶阿西（凑过来）："区别大了！DELETE是逐行删除，TRUNCATE是直接清空，性能差几百倍！"
哈吉米："？？？"

🤔 三种删除方式的本质区别

南北绿豆在白板上画了一个对比表。

核心对比

特性DELETETRUNCATEDROP
作用删除数据清空表删除表
删除方式逐行删除直接删除表空间删除表定义+数据
WHERE条件✅ 支持❌ 不支持❌ 不支持
事务回滚✅ 可以❌ 不可以（DDL）❌ 不可以
触发器✅ 触发❌ 不触发❌ 不触发
自增ID不重置重置为1删除
表空间不释放释放释放
速度⭐ 慢⭐⭐⭐⭐⭐ 极快⭐⭐⭐⭐⭐ 极快
恢复binlog恢复很难很难

哈吉米："所以DELETE、TRUNCATE、DROP完全是三种不同的操作？"

阿西噶阿西："对！我们逐个讲解。"

🗑️ DELETE：逐行删除（DML）

DELETE的执行流程

sequenceDiagram
    participant Client
    participant MySQL
    participant InnoDBEngine
    participant UndoLog
    participant Binlog

    Client->>MySQL: DELETE FROM table WHERE id < 100
    MySQL->>InnoDBEngine: 开始事务
    
    loop 逐行删除
        InnoDBEngine->>InnoDBEngine: 查找id < 100的行
        InnoDBEngine->>UndoLog: 记录删除前的数据（用于回滚）
        InnoDBEngine->>InnoDBEngine: 标记行为删除
        Note over InnoDBEngine: 注意：不是物理删除，只是标记
    end
    
    InnoDBEngine->>Binlog: 写binlog
    InnoDBEngine->>MySQL: 提交事务
    MySQL->>Client: 返回影响行数

关键特点：

  1. 逐行删除（遍历表）
  2. 写undo log（用于回滚）
  3. 写binlog（用于主从复制）
  4. 标记删除，不是物理删除

DELETE的语法

-- 删除所有数据
DELETE FROM access_log;

-- 带条件删除
DELETE FROM access_log WHERE create_time < '2024-01-01';

-- 限制删除行数
DELETE FROM access_log WHERE status = 0 LIMIT 1000;

-- 多表删除
DELETE a, b FROM table_a a JOIN table_b b ON a.id = b.a_id WHERE a.status = 0;

DELETE的特点

特点1：可以回滚

START TRANSACTION;

DELETE FROM user WHERE id = 100;

SELECT * FROM user WHERE id = 100;
-- Empty set（数据已删除）

ROLLBACK;  -- 回滚

SELECT * FROM user WHERE id = 100;
-- 数据回来了！✅

原理：undo log保存了删除前的数据。

特点2：自增ID不重置

-- 删除前
SELECT * FROM user;
+----+----------+
| id | username |
+----+----------+
| 1  | alice    |
| 2  | bob      |
| 3  | charlie  |
+----+----------+

-- 删除所有数据
DELETE FROM user;

-- 插入新数据
INSERT INTO user (username) VALUES ('david');

-- 查询
SELECT * FROM user;
+----+----------+
| id | username |
+----+----------+
| 4  | david    |  ← 自增ID继续从4开始
+----+----------+

特点3：表空间不释放

-- 删除前
SELECT 
  table_name,
  ROUND(data_length / 1024 / 1024, 2) AS data_mb,
  ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.TABLES
WHERE table_name = 'access_log';

+------------+---------+----------+
| table_name | data_mb | index_mb |
+------------+---------+----------+
| access_log | 512.00  | 128.00   |
+------------+---------+----------+

-- DELETE删除所有数据
DELETE FROM access_log;

-- 删除后
SELECT ...;
+------------+---------+----------+
| table_name | data_mb | index_mb |
+------------+---------+----------+
| access_log | 512.00  | 128.00   |  ← 空间没有释放！
+------------+---------+----------+

为什么不释放？

InnoDB的DELETE是"标记删除"：
1. 数据页中的数据标记为"已删除"
2. 空间可以被新数据复用
3. 但不会释放给操作系统

释放表空间的方法：
OPTIMIZE TABLE access_log;  -- 重建表，释放空间

DELETE的性能

性能测试（删除500万行）：

DELETE FROM access_log;
-- 耗时：30分钟

为什么这么慢？

1. 逐行删除（扫描500万行）
2. 每行写undo log（500万条undo log）
3. 写binlog（500万行的binlog）
4. 更新索引（所有索引都要更新）

总IO：
- 读：500万行
- 写undo log：500万条
- 写binlog：500万行
- 更新索引：如果有5个索引，就是2500万次索引更新

✂️ TRUNCATE：清空表（DDL）

TRUNCATE的执行流程

sequenceDiagram
    participant Client
    participant MySQL
    participant FileSystem

    Client->>MySQL: TRUNCATE TABLE access_log
    Note over MySQL: 这是DDL，不是DML
    MySQL->>MySQL: 获取表级锁
    MySQL->>FileSystem: 删除.ibd文件
    MySQL->>FileSystem: 创建新的空.ibd文件
    MySQL->>MySQL: 重置自增ID
    MySQL->>Client: 返回成功（极快）
    
    Note over MySQL,FileSystem: 不写undo log<br/>不写binlog（取决于配置）<br/>不逐行删除

关键特点：

  1. 删除表空间文件，重新创建
  2. 不写undo log（不能回滚）
  3. 不逐行删除（直接删文件）
  4. 重置自增ID

TRUNCATE的语法

-- 清空表
TRUNCATE TABLE access_log;

-- 不支持WHERE条件
TRUNCATE TABLE access_log WHERE id < 100;  -- ❌ 语法错误

TRUNCATE的特点

特点1：不能回滚

START TRANSACTION;

TRUNCATE TABLE user;

SELECT * FROM user;
-- Empty set（数据已删除）

ROLLBACK;  -- 回滚

SELECT * FROM user;
-- 还是空的！❌（无法回滚）

原理：TRUNCATE是DDL操作，自动提交，不写undo log。

特点2：重置自增ID

-- 删除前
SELECT * FROM user;
+----+----------+
| id | username |
+----+----------+
| 1  | alice    |
| 2  | bob      |
| 3  | charlie  |
+----+----------+

-- TRUNCATE清空表
TRUNCATE TABLE user;

-- 插入新数据
INSERT INTO user (username) VALUES ('david');

-- 查询
SELECT * FROM user;
+----+----------+
| id | username |
+----+----------+
| 1  | david    |  ← 自增ID重置为1了
+----+----------+

特点3：释放表空间

-- TRUNCATE前
+------------+---------+----------+
| table_name | data_mb | index_mb |
+------------+---------+----------+
| access_log | 512.00  | 128.00   |
+------------+---------+----------+

-- TRUNCATE清空表
TRUNCATE TABLE access_log;

-- TRUNCATE后
+------------+---------+----------+
| table_name | data_mb | index_mb |
+------------+---------+----------+
| access_log | 0.02    | 0.00     |  ← 空间释放了！✅
+------------+---------+----------+

TRUNCATE的性能

性能测试（清空500万行）：

TRUNCATE TABLE access_log;
-- 耗时：0.5秒

为什么这么快？

1. 删除.ibd文件（一次文件删除操作）
2. 创建新的空.ibd文件（一次文件创建操作）
3. 不写undo log
4. 不逐行删除

总IO：
- 删除文件：1次
- 创建文件：1次

性能对比：

操作500万行性能差距
DELETE30分钟-
TRUNCATE0.5秒快3600倍

🔥 DROP：删除表（DDL）

DROP的执行流程

sequenceDiagram
    participant Client
    participant MySQL
    participant MetaData
    participant FileSystem

    Client->>MySQL: DROP TABLE access_log
    MySQL->>MySQL: 获取表级锁
    MySQL->>MetaData: 删除表定义（.frm）
    MySQL->>FileSystem: 删除数据文件（.ibd）
    MySQL->>MySQL: 删除元数据
    MySQL->>Client: 返回成功
    
    Note over MySQL,FileSystem: 表结构+数据都删除<br/>无法恢复（除非有备份）

关键特点：

  1. 删除表定义
  2. 删除表数据
  3. 删除所有索引
  4. 无法恢复

DROP的语法

-- 删除表
DROP TABLE access_log;

-- 如果表存在才删除
DROP TABLE IF EXISTS access_log;

-- 删除多个表
DROP TABLE table1, table2, table3;

-- 删除临时表
DROP TEMPORARY TABLE temp_table;

DROP的特点

特点1：删除表结构

-- DROP后
SHOW TABLES LIKE 'access_log';
-- Empty set（表不存在了）

DESC access_log;
-- ERROR 1146 (42S02): Table 'db.access_log' doesn't exist

特点2：无法恢复（除非有备份）

-- 误删表
DROP TABLE user;

-- 回滚？
ROLLBACK;  -- 无效，DDL自动提交

-- 恢复方法：
-- 1. 从备份恢复
-- 2. 从binlog恢复（如果开启了binlog）

DROP的性能

性能测试：

DROP TABLE access_log;
-- 耗时：0.3秒

跟TRUNCATE差不多快。

📊 三种方式的完整对比

性能对比（500万行数据）

操作执行时间速度
DELETE30分钟⭐
TRUNCATE0.5秒⭐⭐⭐⭐⭐
DROP0.3秒⭐⭐⭐⭐⭐

适用场景对比

场景推荐方式原因
删除部分数据DELETE支持WHERE条件
清空测试表TRUNCATE快，重置自增ID
清空日志表TRUNCATE快，释放空间
删除临时表DROP不再需要表结构
删除废弃表DROP彻底删除
需要回滚DELETE唯一支持回滚
需要触发器DELETE唯一触发DELETE触发器

风险对比

操作风险等级恢复方式
DELETE⚠️ 低ROLLBACK或binlog恢复
TRUNCATE⚠️⚠️ 中binlog恢复（困难）
DROP⚠️⚠️⚠️ 高备份恢复（很难）

🛡️ 生产环境最佳实践

实践1：DELETE分批删除

-- ❌ 错误：一次删除全部
DELETE FROM access_log WHERE create_time < '2024-01-01';
-- 如果有500万行，会锁表很久

-- ✅ 正确：分批删除
DELIMITER $$
CREATE PROCEDURE delete_old_logs()
BEGIN
    DECLARE affected_rows INT DEFAULT 1;
    
    WHILE affected_rows > 0 DO
        DELETE FROM access_log 
        WHERE create_time < '2024-01-01' 
        LIMIT 1000;  -- 每次1000行
        
        SET affected_rows = ROW_COUNT();
        
        -- 休息100ms，避免长时间锁表
        SELECT SLEEP(0.1);
    END WHILE;
END$$
DELIMITER ;

-- 执行
CALL delete_old_logs();

实践2：TRUNCATE前备份

-- 清空表前，先备份
CREATE TABLE access_log_backup LIKE access_log;
INSERT INTO access_log_backup SELECT * FROM access_log;

-- 确认备份成功
SELECT COUNT(*) FROM access_log_backup;

-- 再清空
TRUNCATE TABLE access_log;

实践3：DROP前改名

-- ❌ 直接删除（危险）
DROP TABLE important_table;

-- ✅ 先改名，观察一段时间，确认没问题再删除
RENAME TABLE important_table TO important_table_deleted_20241007;

-- 观察1周，确认没问题
-- ...

-- 1周后，确认删除
DROP TABLE important_table_deleted_20241007;

实践4：开启binlog（便于恢复）

-- 查看binlog是否开启
SHOW VARIABLES LIKE 'log_bin';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |  ← 已开启
+---------------+-------+

-- 如果误删数据，可以从binlog恢复
mysqlbinlog --start-datetime="2024-10-07 10:00:00" \
            --stop-datetime="2024-10-07 10:05:00" \
            mysql-bin.000123 > recover.sql

实践5：设置权限控制

-- 生产环境，普通用户不给DROP权限
REVOKE DROP ON production_db.* FROM 'developer'@'%';

-- 只给DBA DROP权限
GRANT DROP ON production_db.* TO 'dba'@'%';

🎓 面试标准答案

题目：DELETE、TRUNCATE、DROP的区别？

答案：

DELETE：

  • DML操作，删除数据
  • 逐行删除，可以加WHERE条件
  • 写undo log，可以回滚
  • 不重置自增ID
  • 不释放表空间
  • 慢（需要逐行删除）

TRUNCATE：

  • DDL操作，清空表
  • 删除表空间文件，重新创建
  • 不写undo log，不能回滚
  • 重置自增ID
  • 释放表空间
  • 快（直接删除文件）

DROP：

  • DDL操作，删除表
  • 删除表结构+数据
  • 不能回滚
  • 表不存在了
  • 释放表空间
  • 快

适用场景：

  • 删除部分数据：用DELETE
  • 清空表：用TRUNCATE
  • 删除表：用DROP

🎉 结束语

晚上6点，哈吉米终于搞清楚三种删除方式的区别了。

哈吉米："原来DELETE这么慢，清空表应该用TRUNCATE！"

南北绿豆："对，而且TRUNCATE还会重置自增ID，释放表空间。"

阿西噶阿西："记住：能用TRUNCATE就别用DELETE清空表，性能差几千倍！"

哈吉米："还有，生产环境DROP要慎重，先改名观察一段时间！"

南北绿豆："对，DROP一下去，数据就全没了，恢复很难。"

记忆口诀：

DELETE逐行删，慢但能回滚
TRUNCATE删文件，快且释放空间
DROP删表结构，彻底不可恢复
清空表用TRUNCATE，删除部分用DELETE

希望这篇文章能帮你彻底搞清楚DELETE、TRUNCATE、DROP的区别！下次面试被问到，你就能从原理到场景讲得清清楚楚！💪

avatar
文章
阅读
粉丝