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

news/2024/11/8 3:37:45/

目录

一、问题分析

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

方法 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.ppmy.cn/news/1545244.html

相关文章

用图说明 CPU、MCU、MPU、SoC 的区别

CPU CPU 负责执行构成计算机程序的指令&#xff0c;执行这些指令所指定的算术、逻辑、控制和输入/输出&#xff08;I/O&#xff09;操作。 MCU (microcontroller unit) 不同的 MCU 架构如下&#xff0c;注意这里的 MPU 表示 memory protection unit MPU (microprocessor un…

鸿蒙进阶篇-网格布局 Grid/GridItem(二)

hello大家好&#xff0c;这里是鸿蒙开天组&#xff0c;今天让我们来继续学习鸿蒙进阶篇-网格布局 Grid/GridItem&#xff0c;上一篇博文我们已经学习了固定行列、合并行列和设置滚动&#xff0c;这一篇我们将继续学习Grid的用法&#xff0c;实现翻页滚动、自定义滚动条样式&…

配置文件中spring.main.allow-bean-definition-overriding属性

spring.main.allow-bean-definition-overriding属性是SpringBoot应用中的一个关键配置&#xff0c;它决定了是否允许在Spring容器中存在名称相同的Bean定义覆盖。以下是对该属性的详细介绍&#xff1a; 一、属性作用 • 控制Bean定义覆盖&#xff1a;当多个配置类或组件定义了…

OpenResty 1.27.1.1 已经正式发布

OpenResty 1.27.1.1 已经正式发布&#xff0c;这是一个基于 NGINX 和 LuaJIT 的 web 平台。以下是关于此次发布的一些重点信息和更新内容&#xff1a; 下载与安装 你可以在此处下载最新版本的 OpenResty。提供了便携式源代码分发、Win32/Win64 二进制分发以及为 Ubuntu、Debi…

掌握 PyQt5:从零开始的桌面应用开发

PyQT5——图形化界面 文章目录 PyQT5——图形化界面集成化图形界面工具为什么使用 \$ProjectFileDir$?示例场景其他 Varaiablespyuic参数解释整体含义示例使用PyQt5和pyuic 创建pyqt5的程序创建一个窗口app.exec\_()和sys.exit(app.exec_())的区别1. app.exec_()2. sys.exit(a…

论文 | Legal Prompt Engineering for Multilingual Legal Judgement Prediction

这篇文章探讨了如何利用“法律提示工程”&#xff08;LPE&#xff09;来指导大型语言模型&#xff08;LLM&#xff09;进行多语言法律判决预测&#xff08;LJP&#xff09;。主要内容&#xff1a; LPE 的概念&#xff1a; LPE 是指通过设计特定的提示&#xff08;promp…

WebSocket实现消息实时推送

文章目录 websocket介绍特点工作原理 用websocket实现实时推送引入依赖WebSocket 函数定义变量声明初始化 WebSocket 连接WebSocket 连接的初始化和事件处理连接打开事件接收消息处理连接关闭和重连机制心跳机制使用 WebSocket代码完整显示 websocket介绍 WebSocket 是一种网络…

Android 面试Socket与WebSocket

Android 面试Socket与WebSocket 文章目录 Android 面试Socket与WebSocketSocket什么是SocketSocket通讯的过程TCP协议Socket代码示例UDP协议Socket代码示例Socket实现Android跨进程通信 WebSocket什么是websocket Socket 什么是Socket 网络上的两个程序通过以一个双向通讯的链…