MySQL中drop、truncate和delete的区别

1.前言

对于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等等。

2.drop

DROP命令用于删除整个表（结构和数据），或数据库等对象，特点如下：

• 彻底删除：DROP命令不仅删除表内的所有数据，还删除了表的结构定义。这个过程是不可逆的，除非有备份可以恢复。
• 释放资源：执行DROP命令后，与该表相关的数据库资源会被释放。
• 非事务性操作：DROP操作通常不能回滚，执行DROP命令后，相关对象就被立即删除。
• 自增ID：如果创建新表，自增ID会重新开始计数。

代码示例：

DROP TABLE employees;

注意：DROP语句执行后立即生效，无法找回。

3.truncate

TRUNCATE命令用于删除表中的所有行，其特点如下：

• 快速清空表：TRUNCATE比使用DELETE删除表中的所有行要快得多，因为它不逐行删除数据，而是通过释放存储这些数据的数据页来删除数据并重新初始化表。

• 非事务性操作：尽管某些数据库管理系统可能允许TRUNCATE操作在事务中回滚，但在很多情况下，TRUNCATE并不记录详细的日志，因此不能像DELETE操作那样保证事务安全。

• 不触发触发器：通常，执行TRUNCATE操作不会触发表的触发器。

• 自动重置自增ID：对于有自增主键的表，TRUNCATE会重置自增计数器。

• truncate会删除表中所有记录，并且将重新设置高水线和所有的索引。

  就是truncate会删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子），缺省情况下将空间释放到minextents的extent（就是表结构中的段内的区域），除非使用reuse storage（使用这句话，所在的extent空间不会被回收，只是将数据删除掉，数据删除之后的freespace空间，只能供本表使用，其他的不可以使用）。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复

示例：

TRUNCATE TABLE employees;

注意：TRUNCATE语句执行后立即生效，无法找回。

4.delete

DELETE命令用于删除表中的一行或多行记录，具有如下特点：

• 选择性删除：可以通过WHERE子句指定删除哪些行。若不指定WHERE子句，则删除表中的所有行。

• 事务性操作：DELETE操作是事务安全的，这意味着你可以在一个事务中回滚DELETE操作。这在你意外删除了错误数据时非常有用。

• 触发器：如果表上有触发器，执行DELETE操作会触发它们。

• 性能：因为DELETE操作逐行删除数据，并记录日志，所以在删除大量数据时可能会比较慢。

• delete语句不影响表所占用的extent（就是表结构的中的区），高水线(high watermark)保持原位置不变。 （高水位线就存在于段（segment)中，它用于标识段中已使用过的数据块与未使用的数据块二者间交界，扫描表数据的时候，高水位线以下的所有数据块都必须被扫描。）

• 在 InnoDB 中，delete其实并不会真的把数据删除，mysql 实际上只是给删除的数据打了个标记为已删除，因此 delete 删除表中的数据时，表文件在磁盘上所占空间不会变小，存储空间不会被释放，只是把删除的数据行设置为不可见。虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以重用这部分空间（重用 → 覆盖）。

• delete执行时，会先将所删除数据缓存到rollback segement中，事务commit之后生效;delete from table_name删除表的全部数据，对于MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;
  对于delete from table_name where xxx带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间;
  delete操作以后使用 optimize table table_name则会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table操作。

示例：

DELETE FROM employees WHERE department = 'Sales';

5.总结

在速度上，一般来说，drop> truncate > delete。

• 如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；
• 如果想保留表而将所有数据删除，如果和事务无关(不能回滚)，用truncate即可；
• 如果和事务有关，或者想触发trigger，还是用delete；
• 如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。
• truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。
• truncate只能作用于表；delete，drop可作用于表、视图等。
• truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；drop会删除表的结构及其所依赖的约束、索引等。
• truncate会重置表的自增值；delete不会。
• truncate不会激活与表有关的删除触发器；delete可以。
• truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。