MySQL 批量删除海量数据的几种方法

目录

一、问题分析

二、批量删除海量数据的几种方法

方法 1:使用 LIMIT 分批删除

方法 2:通过主键范围分批删除

方法 3:通过自定义批量删除存储过程

方法 4:创建临时表替换旧表

三、性能优化建议

总结


        在数据库的日常维护中,我们经常遇到需要删除大量数据的场景。例如,删除过期日志、清理历史数据等。但如果一次性删除大量数据,可能会导致锁表、事务日志暴增、影响数据库性能等问题。本文将介绍几种高效批量删除 MySQL 海量数据的方法。

一、问题分析

一次性删除大量数据的主要问题在于:

  1. 长时间锁表:大量删除操作会导致数据库长时间加锁,影响其他事务的正常操作。
  2. 事务日志暴增:MySQL 在删除数据时会记录事务日志,大量删除操作可能导致日志文件过大,甚至撑满磁盘。
  3. 影响性能:一次性删除大量数据会占用大量的 CPU 和 IO 资源,对数据库整体性能产生严重影响。

为避免这些问题,可以考虑分批删除等策略来减少对数据库的压力。

二、批量删除海量数据的几种方法

方法 1:使用 LIMIT 分批删除

LIMIT 分批删除是一种常用的处理海量数据的方式。每次删除固定数量的数据,循环执行,直至删除完毕。

示例 SQL:

假设我们要删除 logs 表中创建时间在某个日期之前的所有数据:

-- 设置每批删除的行数
SET @BATCH_SIZE = 1000;-- 分批删除符合条件的数据
DELETE FROM logs 
WHERE create_time < '2023-01-01' 
LIMIT @BATCH_SIZE;

可以将上述语句放入存储过程或在应用层循环调用。每次删除 BATCH_SIZE 行数据,减少锁表时间和日志生成量。

优点:
  • 控制单次删除的量,减少锁表时间和日志生成量。
缺点:
  • 需要循环多次操作,逻辑稍复杂。
注意:
  • 分批删除的 LIMIT 值可以根据实际环境调整。通常 5005000 是较合理的选择。

方法 2:通过主键范围分批删除

如果要删除的数据在主键上是连续的(如自增 ID),可以按主键范围分批删除。这样能够避免 LIMIT 的偏移开销,提高删除效率。

示例 SQL:

假设 logs 表的主键是 id

-- 设置每批删除的范围
SET @start_id = 0;
SET @end_id = 1000;WHILE (@start_id < (SELECT MAX(id) FROM logs WHERE create_time < '2023-01-01')) DODELETE FROM logsWHERE id BETWEEN @start_id AND @end_idAND create_time < '2023-01-01';-- 更新删除范围SET @start_id = @end_id + 1;SET @end_id = @end_id + 1000;
END WHILE;
优点:
  • 主键范围分批避免了 LIMIT 偏移带来的开销。
缺点:
  • 需要知道主键范围,且适用于有连续主键的数据表。

方法 3:通过自定义批量删除存储过程

可以将批量删除逻辑封装成存储过程,利用存储过程自动控制批量删除过程。

示例 SQL:
DELIMITER $$CREATE PROCEDURE batch_delete_logs()
BEGINDECLARE done INT DEFAULT FALSE;DECLARE batch_size INT DEFAULT 1000;WHILE NOT done DODELETE FROM logs WHERE create_time < '2023-01-01' LIMIT batch_size;-- 检查是否还有剩余数据IF ROW_COUNT() < batch_size THENSET done = TRUE;END IF;END WHILE;
END $$DELIMITER ;

执行存储过程:

CALL batch_delete_logs();
优点:
  • 存储过程实现自动化,逻辑清晰,避免多次手动执行 SQL。
缺点:
  • 适用于支持存储过程的场景,对小批量删除非常适合。

方法 4:创建临时表替换旧表

在某些情况下,删除大表中的大量数据可以通过创建新表的方法完成。即先将需要保留的数据转移到新表,再删除旧表。这种方法可以减少锁表时间和日志开销。

步骤:
  1. 创建一个新表(结构与旧表相同)。
  2. 将需要保留的数据插入新表。
  3. 删除旧表,重命名新表为原表名。
示例 SQL:
-- 创建新表
CREATE TABLE logs_new LIKE logs;-- 插入需要保留的数据
INSERT INTO logs_new
SELECT * FROM logs WHERE create_time >= '2023-01-01';-- 删除旧表并重命名新表
DROP TABLE logs;
RENAME TABLE logs_new TO logs;
优点:
  • 避免了大规模的删除操作,减少了锁表时间和日志。
缺点:
  • 需要额外的磁盘空间来存放新表数据。
  • 在业务量大的情况下,可能需要进行额外的锁机制控制。

