drop、truncate和delete的区别及效率
前言
对于drop、truncate和delete,过去对三者的区别有一定简介,但不成体系。因此,参看多篇文章之后进行一个总结。在此之前先简单了解下什么是DDL和DML。
DDL(数据定义语言,Data Definition Language):DDL代表数据定义语言,是一种有助于创建数据库模式的SQL命令。DDL中常用的命令有:create,drop,alter,truncate和rename等等。
DML(数据操作语言,Data Manipulation Language):DML代表数据操作语言,是一种有助于检索和管理关系数据库中数据的SQL命令。DML中常用的命令有:insert,update,delete和select等等。
总之,在研究三者的区别时必须明确drop和truncate是DDL,只能操作表。而delete是DML,能操作表中数据。
1 delete
delete可根据条件删除表中满足条件的数据,如果不指定where子句,那么删除表中所有记录。delete有两种用法,一种是带where条件删除一个范围内的数据,另一种则是不带where条件全部删除。如:delete from table_name where xxx
和delete from table_name
。delete是DML,只删除数据不删除表的结构,会走事务,执行时会触发trigger。
在 InnoDB 中,delete其实并不会真的把数据删除,mysql 实际上只是给删除的数据打了个标记为已删除,因此 delete 删除表中的数据时,表文件在磁盘上所占空间不会变小,存储空间不会被释放,只是把删除的数据行设置为不可见。
###2 truncate
truncate的作用是清空表或者说是截断表,并且重置auto_increment
的值。只能作用于表。不能回滚,也不会触发触发器。
注意:truncate会立刻释放磁盘空间 ,不管是 InnoDB和MyISAM 。对于外键(foreign key )约束引用的表,不能使用 truncate table。对于MyISAM,truncate会重置auto_increment(自增序列)的值为1。
执行truncate语句需要拥有表的drop权限。truncate table类似于delete删除所有行的语句或drop table然后再create table语句的组合。
###3 drop
drop作用是删除表的结构(元数据)和表数据,其用法为:drop table table_name
,drop是DDL,会隐式提交,所以,不能回滚,不会触发触发器。并且drop语句删除表结构及所有数据,并将表所占用的空间全部释
drop语句将删除表的结构所依赖的约束,触发器,索引,依赖于该表的存储过程/函数将保留,但是变为invalid状态。
4、总结
在速度上,一般来说,drop> truncate > delete
。
-
如果想删除部分数据用delete,注意带上where子句,回滚段要足够大;如果想删除表,当然用drop;
-
如果想保留表而将所有数据删除,如果和事务无关(不能回滚),用truncate即可;
-
如果和事务有关,或者想触发trigger,还是用delete;
-
truncate与drop是DDL语句,执行后无法回滚;delete是DML语句,可回滚。
-
truncate只能作用于表;delete,drop可作用于表、视图等。
-
truncate会清空表中的所有行,但表结构及其约束、索引等保持不变;drop会删除表的结构及其所依赖的约束、索引等。
-
truncate会重置表的自增值;delete不会。
-
truncate不会激活与表有关的删除触发器;delete可以。
-
truncate后会使表和索引所占用的空间会恢复到初始大小;delete操作不会减少表或索引所占用的空间,drop语句将表所占用的空间全释放掉。