java生产环境问题-mysql写存储过程定时删除大数据量表

问题：生产环境流水表已经达到4000w条数据，不管是查询还是统计都受到了一定程度的影响。所以创建了分表，按照每个月进行存储。但是主表的数据还是很多，所以想到定时删除。

注意：生产环境之前的配置不算高，所以执行的时候会出现cup飙升的情况。现在做了一次升配，目前是32核的CPU。执行的时候需要实时观察mysql所在服务器的cpu情况，如果持续飙升到100以上，就立马停止掉。这个方法不适用你的服务器。

 怎么查看cpu？

直接在服务器输入top-回车

然后ctrl+c退出

用代码或者存储过程实现都可以。这篇文章就看一下存储过程怎么使用。

逻辑就是：根据表中的时间字段in_time排序，每隔30s删除最早的1000条数据，同时，最近三个月的数据不做删除。还要可以随时停止。

先看下整体代码：

DELIMITER //

-- 创建一个表来存储停止标志
CREATE TABLE IF NOT EXISTS deletion_control (
    stopFlag INT DEFAULT 0
)//

-- 插入一行，初始时将 stopFlag 设置为 0
INSERT INTO deletion_control (stopFlag) VALUES (0)//

CREATE PROCEDURE StopDeletion()
BEGIN
    -- 将停止标志设置为 1
    UPDATE deletion_control SET stopFlag = 1;
END //

CREATE PROCEDURE DeleteOldestCarFlow()
BEGIN
    DECLARE stopFlagValue INT;

    -- 获取停止标志的值
    SELECT stopFlag INTO stopFlagValue FROM deletion_control;

    -- 当停止标志未设置时执行删除操作
    WHILE stopFlagValue = 0 DO
        -- 删除根据 in_time 排序后最早的 100 条数据，并且最近三个月内的数据不删除
        DELETE FROM car_flow
        WHERE in_time <= NOW() - INTERVAL 3 MONTH
        ORDER BY in_time
        LIMIT 100;

        -- 休眠30秒
        DO SLEEP(30);

        -- 重新获取停止标志的值
        SELECT stopFlag INTO stopFlagValue FROM deletion_control;
    END WHILE;
END //

DELIMITER ;

先创建一个表deletion_control 主要用来存放开启还是停止的标识 ，并初始化为0-代表开启

然后创建了一个停止的存储过程StopDeletion（），主要就是把开关表变成1-停止

这个看自己需求，可以直接更改表中的值，就不用这个存储过程了

最后就是主过程DeleteOldestCarFlow()。首先判断表中的数据是0还是1，如果是0继续执行循环。然后开始删除数据，接着休眠30s，最后再查询一遍开关表中的数据是0还是1，直到为1就结束循环。

具体在navicat中是这样的。

可以点击直接执行和停止：

也可以通过这个来停止：