三、性能优化建议

  1. 避免在业务高峰期进行大规模删除,可以选择在夜间等业务低峰期执行。
  2. 适当设置批量大小。批量删除时,LIMIT 的大小需要根据实际情况调整,不宜过大,防止长时间锁表。
  3. 关闭不必要的日志。在某些极端情况下,可以关闭 MySQL 的二进制日志(binlog)来减少日志开销,但此操作有风险,应在充分了解后谨慎使用。

总结

方法适用场景优点缺点
LIMIT 分批删除需要简单分批删除逻辑简单,减少锁表时间需循环操作
主键范围分批删除有连续主键的表高效,无偏移开销需手动指定范围
自定义批量删除存储过程小批量删除自动化操作需要数据库支持存储过程
临时表替换删除数据量非常大避免锁表,减少日志开销需要额外磁盘空间

根据不同的业务场景和需求,选择合适的批量删除方式可以提高 MySQL 的删除效率,减少对数据库的影响。希望本文对大家在 MySQL 的数据清理和维护上有所帮助!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/468951.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

支持 Win10 的网络环境模拟(丢包,延迟,带宽)

升级 Windows 10 以后&#xff0c;原来各种网络模拟软件都挂掉了&#xff0c;目前能用的就是只有 clumsy&#xff1a; 唯一问题是不支持模拟带宽&#xff0c;那么平时要模拟一些糟糕的网络情况的话&#xff0c;是不太方便的&#xff0c;而开虚拟机用 Linux tc 或者设置个远程 l…

【Homework】【5】Learning resources for DQ Robotics in MATLAB

Lesson 5 代码-TwoDofPlanarRobot.m 表示一个 2 自由度平面机器人。该类包含构造函数、计算正向运动学模型的函数、计算平移雅可比矩阵的函数&#xff0c;以及在二维空间中绘制机器人的函数。 classdef TwoDofPlanarRobot%TwoDofPlanarRobot - 表示一个 2 自由度平面机器人类…

在模方置平建筑失败的原因是什么?

在模方置平建筑失败的原因是什么&#xff1f; 可能是obj拓扑不连续&#xff0c;可以在网格大师使用osgb转obj功能&#xff0c;选择拓扑或者重建。 网格大师是一款能够解决实景三维模型空间参考、原点、瓦块大小不统一&#xff0c;重叠区域处理问题的工具“百宝箱”&#xff0c…

【大咖云集 | IEEE计算智能学会广州分会支持】第四届信息技术与当代体育国际学术会议(TCS 2024,12月13-15日)

第四届信息技术与当代体育国际学术会议&#xff08;TCS 2024&#xff09; 2024 4th International Conference on Information Technology and Contemporary Sports 重要信息 会议官网&#xff1a;www.icitcs.net&#xff08;会议关键词&#xff1a;TCS 2024&#xff09; 202…

常用机器人算法原理介绍

一、引言 随着科技的不断发展&#xff0c;机器人技术在各个领域得到了广泛应用。机器人算法是机器人实现各种功能的核心&#xff0c;它决定了机器人的行为和性能。本文将介绍几种常用的机器人算法原理&#xff0c;包括路径规划算法、定位算法和运动控制算法。 二、路径规划算法…

Cynet:全方位一体化安全防护工具

前言 1999年&#xff0c;布鲁斯施奈尔曾说过&#xff1a;“复杂性是安全最大的敌人。”彼时还是19年前&#xff0c;而现在&#xff0c;网络安全已然变得更加繁杂。 近日我在网上冲浪过程中发现了这么一个平台性质的软件&#xff0c;看似具有相当强的防护能力。 根据Cynet的描…

dolphin 配置data 从文件导入hive 实践(一)

datax 支持多种数据源的相互读写&#xff0c;作为开源软件&#xff0c;提供了离线采集功能&#xff0c;方便系统开发&#xff0c;过程中遇到诸多配置&#xff0c;需要开发者自己探索&#xff0c;免费同样有成本 配置模板 {"setting": {},"job": {"s…

Redis如何保证数据不丢失(可靠性)

本文主要以学习为主&#xff0c;详细参考&#xff1a;微信公众平台 Redis 保证数据不丢失的主要手段有两个&#xff1a; 持久化 多机部署 我们分别来看它们两的具体实现细节。 1.Redis 持久化 持久化是指将数据从内存中存储到持久化存储介质中&#xff08;如硬盘&#xf…

Linux数据管理初探

Linux数据管理初探 导语内存管理内存分配内存错用和处理 文件锁定锁文件/区域锁读写和竞争锁命令和死锁 dbm数据库例程dbm访问函数其他dbm函数 总结参考文献 导语 Linux为应用程序提供简洁的视图用来反映可直接寻址的内存空间&#xff08;但实际上可能是内存外存&#xff09;&…

