千万大表如何删除数据

大表数据如何删除

思考：
1.一次行删除可能存在的问题
2.删除条件是否加索引
3.删除方案
4.删除后一些后置处理

一次性删除存在的问题

1.1锁表卡死业务：

因为删除会长时间锁表，（尤其是大事务），其他查询和写入操作被阻塞。
影响：业务接口超时，页面卡死。

删除一千万条数据，耗时间是2个小时，在这个期间用户是无法下单和查询的。

1.2事务日志爆炸：
问题：数据库需要记录所有的删除的事务日志（用于回滚）
后果：日志文件体积暴涨，可能撑满磁盘空间，导致数据库崩溃。

删除一个亿，日志可能曾涨到500G，磁盘直接写满。

1.3cpu飙升，性能下跌 。
问题：删除时候需要更新所有的索引，触发约束检查，执行触发逻辑。

后果：CPU和IO资源被大量占用，数据库整体响应变慢。

删除期间CPU飙升到100%，正常查询延迟可能从2ms升级到10秒。

1.4主从延迟：
问题：主库删除操作会同步到从库，大事务会导致延迟。
后果：从库数据长时间不一致，影响依赖从库的业务（报表，备份）

1.5回滚困难：
问题：删除中途失败或者主动取消时，事务需要回滚。
后果：回滚耗时比删除事件更久，甚至回滚失败导致数据损坏。

删除5000万个数据小时后中断---回滚需要2个小时，业务停摆更久。

因此面对上边的问题，我们需要考虑一些删除前置的工作：
1.评估需要删除的量
2.对应的方案的确认
3.删除条件是否加索引
4.数据备份等等。

2.1删除数量，方案的确认：

删除前要确定删除的量，因为不同的数据量方案是不一样的。

1.删大部分（90%以上）,用新建表+删除旧表更快。
2.删除小部分：直接分批删除更省事儿。

2.2使用索引优化删除条件

删除前：要确认删除的条件是否有索引，如果确保删除的条件是基于索引来的，从而加速查找和删除过程。

1.如果删除的条件中有：ID，日期，确保这些字段有索引
2.所以可以大大提高删除的效率，但是要小心不要对删除过程中的索引造成额外负担。

2.3考虑数据备份：

在进行大规模删除前，要确保自己的数据有备份，大表删除不可避免的会有数据丢失的风险，备份可以帮助你恢复数据。

1.在删除前进行一个完整的数据备份
2.如果通过批次删除的方式，记得进行增量备份，减少数据丢失的风险。

删除大量数据常见的方案：

分批删除：避免事务过大

分批，加短暂的停顿（0.1秒）

如果经常删除大量数据：可以考虑分区，这样可以通过删除分区来大大减少操作时间，而无需朱行删除数据。

键新表，删除旧表

如果有50%的数据，或需要保留的数据较少，我们就可以创建新表并删除旧表，把需要保留的数据保存到新表。
1.键新表：直插入保留的数据
2.重命名表，快速切换新旧表
3.删除旧表
优点：速度很快不会锁表
缺点：需要重新创建索引，外键（可以提前在新表上加好）

使用TRUNCATE，而不是delete（删除整个表的数据的时候）

如果要删除整个表的数据使用truncate要比delete更快，因为他不会逐行删除，而是直接释放标的空间。

了解一下删除加速技巧：

MYSQL：pt-archiver(自动分批删除，低影响)
地峰期间操作：避免业务高峰期，减少冲突。
备库先测试：先在从库模拟操作，验证影响范围。
优先删除冷数据：如将旧表数据迁移到归档表在删除。

删除后的，一些后置处理：

我们需要一些后置处理，比如数据验证，资源释放，监控，日志。
1.数据的验证：验证数据是否删除：查询count一下表就知道了
2.验证正确的数据没有被误操作，而删除，比如查询一下保留的数据保留了。
3.关系系统验证：调接口观察是否正常调用了这些数据。

监控与日志：

监控数据库的性能：
1.观察删除后的CPU，内存，I/0是否回归正常水平
2.检查慢查询日志，确认无因索引失效导致性能问题。