我说mysql之truncate
本文是新系列《我说mysql》的第一篇,从本文开始,我会陆续对mysql相关的重要知识点进行讲解和总结。
今天先讲一下mysql的一个关键字“truncate”,剖析一下它的特点及其与delete、drop的区别。
truncate是DDL,会隐式提交,所以,不能回滚,不会触发触发器。
DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
而 TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
truncate会删除表中所有记录,并且将重新设置高水线和所有的索引,缺省情况下将空间释放到minextents个extent,除非使用reuse storage,。不会记录日志,所以执行速度很快,但不能通过rollback撤消操作(如果一不小心把一个表truncate掉,也是可以恢复的,只是不能通过rollback来恢复)。
当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小,
DELETE操作不会减少表或索引所占用的空间。
drop语句将表所占用的空间全释放掉。
对于外键(foreignkey )约束引用的表,不能使用 truncate table,而应使用不带 where 子句的 delete 语句。
对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。
truncate table不能用于参与了索引视图的表。
总结
- 从速度上来说,drop > truncate > delete。
- 在使用drop和truncate时一定要注意,虽然可以恢复,但为了减少麻烦,还是要慎重。
- 如果想删除部分数据用delete,注意带上where子句,回滚段要足够大;
- 如果想删除表,当然用drop;
- 如果想保留表而将所有数据删除,如果和事务无关,用truncate即可;
- 如果和事务有关,或者想触发trigger,还是用delete;
- 如果是整理表内部的碎片,可以用truncate跟上reuse stroage,再重新导入/插入数据。