【解决方案】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