MySQL 8 RDS(InnoDB)批量插入更新性能优化咨询

我之前帮不少用户处理过类似1亿级表的批量导入+更新场景，你的基础方案思路没问题，但细节上可以调整来大幅缩短耗时，以下是几个经过生产环境验证的最佳实践：

一、拆分INSERT与UPDATE确实会更快 #

你当前用的INSERT ... ON DUPLICATE KEY UPDATE看起来简洁，但底层逻辑是每条记录先尝试插入，主键冲突时再执行更新——这意味着250万条记录每条都要做一次主键索引查找，对于1.2亿行的大表来说，这个索引查找的累积开销非常惊人。

拆分操作的核心是先把冲突记录筛选出来，分阶段处理：

1. 先做批量更新：用临时表和目标表关联，只更新确实存在主键冲突的记录

   UPDATE target_table t
   JOIN source_temp_table s ON t.primary_key = s.primary_key
   SET t.col1 = s.col1, t.col2 = s.col2;
   

   注意：一定要给临时表的主键加索引，否则JOIN时会全表扫描临时表，反而拖慢速度。
2. 再做批量插入：只插入目标表中不存在的记录

   INSERT INTO target_table (col1, col2, primary_key)
   SELECT s.col1, s.col2, s.primary_key
   FROM source_temp_table s
   LEFT JOIN target_table t ON s.primary_key = t.primary_key
   WHERE t.primary_key IS NULL;
   

这种方式的优势是：批量关联操作的索引查找次数远低于逐条尝试插入，尤其是当冲突记录比例低于50%时，速度提升会非常明显；即使冲突比例很高，也能避免“插入失败再更新”的无效开销。

二、InnoDB不能直接禁用索引，但有替代方案 #

MyISAM的ALTER TABLE ... DISABLE KEYS对InnoDB完全无效（只是个空操作，不会实际停止索引维护），但我们可以通过临时删除非主键索引来达到类似的优化效果：

1. 操作前删除目标表的所有非主键索引：

   ALTER TABLE target_table
   DROP INDEX idx_col1,
   DROP INDEX idx_col2;
   

2. 完成导入+更新后，再重建这些索引：

   ALTER TABLE target_table
   ADD INDEX idx_col1(col1),
   ADD INDEX idx_col2(col2);
   

为什么这么做？因为InnoDB在插入/更新时，需要维护所有索引的B+树结构，删除非主键索引后，只需要维护主键索引，能大幅降低IO和CPU开销。虽然重建索引需要时间，但总耗时通常比带着所有索引操作要短30%-70%，具体取决于索引数量和大小。

⚠️ 注意：主键索引不能删除（我们依赖它做冲突检查），而且MySQL 8.0的Online DDL支持索引的删除和重建，不会长时间锁表，RDS实例上可以放心操作，建议选业务低峰期执行。

三、其他锦上添花的优化点 #

1. 优化临时表导入速度：用LOAD DATA INFILE替代批量INSERT导入CSV，速度能快5-10倍。RDS上需要先开启local_infile参数，然后执行：

   LOAD DATA LOCAL INFILE '/path/to/your/data.csv'
   INTO TABLE source_temp_table
   FIELDS TERMINATED BY ',' ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   IGNORE 1 ROWS;
   

   也可以把CSV上传到S3，用RDS原生的导入功能，更适合大文件。
2. 临时调整InnoDB参数：
   • 增大innodb_buffer_pool_size：设到实例内存的70%-80%，让更多数据缓存到内存，减少磁盘IO
   • 把innodb_flush_log_at_trx_commit设为2：临时关闭严格的事务日志刷盘，操作完改回1
   • 开启innodb_autoinc_lock_mode=2：优化批量插入的自增锁逻辑
     这些参数可以通过RDS的参数组临时调整，大部分不需要重启实例。
3. 分批次处理：如果250万条数据还是太大，可以把临时表分成多个10万-50万条的小批次，分阶段执行更新和插入，避免一次性占用过多资源导致锁表或IO瓶颈。
4. 关闭触发器：如果目标表有触发器，临时关闭它（ALTER TABLE target_table DISABLE TRIGGER ALL;），操作完再开启，避免触发器带来的额外开销。

总结最优流程 #

1. 创建和目标表结构一致的临时表，添加主键索引
2. 用LOAD DATA INFILE快速导入CSV到临时表
3. 临时删除目标表的非主键索引、关闭触发器，调整InnoDB参数
4. 执行批量UPDATE关联临时表更新目标表
5. 执行批量INSERT插入不存在的记录
6. 重建目标表的非主键索引，恢复触发器和InnoDB参数

内容的提问来源于stack exchange，提问作者Mike de H