MySQL的delete, drop, truncate有何根本不同?背后原理揭秘！

关于MySQL的delete、drop 、 truncate三者区别，已经有很多解释的文章了，但是基本上都之基于这三个命令的用法上的区别，很难让人记住。很少有从根本原理上解释三者区别的文章，所以我就尝试着整理了一下。

1. DELETE

1. 工作原理

操作需要逐行删除数据，并记录每一行的事务日志，因此相对较慢。

MySQL实战第一讲 － 一条SQL查询语句是如何执行的？_mysql查询语句表不存在是在分析器判断的吗-CSDN博客

我们可以看到即使是执行一条删除数据的简单语句， 也要通过分析器，优化器，执行器，最终调用存储引擎提供的读写结构，进行删除，如果该表有索引，则索引也会被删除。

同时还有一点非常重要，除了删除数据，还得写redo log和binlog日志。

2. Binlog日志内容

binlog 的格式设置，有三种主要格式：

1. STATEMENT 格式：记录 SQL 语句本身。
2. ROW 格式：记录每一行的更改。
3. MIXED 格式：根据具体情况选择使用 STATEMENT 或 ROW 格式。

 假设mysql的binlog格式为row时，我们看看binglog中会如何记录吧。

-- 创建表并插入数据
CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

INSERT INTO example (name) VALUES ('Alice'), ('Bob'), ('Charlie');

执行上述操作后，表中的数据如下所示：

id	name
1	Alice
2	Bob
3	Charlie

-- 删除所有数据（不指定 WHERE 条件）
DELETE FROM example;

在 ROW 格式下，binlog 会记录每一行的删除操作(每一行中的所有的列的内容都被记录，假如有100万行的话， binlog会记录100行的数据。 可想而知binlog的记录会很花时间)

binlog内容：

# at 1234
#230801 10:00:00 server id 1  end_log_pos 1267  Table_map: `database`.`example` mapped to number 100
# at 1267
#230801 10:00:00 server id 1  end_log_pos 1300  Delete_rows: table id 100
### DELETE FROM `database`.`example`
### WHERE
###   @1=1
###   @2='Alice'
# at 1300
#230801 10:00:00 server id 1  end_log_pos 1333  Delete_rows: table id 100
### DELETE FROM `database`.`example`
### WHERE
###   @1=2
###   @2='Bob'
# at 1333
#230801 10:00:00 server id 1  end_log_pos 1366  Delete_rows: table id 100
### DELETE FROM `database`.`example`
### WHERE
###   @1=3
###   @2='Charlie'

3. Redo Log 内容

### REDO LOG ENTRY ###
Transaction ID: 12345
Log Sequence Number (LSN): 987654321
Operation: DELETE
Table: example
Row ID: 1
Old Values: { id: 1, name: 'Alice' }

### REDO LOG ENTRY ###
Transaction ID: 12345
Log Sequence Number (LSN): 987654322
Operation: DELETE
Table: example
Row ID: 2
Old Values: { id: 2, name: 'Bob' }

### REDO LOG ENTRY ###
Transaction ID: 12345
Log Sequence Number (LSN): 987654323
Operation: DELETE
Table: example
Row ID: 3
Old Values: { id: 3, name: 'Charlie' }

• Transaction ID：事务的唯一标识符。
• Log Sequence Number (LSN)：日志序列号，用于标识Redo Log的顺序。
• Operation：具体的数据库操作，如 DELETE。
• Table：表名。
• Row ID：行的唯一标识符。
• Old Values：操作前的数据值。

redo log是物理日志，记录了物理层面数据页的变跟，用于数据库崩溃后进行回复。 binglog记录逻辑日志，用于按时间点恢复数据以及数据复制(如主备复制)的场景。 虽然二者的使用场景不同，但是当执行 `DELETE FROM example;` 语句时都在各自的日志中记录了该表的所有行的数据，如果该表的数据量非常大时，写日志会非常花时间。

4. DELETE 操作不会重置自增主键

-- 插入新数据
INSERT INTO example (name) VALUES ('Dave'), ('Eve');

执行上述操作后，表中的数据如下所示：

id	name
4	Dave
5	Eve

如上所示，自增主键未重置，新插入的数据 id 从之前的最大值继续。

2. drop table

1. 工作原理

DROP TABLE 的操作速度非常快，因为它直接删除表的元数据文件 (.frm) 和数据文件 (.ibd)，从而释放所有与表相关的存储空间。

