MySQL中存储过程大表分批删除历史数据的四种方案

-- 查看有多少数据需要删除
select count(*) as total_to_delete
from orders
where created_at < date_sub(now(), interval 1 year);

-- 检查 created_at 上是否有索引
show index from orders where column_name = 'created_at';

pt-online-schema-change \
  --alter "add index idx_created_at (created_at)" \
  d=your_db,t=orders \
  --execute

-- 至少确认最近的备份时间点
show variables like 'log_bin%';
-- 确认 binlog 是否开启（用于 point-in-time recovery）
show variables like 'log_bin';

delimiter $$

drop procedure if exists batch_delete_old_data$$

create procedure batch_delete_old_data(
    in p_table     varchar(64),   -- 表名
    in p_condition varchar(255),  -- 删除条件
    in p_batch     int,           -- 每批删除条数
    in p_sleep     decimal(3,1)   -- 批间暂停秒数
)
begin
    declare v_rows   int default 1;
    declare v_total  bigint default 0;
    declare v_sql    text;

    -- 动态拼接 sql，支持任意表和条件
    set v_sql = concat('delete from ', p_table,
                       ' where ', p_condition,
                       ' limit ', p_batch);

    while v_rows > 0 do
        set @stmt = v_sql;
        prepare stmt from @stmt;
        execute stmt;
        deallocate prepare stmt;

        set v_rows = row_count();
        set v_total = v_total + v_rows;

        -- 每删除 10 万条输出一次进度
        if v_total % 100000 < p_batch then
            select concat('已删除 ', v_total, ' 条') as progress;
        end if;

        do sleep(p_sleep);
    end while;

    select concat('删除完成，共删除 ', v_total, ' 条记录') as result;
end$$

delimiter ;

-- 场景：删除 orders 表中一年前的数据，每批 1 万条，暂停 0.5 秒
call batch_delete_old_data(
    'orders',
    'created_at < date_sub(now(), interval 1 year)',
    10000,
    0.5
);

+---------------------+
| progress            |
+---------------------+
| 已删除 100000 条    |
+---------------------+

...（中间多轮输出）...

+------------------------------------------+
| result                                   |
+------------------------------------------+
| 删除完成，共删除 45820000 条记录          |
+------------------------------------------+

# macos
brew install percona-toolkit
# ubuntu/debian
apt-get install percona-toolkit
# centos/rhel
yum install percona-toolkit

# 删除 orders 表中一年前的数据，每批 5000 条
pt-archiver \
  --source d=your_db,t=orders,h=127.0.0.1,u=root,p=your_password \
  --where "created_at < date_sub(now(), interval 1 year)" \
  --limit 5000 \
  --bulk-delete \
  --progress 10000 \
  --statistics

# 把旧数据归档到 orders_archive 表，而不是直接删除
pt-archiver \
  --source d=your_db,t=orders,h=127.0.0.1,u=root,p=your_password \
  --dest d=your_db,t=orders_archive \
  --where "created_at < date_sub(now(), interval 1 year)" \
  --limit 5000 \
  --bulk-insert \
  --bulk-delete \
  --progress 10000

import pymysql
import time
import logging
logging.basicconfig(level=logging.info, format='%(asctime)s %(message)s')
logger = logging.getlogger(__name__)
def batch_delete(conn_params, table, condition,
                 batch_size=10000, sleep_seconds=1.0, max_total=0):
    """分批删除历史数据
    args:
        conn_params: mysql 连接参数字典
        table: 表名
        condition: where 条件
        batch_size: 每批删除条数
        sleep_seconds: 批间暂停秒数
        max_total: 最多删除条数，0 为不限制
    """
    conn = pymysql.connect(**conn_params)
    cursor = conn.cursor()
    total_deleted = 0
    try:
        while true:
            sql = f"delete from {table} where {condition} limit {batch_size}"
            cursor.execute(sql)
            conn.commit()
            rows = cursor.rowcount
            if rows == 0:
                break
            total_deleted += rows
            logger.info(f"本批删除 {rows} 条，累计 {total_deleted} 条")
            if max_total > 0 and total_deleted >= max_total:
                logger.info(f"已达到 max_total={max_total}，停止")
                break
            time.sleep(sleep_seconds)
    except exception as e:
        conn.rollback()
        logger.error(f"删除中断，已回滚最后一批。累计 {total_deleted} 条。错误: {e}")
        raise
    finally:
        cursor.close()
        conn.close()
    logger.info(f"删除完成，共删除 {total_deleted} 条记录")
    return total_deleted
# 调用示例
batch_delete(
    conn_params={
        'host': '127.0.0.1',
        'user': 'root',
        'password': 'your_password',
        'database': 'your_db',
    },
    table='orders',
    condition="created_at < date_sub(now(), interval 1 year)",
    batch_size=10000,
    sleep_seconds=1.0,
)

2025-07-06 02:00:01 本批删除 10000 条，累计 10000 条
2025-07-06 02:00:02 本批删除 10000 条，累计 20000 条
2025-07-06 02:00:03 本批删除 10000 条，累计 30000 条
...
2025-07-06 04:35:12 本批删除 3200 条，累计 45823200 条
2025-07-06 04:35:12 删除完成，共删除 45823200 条记录

# crontab: 每天凌晨 3 点执行
# 0 3 * * * /usr/bin/python3 /opt/scripts/clean_old_orders.py
# 或者用 apscheduler 在应用内调度
from apscheduler.schedulers.blocking import blockingscheduler
scheduler = blockingscheduler()
@scheduler.scheduled_job('cron', hour=3, minute=0)
def nightly_cleanup():
    batch_delete(
        conn_params={
            'host': '127.0.0.1',
            'user': 'app',
            'password': '***',
            'database': 'prod',
        },
        table='orders',
        condition="created_at < date_sub(now(), interval 1 year)",
        batch_size=5000,
        sleep_seconds=2.0,
    )
scheduler.start()

-- 按月分区的表示例
create table orders (
    id bigint auto_increment,
    user_id bigint not null,
    amount decimal(10,2),
    created_at datetime not null,
    primary key (id, created_at),
    index idx_created_at (created_at)
) engine=innodb
partition by range (year(created_at) * 100 + month(created_at)) (
    partition p202301 values less than (202302),
    partition p202302 values less than (202303),
    -- ... 每月一个分区
    partition p202412 values less than (202501),
    partition p_future values less than maxvalue
);

-- 删除 2023 年 1 月的分区（瞬间完成）
alter table orders drop partition p202301;

-- 删除多个分区
alter table orders drop partition p202301, p202302, p202303;

-- 简单直接，但会锁表（mysql 5.6+ 支持 online ddl，锁表时间大幅缩短）
optimize table orders;

-- 等效于 optimize，通过重建表释放空间
alter table orders engine=innodb;

# 不锁表重建，适合生产环境
pt-online-schema-change \
  --alter "engine=innodb" \
  d=your_db,t=orders \
  --execute

-- 查看表占用的磁盘空间
select
    table_name,
    round(data_length / 1024 / 1024, 2) as data_mb,
    round(index_length / 1024 / 1024, 2) as index_mb,
    round(data_free / 1024 / 1024, 2) as free_mb
from information_schema.tables
where table_schema = 'your_db'
  and table_name = 'orders';