面试题 - 千万级大表如何快速删除大量数据-CSDN博客

本文链接：https://blog.csdn.net/weixin_46619605/article/details/148330609

千万级大表如何快速删除大量数据

1. 一次性直接删除大量数据，可能存在哪些问题？

千万级的大表，如果一次性删除所有数据，可能会导致数据库锁表、日志膨胀爆炸、CPU飙升、主从延迟等问题。

1.1 锁表卡死业务
问题：删除操作会长时间锁表（尤其是大事务），其他查询和写入操作被阻塞。
后果：业务接口超时、页面卡顿，用户体验断崖式下降。
删除1000万条数据耗时2小时 → 期间用户无法下单或查询。

1.2 事务日志爆炸
问题：数据库需记录所有删除操作的事务日志（用于回滚）。
后果：日志文件体积暴涨，可能撑满磁盘空间，导致数据库崩溃。
删除1亿条数据，日志可能增长到500GB → 磁盘直接写满。

1.3.CPU飙升、性能下跌
问题：删除时需更新所有索引、触发约束检查、执行触发器逻辑。
后果：CPU和I/O资源被大量占用，数据库整体响应变慢。
删除期间数据库CPU飙升至100%，正常查询延迟可能从2ms升到10秒。

1.4. 主从延迟
问题：主库删除操作需同步到从库，大事务会导致复制延迟。
后果：从库数据长时间不一致，影响依赖从库的业务（如报表、备份）。
比如主库删除耗时2小时 → 从库延迟3小时，期间报表数据错误。

1.5. 回滚困难
问题：删除中途失败或主动取消时，事务需回滚。
后果：回滚耗时可能比删除更久，甚至回滚失败导致数据损坏。
删除5000万条数据1小时后中断 → 回滚需要2小时，业务停摆更久。

2. 删除前预演

删除前，我们可能要有一些预演、确认的东西。比如评估要删除的数据量、对应的方案确认、删除条件是否加索引、数据备份等等

2.1 评估删除数据量、方案确认
在删除前，我们要评估一下，要删除的数据量是多少。因为不同的数据量，要选定的删除方案其实是不一样的。

比如：

删大部分（如90%以上）：用新建表+删除旧表法更快。
删小部分（如10%以下）：直接分批删除更省事。

2.2 使用索引优化删除条件
要删除前，我们要确认删除条件是否有索引。删除条件的优化非常重要。你可以确保删除操作是基于索引来执行的，从而加速查找和删除过程。

如果你的删除条件中有某些字段（如日期、ID范围等），确保这些字段有索引。
索引可以大大提高删除的效率，但要小心不要对删除过程中的索引造成额外负担。

2.3 考虑数据备份
在进行大规模删除操作前，确保你有数据的备份。大表删除不可避免地会有数据丢失的风险，备份可以帮助你恢复数据。

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

3. 删除大批量数据的常见方案

3.1 分批删除
为什么用分批思想呢？

打个比喻:假如你需要搬一万块砖到楼顶,你有一个电梯,电梯一次可以放适量的砖（最多放500）,你可以选择一次运送一块砖,也可以一次运送500,你觉得哪个时间消耗大?

分批删除：避免单次事务过大。

delete from tianluo_tab where 条件 LIMIT 1000; – 每次删1000条
循环执行直到删完，每批后加短暂停顿（如0.1秒）。

我们还可以关闭自动提交：减少事务开销。

SET autocommit=0; – 手动控制事务
– 执行删除…
COMMIT;

3.2 分区表大法（提前规划）
如果是经常需要删除大量数据的场景，考虑将表设计为分区表。这样，你可以通过删除分区来大大减少操作时间，而无需逐行删除数据。

比如：数据按时间或范围分区（如日志表）。

– 直接删除整个分区（秒级完成）
ALTER TABLE table DROP PARTITION partition_name;
优点：无需逐行删除，直接清理物理文件。
条件：表必须提前按分区键设计。

3.3 创建新表并删除旧表
如果删除超过50%的数据，或需要保留的数据较少。可以使用创建新表并删除旧表的方式。

步骤如下：

建新表：只插入需要保留的数据。
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE 保留条件;
重命名表：快速切换新旧表。
RENAME TABLE old_table TO old_table_backup, new_table TO old_table;
删旧表：确认数据无误后删除备份表。
DROP TABLE old_table_backup;

优点：速度极快，几乎不锁表。
缺点：需要重建索引、外键等（可提前在新表加好）

3.4 使用TRUNCATE而不是DELETE（删除整个表数据）
如果我们要删除整个表数据，TRUNCATE 通常比 DELETE 更高效，因为它不会逐行删除数据，而是直接释放表的空间。

– 高效清空整个表（保留表结构）
TRUNCATE TABLE tianluo_tab;

3.5 一些删除加速技巧
用专业辅助工具：如 MySQL：pt-archiver（自动分批删除，低影响）。
低峰期操作：避免业务高峰期，减少冲突。
备库先测试：先在从库模拟操作，验证影响。
优先删冷数据：如将旧数据迁移到归档表再删除。