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

2025-03-30 15:50

本文主要是介绍Mysql删除几亿条数据表中的部分数据的方法实现,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定...

1、需求

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

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

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

我随即用

delete from table_name where id < 39000000 limit 10000

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

2、方案

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

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

1. 使用 DELETE 语句分批删除

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

示例 SQL 语句

-- 定义每批删除的数量
SET @BATch_size = 100000; -- 每次删除 10 万条记录
SET android@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 语句

-- 确定半年前的日期
SET @six_months_ago = DATE_SUB(CURDATE(), INTERVAL 6 MONTH);

-- 创建一个临时表,只包含需要保留的数据
CREATE TABLE temp_table AChina编程S 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 来清空整个表,然后只插入最近六个月的数据。这种方法适用于那些可以承受完全清空表的场景。

-- 确定半年前的日期
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;

注意事项

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

示例 SQL 脚本

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

-- 设置变量
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开始的。

CREATE TABLE new_table_name LIKE existing_table_name;

接下来二个方案

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

RENAME TABLE temp TO users;

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

3.1转移

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

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

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

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

到此这篇关于Mysql删除几亿条数据表中的部分数据的方法实现的文章就介绍到这了,更多相关Mysql删除亿条数据表部分数据内容请搜索编程China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持China编程(www.chinasem.cn)!

这篇关于Mysql删除几亿条数据表中的部分数据的方法实现的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1154014

相关文章

使用Python实现一个优雅的异步定时器

《使用Python实现一个优雅的异步定时器》在Python中实现定时器功能是一个常见需求,尤其是在需要周期性执行任务的场景下,本文给大家介绍了基于asyncio和threading模块,可扩展的异步定... 目录需求背景代码1. 单例事件循环的实现2. 事件循环的运行与关闭3. 定时器核心逻辑4. 启动与停

基于Python实现读取嵌套压缩包下文件的方法

《基于Python实现读取嵌套压缩包下文件的方法》工作中遇到的问题,需要用Python实现嵌套压缩包下文件读取,本文给大家介绍了详细的解决方法,并有相关的代码示例供大家参考,需要的朋友可以参考下... 目录思路完整代码代码优化思路打开外层zip压缩包并遍历文件:使用with zipfile.ZipFil

Python处理函数调用超时的四种方法

《Python处理函数调用超时的四种方法》在实际开发过程中,我们可能会遇到一些场景,需要对函数的执行时间进行限制,例如,当一个函数执行时间过长时,可能会导致程序卡顿、资源占用过高,因此,在某些情况下,... 目录前言func-timeout1. 安装 func-timeout2. 基本用法自定义进程subp

Python实现word文档内容智能提取以及合成

《Python实现word文档内容智能提取以及合成》这篇文章主要为大家详细介绍了如何使用Python实现从10个左右的docx文档中抽取内容,再调整语言风格后生成新的文档,感兴趣的小伙伴可以了解一下... 目录核心思路技术路径实现步骤阶段一:准备工作阶段二:内容提取 (python 脚本)阶段三:语言风格调

C#实现将Excel表格转换为图片(JPG/ PNG)

《C#实现将Excel表格转换为图片(JPG/PNG)》Excel表格可能会因为不同设备或字体缺失等问题,导致格式错乱或数据显示异常,转换为图片后,能确保数据的排版等保持一致,下面我们看看如何使用C... 目录通过C# 转换Excel工作表到图片通过C# 转换指定单元格区域到图片知识扩展C# 将 Excel

基于Java实现回调监听工具类

《基于Java实现回调监听工具类》这篇文章主要为大家详细介绍了如何基于Java实现一个回调监听工具类,文中的示例代码讲解详细,感兴趣的小伙伴可以跟随小编一起学习一下... 目录监听接口类 Listenable实际用法打印结果首先,会用到 函数式接口 Consumer, 通过这个可以解耦回调方法,下面先写一个

使用Java将DOCX文档解析为Markdown文档的代码实现

《使用Java将DOCX文档解析为Markdown文档的代码实现》在现代文档处理中,Markdown(MD)因其简洁的语法和良好的可读性,逐渐成为开发者、技术写作者和内容创作者的首选格式,然而,许多文... 目录引言1. 工具和库介绍2. 安装依赖库3. 使用Apache POI解析DOCX文档4. 将解析

Qt中QGroupBox控件的实现

《Qt中QGroupBox控件的实现》QGroupBox是Qt框架中一个非常有用的控件,它主要用于组织和管理一组相关的控件,本文主要介绍了Qt中QGroupBox控件的实现,具有一定的参考价值,感兴趣... 目录引言一、基本属性二、常用方法2.1 构造函数 2.2 设置标题2.3 设置复选框模式2.4 是否

一文详解如何在Python中从字符串中提取部分内容

《一文详解如何在Python中从字符串中提取部分内容》:本文主要介绍如何在Python中从字符串中提取部分内容的相关资料,包括使用正则表达式、Pyparsing库、AST(抽象语法树)、字符串操作... 目录前言解决方案方法一:使用正则表达式方法二:使用 Pyparsing方法三:使用 AST方法四:使用字

C++使用printf语句实现进制转换的示例代码

《C++使用printf语句实现进制转换的示例代码》在C语言中,printf函数可以直接实现部分进制转换功能,通过格式说明符(formatspecifier)快速输出不同进制的数值,下面给大家分享C+... 目录一、printf 原生支持的进制转换1. 十进制、八进制、十六进制转换2. 显示进制前缀3. 指