測試環境基線
| 項目 | 配置 |
|---|
| 硬體 | 虛擬機 4 vCPU / 8 GB RAM / SSD |
| OS | CentOS 7.9 |
| 數據量 | 1,000,000 行 / 3 列（id, rank, log_time） |
| 文件大小 | ~85 MB CSV（含表頭、雙引號包裹、CRLF 換行） |
聲明：虛擬機測試受 CPU 競爭、I/O 抖動影響大，數值僅供相對對比參考。
表結構
CREATE TABLE t1 (
id INT NOT NULL PRIMARY KEY,
rank INT NOT NULL,
log_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
);
MySQL 5.6 導入方案與耗時
| 方案 | 命令/代碼 | 耗時 | 備註 |
|---|
原生 LOAD DATA INFILE | LOAD DATA INFILE '/tmp/t1.csv' INTO TABLE t1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'; | 24.21 s | Server 端直讀，最快 |
| Python mysql-connector | cursor.execute("LOAD DATA LOCAL INFILE ...") | 23 s | 需 local_infile=1，網絡傳輸開銷 |
mysqlimport (多線程) | mysqlimport --use-threads=2 --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by='\r\n' -uroot -p db /tmp/t1.csv | 23.66 s | 封裝 LOAD DATA，支援並行 |
PostgreSQL 13 導入方案與耗時
| 方案 | 命令/代碼 | 耗時 | 備註 |
|---|
原生 COPY FROM | COPY t1 FROM '/tmp/t1.csv' WITH (FORMAT csv, DELIMITER ',', HEADER true); | 7.70 s | Server 端直讀，極快 |
Psycopg2 copy_expert | cur.copy_expert("COPY t1 FROM STDIN WITH (FORMAT csv)", open('/tmp/t1.csv')) | 5.97 s | 流式傳輸，客戶端解析，最快 |
| pgloader (CSV) | pgloader commands.load | 33.51 s | 功能豐富但單線程，適合異構遷移 |
| pgloader (MySQL→PG) | pgloader commands.mysql | 51.27 s | 含 Schema 映射、類型轉換開銷 |
關鍵發現與分析
- PostgreSQL COPY 碾壓 MySQL LOAD DATA：~3-4 倍速度優勢，源於 PostgreSQL 內部流式解析器無需臨時表、鎖粒度更細。
- 驅動層流式導入最快：Psycopg2
copy_expert 甚至超過 Server 端 COPY，因省去 Server 讀文件系統調用，直接網絡流寫入。
- MySQL
mysqlimport --use-threads 並未顯著提速：單表導入受單表鎖限制，多線程僅在多表並行時有效。
- pgloader 定位異構遷移：非高性能批量導入首選，優勢在 Schema 映射、類型轉換、增量同步。
生產環境優化建議
MySQL
SET GLOBAL innodb_flush_log_at_trx_commit=2; 導入期間犧牲 1 秒持久性換寫入吞吐。SET GLOBAL sync_binlog=0; 關閉 Binlog 同步。- 大表考慮
LOAD DATA ... IGNORE LINES 1 跳過表頭，避免 REPLACE 觸發唯一鍵衝突。
- 分區表並行導入：按範圍拆分 CSV，多會話併發
LOAD DATA。
PostgreSQL
SET maintenance_work_mem = '1GB'; SET work_mem = '256MB'; 提升排序/雜湊緩衝。- 導入前
DROP INDEX / ALTER TABLE ... DISABLE TRIGGER ALL，導入後重建。
- 使用
COPY ... FROM PROGRAM 'zcat file.csv.gz' 直接解壓導入，省磁碟 I/O。
- 並行導入：
pgloader 或自寫 COPY FROM STDIN 多進程分片寫入。
附：pgloader 配置範例
-- commands.load (CSV→PG)
LOAD CSV FROM '/tmp/ytt.csv' WITH ENCODING UTF-8
(id, rank, log_time)
INTO postgresql://t_girl:t_girl@127.0.0.1:5432/t_girl?ytt.t1
WITH skip header = 0,
fields optionally enclosed by '"',
fields escaped by backslash-quote,
fields terminated by ','
SET work_mem to '32 MB', maintenance_work_mem to '64 MB';
-- commands.mysql (MySQL→PG)
LOAD DATABASE FROM mysql://python_user:python_user@192.168.1.131:3306/t_girl?t1
INTO postgresql://t_girl:t_girl@127.0.0.1:5432/t_girl?ytt.t1
WITH data only
SET maintenance_work_mem to '64MB', work_mem to '3MB', search_path to 'ytt';
