MySQL procedure大表温柔清理日志

一、存储过程清理数据

虽然这种方式没有使用 shell、python 等语言开多线程并发清理快，但是 “稳”
换一种方式说，我们为什么要拆分位一小段一小段的删除，就是为了不影响数据库对外提供服务，所以清理数据时稍微慢一点并没有关系。

• 手动查询要删除数据的最大自增 id，或者通过 procedure 自动获取

select min(id),max(id) from table_name where create_time < "2021-04-10 00:00:00";

• 修改 procedure 中的 表名、条件、最大ID 等参数

drop procedure if exists dbawsp_delete_data_procedure;
create procedure dbawsp_delete_data_procedure()
begin
    set @start_id=0;
    set @end_id=0;
    set @max_id=0;
    set @create_time="2021-04-10 00:00:00";

    # 自动获取自增 ID
    # select min(id), max(id) into @start_id,@max_id from table_name where id > @start_id and create_time < @create_time;

        while (@end_id<@max_id) do
            # 自增 ID 为连续的时候使用每次叠加计数的方式作为删除条件
            set @end_id=@start_id+1000;

            # 自增 ID 不连续，空洞特别多，使用每次都查询一次批次最大 ID
            # select max(id) into @end_id from (select id from table_name where id >= @start_id and id <= @max_id order by id asc limit 1000) as t;

            delete from table_name where id between @start_id and @end_id and create_time < @create_time;
            set @start_id=@end_id;
        end while;
end;

• 创建并运行，等待串行删除数据完成后删除存储过程

call dbawsp_delete_data_procedure();

drop procedure if exists dbawsp_delete_data_procedure;

• 收缩表空间碎片

optimize table table_name;