• 【解决方案】mysql大数据删除


    背景:生产环境,单表数据量在400W条,数据占空间约20G,无索引。
    数据库引擎使用的是InnoDB,InnoDB数据库对于已经删除的数据只是标记为删除,并不真正释放所占用的磁盘空间,所以InnoDB数据库文件会不断增长。

    目标是根据创建时间,仅保留近一个月的记录,最简朴的sql语句如下:

    DELETE FROM log_interface WHERE datediff(SYSDATE(), createdon) > 30;


    根据执行计划查询预计需要删除300W条,所删数据占空间约15G,根据以往经验,直接用上述sql进行数据删除需要超过十分钟,影响过大。
    1. 如果是要删除整个表,使用命令 truncate table 效率最高;
    2. 如果需要删除的数据量没有这么大,可以分多次删除,每次操作使用 limit 限制删除条数:

    DELETE FROM log_interface WHERE datediff(SYSDATE(), createdon) > 30 limit 10000; -- 删除一次数据大概需要10s


    3. 以上操作都不适应我们当前的场景,我们需要删除的数据超过表数据的50%,建议拷贝所需数据到临时表,然后重命名原表为其他名字,重命名临时表为原表名称,具体sql如下:

    CREATE TABLE `log_interface_bak` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
    `trance_id` varchar(64) DEFAULT NULL COMMENT '一条请求链路(Trace)的唯一标识',
    `span_id` varchar(64) DEFAULT NULL COMMENT '一个工作单元(Span)的唯一标识,必须值',
    `parent_span_id` varchar(64) DEFAULT NULL COMMENT '标识当前工作单元所属的上一个工作单元,Root Span(请求链路的第一个工作单元)的该值为空',
    `hospital_name` varchar(64) DEFAULT NULL COMMENT '所属医院名称',
    `hospital_id` varchar(64) DEFAULT NULL COMMENT '所属医院ID',
    `factory_id` int(11) DEFAULT NULL COMMENT '厂商id',
    `docking_id` int(11) DEFAULT NULL COMMENT '对接类型id',
    `basic_interface_info_id` int(11) DEFAULT NULL COMMENT '标准接口id',
    `interface_name` varchar(64) DEFAULT NULL COMMENT '接口名称',
    `business_id` int(11) DEFAULT NULL COMMENT '业务标签',
    `interface_call_time` datetime(3) DEFAULT NULL COMMENT '接口调用时间',
    `interface_response_time` bigint(20) DEFAULT NULL COMMENT '接口响应时间',
    `interface_response_state` tinyint(1) DEFAULT NULL COMMENT '接口响应状态 0 失败 1 成功',
    `interface_fail_reason` varchar(1024) DEFAULT NULL COMMENT '接口调用失败原因',
    `input_param` text COMMENT '接口入参',
    `output_param` text COMMENT '接口出参',
    `remarks` varchar(1024) DEFAULT NULL COMMENT '备注',
    `deletion_state` char(1) DEFAULT '0' COMMENT '删除状态,0未删除,1已删除',
    `createdon` datetime(3) DEFAULT NULL COMMENT '创建时间',
    `createdby` varchar(64) DEFAULT NULL COMMENT '创建者',
    `modifiedon` datetime DEFAULT NULL COMMENT '修改时间',
    `modifiedby` varchar(64) DEFAULT NULL COMMENT '修改者',
    `is_basic_type` tinyint(1) DEFAULT NULL COMMENT '是否标准接口 0 否 1 是',
    `path` varchar(128) DEFAULT NULL COMMENT '请求地址',
    `flow` bigint(20) DEFAULT NULL COMMENT '数据流量',
    `business_log_tag` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11456328 DEFAULT CHARSET=utf8 COMMENT='接口日志(标准接口,医院接口)记录表';

    将 log_interface 表中需要留存的数据备份到 log_interface_bak 表:

    insert into log_interface_bak SELECT * FROM log_interface WHERE datediff( SYSDATE( ), createdon ) < 30; -- 耗时196.427s

    对表进行重命名:

    RENAME TABLE log_interface to log_interface_a , log_interface_old to log_interface; -- 耗时0.238s

    最后删除log_interface_a表。

    参考文档:http://mysql.rjweb.org/doc.php/deletebig

  • 相关阅读:
    设计模式学习笔记之设计原则
    设计模式学习笔记之生成器模式
    设计模式学习笔记之适配器模式、外观模式
    java中的日期类型之间转换
    JS刷新当前页面的几种方法总结
    Java_枚举
    动态规划详解_2
    Java算法-动态规划详解
    Java经典算法题_2
    Java算法
  • 原文地址:https://www.cnblogs.com/miaoying/p/13748558.html
Copyright © 2020-2023  润新知