随手一条 DELETE 就是一场事故第 7 篇教了怎么把误删找回来这一篇讲怎么不制造那次误删之后更常见的事故清理亿级历史数据。业务方的需求听起来只有一句话——“把三年前的订单删掉”落到数据库上却是最凶险的一类操作一条DELETE FROM orders WHERE created_at ...圈住两亿行undo 表空间被撑到磁盘报警、一个不可拆分的巨型事务让从库延迟爬到小时级第 1 篇 MTS 栅栏的极端形态、回滚比删除更慢、删完磁盘还不归还第 1 篇的高水位。正确的形态是一个工程分块、限流、归档先行、在线重建收尾。本篇把清理拆成分块删除动力学和在线重建工具账两段各做一个模拟。为什么删除必须是项目而不是语句先立三条约束。约束一事务要小。InnoDB 的回滚段按事务累积亿行级事务的 undo 既撑磁盘又拖慢 purge中途 kill 还要花时间回滚所以按主键或索引范围切成万行一块每块一个短事务。约束二从库要追得上。删除的行事件同样要进 binlog、在从库逐个回放块间节奏必须由从库延迟驱动而不是拍脑袋的sleep 0.5——这就是限流器的全部哲学。约束三数据要有人接。清理和归档是同一件事的两面INSERT INTO archive_db.orders SELECT ...按同一批主键与DELETE放进同一个事务两份账要么都动要么都不动块大小下查出的行集与删掉的行集必须严格同界用WHERE id BETWEEN a AND b而不是LIMIT二义定位。工程上这套循环叫分块归档删除pt-archiver是它的现成封装--limit定块、--commit-each定事务、--sleep/--max-lag定限流、--where定圈选。实验一块与块之间睡多久从库说了算两百万行待清、每块一万行主库侧每块 0.5 秒写完从库回放速率只有四分之一行锁与二级索引维护的折损。对比固定节奏每块睡 0.5 秒与延迟超过 0.2 秒就暂停派发下一块的自适应策略。TOTAL,CHUNK2_000_000,10_000MR,AR20_000,5_000# 行/秒: 主库产出 vs 从库回放LOW0.2# 自适应恢复线(延迟秒): 高于它就只追不写defsimulate(fixed_sleepNone,adaptiveFalse):dt0.05tlagstalepeak0.0remaining,write_left,napTOTAL,0.0,0.0whileremaining0orwrite_left0ornap1e-9orlag1e-9:if(write_left1e-9andnap1e-9andremaining0and(notadaptiveorlag/ARLOW)):cmin(CHUNK,remaining)# 开写下一块write_left,remainingc,remaining-cifwrite_left1e-9:stepmin(dt,write_left/MR)rowsmin(write_left,MR*step)write_left-rows lagrows-AR*step# 从库同时在追, 但追不上ifwrite_left1e-9andfixed_sleepisnotNone:napfixed_sleepelifnap1e-9:stepmin(dt,nap)nap-step lagmax(0.0,lag-AR*step)else:stepdt# 空转: 只消化欠账lagmax(0.0,lag-AR*step)tstepiflagAR:stalestep peakmax(peak,lag/AR)returnt,peak,stale naivesimulate(fixed_sleep0.5)adasimulate(adaptiveTrue)print(固定节奏(每块睡0.5s): 全程 %.0fs, 峰值延迟 %.0fs, 读陈旧(1s)时长 %.0fs%(naive[0],naive[1],naive[2]))print(自适应限流(延迟%.1fs 暂停派发): 全程 %.0fs, 峰值延迟 %.1fs, 读陈旧时长 %.0fs%(LOW,ada[0],ada[1],ada[2]))print(理论边界: 主库裸速 %.0fs, 从库回放上限 %.0fs —— 自适应逼近后者, 这就是可陪跑的价格%(TOTAL/MR,TOTAL/AR))print(每块 一个短事务(INSERT 归档表 DELETE 同批行, 同事务保证两份账一致), 块间看延迟脸色决定睡不睡)运行输出固定节奏(每块睡0.5s): 全程 400s, 峰值延迟 200s, 读陈旧(1s)时长 399s 自适应限流(延迟0.2s 暂停派发): 全程 400s, 峰值延迟 1.7s, 读陈旧时长 180s 理论边界: 主库裸速 100s, 从库回放上限 400s —— 自适应逼近后者, 这就是可陪跑的价格 每块 一个短事务(INSERT 归档表 DELETE 同批行, 同事务保证两份账一致), 块间看延迟脸色决定睡不睡两种策略总时长都是 400 秒——因为瓶颈本来就在从库回放速率固定节奏只是把账欠到最后集中爆。区别在形状固定节奏的延迟一路线性爬到 200 秒期间所有从库读都是陈旧数据5 秒熔断线早在第 5 篇就把它打回主库主库平白多扛全部读流量自适应把峰值压在 1.7 秒读陈旧时长少一半。另一个反直觉点删除任务主库很快从来不是好事限流器的目标函数应该是从库延迟上界 业务高峰避让而不是今晚必须删完。实验二表瘦身收尾的在线重建——pt-osc 与 gh-ost 的账删完历史数据表文件还是三百行的体量高水位不降第 1 篇。收尾手段是在线重建pt-osc 与 gh-ost 都走建 ghost 表 → 搬保留行 → 追增量 → RENAME 交换分歧在增量怎么搬。pt-osc 在原表挂三个触发器每笔业务写顺手往 ghost 记一份gh-ost 不碰原表伪装成从库消费 binlog 行事件异步重放到 ghost。骨架相同窗口期那几百万笔业务写的去向就是两案的量级差异算给你看。KEEP,DISCARD100_000_000,200_000_000# 保留新数据, 丢弃历史数据MIGRATION_MIN240# 拷贝窗口(分钟): 100M 行 ÷ 拷贝速率BIZ{insert:5_000_000,update:2_000_000,delete:1_000_000}# 窗口内业务写ROW_KB0.35# 平均行大小copy_opsKEEP biz_opssum(BIZ.values())pt{ghost写入:copy_opsbiz_ops,原表额外写(触发器):biz_ops,ghost额外读(重拷被改块):int(biz_ops*0.18)}gh{ghost写入:copy_opsbiz_ops,原表额外写(触发器):0,ghost额外读(重拷被改块):int(biz_ops*0.18)}print(拷贝主体两案相同: %s 行写入 ghost; 差异在增量搬运%format(copy_ops,,))forname,min((pt-osc,pt),(gh-ost,gh)):extram[原表额外写(触发器)]m[ghost额外读(重拷被改块)]print(%-7s: 原表额外写 %s 行, ghost 额外读 %s 行, 总增量 %s 行(约 %.1fGB 行宽)%(name,format(m[原表额外写(触发器)],,),format(m[ghost额外读(重拷被改块)],,),format(extra,,),extra*ROW_KB/1_048_576))pt_trig_latency0.4# 每笔业务写多一条触发器 insert 的开销(ms)print(\n业务侧观测: pt-osc 窗口内每笔写 %.1fms 触发器开销(8M 笔累计 %.0f 分钟 CPU); gh-ost 业务写路径不变%(pt_trig_latency,biz_ops*pt_trig_latency/1000/60))print(切换动作: pt-osc RENAME 元数据级交换(毫秒, 但需要拿得住 MDL); gh-ost cut-over 锁原表-追平binlog-交换-解锁(默认 max-lag 节流, 秒级停顿可预期))print(失败面: pt-osc 中途有人 ALTER 原表直接报错回滚; 触发器占用列额度, 宽表可能建不下)print( gh-ost 依赖 binlog: 保留时长必须 拷贝窗口 %d 分钟, binlog_row_image 必须 FULL%MIGRATION_MIN)archive_rowsDISCARDprint(\n别忘了归档: ghost 只装留下的 %s 行, 被丢弃的 %s 行不在任何一方的命运里——%(format(KEEP,,),format(archive_rows,,)))print( 要么先跑实验A的分块INSERT归档DELETE把 %s 行搬去归档库, 再触发重建;%format(archive_rows,,))print( 要么重建前在 ghost 定义里保住全部行, 切换后再分块清——两种都要把数据去向写进变更单)lag12.0print(节流联动: gh-ost --max-lag-millis 与实验A同一根线: 从库延迟 %.0fs 时拷贝器自动降速, 业务读从库不受伤%lag)运行输出拷贝主体两案相同: 100,000,000 行写入 ghost; 差异在增量搬运 pt-osc : 原表额外写 8,000,000 行, ghost 额外读 1,440,000 行, 总增量 9,440,000 行(约 3.2GB 行宽) gh-ost : 原表额外写 0 行, ghost 额外读 1,440,000 行, 总增量 1,440,000 行(约 0.5GB 行宽) 业务侧观测: pt-osc 窗口内每笔写 0.4ms 触发器开销(8M 笔累计 53 分钟 CPU); gh-ost 业务写路径不变 切换动作: pt-osc RENAME 元数据级交换(毫秒, 但需要拿得住 MDL); gh-ost cut-over 锁原表-追平binlog-交换-解锁(默认 max-lag 节流, 秒级停顿可预期) 失败面: pt-osc 中途有人 ALTER 原表直接报错回滚; 触发器占用列额度, 宽表可能建不下 gh-ost 依赖 binlog: 保留时长必须 拷贝窗口 240 分钟, binlog_row_image 必须 FULL 别忘了归档: ghost 只装留下的 100,000,000 行, 被丢弃的 200,000,000 行不在任何一方的命运里—— 要么先跑实验A的分块INSERT归档DELETE把 200,000,000 行搬去归档库, 再触发重建; 要么重建前在 ghost 定义里保住全部行, 切换后再分块清——两种都要把数据去向写进变更单 节流联动: gh-ost --max-lag-millis 与实验A同一根线: 从库延迟 12s 时拷贝器自动降速, 业务读从库不受伤选型口径可以背下来高写入热表优先 gh-ost原表零侵入、可控节流、可暂停重试代价是它是个常驻进程、要求 ROW 全镜像 binlog 且保留期覆盖整个窗口pt-osc 赢在无外部依赖、纯 SQL 可做、rename 原子性干脆输在触发器把每笔写放大一次、且中途 DDL 直接翻车。两案共同的硬要求表要有主键或唯一键做分块坐标ghost 需要约一倍的磁盘余量rename 后旧表至少留置一个观察期再删。更根本的解法与变更清单时间序大表订单、流水建表即分区按月 RANGE清理退化为ALTER TABLE ... DROP PARTITION秒级、零复制、空间立即归还——所有分块删除都是在为没分区还债。冷数据有查询需求就上归档库分块删除的目的地是归档实例可低规格、可换引擎别删进无人认领的 binlog。变更单四件套块大小与限流阈值、从库延迟熔断线多少秒暂停/恢复、归档去向与行数对账口径、中途暂停与续跑方案断点已处理的最大主键。避开三样时刻业务高峰、备份窗口第 6 篇的 XtraBackup 与重建抢 IO、切换演练日。删除类变更配守恒对账主键段行数、金额字段总和、归档表行数三者勾稽任一不平立即停手。清理与重建解决的是单实例内的历史包袱。但整个系列反复假设的机房还活着终将被打破当一整个机房断电、网络孤岛、或 Region 级不可用时高可用体系怎么有计划地扛过去而不是碰运气下一篇《数据库高可用与容灾实战9机房级故障演练混沌工程怎么做到可回滚》。参考来源Percona Toolkit Documentationpt-online-schema-changehttps://docs.percona.com/percona-toolkit/pt-online-schema-change.htmlPercona Toolkit Documentationpt-archiverhttps://docs.percona.com/percona-toolkit/pt-archiver.htmlGitHubgithub/gh-osthttps://github.com/github/gh-ostMySQL 8.0 Reference ManualPartitioning of Tableshttps://dev.mysql.com/doc/refman/8.0/en/partitioning.htmlGitHubpercona/percona-toolkithttps://github.com/percona/percona-toolkit本系列已结集为免费专栏数据库高可用与容灾实战从主从复制到机房级演练进阶推荐付费专栏Python 自动化接单实战从脚本到第一单限时 ¥9.9首篇免费试读
随手一条 DELETE 就是一场事故第 7 篇教了怎么把误删找回来这一篇讲怎么不制造那次误删之后更常见的事故清理亿级历史数据。业务方的需求听起来只有一句话——“把三年前的订单删掉”落到数据库上却是最凶险的一类操作一条DELETE FROM orders WHERE created_at ...圈住两亿行undo 表空间被撑到磁盘报警、一个不可拆分的巨型事务让从库延迟爬到小时级第 1 篇 MTS 栅栏的极端形态、回滚比删除更慢、删完磁盘还不归还第 1 篇的高水位。正确的形态是一个工程分块、限流、归档先行、在线重建收尾。本篇把清理拆成分块删除动力学和在线重建工具账两段各做一个模拟。为什么删除必须是项目而不是语句先立三条约束。约束一事务要小。InnoDB 的回滚段按事务累积亿行级事务的 undo 既撑磁盘又拖慢 purge中途 kill 还要花时间回滚所以按主键或索引范围切成万行一块每块一个短事务。约束二从库要追得上。删除的行事件同样要进 binlog、在从库逐个回放块间节奏必须由从库延迟驱动而不是拍脑袋的sleep 0.5——这就是限流器的全部哲学。约束三数据要有人接。清理和归档是同一件事的两面INSERT INTO archive_db.orders SELECT ...按同一批主键与DELETE放进同一个事务两份账要么都动要么都不动块大小下查出的行集与删掉的行集必须严格同界用WHERE id BETWEEN a AND b而不是LIMIT二义定位。工程上这套循环叫分块归档删除pt-archiver是它的现成封装--limit定块、--commit-each定事务、--sleep/--max-lag定限流、--where定圈选。实验一块与块之间睡多久从库说了算两百万行待清、每块一万行主库侧每块 0.5 秒写完从库回放速率只有四分之一行锁与二级索引维护的折损。对比固定节奏每块睡 0.5 秒与延迟超过 0.2 秒就暂停派发下一块的自适应策略。TOTAL,CHUNK2_000_000,10_000MR,AR20_000,5_000# 行/秒: 主库产出 vs 从库回放LOW0.2# 自适应恢复线(延迟秒): 高于它就只追不写defsimulate(fixed_sleepNone,adaptiveFalse):dt0.05tlagstalepeak0.0remaining,write_left,napTOTAL,0.0,0.0whileremaining0orwrite_left0ornap1e-9orlag1e-9:if(write_left1e-9andnap1e-9andremaining0and(notadaptiveorlag/ARLOW)):cmin(CHUNK,remaining)# 开写下一块write_left,remainingc,remaining-cifwrite_left1e-9:stepmin(dt,write_left/MR)rowsmin(write_left,MR*step)write_left-rows lagrows-AR*step# 从库同时在追, 但追不上ifwrite_left1e-9andfixed_sleepisnotNone:napfixed_sleepelifnap1e-9:stepmin(dt,nap)nap-step lagmax(0.0,lag-AR*step)else:stepdt# 空转: 只消化欠账lagmax(0.0,lag-AR*step)tstepiflagAR:stalestep peakmax(peak,lag/AR)returnt,peak,stale naivesimulate(fixed_sleep0.5)adasimulate(adaptiveTrue)print(固定节奏(每块睡0.5s): 全程 %.0fs, 峰值延迟 %.0fs, 读陈旧(1s)时长 %.0fs%(naive[0],naive[1],naive[2]))print(自适应限流(延迟%.1fs 暂停派发): 全程 %.0fs, 峰值延迟 %.1fs, 读陈旧时长 %.0fs%(LOW,ada[0],ada[1],ada[2]))print(理论边界: 主库裸速 %.0fs, 从库回放上限 %.0fs —— 自适应逼近后者, 这就是可陪跑的价格%(TOTAL/MR,TOTAL/AR))print(每块 一个短事务(INSERT 归档表 DELETE 同批行, 同事务保证两份账一致), 块间看延迟脸色决定睡不睡)运行输出固定节奏(每块睡0.5s): 全程 400s, 峰值延迟 200s, 读陈旧(1s)时长 399s 自适应限流(延迟0.2s 暂停派发): 全程 400s, 峰值延迟 1.7s, 读陈旧时长 180s 理论边界: 主库裸速 100s, 从库回放上限 400s —— 自适应逼近后者, 这就是可陪跑的价格 每块 一个短事务(INSERT 归档表 DELETE 同批行, 同事务保证两份账一致), 块间看延迟脸色决定睡不睡两种策略总时长都是 400 秒——因为瓶颈本来就在从库回放速率固定节奏只是把账欠到最后集中爆。区别在形状固定节奏的延迟一路线性爬到 200 秒期间所有从库读都是陈旧数据5 秒熔断线早在第 5 篇就把它打回主库主库平白多扛全部读流量自适应把峰值压在 1.7 秒读陈旧时长少一半。另一个反直觉点删除任务主库很快从来不是好事限流器的目标函数应该是从库延迟上界 业务高峰避让而不是今晚必须删完。实验二表瘦身收尾的在线重建——pt-osc 与 gh-ost 的账删完历史数据表文件还是三百行的体量高水位不降第 1 篇。收尾手段是在线重建pt-osc 与 gh-ost 都走建 ghost 表 → 搬保留行 → 追增量 → RENAME 交换分歧在增量怎么搬。pt-osc 在原表挂三个触发器每笔业务写顺手往 ghost 记一份gh-ost 不碰原表伪装成从库消费 binlog 行事件异步重放到 ghost。骨架相同窗口期那几百万笔业务写的去向就是两案的量级差异算给你看。KEEP,DISCARD100_000_000,200_000_000# 保留新数据, 丢弃历史数据MIGRATION_MIN240# 拷贝窗口(分钟): 100M 行 ÷ 拷贝速率BIZ{insert:5_000_000,update:2_000_000,delete:1_000_000}# 窗口内业务写ROW_KB0.35# 平均行大小copy_opsKEEP biz_opssum(BIZ.values())pt{ghost写入:copy_opsbiz_ops,原表额外写(触发器):biz_ops,ghost额外读(重拷被改块):int(biz_ops*0.18)}gh{ghost写入:copy_opsbiz_ops,原表额外写(触发器):0,ghost额外读(重拷被改块):int(biz_ops*0.18)}print(拷贝主体两案相同: %s 行写入 ghost; 差异在增量搬运%format(copy_ops,,))forname,min((pt-osc,pt),(gh-ost,gh)):extram[原表额外写(触发器)]m[ghost额外读(重拷被改块)]print(%-7s: 原表额外写 %s 行, ghost 额外读 %s 行, 总增量 %s 行(约 %.1fGB 行宽)%(name,format(m[原表额外写(触发器)],,),format(m[ghost额外读(重拷被改块)],,),format(extra,,),extra*ROW_KB/1_048_576))pt_trig_latency0.4# 每笔业务写多一条触发器 insert 的开销(ms)print(\n业务侧观测: pt-osc 窗口内每笔写 %.1fms 触发器开销(8M 笔累计 %.0f 分钟 CPU); gh-ost 业务写路径不变%(pt_trig_latency,biz_ops*pt_trig_latency/1000/60))print(切换动作: pt-osc RENAME 元数据级交换(毫秒, 但需要拿得住 MDL); gh-ost cut-over 锁原表-追平binlog-交换-解锁(默认 max-lag 节流, 秒级停顿可预期))print(失败面: pt-osc 中途有人 ALTER 原表直接报错回滚; 触发器占用列额度, 宽表可能建不下)print( gh-ost 依赖 binlog: 保留时长必须 拷贝窗口 %d 分钟, binlog_row_image 必须 FULL%MIGRATION_MIN)archive_rowsDISCARDprint(\n别忘了归档: ghost 只装留下的 %s 行, 被丢弃的 %s 行不在任何一方的命运里——%(format(KEEP,,),format(archive_rows,,)))print( 要么先跑实验A的分块INSERT归档DELETE把 %s 行搬去归档库, 再触发重建;%format(archive_rows,,))print( 要么重建前在 ghost 定义里保住全部行, 切换后再分块清——两种都要把数据去向写进变更单)lag12.0print(节流联动: gh-ost --max-lag-millis 与实验A同一根线: 从库延迟 %.0fs 时拷贝器自动降速, 业务读从库不受伤%lag)运行输出拷贝主体两案相同: 100,000,000 行写入 ghost; 差异在增量搬运 pt-osc : 原表额外写 8,000,000 行, ghost 额外读 1,440,000 行, 总增量 9,440,000 行(约 3.2GB 行宽) gh-ost : 原表额外写 0 行, ghost 额外读 1,440,000 行, 总增量 1,440,000 行(约 0.5GB 行宽) 业务侧观测: pt-osc 窗口内每笔写 0.4ms 触发器开销(8M 笔累计 53 分钟 CPU); gh-ost 业务写路径不变 切换动作: pt-osc RENAME 元数据级交换(毫秒, 但需要拿得住 MDL); gh-ost cut-over 锁原表-追平binlog-交换-解锁(默认 max-lag 节流, 秒级停顿可预期) 失败面: pt-osc 中途有人 ALTER 原表直接报错回滚; 触发器占用列额度, 宽表可能建不下 gh-ost 依赖 binlog: 保留时长必须 拷贝窗口 240 分钟, binlog_row_image 必须 FULL 别忘了归档: ghost 只装留下的 100,000,000 行, 被丢弃的 200,000,000 行不在任何一方的命运里—— 要么先跑实验A的分块INSERT归档DELETE把 200,000,000 行搬去归档库, 再触发重建; 要么重建前在 ghost 定义里保住全部行, 切换后再分块清——两种都要把数据去向写进变更单 节流联动: gh-ost --max-lag-millis 与实验A同一根线: 从库延迟 12s 时拷贝器自动降速, 业务读从库不受伤选型口径可以背下来高写入热表优先 gh-ost原表零侵入、可控节流、可暂停重试代价是它是个常驻进程、要求 ROW 全镜像 binlog 且保留期覆盖整个窗口pt-osc 赢在无外部依赖、纯 SQL 可做、rename 原子性干脆输在触发器把每笔写放大一次、且中途 DDL 直接翻车。两案共同的硬要求表要有主键或唯一键做分块坐标ghost 需要约一倍的磁盘余量rename 后旧表至少留置一个观察期再删。更根本的解法与变更清单时间序大表订单、流水建表即分区按月 RANGE清理退化为ALTER TABLE ... DROP PARTITION秒级、零复制、空间立即归还——所有分块删除都是在为没分区还债。冷数据有查询需求就上归档库分块删除的目的地是归档实例可低规格、可换引擎别删进无人认领的 binlog。变更单四件套块大小与限流阈值、从库延迟熔断线多少秒暂停/恢复、归档去向与行数对账口径、中途暂停与续跑方案断点已处理的最大主键。避开三样时刻业务高峰、备份窗口第 6 篇的 XtraBackup 与重建抢 IO、切换演练日。删除类变更配守恒对账主键段行数、金额字段总和、归档表行数三者勾稽任一不平立即停手。清理与重建解决的是单实例内的历史包袱。但整个系列反复假设的机房还活着终将被打破当一整个机房断电、网络孤岛、或 Region 级不可用时高可用体系怎么有计划地扛过去而不是碰运气下一篇《数据库高可用与容灾实战9机房级故障演练混沌工程怎么做到可回滚》。参考来源Percona Toolkit Documentationpt-online-schema-changehttps://docs.percona.com/percona-toolkit/pt-online-schema-change.htmlPercona Toolkit Documentationpt-archiverhttps://docs.percona.com/percona-toolkit/pt-archiver.htmlGitHubgithub/gh-osthttps://github.com/github/gh-ostMySQL 8.0 Reference ManualPartitioning of Tableshttps://dev.mysql.com/doc/refman/8.0/en/partitioning.htmlGitHubpercona/percona-toolkithttps://github.com/percona/percona-toolkit本系列已结集为免费专栏数据库高可用与容灾实战从主从复制到机房级演练进阶推荐付费专栏Python 自动化接单实战从脚本到第一单限时 ¥9.9首篇免费试读
![GO [ 泛型 ]](http://pic.xiahunao.cn/yaotu/GO [ 泛型 ])
完全指南：模板、分解与版本管理)
