Mysql删除几亿条数据表中的部分数据的方法实现!

Mysql删除几亿条数据表中的部分数据的方法实现!

作者:树欲静而风不止
在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定的参考价值,感兴趣的可以了解一下。

1、需求

老项目,实时数据还是用mysql去存储的,而且又没有定时去清理数据的程序。目前这些实时数据只需保留近半年的即可。

2025033010200347

其他几张实时表数据量没有这么夸张。

我随即用

1
delete from table_name where id < 39000000 limit 10000

但是几亿条记录用这个方法太慢了。

2、方案

虽然之前也处理过类似问题,但还是随手问了一下AI,有无更优解。

在MySQL中删除一个大表(例如含有几亿条记录)中的数据时,需要特别注意操作的性能和对系统的影响。如果您的目标是删除半年前的数据,您可以采用以下几种策略来高效地完成这项任务:

1. 使用 DELETE 语句分批删除

一种常见的方法是使用 DELETE 语句,并通过分批删除数据来减轻数据库的压力。这可以通过在 DELETE 语句中添加 LIMIT 子句来实现。

示例 SQL 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 定义每批删除的数量
SET @batch_size = 100000; -- 每次删除 10 万条记录
SET @counter = 0;
-- 确定半年前的日期
SET @six_months_ago = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
-- 分批删除数据
REPEAT
    DELETE FROM your_table
    WHERE time < @six_months_ago
    LIMIT @batch_size;
    SET @counter = @counter + 1;
UNTIL @counter = 0 END REPEAT;

2. 使用 INPLACE ALTER TABLE

对于InnoDB表,可以使用 ALTER TABLE 来删除数据,这通常比 DELETE 更快。然而,此方法要求您能够确定删除的行不会被引用(例如通过外键)。此外,这种方法可能不是所有版本的MySQL都支持。

示例 SQL 语句

1
2
3
4
5
6
7
8
9
10
11
-- 确定半年前的日期
SET @six_months_ago = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
-- 创建一个临时表,只包含需要保留的数据
CREATE TABLE temp_table AS SELECT * FROM your_table WHERE time >= @six_months_ago;
-- 删除原始表
DROP TABLE your_table;
-- 重命名临时表为原始表的名字
RENAME TABLE temp_table TO your_table;

3. 使用 TRUNCATE TABLE 和重新插入数据

如果您可以接受删除所有数据并重新插入数据,那么可以使用 TRUNCATE TABLE 来清空整个表,然后只插入最近六个月的数据。这种方法适用于那些可以承受完全清空表的场景。

1
2
3
4
5
6
7
8
9
10
-- 确定半年前的日期
SET @six_months_ago = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
-- 清空表
TRUNCATE TABLE your_table;
-- 重新插入最近六个月的数据
INSERT INTO your_table
SELECT * FROM your_table
WHERE time >= @six_months_ago;

注意事项

  • 备份数据:在执行任何大规模删除操作之前,一定要做好数据备份以防万一。
  • 监控性能:在执行删除操作时,应密切监控MySQL服务器的性能指标,如CPU使用率、磁盘I/O等。
  • 禁用外键检查:如果表上有外键约束,可以暂时禁用外键检查。
  • 禁用自动提交:在开始删除操作之前,可以关闭自动提交,然后在删除完成后手动提交事务。

示例 SQL 脚本

以下是一个完整的SQL脚本示例,使用分批删除的方式删除半年前的数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 设置变量
SET @batch_size = 100000; -- 每次删除 10 万条记录
SET @counter = 0;
-- 确定半年前的日期
SET @six_months_ago = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
-- 分批删除数据
REPEAT
    DELETE FROM your_table
    WHERE time < @six_months_ago
    LIMIT @batch_size;
    SET @counter = @counter + 1;
UNTIL @counter = 0 END REPEAT;

总结

选择哪种方法取决于您的具体需求和资源。如果数据量非常大,建议先在一个较小的数据集上测试所选方法的性能,然后再应用到整个表。此外,在执行此类操作时,最好在业务低峰期进行,以减少对应用程序的影响。

看来也没有更好的办法,还是这些老办法。

3、实战

先新建一张copy的表。

注意:新建表的自增索引ID,还是会从1开始的。

1
CREATE TABLE new_table_name LIKE existing_table_name;

接下来二个方案

1、实时数据不能停的,先改名,把名称互换。等数据插入一波,再启动迁移。主要是为了插入一波后自增索引自动调整。避免冲突

1
RENAME TABLE temp TO users;

2、实时数据能停的,那么就先停掉。再名称互换,再改动自增索引起点值。这时候其实可以开启插入程序了。

3.1转移

一张表近半年的数据是从390000000差不多开始。那么就好处理了

2025033010200348

几千万条数据也是比较快的。

另一张表更大,有近1-2个亿的数据,那么我是2kw-3kw 批量一次,也就半小时。即可完成。

到此这篇关于Mysql删除几亿条数据表中的部分数据的方法实现的文章就介绍到这了。

 

学习资料见知识星球。

以上就是今天要分享的技巧,你学会了吗?若有什么问题,欢迎在下方留言。

快来试试吧,小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​!

更多技巧, www.excelbook.cn

欢迎 加入 零售创新 知识星球,知识星球主要以数据分析、报告分享、数据工具讨论为主;

Excelbook.cn Excel技巧 SQL技巧 Python 学习!

你将获得:

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群,共享资源。

5、优惠的会员商品。

6、一次付费只需129元,即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称:Excelbook
2、本站永久网址:http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场,并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报。
6、本站资源大多存储在云盘,如发现链接失效,请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>