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表空间中去，便于进行回滚（rollback）和重做操作，因此生成的大量操作日志也会占用磁盘空间。

2.2 truncate

truncate是数据库DDL定义语言，不受事务影响，也不会触发 trigger。执行操作后会立即生效，无法找回删除的数据。

执行truncate table table_name 会立刻释放磁盘空间 ，不管是 InnoDB和MyISAM 都一样 。

truncate可以退快速清空一个表。并且重置auto_increment自动增长的值。针对不同类型的数据存储引擎是有区别的，具体如下：

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 执行成功后不管是MyISM还是InnoDB都会立刻释放磁盘空间 ，并且会删除该数据表上依赖的约束(constrain)、触发器(trigger)、索引(index); 依赖于该表的存储过程/函数将保留,但是会变为失效状态。

总结

在工作当中执行数据库删除的时候一定要慎重再慎重，建议每次进行数据删除的使用最好数据表的备份工作，这样就会大大减少你删除跑路的几率。很多时候不要过于相信自己的动手能力，老虎还有打盹的时候，万一手滑了呢。尽可能养成好的数据库运维习惯，这样会让自己少跌跟头，你的事业才会更加顺利