背景:生产环境,单表数据量在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