萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL SOURCE 大檔案導入實戰:分卷腳本與參數調優雙管齊下

場景:10 GB SQL 檔案導入

單檔過大易導致超時、記憶體暴增、網路中斷回滾。核心策略:拆分檔案 + 調大緩衝 + 關閉自動提交。

方法一:拆分 SQL 檔案並批次導入

1. 邏輯拆分(按表)

# 導出時按表分檔
mysqldump -uroot -p --default-character-set=utf8mb4 dbname table1 > table1.sql
mysqldump -uroot -p --default-character-set=utf8mb4 dbname table2 > table2.sql
# ...

導入:

mysql -uroot -p dbname < table1.sql
mysql -uroot -p dbname < table2.sql

2. 物理拆分(大表按行數)

# Linux split 按行切分(每 50 萬行一檔)
split -l 500000 huge_table.sql part_

# 生成 part_aa, part_ab... 逐個導入
for f in part_*; do mysql -uroot -p dbname < "$f"; done

3. 主控腳本統一調度

# master.sql
SOURCE /backup/part_aa.sql;
SOURCE /backup/part_ab.sql;
-- ...

mysql -uroot -p --default-character-set=utf8mb4 dbname < master.sql

方法二:調優 MySQL 參數支援大單檔

[mysqld]
# 連線逾時延長
interactive_timeout = 3600
wait_timeout = 3600

# 封包上限(依檔案大小調整,建議 256M~1G)
max_allowed_packet = 512M

# InnoDB 寫入優化
innodb_buffer_pool_size = 4G      # 實體記憶體 60-70%
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2  # 導入期間暫設 2,完成後改回 1
innodb_flush_method = O_DIRECT

重啟後生效。導入完成務必改回 innodb_flush_log_at_trx_commit = 1 保證 ACID。

實測案例

# 測試表結構
CREATE TABLE emp(
  eid INT PRIMARY KEY AUTO_INCREMENT,
  ename VARCHAR(20) NOT NULL,
  esex VARCHAR(10),
  deptid INT NOT NULL
);
CREATE TABLE dept(
  deptid INT PRIMARY KEY AUTO_INCREMENT,
  dname VARCHAR(20) NOT NULL,
  daddress VARCHAR(200)
);
INSERT ...(含中文測試資料)

導入指令:

SOURCE "/full/path/mytest_emp_dept.sql"

進階:並行導入(MySQL 8.0 + mydumper/myloader)

# 導出
mydumper -u root -p -B dbname -o /backup -t 8 -c

# 導入(8 線程並行)
myloader -u root -p -B dbname -d /backup -t 8

大表並行導入速度提升 5-10 倍,生產環境強烈推薦。

copyright © 萬盛學電腦網 all rights reserved