首页
学习
活动
专区
工具
TVP
发布
精选内容/技术社群/优惠产品,尽在小程序
立即前往

mysql 清理大量数据的存储过程

基础概念

MySQL 是一个关系型数据库管理系统,广泛用于数据存储和管理。清理大量数据通常指的是删除表中的旧数据或无效数据,以释放存储空间并提高查询性能。

相关优势

  1. 提高性能:删除不必要的数据可以减少磁盘I/O操作,加快查询速度。
  2. 节省空间:释放存储空间,避免数据库文件过大。
  3. 数据维护:保持数据的整洁和有效性,便于后续的数据分析和处理。

类型

  1. 物理删除:直接从磁盘上删除数据文件。
  2. 逻辑删除:在表中添加一个标记字段,如 is_deleted,将需要删除的数据标记为已删除,而不是实际删除数据。

应用场景

  • 日志清理:删除过期的日志数据。
  • 用户数据清理:删除不再需要的用户数据。
  • 缓存清理:清理过期的缓存数据。

存在的问题及解决方法

问题:删除大量数据时速度慢

原因

  • 删除操作涉及磁盘I/O操作,速度较慢。
  • 删除大量数据会导致事务日志增加,影响性能。

解决方法

  1. 分批删除:将大量数据分成多个小批次进行删除。
  2. 使用 TRUNCATE TABLE:对于全表删除操作,使用 TRUNCATE TABLEDELETE 更快,因为它不记录单行删除操作。
  3. 优化索引:确保表上有适当的索引,以加快删除操作。

问题:删除操作影响在线业务

原因

  • 删除操作可能会锁定表,影响在线业务。

解决方法

  1. 低峰期操作:在业务低峰期进行数据删除操作。
  2. 使用 ON DELETE CASCADE:在删除主表记录时,自动删除相关联的子表记录。
  3. 异步删除:将删除操作放入后台任务中执行,避免影响在线业务。

示例代码

以下是一个分批删除数据的存储过程示例:

代码语言:txt
复制
DELIMITER //

CREATE PROCEDURE BatchDeleteData(IN tableName VARCHAR(255), IN batchSize INT)
BEGIN
    DECLARE totalRows INT;
    DECLARE deletedRows INT;
    DECLARE continueHandler CONDITION FOR SQLSTATE '02000';

    SET @sql = CONCAT('SELECT COUNT(*) INTO @totalRows FROM ', tableName);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    WHILE @totalRows > 0 DO
        SET @sql = CONCAT('DELETE FROM ', tableName, ' LIMIT ', batchSize);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        SET deletedRows = ROW_COUNT();
        SET @totalRows = @totalRows - deletedRows;

        SELECT CONCAT('Deleted ', deletedRows, ' rows. Remaining rows: ', @totalRows);
    END WHILE;
END //

DELIMITER ;

参考链接

通过上述方法和示例代码,可以有效地清理MySQL中的大量数据,并解决相关问题。

页面内容是否对你有帮助?
有帮助
没帮助

相关·内容

MySQL存储过程_MySQL创建存储过程

什么是存储过程 存储过程就是事先经过编译并存储数据库中一段 SQL 语句集合; 为什么使用存储过程 调用存储过程可以简化应用开发人员很多工作,减少数据数据库和应用服务器之间传输,对于提高数据处理效率是有好处...存储过程特点: 封装,复用, 可以把某一业务SQL封装在存储过程中,需要用到时候直接调用即可; 可以接收参数,也可以返回数据, 在存储过程中,可以传递参数,也可以接收返回值; 减少网络交互,提升效率,...delimiter 指定SQL语句结束符 存储过程变量 在MySQL中,变量一般可分为分为三种类型: 系统变量、用户定义变量、局部变量; 一、系统变量 系统变量是MySQL服务器系统自身提供...循环语句在编程中经常被用到,常用于对批量数据进行循环处理,在mysql存储过程中,也提供了几种常用循环语句,包括:while循环,repeat循环,和loop循环; 1、while循环语句 while...,使用存储过程也可以完成,但是存储函数局限在于,函数必须要有返回结果; 八、存储过程中游标的使用 游标(CURSOR)是用来存储查询结果集数据类型 , 在存储过程和函数中可以使用游标对结果集进行循环处理

22.2K21

Mysql存储过程

存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用一种数据库对象。...2.2、Mysql存储过程缺点   存储过程,往往定制化于特定数据库上,因为支持编程语言不同。当切换到其他厂商数据库系统时,需要重写原有的存储过程。   ...存储过程性能调校与撰写,受限于各种数据库系统。 3、存储过程创建和调用   存储过程就是具有名字一段代码,用来完成一个特定功能。   创建存储过程保存在数据数据字典中。...  解析:默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定数据库下,那么在过程名前面加数据库名做前缀。...  MySQL存储过程参数用在存储过程定义,共有三种参数类型,IN、OUT、INOUT,形式如: 1 CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形

