一、回顾delete、drop、truncate

再介绍删除解决方案前，先来回顾下三种删除表的操作：delete语句、truncate语句以及drop语句。

1、delete、truncate、drop概述

（1）delete

• 属于DML语言，每次删除一行，都在事务日志中为所删除的每行记录一项，产生rollback，事务提交之后才生效

• 如果有相应的 trigger，执行的时候将被触发

• 如果删除大数据量的表速度会很慢

• 删除表中数据而不删除表的结构，同时也不释放空间

（2）truncate

• truncate是DDL语言, 操作立即生效，自动提交，原数据不放到rollback segment中，不能回滚， 操作不触发trigger

• 删除内容、释放空间但不删除表的结构

• 当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小；

（3）drop

• drop也属于DDL语言，立即执行，执行速度最快

• 删除内容和定义，释放空间。删除之后，依赖于该表的存储过程/函数将保留，但是变为invalid状态

2、区别

（1）表和索引所占空间　　

• 当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小
• DELETE操作不会减少表或索引所占用的空间
• DROP语句将表所占用的空间全释放掉

（2）应用范围　　

• TRUNCATE 只能对table；
• DELETE可以是table和view。

（3）执行速度　　

　　drop > truncate > delete

（4）删除原理

• delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页。

• delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。truncate table 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

• 当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。

• 如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变；使用truncate删除之后，种子会恢复到初始值。

3、总结

（1）使用场景

• 当你不再需要该表时， 用 drop

• 当你仍要保留该表，但要删除所有记录时， 用 truncate

• 当你要删除部分记录时，用 delete.

（2）注意事项

• 在没有备份情况下，谨慎使用 drop 与 truncate。

• 对于由 FOREIGN KEY 约束引用的表，应使用 DELETE 语句，可以激活触发器。

下面说下删除大量数据的解决方案：

二、delete批量删除

• delete执行速度与索引量成正比，若表中索引量较多，使用delete会耗费数小时甚至数天的时间

• 执行大批量删除的时候注意要使用上limit。因为如果不用limit，删除大量数据很有可能造成死锁lock wait timeout exceed。

• 删除的条件where尽量命中索引

DELETE FROM t_test LIMIT 100000

或者建立存储过程：

delimiter $$
DROP PROCEDURE IF EXISTS proc_batch_delete;
CREATE PROCEDURE proc_batch_delete()
BEGIN
    DECLARE tcount BIGINT;
    SELECT count(0) INTO tcount FROM t_test;    
    WHILE tcount>0 DO
    DELETE FROM t_test LIMIT 1000;
    END WHILE;
    SELECT tcount;
END $$
delimiter ;

//调用存储过程 CALL proc_batch_delete;

三、使用drop

1、基于老表新建新表

create table new_test like t_test;

表结构与原表结构相同

2、插入数据

这里要看下是删除的数据多，还是保留的数据多，我们这里默认删除的数据多，所以新表中只保留有用的数据！几千万的数据量一定要分批插入，一次50万为最佳，毕竟mysql的数据处理能力有限，可以按id查询后插入!

INSERT INTO new_test SELECT *FROM t_test WHERE id>500000 AND id<=1000000;

3、drop原表 

DROP TABLE t_test;

4、新表改名为原表名 

ALTER TABLE t_test_new RENAME t_test;

四、使用truncate

（未完待续。。。）