MySQL INSERT 語法詳解與大批量寫入性能優化實戰指南

INSERT 是 MySQL 最核心的寫入操作，但隨著數據量增長，單條插入性能瓶頸明顯。本文系統梳理 INSERT 語法變體、PHP/Java 客戶端調用、批次插入、事務優化、參數調優五大維度，提供從千條到億級寫入的完整優化路徑。

一、INSERT 基礎語法

-- 標準語法
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

-- 多行插入（單條 SQL）
INSERT INTO table_name (col1, col2) VALUES 
('a', 1), ('b', 2), ('c', 3);

-- 插入查詢結果
INSERT INTO target_tbl (col1, col2) SELECT col1, col2 FROM source_tbl WHERE ...;

-- 忽略重複鍵錯誤
INSERT IGNORE INTO table_name ... VALUES ...;

-- 衝突時更新
INSERT INTO table_name ... VALUES ... ON DUPLICATE KEY UPDATE col2=VALUES(col2);

二、客戶端調用示例

PHP (mysqli 預處理 - 推薦)

$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);

foreach ($batch as $row) {
    $name = $row['name']; $email = $row['email'];
    $stmt->execute();
}
$stmt->close();

Java (JDBC 批次處理)

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    for (User u : batch) {
        ps.setString(1, u.getName());
        ps.setString(2, u.getEmail());
        ps.addBatch();
    }
    int[] results = ps.executeBatch();  // 一次網絡往返
}

三、批量寫入優化三板斧

1. 多值單條 SQL（降低解析/網絡開銷）

-- 將 1000 條單行合併為 1 條 1000 行 SQL
INSERT INTO tbl (a,b) VALUES (1,2), (3,4), ... (1999,2000);

限制：max_allowed_packet（默認 4MB/1MB），超大批次需分片

2. 事務包裹（減少 fsync 頻率）

START TRANSACTION;
INSERT ...; INSERT ...; ... (批次 1000-5000 行)
COMMIT;

原理：InnoDB 預設 innodb_flush_log_at_trx_commit=1，每次提交刷盤。事務合併可將刷盤頻率降低 N 倍

3. LOAD DATA INFILE（極速導入）

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

優勢：繞過 SQL 解析、索引逐行維護、WAL 寫入優化，**比 INSERT 快 10-20 倍**

前提：secure_file_priv 允許、文件在服務器本地、表無觸發器/外鍵

四、關鍵配置參數調優

五、實戰優化清單

1. 禁用自動提交：SET autocommit=0，手動控制事務邊界
2. 延遲索引維護：MyISAM ALTER TABLE ... DISABLE KEYS → 導入 → ENABLE KEYS
3. 主鍵順序寫入：自增主鍵或有序 UUID，避免 B+ 樹頁分裂
4. 批次大小調優：1000-5000 行/批次，根據行長度與 max_allowed_packet 調整
5. 並行導入：分區表 + 多線程並行 LOAD DATA，線程數 = CPU 核心數
6. 監控指標：Innodb_rows_inserted、Innodb_dml_latency、Innodb_log_waits

六、常見陷阱避坑

陷阱 1：大事務長時間持有鎖

拆分小批次提交，避免阻塞其他事務、Undo Log 膨脹

陷阱 2：INSERT IGNORE 吞沒非重複鍵錯誤

僅忽略 Duplicate Key，其他錯誤（外鍵、非空）仍報錯，勿濫用

陷阱 3：ON DUPLICATE KEY UPDATE 觸發自增增長

即使不插入新行，AUTO_INCREMENT 仍遞增，導致主鍵空洞

陷阱 4：LOAD DATA 字符集不匹配

指定 CHARACTER SET utf8mb4，否則亂碼