Mysql删除大批量数据的方法

原文：http://blog.csdn.net/songyanjun2011/article/details/7308414

 

MySQL删除大批量数据 

1.删除大表的部分数据

一个表有1亿6000万的数据，有一个自增ID。最大值就是1亿6000万，需要删除大于250万以后的数据，有什么办法可以快速删除？ 

看到MySQL文档有一种解决方案：http://dev.mysql.com/doc/refman/5.0/en/delete.html 

         If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. To avoid this problem, or simply to minimize the time that the table remains locked, the following strategy (which does not use DELETE at all) might be helpful:

        Select the rows not to be deleted into an empty table that has the same structure as the original table:
        INSERT INTO t_copy SELECT * FROM t WHERE ... ;

        Use RENAME TABLE to atomically move the original table out of the way and rename the copy to the original name:
        RENAME TABLE t TO t_old, t_copy TO t;

        Drop the original table:
        DROP TABLE t_old; 

删除大表的多行数据时，会超出innod block table size的限制，最小化的减少锁表的时间的方案是： 
1、选择不需要删除的数据，并把它们存在一张相同结构的空表里 
2、重命名原始表，并给新表命名为原始表的原始表名
3、删掉原始表

原文：http://www.2cto.com/database/201211/170796.html

2、删除带索引的表

       在My SQL数据库使用中，有的表存储数据量比较大，达到每天三百万条记录左右，此表中建立了三个索引，这些索引都是必须的，其他程序要使用。由于要求此表中的数据只保留当天的数据，所以每当在凌晨的某一时刻当其他程序处理完其中的数据后要删除该表中昨天以及以前的数据，使用delete删除表中的上百万条记录时，MySQL删除速度非常缓慢，每一万条记录需要大概4分钟左右，这样删除所有无用数据要达到八个小时以上，这是难以接受的。
       

       查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的，于是删除掉其中的两个索引后测试，发现此时删除速度相当快，一百万条记录在一分钟多一些，可是这两个索引其他模块在每天一次的数据整理中还要使用，于是想到了一个折中的办法：

        在删除数据之前删除这两个索引，此时需要三分钟多一些，然后删除其中无用数据，此过程需要不到两分钟，删除完成后重新创建索引，因为此时数据库中的数据相对较少，约三四十万条记录(此表中的数据每小时会增加约十万条)，创建索引也非常快，约十分钟左右。这样整个删除过程只需要约15分钟。对比之前的八个小时，大大节省了时间。