MySQL（InnoDB）批量插入性能优化求助：400k数据插入过慢

问题核心 #

你当前的插入语句每行都会执行两次独立子查询，哪怕tab1.col1和tab2.col2有索引，400k行数据也会触发800k次额外查询——这才是性能瓶颈的根源，之前的优化手段没触碰到这个核心问题。

优化步骤 #

1. 预构建映射字典，消除子查询

先一次性把tab1和tab2中需要的映射关系加载到Python内存中，插入时直接从字典取值，彻底避免每行的子查询：

# 预查询tab1的col1到id的映射
cursor.execute("SELECT col1, id FROM tab1")
tab1_map = {row[0]: row[1] for row in cursor.fetchall()}

# 预查询tab2的col2到id的映射
cursor.execute("SELECT col2, id FROM tab2")
tab2_map = {row[0]: row[1] for row in cursor.fetchall()}

修改插入语句为无嵌套子查询的版本：

INSERT INTO tab3(phrase, link_1, link_2) VALUES(%s, %s, %s)

准备参数时直接从字典取对应id：

params = [
    (phrase_val, tab1_map[col1_val], tab2_map[col2_val])
    for phrase_val, col1_val, col2_val in your_data_source
]

2. 优化批量插入逻辑

• 调整批量大小：每次用executemany插入1000-5000行（根据服务器内存和数据库配置调整，避免单次批量过大导致内存溢出）。
• 手动控制事务：关闭自动提交，每插入N批后提交一次，或者用单个事务包裹整个插入过程（注意：大事务会占用更多日志空间，确保innodb_log_file_size配置足够）：

connection.autocommit(False)
try:
    batch_size = 2000
    for i in range(0, len(params), batch_size):
        batch = params[i:i+batch_size]
        cursor.executemany(insert_stmt, batch)
    connection.commit()
except Exception as e:
    connection.rollback()
    raise
finally:
    connection.autocommit(True)

3. 数据库临时参数调优（可选）

插入前临时调整数据库参数，进一步降低插入开销：

• 关闭外键检查（需确保插入的link_1/link_2值合法，避免数据不一致）：

SET FOREIGN_KEY_CHECKS = 0;

• 临时调整InnoDB日志刷新策略（插入完成后改回1保证数据安全）：

SET innodb_flush_log_at_trx_commit = 2;

• 关闭自动提交：

SET AUTOCOMMIT = 0;

插入完成后恢复参数：

SET FOREIGN_KEY_CHECKS = 1;
SET innodb_flush_log_at_trx_commit = 1;
SET AUTOCOMMIT = 1;

效果验证 #

以上优化后，子查询的额外开销被完全消除，批量插入效率会提升数十倍，400k行数据应该能在数分钟内完成插入。

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