MySQL大表区间删除与精确ID匹配删除效率对比及优化咨询

方案可行性结论 #

你提出的临时表关联删除方案不会更快，效率远低于当前的区间删除方案，完全不推荐使用。
原因如下：

• 存储3亿条待删id到临时表本身就需要消耗大量磁盘IO、内存空间，额外增加了不必要的性能开销
• DELETE FROM table_name WHERE id IN (SELECT id FROM _tmp_table_name) 这类查询在子查询返回数据量极大的情况下，MySQL优化器大概率无法生成最优执行计划，甚至可能放弃走主键索引转而执行全表扫描，性能会比当前基于主键的范围删除差很多。

当前删除效率低的核心原因 #

你现在每次删除100万行的批量设置过大，是耗时过长的核心原因：

• 单批次100万行对应的事务过大，会产生海量的undo日志、binlog日志，提交事务时需要刷入大量数据到磁盘，而你的实例是db.r3.large，仅15.25GB内存、2vCPU，IO性能上限很低，大事务会直接打满IOPS，导致后续操作阻塞
• 每次删除大量数据后，MySQL需要同步维护表上的所有二级索引，大批次删除对应的索引维护开销是集中爆发的，进一步拉长了单次删除的耗时

优化方案 #

方案一：在现有删除逻辑上调整，无业务侵入，无需停机 #

• 把单次删除的行数从100万下调到 1000~5000行，大幅减小事务粒度，平摊IO和索引维护开销
• 调整删除语句逻辑，固定id范围避免重复扫描：

  DELETE FROM table_name WHERE id >= {当前批次起始id} AND id < {当前批次起始id + 步长} AND id < 1762163840 LIMIT 5000;
  

  每次执行完更新当前批次的起始id即可，全程走主键索引，执行效率更高
• 每批次删除完成后添加0.1~0.5秒的休眠，给RDS的IO预留缓冲空间，避免长时间占满IOPS影响实例正常运行
• 临时调整RDS参数（删除完成后记得改回原有配置）：
  • 把innodb_flush_log_at_trx_commit设为2，sync_binlog设为1000，能大幅降低日志刷盘的开销，提升删除速度
  • 如果有非必要的二级索引，可以先删除，待数据清理完成后再重建，索引重建的开销远低于删除过程中逐行维护索引的开销

方案二：速度最快，适合能接受短时间业务停写的场景 #

你需要保留的7亿行数据占总数据量的70%，可以通过表替换的方式完成清理：

1. 新建一张和原表结构完全一致的空表
2. 把2021年及之后的7亿行数据批量导入新表，导入是顺序写操作，性能远高于随机删除
3. 锁定原表禁止写入，同步删除过程中产生的增量数据到新表
4. 执行RENAME TABLE 原表 TO 原表_old, 新表 TO 原表完成切换
5. 验证业务正常后删除旧表即可
   这种方案整体耗时可以控制在几小时到1天以内，远快于逐条删除的方案。

内容的提问来源于stack exchange，提问作者shekhar