1.  .frm 文件用于存储表的结构定义。这包括表头、索引、约束等信息。每个表都有一个对应的 .frm 文件。
2. .ibd 文件用于存储表的数据和索引。对于InnoDB存储引擎的表，数据和索引都保存在 .ibd 文件中。

2. 示例说明

1. 创建表并插入数据

-- 创建一个示例表
CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- 插入数据
INSERT INTO example (name) VALUES ('Alice'), ('Bob'), ('Charlie');

执行上述SQL语句后，MySQL会在数据目录下创建两个文件：

• example.frm：存储表的结构定义
• example.ibd：存储表的数据和索引

2. 查看文件

在mysql的默认的数据目录下，可以看到以下文件：

/var/lib/mysql/your_database/example.frm
/var/lib/mysql/your_database/example.ibd

3. 删除表

DROP TABLE example;

执行 DROP TABLE 后，MySQL会删除 example.frm 和 example.ibd 文件，从而释放所有与表相关的存储空间。这是一个快速的操作，因为它直接删除文件，而不需要逐行处理数据。

当执行 DROP TABLE 操作时，即使 binlog format 设置为 ROW，binlog 仍然会以 STATEMENT 格式记录。

# at 120
#220803 16:38:20 server id 1 end_log_pos 100 Query thread_id=2 exec_time=0 error_code=0 
SET TIMESTAMP=1659571100/*!*/; 
DROP TABLE `test`.`employees`/*!*/;

3. TRUNCATE TABLE

1. 工作原理

TRUNCATE TABLE 操作实际上会执行一个内部的 DROP 和 CREATE 操作：

• 内部 类似于DROP的 操作：
  • 删除所有的数据页(删除.ibd 文件)，但保留 .frm 文件（表结构定义文件），避免重新定义表的结构，从而简化表的重建过程
  • 该操作不会记录逐行删除的数据，也不会在事务日志中记录每一行的删除操作，因此操作速度比 DELETE FROM 快得多，但稍慢于 DROP TABLE，因为 TRUNCATE TABLE 需要重新初始化表结构

• 内部 CREATE 操作：

  • 重新初始化表的结构，将自增主键重置为初始状态(从1开始)。
  • 该表在文件系统中仍然保留相同的 .frm 文件，但数据文件被清空。

2. 示例说明

# trancate执行前，数据目录下有以下文件：
/var/lib/mysql/your_database/example.frm
/var/lib/mysql/your_database/example.ibd

TRUNCATE TABLE example;

# truncte执行后.bd文件被删除， 表结构定义文件（.frm）保留
/var/lib/mysql/your_database/example.frm

即使在 ROW 格式下，MySQL 会使用 STATEMENT 格式来记录TRUNCATE TABLE 。示例如下：

# at 180
#220803 16:38:20 server id 1 end_log_pos 180 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1659571100/*!*/;
TRUNCATE TABLE `test`.`employees`/*!*/;

TRUNCATE 操作会重置自增主键

INSERT INTO example (name) VALUES ('Eve'), ('Frank');

执行上述操作后，表中的数据如下所示：

id	name
1	Eve
2	Frank

如上所示，自增主键被重置，新插入的数据 id 从1开始。

4. 总结

区别	delete	truncate	drop
SQL类型	DML	DDL	DDL
影响范围	全部数据或者部分数据(带where)	无法带where，影响整个表	无法带where，影响整个表
删除内容	删除表的全部或部分数据 保留表结构还在	删除表中所有数据(直接删除.idb文件) 保留表结构	删除表中所有数据，包括表结构，索引和权限等(直接删除.idb文件和.frm文件)
需要的权限	delete权限	drop权限	drop权限
支持回滚	支持	不支持	不支持
如何记入日志	binlog中详细记录各个行的删除前后的全部数据	只有truncate table的语句	只有drop table的语句
执行速度	速度最慢 需要逐行删除数据和索引	速度快	速度最快(drop > truncate >> delete)
是否重置自增主键	不重置	重置	全部被删除，所以就不存在自增主键
是否可恢复	通过binlog可以恢复被删除的数据	无法恢复 ※阿里云RDS mysql有自动备份时可使用PITR来恢复truncate执行之前的数据 (全量备份+binlog，truncate语句不执行)	无法恢复 ※阿里云RDS mysql有自动备份时可使用PITR来恢复drop执行之前的数据 (全量备份+binlog，drop语句不执行)