-- 非空表批量導入前
ALTER TABLE `tbl_name` DISABLE KEYS;
-- 導入數據 (LOAD DATA / 批量 INSERT)
LOAD DATA INFILE '/path/data.txt' INTO TABLE `tbl_name` ...;
-- 或
INSERT INTO `tbl_name` VALUES (...), (...), ...;
-- 導入完成後
ALTER TABLE `tbl_name` ENABLE KEYS;
原理: MyISAM 批量導入後統一排序建索引,比逐行維護 B+樹快數量級。
注意: 空表導入無需此操作 (MyISAM 導入後才建索引);InnoDB **不支持** DISABLE KEYS。
SET SESSION UNIQUE_CHECKS = 0;
-- 批量導入
SET SESSION UNIQUE_CHECKS = 1;
LOAD DATA INFILE '/var/lib/mysql-files/data.csv'
INTO TABLE `tbl_name`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(col1, col2, col3, ...);
前提: secure_file_priv 允許路徑、文件在服務器本地、權限正確。
速度: 比 INSERT 快 10-20 倍。
SET SESSION UNIQUE_CHECKS = 0;
-- 導入
SET SESSION UNIQUE_CHECKS = 1;
SET SESSION FOREIGN_KEY_CHECKS = 0;
-- 導入
SET SESSION FOREIGN_KEY_CHECKS = 1;
SET SESSION AUTOCOMMIT = 0;
-- 批量 INSERT (建議每 1000-5000 行 COMMIT 一次)
INSERT INTO `tbl_name` VALUES (...), (...), ...; -- 批次 1
COMMIT;
INSERT INTO `tbl_name` VALUES (...), (...), ...; -- 批次 2
COMMIT;
-- ...
SET SESSION AUTOCOMMIT = 1;
原理: 減少 Redo Log fsync 次數、Binlog 組提交、鎖持有時間。
-- 應用層拼接 (單條 SQL 最大 max_allowed_packet,默認 64MB)
INSERT INTO `tbl_name` (col1, col2, col3) VALUES
(?, ?, ?), (?, ?, ?), ... (?, ?, ?); -- 500-2000 行/批
對比: 1 萬條單 INSERT ≈ 30 秒;批量 1000 行/條 ≈ 0.5 秒 (提升 60 倍)。
SET SESSION UNIQUE_CHECKS=0, FOREIGN_KEY_CHECKS=0, AUTOCOMMIT=0;
LOAD DATA INFILE ... INTO TABLE `tbl_name` ...;
COMMIT;
SET SESSION UNIQUE_CHECKS=1, FOREIGN_KEY_CHECKS=1, AUTOCOMMIT=1;
# Python 伪代碼
batch_size = 2000
rows = [...] # 數據源生成器
conn = get_connection()
cursor = conn.cursor()
cursor.execute("SET SESSION UNIQUE_CHECKS=0, FOREIGN_KEY_CHECKS=0, AUTOCOMMIT=0")
sql = "INSERT INTO `tbl_name` (col1, col2, col3) VALUES " + ",".join(["(%s,%s,%s)"] * batch_size)
for i in range(0, len(rows), batch_size):
batch = rows[i:i+batch_size]
flat = [val for row in batch for val in row]
cursor.execute(sql, flat)
if (i // batch_size) % 10 == 0:
conn.commit() # 每 10 批提交一次,平衡事務大小與恢復點
conn.commit()
cursor.execute("SET SESSION UNIQUE_CHECKS=1, FOREIGN_KEY_CHECKS=1, AUTOCOMMIT=1")
| 參數 | 導入時建議值 | 說明 |
|---|---|---|
innodb_buffer_pool_size | 物理內存 80%+ | 盡量缓住所有數據/索引頁 |
innodb_log_file_size | 4 GB | 減少 Checkpoint 刷盤頻率 |
innodb_flush_log_at_trx_commit | 2 | 事務提交不強制 fsync,僅寫 OS 緩存 (風險:掉電丟 1 秒) |
sync_binlog | 0 | Binlog 不強制 fsync (風險:掉電丟 Binlog) |
innodb_doublewrite | OFF | 關閉雙寫緩衝 (風險:頁損壞),極速導入可用 |
innodb_change_buffer_max_size | 50 | 加大 Change Buffer 合併二級索引寫入 |
⚠️ 警告: 生產核心庫**嚴禁**調整雙 1 / 雙寫 / Redo 安全參數!僅限可重建的導入環境、測試環境、數據倉庫 ETL。
-- 行數核對
SELECT COUNT(*) FROM `tbl_name`;
-- 關鍵指標抽樣
SELECT MIN(id), MAX(id), COUNT(DISTINCT col_a) FROM `tbl_name`;
-- 索引碎片檢查
ANALYZE TABLE `tbl_name`;
SHOW TABLE STATUS LIKE 'tbl_name'\G -- Data_free 應極小