1. 先厘清一件事这两个命令到底差在哪DELETE和TRUNCATE的区别几乎每本入门书都会写上一句“一个删行、一个清表”然后就翻篇了。可真正在生产上出过事的人都清楚这句话的信息量约等于零。我在实际工作里见过的两类典型工单几乎都源于没弄清这两个命令一类是业务方要清空一张日志表开发图省事写了DELETE FROM log_table跑了四十分钟没跑完从库延迟涨到两小时报警响了一整夜另一类更省事直接上TRUNCATE可这张表被别的表外键引用着语句报错运维临时改脚本又踩了锁。这两件事的共同点是大家对“删除”这件小事的心理预期远远低于它在数据库内部的实际成本。我把话说直白一点DELETE是 DML它做的事情叫“把某几行标记成不可见并把旧值抄一份留档”本质上是可撤销的数据修改TRUNCATE是 DDL它做的事情叫“把整张表的物理载体换掉”本质上是结构层面的重置。前者像你在账本上一笔一笔划掉记录每划一笔都要留底稿后者像你直接把整本账本换个新的旧的那本丢进碎纸机。同样叫删除走的根本不是同一条路。这篇文章想解决的就是“知道区别”到“敢在生产上动手”之间那一段。它适合三类人写业务代码的后端同学你写的每一行DELETE都会在某个深夜变成 DBA 的工单刚接手数据库运维的同学你需要一套能直接抄的执行流程还有做数据清理脚本、归档任务的同学你们面对的往往是千万级、亿级的大表删法选错代价是按天算的。我会把底层机制、影响范围、分批策略、参数计算、踩坑排查这几块拆开讲涉及具体参数的地方都会给出估算过程方便你按自己的表结构换算。提醒下文所有示例都是通用写法不同数据库版本的行为存在差异MySQL 与 PostgreSQL 的差别我在关键处会单独标注。上线前一定要在预发环境用同量级数据验证别拿生产库当试验田。1.1 三条暗线日志、锁、空间想彻底理解这两个命令盯着语法没用得看它们各自触发了哪几条“暗线”。我把这三条线列出来后面所有分析都围绕它们展开。第一条是日志线。DELETE每删一行都要写 undo 记录旧值留档供回滚和一致性读使用、写 redo 日志保证崩溃可恢复如果开了行格式的二进制日志还要把每一行的前镜像和后镜像都记进去。一张五千万行的表一次DELETE就是五千万份这样的记录。TRUNCATE呢它只产生一条 DDL 语句的日志无论表里是一行还是一百亿行日志量几乎是常数。这个差异直接决定了 binlog 体积、磁盘水位和主从同步的负担。第二条是锁线。DELETE加的是行级锁锁的是它碰到的那些行具体锁范围还跟索引、隔离级别有关。好处是并发友好坏处是删得越多、锁越多锁等待和死锁概率呈线性上升。TRUNCATE加的是元数据层面的排他锁也就是整张表的“施工围挡”。它不会一行一行地锁但它需要等所有正在访问这张表的事务结束而且一旦它开始排队后面所有想访问这张表的请求都会被堵在它后面这叫元数据锁队列堆积很典型的一类雪崩。第三条是空间线。这是最容易被忽略、也最容易让人产生“删了怎么没变小”困惑的一条。DELETE删掉的行在 InnoDB 里只是被标记成删除页还在空间还在表空间文件里只是变成了可复用的空闲页。所以你会看到表数据只剩一千万行文件还是 50GB。TRUNCATE则直接把表空间换新文件立刻回到初始大小。1.2 影响范围为什么在大表上被放大单看这三条线小表上几乎无感删一千行毫秒级谁也不会在意。但成本是行数乘以每行开销这个乘法在大表上会变得非常残酷。举个具体的量级感受一张两亿行的表平均行长 250 字节数据大约 50GB加上两三个二级索引可能到 70GB。用DELETE全删会产生数十 GB 的 undo、上百 GB 的 binlog行格式执行时间从几十分钟到几小时不等期间主库 IO 打满、从库延迟持续增长、磁盘水位告警。用TRUNCATE元数据操作本身可能几百毫秒就完成了但背后释放几十 GB 文件、清理缓冲池里对应的页是另一笔账我在第 3 章会细说。所以“大表删除影响”这个说法其实不准确——影响不是删除这个动作带来的是删除方式与表的体量不匹配带来的。选对方式大表删除可以很平稳选错方式一张几千万行的表就足以让整个集群抖三抖。2. 机制拆解同样叫删除内部走了两条完全不同的路2.1 DELETE 的逐行旅程一条DELETE FROM t WHERE id 100在 InnoDB 里要走完这么一串动作通过聚簇索引定位到目标行如果WHERE条件没走索引那就是全表扫描这是第一个性能陷阱对定位到的记录加排他锁如果条件列没索引在可重复读隔离级别下可能退化成锁住扫描过的所有记录甚至间隙把这一行的旧值写进 undo 日志这份 undo 既是回滚的依据也是一致性读MVCC时其他事务看到旧版本的来源在聚簇索引的记录头上打一个删除标记位记录并没有立刻物理消失写 redo 日志保证崩溃后能重放事务提交时配合两阶段提交把变更写进 binlog。注意第 4 步打标记不等于真删除。真正的物理清理是后台 purge 线程异步做的它要判断这行还有没有更早的读视图在引用。如果此时有一个长事务一直不提交purge 就会被卡住undo 只增不减历史版本链越拉越长这就是所谓的 purge 滞后。information_schema.INNODB_TRX里的trx_started和SHOW ENGINE INNODB STATUS里的 History list length是观察这个问题的两个关键指标。删得越多这条链路上的每一环都被放大undo 表空间膨胀、purge 线程追不上、二级索引的清理滞后、binlog 暴涨。而且DELETE是逐行处理并持锁的一个包含几百万行删除的大事务会长时间持有大量行锁其他业务的更新请求全部排队。还有一个常被忽视的点DELETE会触发BEFORE DELETE/AFTER DELETE触发器也会触发外键的级联删除动作。这两样东西在大批量删除时是纯负担尤其级联删除它会把成本扩散到你没预料到的其他表上。2.2 TRUNCATE 的换页逻辑TRUNCATE TABLE t在很多数据库里的实现思路是“丢弃旧载体造一个新的”。以 MySQL 的 InnoDB 为例它的本质接近DROP TABLE加CREATE TABLE清理掉原来的段和表空间文件按原表定义重建一个空表自增值重置回起点统计信息重新生成。语句返回的影响行数通常是 0因为引擎根本没去数删了多少行——它压根没删行。这里有几个必须记住的副作用不可回滚。它是 DDL执行即刻隐式提交事务包不住它。你在事务里写TRUNCATE等于在事务中间强制提交了一次前面的操作也一起落地了。不触发 DELETE 触发器。依赖触发器做审计、做同步的逻辑会静默失效这类坑非常隐蔽。外键限制。MySQL 里被外键引用的表无法TRUNCATE必须先处理引用关系这是它和DELETE最硬的行为差异之一。权限要求更高。需要 DROP 级别的权限很多只给了增删改权限的业务账号根本执行不了这其实是一道有价值的保护。锁的形态不同。它要拿元数据排他锁必须等待所有引用该表的活跃事务结束。一旦有慢查询正在扫这张表TRUNCATE就会无限期排队而它后面排着的所有请求也一起被堵住。PostgreSQL 的行为又有区别这点必须单独说。PostgreSQL 的TRUNCATE是可以放进事务里回滚的这常让从 MySQL 转过来的同学很意外它拿的是ACCESS EXCLUSIVE锁同样会阻塞所有访问它支持CASCADE级联清理被外键引用的子表也支持RESTART IDENTITY重置序列它不触发ON DELETE触发器但会触发ON TRUNCATE触发器物理实现上是给表换一个新的 relfilenode旧文件由后台清理空间立刻从表本身释放。2.3 一张表看清全部差异维度DELETETRUNCATE语句类型DMLDDL是否可回滚可以MySQL/PG 均可MySQL 不可PostgreSQL 可以是否支持条件支持 WHERE只能整表日志量与行数成正比近乎常数锁形态行锁范围受索引与隔离级别影响元数据排他锁 / 表级排他锁触发器触发 DELETE 触发器不触发 DELETE 触发器自增值保留重置空间释放不释放仅标记可复用立即释放外键引用表可执行可能触发级联MySQL 不可PG 需 CASCADE执行速度与行数、索引数成正比与行数基本无关主从影响binlog 按行记录体积巨大一条 DDL体积很小看这张表能得出一个很实用的判断当你需要“条件”、需要“可回滚”、需要“触发器联动”时只能选DELETE当你需要“整表清空且快”时选TRUNCATE但要接受它所有的副作用。真正困难的不是这两种情况而是“按条件删掉表里 80% 的数据”这种中间态——DELETE太慢TRUNCATE又不满足条件这才是生产上最常见的难题。3. 大表删除的真实影响慢只是最轻的那个问题3.1 主从延迟是被什么拉长的主从延迟的根源在于“从库只能串行重放同一个事务”。行格式的 binlog 里DELETE影响多少行就有多少条行变更事件。一个事务删一千万行从库就得把这一个事务完整重放完才算数中间不能并行。多线程复制虽然能按库或按逻辑时钟并行但对单个大事务无能为力。于是主库几十分钟跑完从库可能要跑更久——因为从库往往还承担着大量读请求IO 资源本就不富裕。延迟的后果不只是报表数据旧。读写分离的架构里刚删掉的数据在从库还能读到业务方会以为删除失败反复重试延迟超过阈值触发报警运维介入排查又是一轮沟通成本。更极端的是如果删除期间 binlog 把磁盘写满整个实例会进入只读状态那就不是延迟的问题了是在线故障。TRUNCATE在这一点上优势明显binlog 里就是一条语句从库重放极快。但要注意从库执行 DDL 时同样需要拿排他锁如果从库上有长查询这个 DDL 也会排队延迟照样会涨——只是涨的原因从“重放慢”变成了“等锁”。3.2 锁等待与连接池雪崩批量DELETE期间如果删除条件没有走索引事情会变得非常难看。没有索引意味着全表扫描而扫描过程中碰到的每一行都可能被加锁可重复读隔离级别下扫过的记录都会加锁包括不满足条件的效果接近锁表。这时候其他业务对这个表的任何写操作都会阻塞连接池里的连接一个个被占住超时、重试、再占住很快就满了。应用层的表现是接口大面积超时而数据库层面只是“有个删除任务在跑”。死锁也是这个阶段的高发问题。当两个不同方向的批量删除脚本同时运行或者删除脚本与业务更新语句交叉持锁间隙锁的存在会让死锁概率显著上升。我在实际中见过的最典型场景是两个清理任务按不同顺序扫同一张表互相等待数据库选一个回滚脚本没做重试就直接挂了留下删了一半的数据。一条硬经验任何在生产上跑的批量删除WHERE条件必须命中索引且执行前必须用EXPLAIN确认。这一条能挡掉八成的锁问题。3.3 空间不释放与表膨胀“我把一亿行删了为什么磁盘没变”这个问题我被问过太多次。答案是DELETE释放的是页内的行槽位这些页变成空闲页留在表空间里供后续插入复用。表文件不会缩小甚至可能因为页分裂产生碎片而略微变大。MySQL 想真正回收空间得重建表OPTIMIZE TABLE或者ALTER TABLE ... ENGINEInnoDB本质都是把数据导一遍再换回去。这个操作需要额外的磁盘空间大约等于表大小、需要拿排他锁或走在线 DDL 流程执行时间也不短。生产上更稳妥的做法是用第三方在线变更工具或者干脆在低峰期做并预留足够的磁盘。PostgreSQL 类似普通VACUUM只把空间标记为可复用VACUUM FULL才真正重写文件而它会持排他锁大表上基本等于停服所以更多人改用第三方重打包工具。还有一个连带影响表膨胀之后全表扫描要读的页数没减少反而可能因为碎片变多而变慢。也就是说删除并没有让查询变快反而可能变慢。这一点在做容量规划时必须提前算进去。3.4 undo、临时空间与磁盘水位MySQL 里大事务删除最危险的连带效应是 undo 表空间膨胀。undo 是为了支持回滚和一致性读一个删除一亿行的事务undo 里就要留一亿行的旧值几十 GB 起步。如果磁盘余量不足直接写满实例进入只读。哪怕没写满purge 线程在事务提交后还要花很长时间清理这段时间 history list length 居高不下所有查询的一致性读都要沿着版本链往回找读性能整体下滑。磁盘水位还要算上 binlog。行格式下删除一亿行的 binlog 体积可能和表数据本身一个量级。如果 binlog 保留策略是七天这一波删除的影响会持续一周。所以在动手之前我会把这几项算清楚本次删除涉及多少行、平均行长多少粗略估算 undo 与 binlog 的量级当前磁盘余量是否大于“预估增量 × 2”binlog 保留天数与日均增量是否匹配从库延迟阈值是多少超过后脚本能否自动暂停。这四项里任何一项没底就不要在生产上开跑。4. 实操方案四种场景四套打法4.1 场景一整表清空TRUNCATE 的正确打开方式整表清空看起来最简单但恰恰是最需要前置检查的。我通常会按这个顺序走一遍-- 1. 确认表的基本情况行数、大小、索引 SELECT table_rows, data_length, index_length FROM information_schema.tables WHERE table_schema your_db AND table_name your_table; -- 2. 确认外键引用关系MySQL SELECT table_name, column_name, constraint_name FROM information_schema.key_column_usage WHERE referenced_table_schema your_db AND referenced_table_name your_table; -- 3. 确认没有长事务在跑 SELECT trx_id, trx_state, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec FROM information_schema.innodb_trx ORDER BY trx_started; -- 4. 确认没有慢查询占着这张表 SELECT id, user, time, state, LEFT(info, 80) AS sql_head FROM information_schema.processlist WHERE info LIKE %your_table%;四步都干净了再执行。执行时建议先设一个锁等待超时避免无限期排队把后面的请求全堵住SET SESSION lock_wait_timeout 10; TRUNCATE TABLE your_table;lock_wait_timeout的单位是秒设成 10 意味着如果十秒内拿不到元数据锁就放弃。这个值比默认的几万秒友好得多——宁可报错重试也不要把整张表的访问入口堵死。还需要提醒三点。第一TRUNCATE之后自增值重置如果业务依赖自增 ID 的单调性做排序或分页行为会变。第二如果这张表上有审计触发器TRUNCATE不会触发需要单独补记录。第三如果 MySQL 版本较老TRUNCATE在表上有外键引用时会直接报错这个报错信息有时不够直白容易被误判成权限问题。4.2 场景二按条件删掉大部分数据分批 主键游标这是最难的场景也是实际工作中遇到最多的。表里一亿行要删掉八千万WHERE create_time 2024-01-01。这时候直接DELETE是自找麻烦TRUNCATE又不满足条件。标准解法是分批并且用主键游标而不是LIMIT偏移。为什么不用LIMIT offset, size因为偏移量越大数据库要跳过的行越多越到后面越慢而且跳过的过程中还可能持有不必要的锁。主键游标法的思路是“记住上次处理到哪个主键”每次从那个位置往后取一批索引定位成本恒定。MySQL 可以直接用DELETE ... LIMIT-- 每批 2000 行循环执行直到影响行数为 0 DELETE FROM your_table WHERE create_time 2024-01-01 ORDER BY id LIMIT 2000;注意这里加了ORDER BY id目的是让删除顺序与主键索引一致减少随机 IO。如果create_time上有索引优化器可能选择走create_time索引效果也可以但一定要EXPLAIN确认最怕的是它退化成全表扫描加LIMIT——那就变成每批都扫一遍全表。PostgreSQL 不支持DELETE ... LIMIT要用 CTE 包一层WITH batch AS ( SELECT ctid FROM your_table WHERE create_time 2024-01-01 LIMIT 2000 ) DELETE FROM your_table t USING batch b WHERE t.ctid b.ctid;ctid是物理行位置用它可以避免对主键的额外依赖但要注意ctid会因为更新或 vacuum 而变化所以这批和下一批之间不能有并发的更新操作否则可能漏删或重复处理。更保险的写法是用主键范围DELETE FROM your_table WHERE id IN ( SELECT id FROM your_table WHERE create_time 2024-01-01 ORDER BY id LIMIT 2000 );外层脚本控制循环节奏这是决定成败的关键部分。用 Python 写个骨架把延迟监控和自动刹车都放进去import time import pymysql BATCH 2000 MAX_LAG 30 # 从库延迟上限秒 SLEEP_BETWEEN 0.05 # 每批之间喘口气 conn pymysql.connect(hostprimary, usercleaner, password***, databaseyour_db, autocommitTrue) cur conn.cursor() while True: affected cur.execute( DELETE FROM your_table WHERE create_time 2024-01-01 ORDER BY id LIMIT %s, (BATCH,) ) if affected 0: break # 每 10 批检查一次从库延迟 lag cur.execute(SHOW REPLICA STATUS) # 实际要取字段值 if lag and lag MAX_LAG: print(延迟过高暂停 60 秒) time.sleep(60) else: time.sleep(SLEEP_BETWEEN)这个骨架的核心思想是自我限速数据库不紧张就快跑从库跟不上了就主动暂停。生产上跑清理任务宁可慢两小时也不要让监控响一晚上。心得分批删除的脚本一定要做成幂等且可断点续跑的。因为绝大多数情况下你都会中途停下来延迟高了、业务高峰到了、要发版了如果脚本不能从中断处接着跑你会被迫重来。4.3 场景三只删一小部分数据把索引这件事做对如果只删几千行通常不需要分批但索引必须确认。几个典型问题WHERE里对索引列用了函数比如WHERE DATE(create_time) 2024-01-01索引直接失效变成全表扫。正确写法是范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02。隐式类型转换同理。字符类型的列用数字去比较或者反过来都可能让索引失效。这种情况在大表上尤其致命因为一次全表扫可能要几十秒期间扫过的行都被加锁。还有一种容易被忽略的WHERE命中的行数占比很高时优化器可能主动放弃索引走全表扫。比如你要删掉表里 40% 的行即使用了索引优化器算下来也觉得全表扫更便宜。这种情况下分批反而能让优化器稳定走索引——因为每批的LIMIT很小走索引的成本明显更低。这也是“大比例删除必须分批”的另一个理由。执行前统一做这三件事-- 1. 看执行计划type 不能是 ALLkey 不能是 NULL EXPLAIN DELETE FROM your_table WHERE create_time 2024-01-01; -- 2. 先数一遍要删多少心里有底 SELECT COUNT(*) FROM your_table WHERE create_time 2024-01-01; -- 3. 事务里先跑一遍确认无误再提交小批量适用 START TRANSACTION; DELETE FROM your_table WHERE id 12345; -- 核对影响行数和数据状态后 COMMIT;4.4 场景四分区表把删除变成元数据操作如果一张表需要长期反复做“按时间清理”最值得投入的优化是改造成分区表。分区表的DROP PARTITION是纯元数据操作无论分区里有多少行耗时都在毫秒级不产生行级日志不触发逐行删除对主从的影响也极小。这是大表清理的最优解没有之一。建表示例按范围分区CREATE TABLE log_table ( id BIGINT NOT NULL AUTO_INCREMENT, biz_id BIGINT NOT NULL, create_time DATETIME NOT NULL, content VARCHAR(512), PRIMARY KEY (id, create_time), KEY idx_create_time (create_time) ) ENGINEInnoDB PARTITION BY RANGE (MONTH(create_time)) ( PARTITION p202401 VALUES LESS THAN (2), PARTITION p202402 VALUES LESS THAN (3), PARTITION p202403 VALUES LESS THAN (4), PARTITION pmax VALUES LESS THAN MAXVALUE );清理时直接丢掉整个分区ALTER TABLE log_table DROP PARTITION p202401;这里有个硬性约束必须提前说清楚分区键必须包含在主键或唯一键里否则建表就报错。上面示例里主键写成(id, create_time)就是这个原因。这个约束会带来的代价是主键不再是单列基于id的单条查询依然没问题但如果有其他唯一约束需要一并调整。另外一个坑是MAXVALUE分区。如果建表时留了pmax兜底新数据都会落进去你需要定期把它REORGANIZE成具体分区否则清理脚本删不到它。我一般建议在定时任务里加一步每月检查pmax里有没有数据有就拆分。分区裁剪是另一件要验证的事。加完分区后业务侧的查询必须能命中裁剪也就是WHERE里带上分区键否则会扫全部分区性能反而比不分区的表更差。改造分区表之前务必把业务侧所有涉及这张表的 SQL 捞出来看一遍。4.5 参数怎么定批次大小与耗时估算批次大小不是拍脑袋定的我给一个可复现的算法。设表有 N 行平均行长 B 字节删除比例 r目标单批耗时不超过 T建议 0.2 秒到 0.5 秒。第一步估算单行删除的实际开销。DELETE的成本主要在三块索引定位、undo 写入、binlog 写入。经验上一行删除的实际 IO 开销大约是行长的 2 到 3 倍含索引。假设 B 250 字节单行开销约 600 字节。第二步用实测校准。拿预发环境同结构的表跑DELETE ... LIMIT 1000看耗时。假设实测 1000 行耗时 80 毫秒即单行 0.08 毫秒。第三步反推批次。目标是单批 T 0.3 秒则批次大小约为300 / 0.08 ≈ 3750取整到 2000 或 3000 更稳妥留出抖动余量。第四步估算总耗时。总行数N × r 8000 万批次 2000共 4 万批。每批 0.2 秒纯执行时间约 2.2 小时加上每批之间的 sleep0.05 秒和延迟触发的暂停实际按 3 到 4 小时规划。这个量级必须安排在业务低峰期并且提前和业务方对齐。第五步估算资源占用。undo 的峰值取决于单批事务大小2000 行 × 600 字节 ≈ 1.2MB很小这是分批的另一个好处——把大事务拆成小事务undo 压力几乎消失。binlog 增量约等于总行数 × B ≈ 20GB需要提前确认 binlog 保留策略和磁盘余量。这张估算表我一般会写进变更单不是为了走流程而是为了在出问题时能快速判断“是不是超预期了”项目估算值实测值备注涉及行数8000 万待填先 COUNT 确认批次大小2000待填预发实测校准单批耗时0.2 秒待填超 0.5 秒要降批次总耗时3 到 4 小时待填含暂停时间binlog 增量约 20GB待填看磁盘余量从库延迟峰值 30 秒待填超阈值自动暂停5. 常见问题与排查实录5.1 删完了磁盘为什么没降这是最高频的问题。MySQL 里先确认三件事表空间是不是独立表innodb_file_per_table、有没有正在跑的长事务卡住 purge、以及是不是只删了部分行。如果确认是空闲页未回收那就得重建表-- 查看表的空闲空间近似值 SELECT table_name, data_length / 1024 / 1024 AS data_mb, data_free / 1024 / 1024 AS free_mb FROM information_schema.tables WHERE table_schema your_db AND table_name your_table; -- 重建表回收空间执行期间会占用额外磁盘并影响写入 ALTER TABLE your_table ENGINEInnoDB;重建表前必须确认磁盘余量大于表大小并且安排在低峰期。大表上这个操作可能跑几小时中途失败会留下临时文件需要手工清理。PostgreSQL 里普通VACUUM不会缩小文件只标记可复用。要真正缩小得用VACUUM FULL但它持排他锁实操中更常见的方案是用第三方在线重打包工具或者干脆接受“文件不缩、空间复用”的事实把监控指标从“磁盘占用”改成“可用空间比例”。5.2 TRUNCATE 卡住不动了TRUNCATE挂住几乎只有一个原因拿不到元数据锁。它需要等所有持有该表元数据锁读锁的事务结束而这其中可能包括一个跑了半小时的报表查询或者一个忘了提交的会话。排查路径很固定-- MySQL 8.0直接看元数据锁等待 SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_NAME your_table; -- 配合进程列表找出阻塞源 SELECT id, user, host, db, command, time, state, LEFT(info, 100) FROM information_schema.processlist WHERE time 60 ORDER BY time DESC;找到阻塞的长事务后要么等它结束要么联系对应业务方确认能否终止。切忌直接KILL掉不认识的会话——那可能是一个正在做数据迁移的任务杀掉会造成数据不一致。还有一个隐蔽情况TRUNCATE本身执行完了但客户端还在等。这是因为大表空间释放涉及缓冲池里对应页的清理元数据层面已完成物理清理在后台继续。此时观察到的“卡住”其实是文件系统和 IO 在忙不用干预等着就好。5.3 误删之后怎么办先说结论DELETE误删还有救TRUNCATE误删基本只能靠备份。DELETE的救援路径有两条。第一条是 binlog 反向解析把删除语句对应的行变更事件反转成插入生成回滚脚本。前提是 binlog 格式为行格式且保留期覆盖了误删时间点这也是我坚持所有生产实例必须用行格式的原因之一。第二条是备份加时间点恢复恢复到误删前的某个时刻再把缺的数据导回原库。TRUNCATE没有第二条路之外的选项因为它是 DDL不产生行级变更记录binlog 里只有一条语句没有“被删掉的数据长什么样”这个信息。所以我的做法是凡是允许执行TRUNCATE的账号必须和业务账号分离且执行前强制走一次备份确认。这个流程听起来繁琐但它拦住的是那种“本来只想删测试数据结果连到了生产库”的事故。5.4 问题速查表现象可能原因快速验证处理方向删除跑得极慢WHERE 未命中索引全表扫EXPLAIN看 type加索引或改条件写法从库延迟持续增长大事务 binlog 无法并行重放看延迟曲线与事务大小改分批缩小事务业务写入大面积超时行锁等待或元数据锁排队看innodb_trx与锁等待停脚本降批次错峰删除后磁盘未降空间仅标记可复用看data_free字段低峰期重建表查询变慢表碎片增多页密度下降对比删除前后扫描行数重建表或重新统计TRUNCATE 无响应元数据锁被长事务占用看metadata_locks定位并处理长事务自增 ID 突然跳变TRUNCATE 重置或批量删除后重启看表状态业务侧不要依赖连续性undo 表空间暴涨大事务 purge 滞后看 history list length拆事务检查长事务6. 上线前的检查清单和几点个人体会6.1 一份可以直接抄的检查清单我把这些年踩过的坑浓缩成一张清单任何一次生产删除都过一遍能挡掉绝大多数事故删除方式是否与数据量匹配全清用TRUNCATE大比例有条件删除用分批小比例确认走索引WHERE条件是否命中索引EXPLAIN的 type 是否为ALL是否已COUNT确认影响行数并与业务方核对数量级是否存在外键引用是否需要处理级联是否有触发器依赖TRUNCATE会不会让它静默失效当前是否有长事务、慢查询占用目标表磁盘余量是否大于预估增量两倍binlog 保留期是否覆盖影响窗口从库延迟阈值是否明确脚本是否有自动暂停是否已备份恢复路径是否验证过不只是“有备份”而是“演练过恢复”执行账号是否为专用账号权限是否最小化是否安排在低峰期是否有回滚或中止方案执行过程中谁在盯着异常时找谁。这张清单里我认为最容易被跳过、也最该保留的是第九条。很多团队有备份但从来没验证过备份能不能恢复真出事的时候才发现备份文件不完整或者恢复流程跑不通。6.2 我自己的几条经验第一条能不动数据就别动数据。清理历史数据这件事最优雅的方案永远是分区表加DROP PARTITION其次是归档到冷存储再从主表删。如果业务上只是“不查了”加个状态位过滤往往比物理删除划算得多——虽然这会把技术债推给查询侧但至少不会在某个深夜炸掉。第二条任何删除脚本都要先在小表上跑通全流程。我会在一个几百万行的影子表上完整跑一遍看耗时曲线、看延迟变化、看资源占用然后把批次大小和 sleep 参数照搬到生产。直接在生产上调参等于拿真实业务做实验。第三条把“删除”当成一次发布来管理。有变更单、有预演、有监控、有回滚方案、有明确的结束标志。我见过太多事故是因为“我就删一下很快的”这种心态——恰恰是这种轻敌让人忘了检查外键、忘了确认连的是哪个库。第四条关于TRUNCATE的权限我的建议是业务账号一律不给。这不是不信任同事而是这个命令的破坏力和它的执行速度完全不匹配一秒执行完几小时恢复不了。把权限收归到少数几个人手里加上一条“执行前必须先备份”的硬规矩成本极低收益极高。最后分享一个排查小技巧当你怀疑某个删除操作影响了性能但又不确定是哪一条 SQL去看performance_schema里按行数排序的语句统计或者直接对比操作前后的Com_delete计数差。定位到具体语句之后再去看它的执行计划和锁等待比盲目猜测高效得多。
