MySQL中删除操作Delete、Truncate、Drop用法比较

1、执行速度比较

Delete、Truncate、Drop关键字都可以删除数据。
drop > truncate > delete

2、原理方面

2.1 delete
delete 属于数据库 DML 操作语言，只会删除数据表中的记录，会执行事务，执行的时候也会触发触发器。

InnoDB 存储引擎中，执行 delete 操作只会给删除的记录打上删除标记，并不会真正删除数据。
只是把删除的数据记录设置为不可见，也不会释放磁盘空间。如果插入新的数据可以覆盖该部分的空间。

如果开启事务的话，执行 delete 操作，会先将要删除的数据缓存到 rollback segement 中，等事务 commit 之后才生效。

“delete from table_name” 不带查询条件，会删除表中的全部数据，MyISAM 引擎会立刻释放磁盘空间，InnoDB 不会释放磁盘空间。
如果是带查询条件的话，都不释放磁盘空间，可以执行 “optimize table table_name” 来立即释放磁盘空间。建议如果需要释放存储空间的话，可以执行 delete 后，然后执行 “optimize table table_name” 语句达到清理磁盘空间的目的。

-- 查询数据库test对应的表t_user 占用的磁盘空间
select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') as table_size 
from information_schema.tables 
where table_schema='test' AND table_name='t_user';

说明：delete 操作逐行执行删除的，并且同时将每行的删除操作日志记录在 redo 和 undo 表空间中去，便于进行回滚和重做操作，因此生成大量操作日志也会占用磁盘空间。

2.2 truncate
truncate 是数据库 DDL 定义语言，不受事务影响，也不会触发 trigger。执行操作后会立即生效，无法找回删除的数据。
执行 “truncate table table_name” 会立即释放磁盘空间，不管是 InnoDB 还是 MyISAM 存储引擎都一样。

truncate 可以快速清空一个表。并且重置 auto_increament 自动增长的值。针对不同类型的数据存储引擎是有区别的，具体如下：
MyISAM：truncate 会重置 auto_increment (自增序列) 的值为 1。而 delete 后表仍然保持 auto_increment。

InnoDB：truncate 会重置 auto_increment 的值为 1。delete 表之后仍然保持 auto_increment。但是在做 delete 整个表之后重启 MySQL 的话，则重启后的 auto_increment 会被置为 1。

说明：InnoDB 的表本身是无法持久保存 auto_increment。delete 表之后 auto_increment 仍然保存在内存，但是重启后就找不到了，只能从 1 开始。实际上重启后的 auto_increment 会从 “select 1+MAX(ai_col) from t” 开始。

使用 truncate 操作的时候最好要做好备份，避免出现不可挽回的情况。

2.3 drop
drop 属于数据库 DDL 定义语言，和 truncate 一样。执行后会立即生效，不可恢复。
“drop table table_name” 执行成功后，不管是 MyISAM 还是 InnoDB 都会立刻释放磁盘空间，并且会删除该数据表上依赖的约束（constrain）、触发器（trigger）和索引（index）。依赖于该表的存储过程/函数将保留，但是会变为失效状态。

总结

在工作中，执行数据库删除操作的时候一定要慎重再慎重，建议每次进行数据删除的时候，最好提前进行备份。