如何通过SQLAlchemy加速MySQL/MariaDB批量插入？

你遇到的SQLAlchemy批量插入速度不如原生SQL的问题很常见，结合你的测试场景，我整理了几个针对性的优化方向，帮你大幅提升插入效率：

1. 切换到SQLAlchemy Core层批量插入 #

session.bulk_save_objects()虽然比普通ORM插入快，但仍保留了ORM层的对象状态跟踪开销。直接用Core层的insert()语句，能完全绕过ORM的实例化逻辑，生成和你测试中一致的扩展INSERT语句，性能和原生SQL几乎持平：

from sqlalchemy import insert

def benchmark_core_bulk_insert(engine, keys, values):
    t0 = time.time()
    # 构造批量插入的参数列表
    insert_data = [{"key": key, "value": value} for key, value in zip(keys, values)]
    stmt = insert(KeyValue).values(insert_data)
    # 使用引擎事务执行，减少连接开销
    with engine.begin() as conn:
        conn.execute(stmt)
    t1 = time.time()
    print(f"Inserted {len(keys)} entries in {t1 - t0:0.2f}s with Core Bulk "
          f"({len(keys)/(t1 - t0):0.2f} inserts/s).")

2. 把LOAD DATA INFILE用到极致（最快方案之一） #

你已经测试过这个方法，但还有几个优化点可以进一步压榨性能：

• 规避转义开销：像你发现的那样，选择数据中极少出现的字符作为字段包围符，能大幅减少转义操作的耗时
• 索引临时禁用：如果用InnoDB，插入前执行ALTER TABLE KeyValue DISABLE KEYS;，插入完成后再执行ALTER TABLE KeyValue ENABLE KEYS;，避免插入时频繁更新索引
• 调整InnoDB核心参数：
  • 把innodb_buffer_pool_size设为服务器内存的50%-70%，让更多数据缓存在内存
  • 临时将innodb_flush_log_at_trx_commit设为2（牺牲少量事务安全性换速度，适合批量插入场景）
  • 开启innodb_autoinc_lock_mode=2，优化自增锁的分配逻辑

3. 选对存储引擎 #

从你的测试数据能明显看出，MyISAM在批量插入上比InnoDB快一个数量级——这是因为MyISAM会批量更新索引，而InnoDB每插入一条都要维护聚簇索引。如果你的业务场景不需要InnoDB的事务、行锁、崩溃恢复等特性，直接换成MyISAM就能获得10倍左右的速度提升。

如果必须用InnoDB，除了上面的参数调整，还可以：

• 开启autocommit，避免频繁提交事务的开销（注意评估数据一致性风险）
• 分批次提交，比如每1000条数据提交一次事务，减少事务日志写入次数

4. 其他细节优化 #

• 压缩数据：你的value字段是Text类型，若业务允许，可先用zlib等工具压缩后再存储，减少IO传输的字节数
• 禁用外键约束：插入前执行SET FOREIGN_KEY_CHECKS=0;，插入完成后恢复SET FOREIGN_KEY_CHECKS=1;，避免外键校验的开销
• 优化连接池：确保SQLAlchemy引擎的连接池配置合理，避免频繁创建销毁数据库连接的损耗

最后补充一下：你提到的《High-speed inserts with MySQL》里的313000条/秒是极致优化场景（MyISAM+无索引+超大缓冲区+批量写入），实际业务中很难完全复刻，但按照上面的方法，把速度提升到每秒几万条是完全可行的。

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