MySQL 按时间分批冷数据归档

业务需求

• 主表：operation_log，时间字段 create_time DATETIME
• 归档规则：自动迁移 1 个月前全部数据到归档表 operation_log_archive，迁移完成后原表删除对应数据
• 核心优势：小批量循环 + 短事务 + 批次休眠，工作时间执行仅轻微瞬时压力，不会锁表阻塞线上业务
• 适用引擎：InnoDB（线上标准）

归档

1、主表 & 归档表结构

-- 业务主表
CREATE TABLE `operation_log` (
  id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
  user_id INT NOT NULL,
  operate_type TINYINT,
  content VARCHAR(1000),
  create_time DATETIME NOT NULL COMMENT '归档时间筛选字段'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '业务操作日志主表';

-- 必须给时间字段加索引（归档性能核心，无索引禁止归档）
ALTER TABLE operation_log ADD INDEX idx_create_time(create_time);

-- 创建归档表，完全复制主表结构
CREATE TABLE `operation_log_archive` LIKE `operation_log`;

2、前置校验 SQL（执行归档前必跑）：

-- 1. 统计待归档总数据量，预估IO压力
SELECT COUNT(*) AS total_archive_rows 
FROM operation_log 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 MONTH);

-- 2. 校验索引是否生效，type=range 代表索引正常；type=ALL 全表扫描禁止归档
EXPLAIN 
SELECT * FROM operation_log 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 MONTH) LIMIT 10000;

3、数据库账号需要权限：SELECT, INSERT, DELETE, LOCK TABLES

-- 归档专用账号授权示例
CREATE USER 'archive_user'@'localhost' IDENTIFIED BY 'Archive@123456';
GRANT SELECT,INSERT,DELETE ON 业务库名.* TO 'archive_user'@'localhost';
FLUSH PRIVILEGES;

4、完整分批归档 Shell 脚本（生产可用）

#!/bin/bash
##########################################################################
# MySQL 分批冷数据归档脚本
# 功能：迁移1个月前数据到归档表，分批循环，自带休眠限流，不阻塞线上业务
##########################################################################
# ====================== 配置区（按需修改）======================
DB_HOST="127.0.0.1"
DB_PORT="3306"
DB_USER="archive_user"
DB_PWD="Archive@123456"
DB_NAME="你的业务数据库名"
MAIN_TABLE="operation_log"       # 业务主表
ARCH_TABLE="operation_log_archive" # 归档表
BATCH_SIZE=5000                  # 每批处理行数，白天建议5000，凌晨可10000
SLEEP_SECONDS=1                  # 每批处理完成休眠时长，限流用
RETENTION_MONTH=1                # 保留近1个月数据，1个月前归档
LOG_FILE="/var/log/mysql_archive.log" # 归档日志路径
# ==============================================================

# 日志打印函数
log_print() {
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" >> $LOG_FILE
    echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1"
}

# 初始化日志
log_print "===== 归档任务开始 ====="

# 循环分批迁移，直到无旧数据
loop_count=0
while true; do
    loop_count=$((loop_count + 1))
    # 执行单批次事务：先复制，再删除，原子操作
    mysql -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PWD $DB_NAME << EOF
START TRANSACTION;
-- 复制一批旧数据到归档表
INSERT INTO $ARCH_TABLE
SELECT * FROM $MAIN_TABLE 
WHERE create_time < DATE_SUB(NOW(), INTERVAL $RETENTION_MONTH MONTH)
LIMIT $BATCH_SIZE;

-- 删除原表已复制的数据
DELETE FROM $MAIN_TABLE 
WHERE create_time < DATE_SUB(NOW(), INTERVAL $RETENTION_MONTH MONTH)
LIMIT $BATCH_SIZE;
COMMIT;
EOF

    # 获取本次操作影响行数
    affected_rows=$(mysql -s -h$DB_HOST -P$DB_PORT -u$DB_USER -p$DB_PWD $DB_NAME -e "SELECT ROW_COUNT();")

    # 影响行数为0，代表无数据可归档，退出循环
    if [ $affected_rows -eq 0 ]; then
        log_print "第${loop_count}批：无待归档数据，归档流程结束"
        break
    fi

    log_print "第${loop_count}批：成功处理${affected_rows}条数据，休眠${SLEEP_SECONDS}秒释放资源"
    sleep $SLEEP_SECONDS
done

log_print "===== 归档任务全部完成 ====="
echo -e "\n" >> $LOG_FILE

执行时段	BATCH_SIZE	SLEEP_SECONDS	适用场景
凌晨低峰	10000	0.5	无业务流量，全速归档
午间低谷	5000	1.0	少量用户访问
白天工作时间	3000	1.5	正常业务流量，极致限流

5、回收 InnoDB 磁盘空间

InnoDB DELETE 不会自动释放磁盘，归档完成执行优化：

OPTIMIZE TABLE operation_log;

注意：千万级大表 OPTIMIZE 会短暂锁表，建议凌晨执行

6、归档表压缩优化（冷数据只读，节省存储）

ALTER TABLE operation_log_archive ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

回滚

场景1: 归档中途业务报错，需要恢复已迁移数据

1、停止归档脚本

2、把归档表数据导回主表（按时间区间）

START TRANSACTION;
INSERT INTO operation_log
SELECT * FROM operation_log_archive 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 MONTH);
COMMIT;

场景2: 表重复数据

-- 按主键去重，删除重复行
DELETE t1 FROM operation_log_archive t1
JOIN operation_log_archive t2 
WHERE t1.id = t2.id AND t1.create_time < DATE_SUB(NOW(), INTERVAL 1 MONTH);