6.7K10
  • Mysql数据库-存储过程

    Mysql数据库-存储过程 1. 存储过程和函数概述 1. 存储过程和函数是事先经过编译并存储数据库中一段SQL语句集合 a. 类似于java中方法: 是一段java代码集合 b....减少数据数据库和应用服务器之间传输,对于提高数据处理效率 java : 1....查看和删除存储过程 3.1 语法: -- 查询数据库中所有的存储过程 select * from mysql.proc where db='数据库名称'; -- 删除存储过程 drop procedure...[if exists] 存储过程名称; 3.2 实例: -- 查询数据库中所有的存储过程 select * from mysql.proc where db='db2'; -- 删除存储过程 drop...procedure if exists stu_group; 执行如下: -- 查询数据库 db2 中所有的存储过程 mysql> select * from mysql.proc where db

    11K10

    MySQLMySQL 存储过程

    MySQL 存储过程(了解) 1 什么是存储过程 MySQL 5.0 版本开始支持存储过程 存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用一种数据 库对象。...存储过程是为了完成特定功能SQL语句集,经编译创建并保存在数据库中,用户可通过 指定存储过程名字并给定参数(需要时)来调用执行。 简单理解: 存储过程其实就是一堆 SQL 语句合并。...中间加入了一些逻辑控制 2 存储过程优缺点 优点:   存储过程一旦调试完成后,就可以稳定运行,(前提是,业务需求要相对稳定,没有变化)   存储过程减少业务系统与数据交互,降低耦合...,数据库交互更加快捷(应用服务器,与 数据库服务器不在同一个地区) 缺点:    在互联网行业中,大量使用MySQLMySQL存储过程与Oracle相比较弱,所以较少使用,并且互联网行业需求变化较快也是原因之一... 阿里代码规范里也提出了禁止使用存储过程存储过程维护起来的确麻烦; 3 存储过程创建方式 方式1 1) 数据准备 创建商品表 与 订单表 # 商品表 CREATE TABLE goods(

    16.1K10

    MySQL 存储过程

    1.1 简介 1.1.1 概述   MySQL 5.0 版本开始支持存储过程存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用一种数据库对象。...存储过程是为了完成特定功能 SQL 语句集,经编译创建并保存在数据库中,用户可通过指定存储过程名字并给定参数(需要时)来调用执行。...存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。 ? 1.1.2 优缺点 ☞ 优点  ① 存储过程可封装,并隐藏复杂商业逻辑。  ...④ 存储过程可以用在数据检验,强制实行商业逻辑等。 ☞ 缺点  ① 存储过程,往往定制化于特定数据库上,因为支持编程语言不同。当切换到其他数据库系统时,需要重写原有的存储过程。  ...② 存储过程性能调校与撰写,受限于各种数据库系统。 1.1.3 阿里规约 ?

    13.4K31

    MySQL存储过程

    (3).较快执行速度:如果某一操作包含大量Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理执行速度快很多。因为存储过程是预编译。...MySQL存储过程 存储过程数据一个重要功能,MySQL 5.0以前并不支持存储过程,这使得MySQL在应用上大打折扣。...好在MySQL 5.0开始支持存储过程,这样即可以大大提高数据处理速度,同时也可以提高数据库编程灵活性。...MySQL存储过程创建 语法 CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...]...数据库.存储过程名; MySQL存储过程修改 ALTER PROCEDURE 更改用CREATE PROCEDURE 建立预先指定存储过程,其不会影响相关存储过程存储功能。

    13.7K30

    MySQL存储过程

    文章目录 创建/调用存储过程 存储过程体 为语句块贴标签 参数列表 存储过程优缺点 存储过程优点 存储过程缺点 存储过程和函数可以理解为一段sql集合,他们被事先编译好并且存储数据库中。...因为out是向调用者输出参数,不接收输入参数,所以存储过程p_out为null +-------+ | p_out | +-------+ | 2 | +-------+ mysql>...存储过程优缺点 存储过程优点 1.第一点优势就是执行速度快,因为我们每个sql语句执行都需要经过编译,然后在运行,但是存储过程都是直接编译好了之后直接运行即可 2.第二点优势就是减少网络流量,我们传输一个存储过程比我们传输大量...因为我们在写好一个存储过程之后,再次调用只需要一个名称即可,也就是一次编写,随处调用,而且使用存储过程也可以让程序模块化加强 存储过程缺点 1.移植性差。...因为存储过程是和数据库绑定,如果我们更换数据库之类操作,可能很多地方需要改动。 2.修改不方便。

    10.1K30

    MySQL存储过程

    一、存储过程 1-1、含义:一组预先编译好SQL语句集合,理解成批处理语句 1、提高代码重用性 2、简化操作 3、减少了编译次数并且减少了和数据库服务器连接次数,提高了效率 好处: 1、提高代码重用性...2、简化操作 1-2、语法: CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体(一组合法SQL语句) END 注意: 1、参数列表包含三部分 参数模式 参数名...,begin end可以省略 存储过程体中每条sql语句结尾要求必须加分号。...存储过程结尾可以使用 delimiter 重新设置 语法: delimiter 结束标记 案例: delimiter $ 二、存储过程创建和使用语法 2-1、语法 CALL 存储过程名(实参列表);...语法:drop procedure 存储过程名 DROP PROCEDURE p1; #错误演示,不支持批量删除 DROP PROCEDURE p2,p3 四、查看存储过程信息 语法:show

    8.8K10

    MySQL 存储过程

    存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能SQL 语句集,它存储数据库中,一次编译后永久有效,用户通过指定存储过程名字并给出参数(如果该存储过程带有参数...存储过程数据库中一个重要对象。在数据量特别庞大情况下利用存储过程能达到倍速效率提升 一、存储过程概述 1.1、什么是存储过程 存储过程数据库中一个重要对象。...存储过程是在数据库系统中,一组为了完成特定功能SQL 语句集。存储过程存储数据库中,一次编译后,到处运行。不需要再次编译,用户通过指定存储过程名字并传递参数(如果该存储过程带有参数)来执行。...1.2、存储过程特点 用来完成较复杂业务 比较灵活,易修改,好编写,可编程性强 编写好存储过程可重复使用 1.3、存储过程优缺点 优点 存储过程在创建时候直接编译,sql语句每次使用都要编译,效率高...存储过程可以被重复使用。 存储过程只连接一次数据库,sql语句在访问多张表时,连接多次数据库。 存储程序是安全存储过程应用程序授予适当权限。

    37.2K20

    MySQLMySQL存储过程(1)

    目录 什么是存储过程 有哪些特性 入门案例 MySQL操作-变量定义 存储过程传参-in 存储过程传参-out 存储过程传参-inout 存储过程传参-in,out, inout 什么是存储过程 MySQL...简单说,存储过程就是一组SQL语句集,功能强大,可以 实现一些比较复杂逻辑功能,类似于JAVA语言中方法; 存储过就是数据库 SQL 语言层面的 代码封装与重用。...2:在该数据库下导入sql脚本:procedure_data.sql 操作-创建存储过程 delimiter $$ create procedure proc01() begin select empno...会话变量在每次建立一个新连接时 候,由MYSQL来初始化。MYSQL会将当前所有全局变量值复制一份。来做为会话变量。...-in,out, inout in 输入参数,意思说你参数要传到存过过程过程里面去,在存储过程中修改该参数值不能被 返回 out 输出参数:该值可在存储过程内部被改变,并向外输出 inout 输入输出参数

    1.8K20

    mysql存储过程

    1、存储过程 1)“存储过程和函数”:类似于java中方法,python中函数。...2)“使用存储过程好处”:   ① 提高代码重用性;   ② 简化操作;   ③ 减少了编译次数,并且减少了和数据库服务器连接次数,提高了效率。   ...注意:每执行一句sql语句,就会连接mysql服务器一次。 3)“存储过程含义”:一组预先编译好sQL语句集合。...3)delimiter定义存储过程结束标记   在mysql语句中,由于每一个语句后面必须要;结尾,而存储过程也需要一个符号结尾,为了防止混淆,我们需要在创建存储过程之前,先使用delimiter定义一个存储过程结束标记...delimiter $ 4)存储过程调用 call 存储过程名(实参列表); 3、空参存储过程 注意:“存储过程整个执行过程,最好在CMD窗口中执行” -- 创建一个存储过程 delimiter

    11.7K10

    MySQL存储过程

    存储过程优点 ① 提供了代码可重用性。 ② 简化了数据库操作,讲业务逻辑细节隐藏在存储过程中。 ③ 减少了编译次数,减少了网络IO次数,从而提高操作效率。...存储过程演示 无参存储过程 代码实例: 向b_user表中插入2条数据 #创建存储过程 delimiter $ create procedure pro_insert() begin insert into...;$ #调用存储过程 call pro_insert(); 创建带有in模式参数存储过程 代码实例: 用于向b_user表中插入2条数据,性别由客户输入 #创建存储过程 delimiter $ create...创建多个带有in参数存储过程 代码实例: 用于向b_user表中插入2条数据,用户名和性别由客户输入 #创建存储过程 delimiter $ create procedure pro_insert3(...存储过程名称; 查看存储过程 语法: show create procedure 存储过程名称; 修改存储过程 目前,MySQL还不提供对已存在存储过程代码修改。

    11.8K10

    MySQL存储过程

    MySQL 判断表索引是否存在,不存在则创建存储过程书写如下。折腾了一个下午,很多细节需要了解,不然会出现自认为莫名其妙问题。...(2)DELIMITER $用于改变MySQL分隔符,否则MySQL在回车情况下会按照默认分割符分号;将存储过程拆分,使其执行失败。...image.png (5)还有一个很苛刻地方就是,MySQL存储过程不能写在同一行。...这种变量要在变量名称前面加上“@”符号,这个变量可以在被调用存储过程或者代码之间共享数据,有点像CC++中局部static变量。具体例子可参考: MySQL存储过程——变量。...,删除等相关命令,与数据操作语句很相似,举例如下: --查看当前数据存储过程 show procedure status; --查看存储过程创建语句(\G表示以列形式展示结果) show create

    11.3K10

    MySQL存储过程

    文章目录 为什么使用存储过程 书写基本格式 关于参数 存储过程中开启事务 返回多个结果集 设置变量 存储过程:一段SQL语句集合 为什么使用存储过程 1、解耦合。...数据库部分可交由专门数据库管理人员去做,像前后端联合协作那样提供接口供后端调度。(这两天对后端开发又有了新理解:作为前端和数据库之间数据转接中间人。...但是存储过程只需要一次编译,多次运行。 4、提高系统安全性。存储过程可以使用权限控制,而且参数化存储过程可以有效防止注入攻击,保证了其安全性。 5、重用性强。一次编写,随处调用。...书写基本格式 create procedure 存储过程名(参数列表) begin 存储过程体 end 示例: delimiter $  #将语句结束符号从分号;临时改为$(可以是自定义) create...设置变量 在存储过程中如果要设置变量: declare 变量 数据类型; create procedure booktickets_CP(in id_ int, in cid_ int, in site

    7.9K30

    mysql存储过程

    INTO [@PART](https://my.oschina.net/u/857680); -- 将提取结果带入游标 CLOSE LOOP_T; -- 关闭游标 在MySQL存储过程中经常会看到这句话...READS SQL DATA:表示子程序包含读数据语句,但不包含写数据语句。 MODIFIES SQL DATA:表示子程序包含写数据语句。...SQL SECURITY DEFINER:表示执行存储过程程序是由创建该存储过程用户权限来执行。...SQL SECURITY INVOKER:表示执行存储过程程序是由调用该存储过程用户权限来执行。...存储方法 存储方法与存储过程区别 1,存储方法参数列表只允许IN类型参数,而且没必要也不允许指定IN关键字 2,存储方法返回一个单一值,值类型在存储方法头部定义 3,存储方法可以在SQL语句内部调用

    8.2K10

    MySQL 存储过程

    1.简介 存储过程(Stored Procedure)是一种存储数据库中程序,可供外部程序调用一种数据库对象。...存储过程没有返回值,但是它可以通过输出参数实现数据返回,同时还可以产生一个查询结果返回到客户端。 存储过程经编译创建并保存在数据库中,用户可通过指定存储过程名字并给定参数(需要时)调用执行。...存储过程思想上很简单,就是数据库 SQL 语句封装与重用。 MySQL 5.0 版本引入了对存储过程存储函数和触发器等存储程序支持。...减少网络流量: 存储过程在服务器端执行,只返回结果,减少了在网络上传输数据量。 不过,存储过程也存在一些缺点: MySQL 存储过程语法和其他数据库之间不兼容,无法直接移植。...存储过程需要占用数据库服务器资源,包括 CPU、内存等。MySQL 对于大量逻辑处理支持不够完善。 存储过程开发和维护需要专业技能。

    32720

    Mysql存储过程

    为什么要使用存储过程:优点 1 通过吧处理封装在容易使用单元中,简化复杂操作 2 由于不要求反复建立一系列处理步骤,这保证了数据完整性。...如果开发人员和应用程序都使用了同一存储过程,则所使用代码是相同。还有就是防止错误,需要执行步骤越多,出错可能性越大。防止错误保证了数据一致性。 3 简化对变动管理。...许多数据库管理员限制存储过程创建,允许用户使用存储过程,但不允许创建存储过程  存储过程是非常有用,应该尽可能使用它们  执行存储过程 MySQL存储过程执行为调用...BEGIN和END语句用来限定存储过程体,过程体本身是个简单SELECT语句  在MYSQL处理这段代码时会创建一个新存储过程productpricing。没有返回数据。...因为这段代码时创建而不是使用存储过程Mysql命令行客户机分隔符  默认MySQL语句分隔符为分号 ; 。Mysql命令行实用程序也是 ; 作为语句分隔符。

    6.1K30
    领券