数据库系列——删除命令delete、truncate、drop的使用与区别

delete、truncate、drop命令之间的区别

* 注：DML（数据操作语言）

DDL（数据定义语言）

	delete	truncate	drop
命令类型	DML	DDL	DDL
功能	根据指定的SQL语句从表中删除单个或多个记录内容，但是不影响表结构，回滚段要足够大； 如果和事务有关，或者想触发trigger，还是用delete	从数据库中删除所有记录，但表结构及其列、约束、索引等保持不变。相当于先 drop table 然后 create table。 新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用delete。如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。	删除表的数据、结构、定义，包括被依赖的约束(constrain)，触发器(trigger)，索引(index)；依赖于该表的存储过程/函数将保留，但是变为invalid状态。
语法	delete from 表名 [where子句]	truncate 表名	drop 表名
执行返回值	int类型	语句执行后没有返回值，或者说返回值没有具体的含义。
其他表有引用本表列的外键时	不带where子句的delete语句可以执行	truncate会执行失败，不能truncate一个带有外键的表，如果要删除首先要取消外键，然后删除；
操作对象	table，view，synonym	table	表table
rollback segment	删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复。	删除的数据是操作立即生效，原数据不放到rollback segment中，不能回滚，数据不可以回滚恢复。
日志与执行速度	当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源，delete命令比truncate命令使用更多的事务空间，因为需要维护日志，所以执行速度很慢。	使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。在事务日志中维护最少的日志记录，因此执行速度更快	由于truncate table不记录在日志中，所以速度很快。drop > truncate > delete
对于自增字段的影响	使用DELETE语句删除表中所有记录后，再次向表中添加记录时自动增加字段的值为删除时该字段的最大值加1	使用truncate可以回收表的水位，使自增字段值归零。使用TRUNCATE语句删除表中的数据后，再次向表中添加记录时自动增加字段的默认初始值重新由1开始；
锁定	行级锁定，表中的每一行都被锁定以进行删除	采用表级锁定，锁定了整个表以删除所有记录
索引视图	可以与索引视图一起使用	不能与索引视图一起使用
空间释放	不影响表所占用的extent，高水位(high watermark)保持原位置不动	缺省情况下将空间释放到minextents个extent，除非使用reuse storage。否则truncate会将高水位复位，因为默认情况下truncate table = truncate table drop storage;	将表所占用的空间全部释放，
关于事务	不会自动提交事务，操作会触发trigger	执行后会自动提交事务，和事务无关，操作不触发trigger
安全性	delete是对象必须是本模式下的，或被授予delete on schema.table 或 delete any table的权限。	truncate table的对象必须是本模式下的，或者有drop any table的权限；	小心使用drop和truncate，尤其没有备份的时候

SQL调优时建议使用truncate代替delete：

PS：使用select语句查询数据时，数据库会扫描高水位线以下的数据块，因为高水位线没有变化，所以扫描的时间不会减少，所以才会出现使用delete删除数据以后，查询的速度还是和delete以前一样。

举例说明：有个1亿条数据的表data_t，只需要保留其中的前5条数据，其他删除，如何做？

这就需要用truncate table来搞定了，如下：

select * into temporary_t from data_t limit 0,5 order by id
# temporary_t是临时表

truncate table data_t

insert  data_t select * from temporary_t

drop table temporary_t