MySQL 資料插入優化大全：MyISAM/InnoDB 分引擎策略、參數調優、批次寫入、LOAD DATA

MyISAM 引擎優化

1. 並發插入控制

concurrent_insert = 2  # 任意表允許尾部並發插入（預設 1：無洞表）

定期 OPTIMIZE TABLE 清理碎片。

2. 寫入優先級調整

max_write_lock_count = 1      # 處理 1 次寫鎖後讓讀鎖插隊
low_priority_updates = 1      # 直接降低寫優先級，讀優先

視業務「讀多寫少」選擇。

3. 批次插入語法

-- 優：單條 INSERT 多值
INSERT INTO t (a,b) VALUES (1,2),(3,4),(5,6);

-- 劣：多條 INSERT
INSERT INTO t (a,b) VALUES (1,2);
INSERT INTO t (a,b) VALUES (3,4);

4. 批次緩衝區

bulk_insert_buffer_size = 64M  # 預設 8M，MyISAM 專用

5. 非空表大量導入

ALTER TABLE t DISABLE KEYS;
LOAD DATA INFILE 'data.txt' INTO TABLE t;
ALTER TABLE t ENABLE KEYS;

空表無需 DISABLE KEYS（MyISAM 空表先寫資料再建索引）。

6. INSERT DELAYED

INSERT DELAYED INTO t (a,b) VALUES (1,2);

寫入佇列，批次落盤，**不回傳自增 ID**，5.6+ 棄用，建議應用層批次。

7. LOAD DATA INFILE（最快，約 INSERT 20 倍）

LOAD DATA INFILE '/path/data.txt'
INTO TABLE t
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

InnoDB 引擎優化

1. 關閉唯一性檢查

SET unique_checks = 0;
-- 導入資料
SET unique_checks = 1;

2. 關閉外鍵檢查

SET foreign_key_checks = 0;
-- 導入資料
SET foreign_key_checks = 1;

3. 關閉自動提交（手動事務）

SET autocommit = 0;
-- 批次 INSERT
COMMIT;
SET autocommit = 1;

4. 主鍵順序寫入

InnoDB 聚簇索引按 PK 儲存，**按 PK 遞增順序導入**可避免頁分裂，大幅提速。

5. 禁用 Change Buffer（5.5+）

SET GLOBAL innodb_change_buffer_max_size = 0;
-- 導入完成後恢復

硬體層面

• 高 IOPS 存儲（NVMe SSD、RAID 10）。
• 足夠記憶體容納 innodb_buffer_pool（熱資料 100% 命中）。
• 獨立 Binlog 磁碟。

總結對照表