Mysql中truncate table和delete语句的区别

本文探讨了Mysql中truncatetable与delete语句在删除表内所有数据时的区别,包括执行速度、日志记录、空间释放及对外键的影响等,并提供了在大数据量场景下的优化建议。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Mysql中的truncate table和delete语句都可以删除表里面所有数据,但是在一些情况下有些不同!

例子:

truncate table gag;

(1)truncate table删除速度更快,,但truncate table删除后不记录mysql日志,不可以恢复数据。(谨慎使用)

(2)如果没有外键关联,innodb执行truncate是先drop table(原始表),再创建一个跟原始表一样空表,速度要远远快于delete逐条删除行记录。(思考:删除百万级数据的时候是否可用truncate table)

(3)如果使用innodb_file_per_table参数,truncate table 能重新利用释放的硬盘空间,在InnoDB Plugin中,truncate table为自动回收,如果不是用InnoDB Plugin,那么需要使用optimize table来优化表,释放空间。

truncate table删除表后,optimize table尤其重要,特别是大数据数据库,表空间可以得到释放!

(4)表有外键关联,truncate table删除表数据为逐行删除,如果外键指定级联删除(delete cascade),关联的子表也会会被删除所有表数据。如果外键未指定级联(cascde),truncate table逐行删除数据,如果是父行关联子表行数据,将会报错。

注意:

一个大的 DELETE 或 INSERT 操作,要非常小心,因为这两个操作是会锁表的,表一锁住,其他操作就进不来了。因此,我们要交给DBA去拆分,重整数据库策略,比如限制处理1000条。

另外,扩展下删除和索引的联系(关于索引优化,后面的查询优化也会讲解),由于索引需要额外的维护成本;因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以,在我们删除数据库百万级别数据的时候,查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。所以我们想要删除百万数据的时候可以先删除索引(此时大概耗时三分多钟),然后删除其中无用数据,此过程需要不到两分钟,删除完成后重新创建索引(此时数据较少了)创建索引也非常快,约十分钟左右。与之前的直接删除绝对是要快速很多,更别说万一删除中断,一切删除会回滚。那更是坑了。

### MySQL 中 `TRUNCATE`、`DROP` `DELETE` 的区别及用法 #### 一、基本概念与功能对比 - **`DROP`**: 这是一个用于删除整个表或数据库的命令。执行该语句后,不仅表中的数据会被清除,而且表本身的结构也将被销毁[^1]。这意味着,在执行 `DROP` 后,原表不再存在于数据库中。 - **`TRUNCATE`**: 此命令仅清空表中的所有记录,而保留表的结构不变。它是一种快速的操作方法,通常比 `DELETE` 更高效[^4]。需要注意的是,`TRUNCATE` 是不可逆的操作,无法通过事务回滚来恢复已删除的数据。 - **`DELETE`**: 它允许有条件地或者无条件地从表中删除特定行。如果不指定 `WHERE` 条件子句,则会删除表中的所有行;然而,这并不会影响到表的定义本身[^3]。另外,由于其支持事务特性,因此可以通过回滚机制撤销误删操作。 #### 二、性能比较 在实际应用过程中,三种方式对于资源消耗程度有所不同: - 性能上来看,`DROP` 表现最佳因为它直接消除了对象及其关联元信息; - 接着是 `TRUNCATE` ,因为它是重置指针而非逐条处理每一条记录从而达到效率上的提升; - 而最后才是 `DELETE` 方法,尤其是在大规模数据集下表现尤为明显[^2]。 #### 三、适用场景分析 基于上述特点可得出如下结论: | 命令 | 是否保留表结构 | 数据是否可恢复 | 主要用途 | |------------|------------------|--------------------|--------------------------------------------------------------------------| | `DROP` | 不保留 | 不可恢复 | 当需要彻底移除某个表格时使用 | | `TRUNCATE` | 保留 | 不可恢复 | 清理大量不需要的历史数据但又要保持原有架构设计的情况下 | | `DELETE` | 保留 | 支持事务控制下的恢复 | 动态调整某些满足特定条件的数据集合 | #### 四、注意事项 当考虑采用哪种手段来进行清理作业之前,请务必确认需求并评估风险因素。例如,如果错误地实施了一个不可逆转的动作(`TRUNCATE`)可能会带来严重后果。此外还需注意权限设置问题——并非所有的用户都拥有足够的权利去执行这些破坏性的指令[^5]。 ```sql -- 示例代码展示如何分别运用三个关键字 -- 使用 DROP 删除名为 'employees' 的整张表 DROP TABLE employees; -- 利用 TRUNCATE 将 'orders' 表里的所有订单资料抹掉但仍维持原始框架 TRUNCATE TABLE orders; -- 应用 DELETE 移除年龄大于等于60岁的员工记录 DELETE FROM staff WHERE age >= 60; ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值