mysql如何批量删除海量数据

最新推荐文章于 2025-11-03 14:31:10 发布

原创最新推荐文章于 2025-11-03 14:31:10 发布 · 781 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #大数据 #面试题

个人学习专栏收录该内容

15 篇文章

订阅专栏

一、快速删除大表的部分数据

在处理一个包含1亿6000万条记录的大表时，若需要删除ID大于250万之后的数据，直接执行DELETE语句可能会导致长时间的锁表，严重影响数据库性能。MySQL官方文档提供了一种高效的解决方案，具体步骤如下：

创建备份表并插入不需要删除的数据：
首先，创建一个与原始表结构相同的空表（例如命名为t_copy）。然后，使用INSERT INTO ... SELECT语句，将原始表t中ID小于或等于250万的数据插入到备份表t_copy中。

重命名表：
使用RENAME TABLE语句，将原始表t重命名为t_old，并将备份表t_copy重命名为原始表的名称t。这一步是原子操作，确保了数据的一致性。

删除原始表：
最后，删除已经不再需要的原始表t_old。

这种方法避免了长时间锁表，并且通过原子操作保证了数据的安全性。

二、删除带索引的大表数据

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

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

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