先给结论:3 亿行 + 6 个二级索引的表,DELETE 归档的最优解不是"把 chunk 调小",而是把归档路径从 DELETE 改成 DROP PARTITION——一次 DDL 替代 3.4 小时逐行删除,undo 产生速率直接归零,purge 压力消失。如果暂时无法加分区(DDL 窗口不够),就按下面的自适应限速脚本把 DELETE 速率压在 purge 稳态能力之内。
一、为什么 DELETE 路径天然追不上 purge
你的表 3.2 亿行、6 个二级索引。每删 1 行主表,InnoDB 要:
- 写 1 条 undo(主表)
- 标记 6 条二级索引记录为删除(但二级索引的 purge 是异步的,由 purge 线程逐条清理)
- 产生 6 条二级索引的 undo(用于 MVCC 可见性)
实际 undo 产生速率 ≈ DELETE_rate × 7 条记录(1 主表 + 6 二级索引)。你 50k 行/批 × 200ms 间隔 ≈ 250k rows/s,对应 undo 产生 ≈ 1.75M records/s。而 4 个 purge 线程的稳态消费能力(你实测 280 万 / 12 min ≈ 3,900 records/s)差了 450 倍。这不是调 chunk 能弥补的量级差距——除非 chunk 小到 10~20 行/批,总时长会拉到几十小时。
核心矛盾:DELETE 是"产生 undo 的速率"问题,purge 是"消费 undo 的速率"问题,两者差 2~3 个数量级时,任何 chunk/间隔组合都只是在"追"而不是"平衡"。
补充(v2 修订):purge 线程的单次迭代处理量受 innodb_purge_batch_size(默认 300)约束——每次迭代最多处理 300 条 undo record。4 个 purge 线程 × 300 条/批 × 迭代频率 ≈ 理论上限。如果你实测 3,900 records/s,反推迭代频率约 3.3 次/s,说明 purge 线程大部分时间在等 lock 或 IO。调 innodb_purge_batch_size 到 1000 可以单次多处理,但不能突破 IO 瓶颈——purge 最终是随机读 undo tablespace + 随机读二级索引页,IO 密集。所以真正影响 purge 速率的是 innodb_io_capacity(建议 ≥ 2000)和 innodb_io_capacity_max(建议 ≥ 4000),而不是 purge 线程数。MySQL 8.0.21+ 支持 innodb_purge_threads=0(自动按 CPU 核数),但这同样受 IO 限制。
二、更轻量的归档路径:PARTITION BY RANGE + DROP PARTITION
你的表按 created_at 做时间维度归档(90 天前的流水),天然适合 RANGE 分区:
-- 1. 加分区(Online DDL,5.7+ 支持 ALGORITHM=INPLACE,不锁 DML)
-- 按 30 天一个分区,保留最近 180 天
ALTER TABLE t_payment_flow
PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p_20250401 VALUES LESS THAN (TO_DAYS('2025-05-01')),
PARTITION p_20250501 VALUES LESS THAN (TO_DAYS('2025-06-01')),
PARTITION p_20250601 VALUES LESS THAN (TO_DAYS('2025-07-01')),
PARTITION p_20250701 VALUES LESS THAN (TO_DAYS('2025-08-01')),
PARTITION p_20250801 VALUES LESS THAN (TO_DAYS('2025-09-01')),
PARTITION p_20250901 VALUES LESS THAN (TO_DAYS('2025-10-01')),
PARTITION p_20251001 VALUES LESS THAN (TO_DAYS('2025-11-01')),
PARTITION p_max VALUES LESS THAN MAXVALUE
);
-- 2. 归档 = 导出 + DROP(不再是 DELETE)
-- 2a. 导出 90 天前的分区到 Parquet
mysqldump --single-transaction --where="created_at < '2025-07-25'" \
t_payment_flow > /data/archive/t_payment_flow_20250725.sql
-- 或者用 pt-select / SELECT INTO OUTFILE + 外部 ETL 转 Parquet
-- 2b. 删除分区(瞬时完成,不产生 undo,不经过 purge)
ALTER TABLE t_payment_flow DROP PARTITION p_20250401;
ALTER TABLE t_payment_flow DROP PARTITION p_20250501;
ALTER TABLE t_payment_flow DROP PARTITION p_20250601;
关键优势:
DROP PARTITION直接释放 .ibd 文件的空间(比 DELETE 后碎片化好得多),不产生 undo log,purge 线程零压力;- 执行时间:3.2 亿行的表加 PARTITION 的 Online DDL 约 30~60 分钟(取决于 IO 和 buffer pool 命中率),但只做一次;之后每天归档只需
DROP PARTITION,秒级完成; - 二级索引的碎片化问题(你 6 个索引涨了 38GB Data_free)也一并解决——DROP PARTITION 直接释放对应索引页,不需要 OPTIMIZE TABLE。
代价与风险:
- 加分区的 DDL 需要 30~60 分钟窗口,期间
ALGORITHM=INPLACE允许并发 DML(你白天 1200 QPS 写入不受影响),但需要innodb_online_alter_log_max_size足够大(建议 ≥ 2GB); - 分区表不支持跨分区的唯一索引(PRIMARY KEY 必须包含分区键
created_at)——如果你的表有不含created_at的唯一索引,需要先改造; - 分区粒度建议 30 天(太细管理成本高,太粗 DROP 时释放空间不够精准)。
v2 补充——DROP PARTITION 的 binlog 影响:如果开启了 binlog(生产必开),DROP PARTITION 会写一条 DDL binlog 事件(ALTER TABLE ... DROP PARTITION),下游从库会同步执行。3.2 亿行的分区 DROP 在从库上同样需要释放 .ibd 空间,从库的 IO 压力不可忽略(约 20~40GB 文件删除 + fsync)。如果从库磁盘空间紧张(剩余 < 50GB),建议先在从库上预清理或扩容。
如果加分区窗口实在排不上,退而求其次用 DELETE ... WHERE created_at < ? 但按主键范围切片(你已提到这个思路),核心改进:
-- 按 id 范围切片,每批 5000 行,自适应间隔
-- 关键:用 id 范围而不是 created_at 范围,避免二级索引扫描
DELETE FROM t_payment_flow
WHERE id BETWEEN :start_id AND :end_id
AND created_at < '2025-07-25'
LIMIT 5000;
三、自适应限速脚本(DELETE 路径的兜底方案)
如果最终仍走 DELETE 路径,下面的脚本实时监控 purge 消费速率并动态调整间隔:
#!/bin/bash
# adaptive_delete.sh — 自适应限速批量删除
# 用法: ./adaptive_delete.sh <start_id> <end_id> <cutoff_date>
START_ID=$1
END_ID=$2
CUTOFF=$3
BATCH=5000
BASE_SLEEP=200 # 基础间隔 ms
MAX_SLEEP=5000 # 最大间隔 ms
HLL_WARN=500000 # History List Length 告警阈值
HLL_CRIT=1000000 # History List Length 危险阈值
get_hll() {
mysql -N -e "SHOW ENGINE INNODB STATUS" 2>/dev/null | \
grep -oP 'History list length:\s+\K\d+'
}
get_purge_rate() {
# 采 5s 内 Innodb_purge_rows_removed 增量
local t1=$(mysql -N -e "SHOW GLOBAL STATUS LIKE 'Innodb_purge_rows_removed'" | awk '{print $2}')
sleep 5
local t2=$(mysql -N -e "SHOW GLOBAL STATUS LIKE 'Innodb_purge_rows_removed'" | awk '{print $2}')
echo $(( (t2 - t1) / 5 )) # rows/s
}
get_buffer_pool_wait() {
# v2 新增:buffer pool 等待作为 purge 背压的领先指标
mysql -N -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free'" | awk '{print $2}'
}
CUR_ID=$START_ID
SLEEP_MS=$BASE_SLEEP
while [ "$CUR_ID" -lt "$END_ID" ]; do
NEXT_ID=$((CUR_ID + BATCH))
[ $NEXT_ID -gt $END_ID ] && NEXT_ID=$END_ID
# 执行一批
mysql -e "DELETE FROM t_payment_flow WHERE id BETWEEN $CUR_ID AND $NEXT_ID AND created_at < '$CUTOFF' LIMIT $BATCH"
# 读 History List Length
HLL=$(get_hll)
# v2 新增:同时检查 buffer pool wait(purge 跟不上时,写线程会等 buffer pool 腾页)
BP_WAIT=$(get_buffer_pool_wait)
if [ "$BP_WAIT" -gt 1000 ]; then
echo "[$(date '+%H:%M:%S')] WARNING: buffer_pool_wait_free=$BP_WAIT, purge 背压"
SLEEP_MS=$((MAX_SLEEP))
fi
if [ "$HLL" -gt "$HLL_CRIT" ]; then
SLEEP_MS=$((MAX_SLEEP))
echo "[$(date '+%H:%M:%S')] HLL=$HLL > CRIT, sleeping ${SLEEP_MS}ms"
elif [ "$HLL" -gt "$HLL_WARN" ]; then
SLEEP_MS=$((SLEEP_MS * 2))
[ $SLEEP_MS -gt $MAX_SLEEP ] && SLEEP_MS=$MAX_SLEEP
echo "[$(date '+%H:%M:%S')] HLL=$HLL > WARN, sleeping ${SLEEP_MS}ms"
else
SLEEP_MS=$((SLEEP_MS / 2))
[ $SLEEP_MS -lt $BASE_SLEEP ] && SLEEP_MS=$BASE_SLEEP
fi
sleep $(awk "BEGIN {print $SLEEP_MS / 1000}")
CUR_ID=$NEXT_ID
done
echo "[$(date '+%H:%M:%S')] Done. Final HLL: $(get_hll)"
这个脚本的核心逻辑:不是固定 chunk/间隔,而是把 History List Length 当作反馈信号——HLL 涨了就减速,HLL 降了就加速。这比"拍一个固定值"更稳健,因为 purge 的稳态速率受白天 OLTP 写入、buffer pool 命中率、二级索引页分布共同影响,固定值无法适应。
v2 补充——Innodb_buffer_pool_wait_free 是比 HLL 更领先的背压指标:HLL 涨说明 undo 在积压(已经发生了),但 Innodb_buffer_pool_wait_free 涨说明写线程已经在等 buffer pool 腾页了(purge 跟不上 → dirty page 无法刷 → buffer pool 满 → 写阻塞)。这个指标在 HLL 还没到 CRIT 时就会先涨,是更好的"提前刹车"信号。
四、purge 追不追得上的量化判据
不靠"跑完看 HLL 峰值",而是跑的过程中实时算:
purge 消费速率 R_purge = Δ(Innodb_purge_rows_removed) / Δt [records/s]
undo 产生速率 R_undo = BATCH / (exec_time + sleep_time) × 7 [records/s]
安全条件: R_undo ≤ 0.5 × R_purge
用上面的 get_purge_rate() 函数每 30s 采一次 R_purge,和当前 R_undo 对比。如果 R_undo > 0.5 × R_purge 持续 2 个采样周期,立刻加大 sleep。
HLL 本身是"滞后指标"(它反映的是"已经积压了多少"),而 R_purge vs R_undo 是"领先指标"(反映的是"还在不在积压")。两个一起看才完整。
五、6 个二级索引碎片化的治理
如果你最终走 DELETE 路径(不是 DROP PARTITION),碎片化治理建议:
- 不要批删后统一 OPTIMIZE——3.2 亿行 × 6 索引的 OPTIMIZE 要 2~4 小时,窗口不够且期间锁升级风险大;
- 分批重建:按
ALTER TABLE ... ALTER INDEX idx_xxx INVISIBLE→DROP INDEX→ADD INDEX逐个处理,每个索引单独 Online DDL(约 10~20 分钟/个),6 个索引分 3 天做; - 更优解:如果走了 PARTITION 路径,碎片化问题自动消失——DROP PARTITION 直接释放物理空间,不需要 OPTIMIZE。
六、方案取舍总结
| 方案 | 归档耗时 | undo 压力 | 碎片化 | DDL 风险 | 推荐度 | |------|---------|----------|--------|---------|--------| | DELETE 50k/200ms(现状) | 3h40m | 极高(HLL 280 万) | 38GB | 无 | ✗ | | DELETE 5k/自适应 | 6~12h | 可控(HLL < 50 万) | 38GB | 无 | △ | | DROP PARTITION | 一次 DDL 30~60min + 每天 DROP < 1s | 零 | 零 | 中(唯一键限制) | ✓ |
最终建议:先用 EXPLAIN 确认你的 6 个二级索引里有没有可以合并或删掉的(6 个索引对 3.2 亿行的表来说偏多,每多一个索引 purge 压力 +14%),再做分区改造。如果 6 个索引都能砍到 3 个,undo 产生速率从 ×7 降到 ×4,DELETE 路径的可行性窗口会宽不少——但 DROP PARTITION 仍然是更干净的方案。