MSSQL、MySQL 数据库删除大批量千万级百万级数据的优化

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

 

SQL Server上面删除1.6亿条记录，不能用Truncate（因为只是删除其中少部分数据）。

经过实验，每次删除400万条要花1.5 - 3小时，而且是越到后面越慢，正常的话，需要大约102个小时，大约4天半时间。这在生产环境下是不能接受的。

经过一个处理之后，我每次删除400万条记录花5 - 6分钟，删除全部1.6亿条记录花了4 - 5个小时！

为什么？？

 

每次删除记录，数据库都要相应地更新索引，这是很慢的IO操作，而且后面索引碎片越来越多，就更慢，这就是为什么一开始只花1.5小时，后面要3小时才能删除400万条记录的原因。

 

删除之前，做个完整备份。
我在删除前先保存当前索引的DDL，然后删除其索引，
然后根据使用的删除条件建立一个临时的索引（这是提高速度的另外一个重要原因！）
开始删除操作，完成之后再重建之前的索引。

 

如果需要保留的数据比较少的话，可以把要保留的数据备份出来。在drop表。重新创建，先不要急着创建索引、主键，把数据导回去，然后在建索引、约束之类的。

 

记得在删除的时候不要在记录日志的模式下面，否则日志文件就要爆了。

 

2、在My SQL数据库使用中，有的表存储数据量比较大，达到每天三百万条记录左右，此表中建立了三个索引，这些索引都是必须的，其他程序要使用。  

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

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

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