如何通过批量INSERT与LOAD DATA高效向MySQL大表导入百万条数据？

本文共计1013个文字，预计阅读时间需要5分钟。

能，但必须分批，不能一股脑儿塞入一整条百万元的语句。MySQL的`max_allowed_packet`默认通常只有4MB，一行数据按50%字节数计算，10万行就接近5MB，超限直接报错：

关键点：

• 必须显式开启事务：BEGIN; + 多条 INSERT + COMMIT;，否则每条语句都自动提交，性能断崖下跌
• 避免在插入过程中有二级索引或外键约束，它们会逐行校验，拖慢速度；可考虑先删索引，插完再建
• INSERT IGNORE 或 ON DUPLICATE KEY UPDATE 会额外增加唯一性检查开销，非必要不用

LOAD DATA INFILE为什么比INSERT快十倍以上？

因为它绕过了 SQL 解析、权限校验、日志格式转换等客户端-服务端交互环节，数据文件由 MySQL 服务端直接读取并写入存储引擎，相当于“裸数据灌入”。实测百万行 CSV 插入，LOAD DATA INFILE 常在 5–10 秒内完成，而等量 INSERT 即使分批也常需 60 秒以上。

使用前提和注意点：

• 文件必须位于 MySQL 服务端本地磁盘（不是你本地机器），路径如 /var/lib/mysql-files/data.csv；若用客户端路径，得加 LOCAL 关键字，但需服务端开启 local_infile=ON 且客户端连接时启用
• 字段分隔符、行结束符要严格匹配，比如 CSV 中含换行符或逗号，必须用 ENCLOSED BY '"' 包裹字段，否则解析错位
• 目标表不能有触发器，否则 LOAD DATA 会跳过触发逻辑（这是设计行为，不是 bug）

Java/Python 程序里调用批量插入，该选 executeBatch() 还是生成 CSV 再 LOAD DATA？

看数据来源。如果数据本来就在内存里（比如从 API 拉下来、计算生成），用 PreparedStatement.addBatch() + executeBatch() 更自然；但如果数据源是文件、或者可以临时落盘，优先走 LOAD DATA INFILE —— 它不经过 JDBC 驱动层，没有序列化/反序列化开销，也不吃 JVM 堆内存。

真实瓶颈常在这里：

• JDBC 批处理仍受限于网络往返和驱动缓冲区，尤其当 rewriteBatchedStatements=true 未开启时，它只是“伪批量”，底层仍是多条独立语句
• Python 的 pymysql.executemany() 默认不重写语句，得手动拼成一条大 INSERT，否则性能接近单条插入
• 用 LOAD DATA 时，程序只需执行一条 SQL 并等待返回，其余全是 MySQL 自己干，CPU 和内存压力全在服务端

插入前忘了关 autocommit，现在卡住了怎么办？

别急着 kill 连接。先查 SHOW PROCESSLIST;，找状态为 Update 或 Writing to net 且 Time 很大的线程，记下 ID；再用 KILL [ID]; 终止。但更关键的是：下次插入前务必执行 SET autocommit = 0;，并在所有 INSERT 后显式 COMMIT。InnoDB 在长事务中会持续持有行锁和 gap 锁，阻塞其他写操作，甚至引发锁等待超时。

容易被忽略的细节：

• autocommit 是会话级变量，每个新连接默认为 1，代码里连上就该立刻设 0
• 即使用了事务，如果中间有 SELECT ... FOR UPDATE 或其它 DML，也会延长锁持有时间
• 某些 ORM（如 Hibernate）会悄悄开启事务又不 commit，得检查其 flush 和 clear 行为