如何提升MySQL 5.7 1:N关联表场景下的数据插入速度

针对你提出的MySQL 5.7 1:N关联表批量导入性能问题，以下是可落地的优化方案，核心逻辑是将逐行单条插入改为批量操作，减少数据库IO交互和事务提交次数：

方案1：临时关联键批量映射（兼容性最好，无并发限制） #

无需限制导入期间的其他表操作，适配所有场景：

• 步骤1：准备关联标识。如果导入的表a原始数据自带全局唯一业务键，可直接作为关联键；没有的话给表a新增临时关联列：

  ALTER TABLE a ADD COLUMN temp_uniq_key VARCHAR(100);
  

• 步骤2：批量插入表a记录，插入时带上对应原始数据的唯一标识：

  INSERT INTO a (name, temp_uniq_key) VALUES 
  ('a1', 'tmp_1'),
  ('a2', 'tmp_2'),
  -- 单次批量可插入1000~5000条
  ('a1000', 'tmp_1000');
  

• 步骤3：一次查询获取自增ID映射关系，无需逐行查询：

  SELECT id, temp_uniq_key FROM a WHERE temp_uniq_key IN ('tmp_1', 'tmp_2', ..., 'tmp_1000');
  

• 步骤4：根据映射关系补全所有表b记录的a_id字段，批量插入表b：

  INSERT INTO b (a_id, name) VALUES
  (1, 'b1'),
  (1, 'b2'),
  (2, 'b3'),
  -- 一次性插入该批次所有关联的b记录
  (1000, 'b2000');
  

• 步骤5：导入完成后可删除临时关联列（可选）

  ALTER TABLE a DROP COLUMN temp_uniq_key;
  

方案2：利用连续自增ID特性（性能最高，要求无并发写入表a） #

如果导入过程中没有其他业务同时写入表a，可利用MySQL批量插入自增ID连续分配的特性，省略查询映射步骤，速度最快：

• MySQL默认配置innodb_autoinc_lock_mode=1下，同一会话批量插入的记录自增ID是连续的，2559258会返回批量插入第一条记录的自增ID
• 操作步骤：
  1. 按固定顺序批量插入N条表a记录
  2. 执行SELECT 2559258获取起始ID记为start_id
  3. 按插入a的顺序直接匹配ID：第1条a对应ID为start_id，第2条为start_id+1，以此类推，直接补全对应b记录的a_id
  4. 批量插入所有表b记录
• 注意：该方案仅适用于导入期间表a无其他写入操作的场景，否则会出现ID匹配错误

通用导入优化配置 #

不管用哪种方案，都可以配合以下配置进一步提升速度：

• 导入期间关闭自动提交和外键检查，所有操作完成后再恢复：

  SET autocommit = 0;
  SET FOREIGN_KEY_CHECKS = 0;
  -- 执行完所有批次导入后执行
  COMMIT;
  SET FOREIGN_KEY_CHECKS = 1;
  SET autocommit = 1;
  

• 按批次提交事务，建议每批次处理1000~5000条a记录+对应的b记录，避免单事务过大
• 导入前先删除表b的非主键索引、触发器，导入完成后再重建
• 适当调大MySQL参数bulk_insert_buffer_size、innodb_buffer_pool_size，提升批量写入性能

百万级以上大数据量方案 #

如果数据量超过百万级别，可直接用LOAD DATA INFILE导入，比批量INSERT速度快3~10倍：

1. 先把所有a数据导出为CSV文件，包含临时关联键
2. 用LOAD DATA INFILE批量导入表a
3. 导出a的id和临时关联键的映射CSV，处理b的CSV文件补全a_id
4. 用LOAD DATA INFILE批量导入表b

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