mysql之drop、truncate和delete的区别

delete

1. delete是DML(data maintain language)，这个操作会被放到rollback segment中，事务提交之后才会生效，如果有相应的触发器trigger，那么执行的时候可以被触发
2. 执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。
3. delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。
4. delete语句不影响表所占用的extent（就是表结构的中的区），高水线(high watermark)保持原位置不变。 （高水位线就存在于段（segment)中，它用于标识段中已使用过的数据块与未使用的数据块二者间交界，扫描表数据的时候，高水位线以下的所有数据块都必须被扫描。）

truncate

truncate 特性

1. truncate是DDL（data define language）即操作会立即生效，原数据不会放到rollback segment中，不能回滚，也不会触发触发器
2. truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents的extent（就是表结构中的段内的区域），除非使用reuse storage（使用这句话，所在的extent空间不会被回收，只是将数据删除掉，数据删除之后的freespace空间，只能供本表使用，其他的不可以使用）。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。
3. 对于外键（foreign key ）约束引用的表，不能使用 truncate table（会报错（Cannot truncate a table referenced in a foreign key constraint ）），也不能使用drop table（会报错（Cannot delete or update a parent row: a foreign key constraint fails）），而应使用不带 where 子句的 delete 语句。
4. truncate table不能用于参与了索引视图的表。

truncate 需注意点

• truncate 约等于 drop+create
• drop table 引起的MySQL 短暂 hang 死的问题，是由于 drop 一张使用AHI空间较大的表时，调用执行AHI的清理动作，会消耗较长时间，执行期间长时间持有dict_operation_lock的X锁，阻塞了其他后台线程和用户线程，drop table执行结束锁释放。
• 由于MySQL 开启了 AHI（自适应哈希索引）后如果 DROP 或 TRUNCATE 一张大表，MySQL 会同时删除 buffer pool 中对应表上的AHI，这个过程需会持有一把数据字典锁（非常重的锁），导致其他用户线程因为无法获数据字典锁而处于 Opening tables 状态。
  • 哈希(hash)是一种非常快的查找方法,在一般情况下这种查找的时间复杂度为O(1),即一般仅需要一次查找就能定位数据。而B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为3-4层,故需要3~4次的查询。
  • InnodB存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升,则建立哈希索引,称之为自适应哈希索引(Adaptive Hash Index,AHI)
  • AHI是通过缓冲池的B+树页构造而来,因此建立的速度很快,而且不需要对整张表构建哈希索引。InnoDB存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。
  • 对于这个问题，可以在DROP TABLE的时候关闭AHI功能，甚至可以永久关闭AHI功能。
• 操作批量truncate该set上的表，而该set是复用了生产的一个库，生产上MySQL积压的用户线程集中运行就导致积压越来越多，执行truncate批量操作锁释放后，就出现了并发线程和连接数瞬间上升的现象。

drop

drop 特点

1. drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。
2. drop语句删除表结构及所有数据，并将表所占用的空间全部释放。
3. drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

drop 需注意点

• MySQL 中删除一个表时, MySQL系统会做如下动作:
  • 删除表数据/索引 (ibd) 和定义 (frm) 文件。
  • 删除触发器。
  • 通过删除要删除的表来更新表定义缓存。
  • 扫描 InnoDB 缓冲池以查找关联页面以使其无效。（内存到的表会遇到系统hang）。
  • DROP 是一个 DDL 语句，它需要持有元数据锁 (MDL) 才能完成，这样会导致所有其他线程必须等待DDL完成，清除表相关的大量数据页会对缓冲池产生额外的压力。
  • 最后，table_definition_cache 操作需要 LOCK_open mutex 来清理，这会导致所有其他线程等待直到删除完成。
  • 为了降低此操作的严重性，我们可以使用 pt-archiver 等工具 通过批量的形式删除大量数据，从而显着降低表大小。一旦我们从大表中删除了记录，DROP 操作就会快速进行而不会对系统性能产生影响。

总结

1. 在速度上，一般来说，drop> truncate > delete。
2. 在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。
3. 如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；如果想删除表，当然用drop；
4. 如果想保留表而将所有数据删除，如果和事务无关(不能回滚)，用truncate即可；
5. 如果和事务有关，或者想触发trigger，还是用delete；
6. 如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。