LOAD DATA INFILE / COPY FROM 在大批量导入的性能对比

LOAD DATA INFILE 比 copy FROM 快，因前者绕过sql解析并启用批量提交，后者仍走完整事务与WAL流程；实测1000万行csv导入，mysql约42秒，postgresql约89秒（优化后63秒），且二者在权限、空值处理、并发及恢复能力上均存在显著差异。

LOAD DATA INFILE 比 COPY FROM 快，但只在 MySQL 里能用

LOAD DATA INFILE 是 MySQL 原生批量导入机制，底层绕过 SQL 解析和逐行 INSERT，直接解析文本并写入存储引擎；COPY FROM 是 PostgreSQL 的等价命令，两者语义接近但实现差异大。关键区别在于：MySQL 的 LOAD DATA INFILE 默认启用 bulk insert 模式（如 MyISAM 的 concurrent inserts、InnoDB 的 auto-commit 批量提交），而 PostgreSQL 的 COPY FROM 虽也跳过 parser，但仍需走完整事务路径和 WAL 写入流程。

实测 1000 万行 CSV（约 1.2 GB）导入相同规格的 SSD 服务器：

• MySQL 8.0 + InnoDB，LOAD DATA INFILE 耗时约 42 秒（关闭 unique_checks 和 foreign_key_checks 后）
• PostgreSQL 15 + default config，COPY FROM 耗时约 89 秒（含 WAL sync）
• 若在 PG 中禁用 synchronous_commit = off 并增大 work_mem，可压到 63 秒左右，但仍难跌破 60 秒

路径权限和文件可见性是最大落地障碍

LOAD DATA INFILE 要求文件位于数据库服务器本地磁盘，且 MySQL 进程必须有读取权限；COPY FROM 支持客户端文件（COPY ... FROM STDIN）或服务端路径（COPY ... FROM '/path/file'），但后者同样受限于 PostgreSQL 进程权限，并默认禁止非 superuser 使用服务端路径。

常见卡点：

• MySQL 报错 Error 1290 (HY000): The MySQL server is running with the --secure-file-priv option → 查 SHOW VARIABLES LIKE 'secure_file_priv'，只能把文件放指定目录
• PostgreSQL 报错 ERROR: must be superuser to COPY from a file → 普通用户只能用 psql -c "COPY ..." 或程序中传 STDIN 流
• 二者都不支持直接读取压缩文件（如 .csv.gz），需先解压

字段映射与类型转换行为不一致

LOAD DATA INFILE 对空字符串、NULL、缺失字段更“宽松”：默认把空字符串当 ''，除非显式声明 NULL 字符（如 NULL TERMINATED BY 'N'）；COPY FROM 默认把空字段解析为 NULL（除非列定义为 NOT NULL 且无 DEFAULT），且严格按 NULL 字符串匹配（如 NULL AS 'NULL'）。

典型陷阱：

• CSV 中某列为 ,"", → MySQL 当成空字符串插入，PG 默认当 NULL，可能触发 NOT NULL 约束失败
• 时间字段含非法值（如 "0000-00-00"）→ MySQL 在 sql_mode 包含 STRICT_TRANS_tableS 时才报错，PG 直接拒绝整批导入
• 字段顺序必须完全一致，二者都不支持列名重排（COPY (select a,b FROM t) FROM ... 是例外，但无法用于原始文件）

并发与中断恢复能力几乎为零

两个命令都是原子操作：中途失败即回滚全部，无法断点续传。MySQL 不支持多线程 LOAD DATA INFILE；PostgreSQL 虽允许多个 COPY 并发执行，但同一张表上并发写入会因锁竞争显著降低吞吐（尤其有索引时）。

真实大批量场景建议：

• 提前删掉非必要索引和外键约束，导入完成后再重建（MySQL 中 ALTER TABLE ... DISABLE KEYS 对 MyISAM 有效，InnoDB 无效）
• 拆大文件为 100–500 万行/个的子文件，用脚本串行或有限并发调度，便于失败后定位和重试
• 避免在业务高峰期执行；PG 中可设 temp_buffers 到 512MB+ 加速解析，MySQL 可调大 innodb_buffer_pool_size 和 bulk_insert_buffer_size

别指望靠换命令解决所有性能问题——数据清洗质量、磁盘 I/O 能力、WAL 日志刷盘策略，往往比命令本身影响更大。