MySQL删除大量数据的一些建议

生产环境,往往需要更新/删除大量的数据,由于很可能消耗太多的IO资源,对于生产繁忙的系统,需要小心,以避免对生产环境造成影响。
删除大量数据还有一些副作用,比如主从延时、数据文件无法收缩、锁表等。

以下是一些要指引和规则:

1、批量删除,这样往往可以工作得更快,你可能需要在每次批量删除前sleep一段时间,控制删除的频率,这样的目的是减少对生产系统的IO冲击,把符合平均分布,避免从库滞后太多;

2、可以考虑分区表技术,我很少用分区表,但删除一个分区,显然比删除大量数据简单方便的多,这也是分区表清理/归档数据的优势所在;

3、按照主键的序列分批分批,或者基于时间分批分批,你总可以找到一种方式批量删除,如果实在没有批量删除的方式,可能你的表结构设计得不好;

4、基于硬件的性能,每批删除的记录数,可以选择几百到几千到几万的数据量,但不要太大,MySQL很难同时处理好大事务和随机小事务;

5、如果要删除大部分数据,那么可以考虑的方式是,创建一个新表,insert要保留的数据,然后切换表;

6、对于大表(InnoDB)删除大量数据,如果是一个很大的事务,中止删除数据的操作,可能需要几倍的时间用于回滚,导致严重的IO瓶颈,而批量删除可以让我们的回滚恢复得快得多。

7、需要留意空间的释放,选择独立表空间会更有利于释放空间。