drop、truncate和delete的区别及效率

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。

1. 如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；

2. 如果想保留表而将所有数据删除，如果和事务无关(不能回滚)，用truncate即可；

3. 如果和事务有关，或者想触发trigger，还是用delete；

4. truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。

5. truncate只能作用于表；delete，drop可作用于表、视图等。

6. truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；drop会删除表的结构及其所依赖的约束、索引等。

7. truncate会重置表的自增值；delete不会。

8. truncate不会激活与表有关的删除触发器；delete可以。

9. truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。