清理大量mysql表数据记录

写在前面的话：
之前工作中遇到一个需求，客户现场mysql 里面有几张表的历史数据量达到 1T，客户要求保留表里近一年的数据，删除历史数据，减小表数据存储空间，并且不能影响正常读写

这种情况下想要实现需求，会有几个问题：

• 由于现场存储上限，不能使用临时表去备份数据，也就不能直接 truncat 或 drop 表后重建；
• 表数据量过大，也不能直接执行 delete from table where dt < “[target_dt]” ，会导致锁表，可能会影响其他业务的读写；
• 由于创建表的时候 dt 列（业务日期）是在联合索引中的字段且不是联合索引的首字短，所以单独使用dt 约束无法触发索引，也就不能快速定位需要删除的数据，会加长delete 操作时间；

分析下来 决定还是使用 delete 操作 ，添加 limit 限制单次删除的数据量，约束列改用 id 主键，直接指定id 的范围去删除；
由于手动触发delete 太浪费时间，所以定义了procedure 去循环执行 delete，定义过程如下：

create procedure data_clean(once_delete_limit int, target_dt varchar(64))
begin

-- 查询 近一年前业务日期 dt 对应的最小的id 赋值为 target_id_index
declare target_id_index bigint;
declare cursor_1 cursor  for select min(id) as target_id_index from dws_xxxx_d where dt=target_dt;
open cursor_1;
fetch cursor_1 into target_id_index;

-- 查询全表最小id 赋值为 min_id_index
declare min_id_index bigint;
declare cursor_2 cursor  for select min(id) as min_id_index from dws_xxxx_d;
open cursor_2;
fetch cursor_2 into min_id_index;

declare end_id_index bigint;
--循环 (如果min_id_index<target_id_index 则循环删除数据)
while min_id_index<target_id_index do
set end_id_index=min_id_index+once_delete_limit;
-- 删除 全表最小id 到（最小id+once_delete_limit）
delete from dws_xxxx_d where id between min_id_index and end_id_index;
-- 更新 全表最小id 变量
set min_id_index=end_id_index-1;
end while;
end;

调用：

call data_clean(10000,date_format(date_add(now(),INTERVAL -1 year),"%Y%m%d"))

经过漫长的等待，删完了，接下来定义了event 每天执行 清理操作:

create event if not exists dws_xxxx_d_data_clean 
ON SCHEDULE INTERVAL EVERY  1 DAY  
DO delete from dws_xxxx_d where dt< date_format(date_add(now(),INTERVAL -1 year),"%Y%m%d");