MySQL 高效批量删除海量数据策略解析

最新推荐文章于 2025-02-12 16:29:40 发布

天冬忘忧

最新推荐文章于 2025-02-12 16:29:40 发布

阅读量1.3k

点赞数 26

分类专栏： SQL 文章标签： sql 大数据数据库

本文链接：https://blog.csdn.net/weixin_64726356/article/details/144203036

版权

SQL 专栏收录该内容

18 篇文章

订阅专栏

在日常的数据库管理与运维工作中，面对海量数据的删除操作，如何做到高效、安全且不影响业务正常运转，是我们常常需要攻克的难题。今天，就结合实际遇到的几种典型场景，深入剖析在 MySQL 中应对不同量级、不同结构表数据删除的有效策略。

一、删除大表的部分数据

假设有一张拥有 1 亿 6000 万条数据且带有自增 ID（最大值即为 1 亿 6000 万）的表，现在需要删除大于 250 万以后的数据，常规的 DELETE 操作在这里可能就会陷入困境。参考 MySQL 官方文档（http://dev.mysql.com/doc/refman/5.0/en/delete.html）给出的智慧方案，我们可以另辟蹊径：

数据筛选与拷贝：创建一张与原始表结构完全一致的空表（假设为 t_copy），借助 INSERT INTO t_copy SELECT * FROM t WHERE... 语句，精准筛选出不需要删除的数据（也就是小于等于 250 万的数据），并存储到 t_copy 表中。这一步骤就像是精心挑选 “幸存者”，将它们安置到安全地带。
表名乾坤大挪移：利用 RENAME TABLE 语句施展神奇的 “魔法”，原子性地将原始表 t 重命名为 t_old，同时把精心准备好的 t_copy 表赋予 t 的 “名号”，即 RENAME TABLE t TO t_old, t_copy TO t。此刻，新表已然 “继承” 了原始表的身份，无缝对接后续业务。
清理旧表余迹：最后一步，通过 DROP TABLE t_old 将被 “打入冷宫” 的旧表彻底删除，干净利落地完成这场数据 “瘦身” 行动。这般操作，巧妙避开了删除大量行数据时可能超出 InnoDB 表锁表大小限制的问题，还大幅缩减了锁表时长，保障数据库整体性能稳定。

二、删除带索引的表

在 MySQL 使用场景里，不乏存储量庞大（日增三百万条记录左右）且配备多个必要索引的表。按照业务规则，需每日凌晨在其他程序处理完数据后，仅留存当天数据，其余过往数据都要清理。起初使用 DELETE 语句按部就班删除上百万条记录时，那蜗牛般的速度令人咋舌，每一万条记录竟耗时约 4 分钟，全部删除完得耗费八个多小时，显然无法满足业务时效性需求。

深入探究 MySQL 官方手册后发现，数据删除速度与索引数量呈正相关。于是大胆尝试，先移除其中两个索引，奇迹发生了！删除速度飙升，一百万条记录仅需一分多钟即可搞定。但这两个索引在后续每日的数据整理环节不可或缺，如何平衡二者呢？折中妙计应运而生：

索引 “暂别”：在数据删除大幕拉开前，果断删除那两个 “碍事” 但又必需的索引，此过程约花费三分钟多一点。这好比给负重奔跑的运动员卸下不必要的装备，轻装上阵迎接挑战。
数据 “清扫”：紧接着，快速执行数据删除操作，得益于索引精简，此时无用数据清理在两分钟内就能高效完成。
索引 “重生”：待数据精简至三四十万条（因每小时数据仍在按约十万条递增），数据库负担大幅减轻，此刻重新创建那两个索引，仅需约十分钟。整个流程下来，从原本的八个多小时锐减至约 15 分钟，极大提升了运维效率，确保凌晨时段数据清理工作及时、流畅完成，不影响新一天业务开展。

三、表的删除复习

在删除数据的时候根据不同的场景使用不同的方法，比如说删除表中部分数据、删除表的结构、删除所有记录并重置自增ID、批量删除大量数据等，可以使用delete、truncate、drop等语句

批量删除

1) 批量删除少量数据

delete from 表名 where key in(值1,...,值n);

1、先确定需要删除数据的主键或者条件；

2、利用主键删除记录。

2) 分批删除小批量数据

如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

1、delete from 表名 where 条件 limit 10000;

2、在一个连接中循环执行 20 次 delete from 表名 limit 500;

3、在 20 个连接中同时执行 delete from 表名 limit 500。

三种方法的比较

1、一次占用锁的时间比较长，可能导致其他客户端一致等待资源。

2、分多次占用锁，串行化执行，不占有锁的间隙，其他客户端可以工作。

3、自己制造锁竞争，加剧并发。可能锁住同一记录导致死锁的可能性增大。

注意：

执行大批量删除的时候最好使用limit，否则很有可能造成死锁。

如果delete的where语句不在索引上，可以先找主键，然后根据主键删除数据。

四、总结

如果需要删除的数据远远大于不用删除的数据

1、先选择不需要删除的数据，并把它们存在一张相同结构的空表里；

2、再重命名原始表，并给新表命名为原始表的原始表名；

3、然后删掉原始表。

如果需要删除超大批量数据

1、先删除表中索引；

2、再删除需要删除的数据；

3、然后重新创建索引。

总之，在 MySQL 数据删除战场，依据不同场景灵活运用上述策略，就像手握精密武器应对复杂战局，既能达成数据清理目标，又能保障数据库性能稳定、业务持续流畅运转，为数据管理工作筑牢坚实根基。后续大家在实践中遇到相关问题，不妨依此思路对症下药，期待共同攻克更多数据库运维挑战！

以上内容参考自：
MySQL 删除数据批量删除(大量)数据_mysql 批量删除-CSDN博客
 MySQL删除大批量数据_mysql8 大表 rename-CSDN博客