SQL INSERT 高效写入技巧

批量insert比单条快10–40倍，10万行单条耗时8–12秒，千行一批仅0.3–0.6秒；需控制每批1000–5000行以避max_allowed_packet限制与锁表风险。

批量 INSERT 比单条快多少？

不是“快一点”，是数量级差异。单条 INSERT INTO t VALUES (...) 每次都走完整事务路径、日志刷盘、索引更新；批量写入能把这些开销均摊。实测 mysql 5.7 下，10 万行数据：单条耗时约 8–12 秒，INSERT INTO t VALUES (...), (...), (...)（一次 1000 行）降到 0.3–0.6 秒。

但别盲目堆高每批行数：max_allowed_packet 会截断超长语句，MySQL 默认通常为 4MB，按平均每行 200 字节算，极限约 2 万行/批——实际建议控制在 1000–5000 行之间。

• 超大批量易触发 Packets larger than max_allowed_packet are not allowed
• 事务太大可能锁表时间过长，尤其在非只读从库或有长查询的场景
• postgresql 对单条 INSERT ... VALUES (...), (...) 行数无硬限制，但解析器仍有内存压力

REPLACE INTO / INSERT ON DUPLICATE KEY UPDATE 性能陷阱

它们不是“带去重的 INSERT”，而是先尝试插入，冲突时再执行更新逻辑——这意味着：主键或唯一索引字段必须存在，且每次都会触发索引查找 + 可能的 delete + insert 或 update。IO 和锁开销远高于纯插入。

如果你只是想忽略重复、不更新任何字段，用 INSERT IGNORE 更轻量；如果真要更新，确认是否真的需要每次覆盖，还是可改为应用层判重后选择性写入。

• INSERT IGNORE 冲突时直接跳过，不报错也不更新，性能最接近纯 INSERT
• ON DUPLICATE KEY UPDATE 会引发额外的行锁和 undo log 记录，高并发下容易锁等待
• sqlite 不支持 ON DUPLICATE KEY UPDATE，得用 INSERT OR IGNORE + 单独 UPDATE 组合，注意事务包住

LOAD DATA INFILE 真快，但权限和路径很挑

这是 MySQL 原生最快写入方式，绕过 SQL 解析层，直接加载文本到引擎缓冲区。比批量 INSERT 快 5–10 倍常见。

但它要求文件在数据库服务器本地（不是你本地机器），且用户需有 FILE 权限。线上环境常被禁用，开发机也未必开放 secure_file_priv 目录。

• 错误 Error 1290 (HY000): The MySQL server is running with the --secure-file-priv option 表示只能从指定目录读
• 路径必须用服务器绝对路径，如 LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
• CSV 格式需严格匹配字段顺序、转义规则，空值要用 N，不是空字符串

PostgreSQL 的 copy 和 prepared statement 配合更稳

PG 没有 LOAD DATA INFILE 对等物，但 COPY FROM STDIN（客户端驱动支持）几乎一样快，且不依赖服务端文件系统权限。配合预编译语句（prepared statement）批量执行 INSERT，是兼顾安全与性能的通用方案。

注意：不要在循环里反复 PREPARE 同一个语句，应提前准备一次，然后多次 EXECUTE；否则准备开销抵消了性能收益。

• Python psycopg2 中用 executemany() 自动绑定参数并复用计划，比手写循环 execute() 快 3–5 倍
• 如果表有触发器，COPY 会绕过它们，而 INSERT 不会——这点容易漏掉业务逻辑
• 时间字段写入时，确保客户端时区与服务端一致，否则 timestamp WITHOUT TIME ZONE 可能错位

事情说清了就结束。真正卡住的往往不是语法，而是权限、配置项、时区、触发器这些隐性约束。