MySql删除⼤量数据解决⽅案
⼀、回顾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
当你仍要保留该表，但要删除所有记录时，⽤ truncatemysql中delete语句
当你要删除部分记录时，⽤ 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
（未完待续。。。）

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。