Python中4个高效小技巧

分享 4 个省时的 Python 技巧&#xff0c;可以节省 10~20% 的 Python 执行时间。 包含编程资料、学习路线图、源代码、软件安装包等&#xff01;【[点击这里]】&#xff01; 反转列表 Python 中通常有两种反转列表的方法&#xff1a;切片或 reverse() 函数调用。这两种方法都…

【黑马Redis原理篇】Redis数据结构

视频来源&#xff1a;原理篇[2,15] 文章目录 1.动态字符串SDS1.1 内部结构&#xff1a; 2.IntSet3.Dict3.1 dict的内部结构3.2 dict的扩容 4.ziplist压缩列表5.QuickList6.SkipList跳表7.RedisObject对象8.Redis的五种数据结构8.1 String8.2 List8.3 Set8.4 Zset 有序集合8.5 …

WPF之iconfont(字体图标)使用

1&#xff0c;前文&#xff1a; WPF的Xaml是与前端的Html有着高度相似性的标记语言&#xff0c;所以Xaml也可同Html一般轻松使用阿里提供的海量字体图标&#xff0c;从而有效的减少开发工作度。 2&#xff0c;下载字体图标&#xff1a; 登录阿里图标库网iconfont-阿里巴巴矢量…

内网部署web项目,外网访问不了?只有局域网能访问!怎样解决?

相关技术 要实现“内网部署&#xff0c;外网访问”&#xff0c;可以使用内网穿透、VPN技术、DMZ主机、端口映射等方法。以下是对这些方法的详细解释&#xff1a; 一、内网穿透 内网穿透是一种技术&#xff0c;它通过将内网设备映射到公网上的方式&#xff0c;实现外网访问内…

Android MVVM demo(使用DataBinding,LiveData,Fresco,RecyclerView,Room,ViewModel 完成)

使用DataBinding&#xff0c;LiveData&#xff0c;Fresco&#xff0c;RecyclerView&#xff0c;Room&#xff0c;ViewModel 完成 玩Android 开放API-玩Android - wanandroid.com 接口使用的是下面的两个&#xff1a; https://www.wanandroid.com/banner/jsonhttps://www.wan…

c++11(一)

c11&#xff08;一&#xff09; 1. C11的发展历史2. 列表初始化2.1 C98传统的{}2.2 C11中的{}2.3 C11中的std::initializer_list 3. 右值引⽤和移动语义3.1 左值和右值3.2 左值引⽤和右值引⽤3.3 引⽤延⻓⽣命周期3.4 左值和右值的参数匹配3.5 右值引⽤和移动语义的使⽤场景3.5…

‍️代码的华尔兹:在 Makefile 的指尖上舞动自动化的诗篇

文章目录 &#x1f636;‍&#x1f32b;️&#x1f636;‍&#x1f32b;️&#x1f636;‍&#x1f32b;️背景——一个优秀工程师必备技能&#x1f636;‍&#x1f32b;️&#x1f636;‍&#x1f32b;️&#x1f636;‍&#x1f32b;️一、&#x1f929;&#x1f929;快速了解…

SpringBoot中使用Thymeleaf模板引擎

和使用freemarker差不多的方式 1、导入thymeleaf的启动器 <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> 2、编写Controller类 3、编写模板页面 注…

vue之子组件向父组件传值

参考博客先挂上 vue3中子传父&#xff08;emit&#xff09;、父传子&#xff08;props&#xff09;一篇文章拿下第一次写文章&#xff0c;告诉你vue3中如何实现父子相传&#xff0c;一篇文章帮 - 掘金 父组件通过 props 向子组件传值 1.子组件使用 $emit 触发事件 2.在父组件…

第26天 安全开发-PHP应用模板引用Smarty渲染MVC模型数据联动RCE安全

时间轴&#xff1a; 演示案例 新闻列表&模板引用-代码RCE安全 知识点 1、PHP 新闻显示-数据库操作读取显示 2、PHP 模版引用-自写模版&Smarty 渲染 3、PHP 模版安全-RCE 代码执行&三方漏洞 新闻列表 1.数据库创建新闻存储 2.代码连接数据库读取 3.页面进行自定…

【微服务】Docker 容器化

一、初识Docker 1. 为什么需要 Docker 大型项目组件较多&#xff0c;运行环境也较为复杂&#xff0c;部署时会遇到一些问题&#xff1a; 依赖关系复杂&#xff0c;容易出现兼容性的问题开发、测试、生产环境有差异 Docker 如何解决依赖的兼容问题 将应用的Libs&#xff08;…