大批量Excel导入MySQL时多表插入更新的SQL性能优化咨询

一、Excel数据预处理优化 #

• 分块读取Excel内容：不要一次性加载整个Excel到内存，按1000~5000行/块的粒度读取两个sheet的内容，既避免内存溢出，也方便后续批量写入数据库。
• 提前完成格式转换与校验：把Excel里的字符串格式、空值等不符合MySQL表定义的内容提前转换完成，不要在写入数据库时逐行处理，减少数据库侧的计算开销。
• 提前完成数据关联映射：预先把sheet_student和sheet_course的数据按course_id字段关联成结构化的内存对象，不要写入数据库时再做跨表关联查询。

注意：你现有表结构存在类型不匹配问题，student表的course_id定义为INT类型，但Excel里的course_id是C开头的字符串格式，需要先把student表的course_id调整为VARCHAR类型，否则数据写入会失败。

二、数据库层优化 #

• 新增联合唯一索引适配更新需求：根据业务唯一标识规则，给student表增加(name, course_id)联合唯一索引，给course表增加(reference_id, course_code)联合唯一索引，不需要先查询再判断插入/更新，直接用MySQL自带语法实现插入即更新。
• 导入期间临时调整数据库参数，导入完成后恢复即可：
  • 关闭自动提交：SET autocommit = 0;
  • 关闭唯一性校验：SET unique_checks = 0;
  • 临时调大innodb_buffer_pool_size、innodb_log_file_size参数，提升写入吞吐量
  • 临时删除非必要的普通索引，导入完成后再重建，减少写入时的索引维护开销

三、写入逻辑优化 #

• 用批量插入+重复更新语法替换逐行写入：
  学生表写入示例：

INSERT INTO student (name, status, course_id) 
VALUES 
('alpha', 0, 'C001'),
('alpha', 1, 'C002'),
-- 批量插入1000~5000行数据
ON DUPLICATE KEY UPDATE 
status = VALUES(status);

课程表写入同理，拿到批量插入学生后生成的主键id作为reference_id，再批量构造course的插入语句即可。

• 用事务包裹批量写入：每1000~5000行的批量写入操作包裹在一个事务里，避免频繁提交事务的IO开销，比逐行提交性能提升至少10倍以上。
• 避免循环查询数据库：完全依赖联合唯一索引和ON DUPLICATE KEY UPDATE语法自动处理插入/更新逻辑，把两次IO（先查再写）减少为一次IO。

四、超大数据量优化（单Excel超过10万行） #

• 把Excel导出为CSV格式，用MySQL原生LOAD DATA INFILE语法导入临时表，再通过临时表和正式表的关联批量更新正式表数据，性能比普通批量插入还要高3~5倍。
• 对course表的写入可以先批量缓存所有和reference_id关联的数据，统一批量写入，不要逐行关联写入。

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