MySQL 大量資料插入效能優化：批次 INSERT、LOAD DATA、索引策略與鎖控制

MySQL 批量插入極致效能調優指南

面對百萬級甚至億級資料匯入，單條 INSERT 效能極差。本文系統整理 MySQL 官方推薦的優化策略：多值 INSERT、LOAD DATA INFILE、索引延遲建立、INSERT DELAYED、鎖控制與事務批次，並提供實測參考數據。

策略對比總覽

1. 多值 INSERT 批次插入

<?php
// PHP PDO 批次插入範例
function batchInsert(PDO $pdo, string $table, array $columns, array $rows, int $batchSize = 1000): int
{
    $placeholders = '(' . implode(',', array_fill(0, count($columns), '?')) . ')';
    $columnList = '`' . implode('`,`', $columns) . '`';
    $sql = "INSERT INTO `{$table}` ({$columnList}) VALUES ";
    
    $total = 0;
    $pdo->beginTransaction();
    
    try {
        foreach (array_chunk($rows, $batchSize) as $chunk) {
            $valuesSql = implode(',', array_fill(0, count($chunk), $placeholders));
            $stmt = $pdo->prepare($sql . $valuesSql);
            
            $paramIndex = 1;
            foreach ($chunk as $row) {
                foreach ($row as $value) {
                    $stmt->bindValue($paramIndex++, $value);
                }
            }
            $stmt->execute();
            $total += $stmt->rowCount();
        }
        $pdo->commit();
    } catch (\Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
    
    return $total;
}

// 使用
$columns = ['username', 'email', 'created_at'];
$rows = [];
for ($i = 0; $i < 50000; $i++) {
    $rows[] = ["user{$i}", "user{$i}@example.com", date('Y-m-d H:i:s')];
}
$inserted = batchInsert($pdo, 'users', $columns, $rows);
echo "批次插入 {$inserted} 筆完成\n";

2. LOAD DATA INFILE 極速匯入

-- CSV 檔案格式 (users.csv)
-- username,email,created_at
-- user1,user1@example.com,2024-01-01 00:00:00
-- user2,user2@example.com,2024-01-01 00:00:01

LOAD DATA INFILE '/var/lib/mysql-files/users.csv'
INTO TABLE users
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(username, email, created_at);

必要設定：

• secure_file_priv：限制匯入目錄，檔案必須置於此目錄下。
• 用戶需 FILE 權限：GRANT FILE ON *.* TO 'user'@'host';
• 本地客戶端匯入：LOAD DATA LOCAL INFILE 需啟用 local_infile=1 (客戶端與伺服器)。

3. 索引延遲建立策略 (MyISAM 為主，InnoDB 參考)

-- 步驟 1：建立表結構 (不含索引)
CREATE TABLE big_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(255),
    category_id INT,
    created_at DATETIME
) ENGINE=MyISAM;

-- 步驟 2：停用索引 (MyISAM)
ALTER TABLE big_table DISABLE KEYS; -- 等同 myisamchk --keys-used=0

-- 步驟 3：大量匯入
LOAD DATA INFILE '...' INTO TABLE big_table ...;

-- 步驟 4：重建索引
ALTER TABLE big_table ENABLE KEYS; -- 等同 myisamchk -rq

-- 步驟 5：壓縮表 (唯讀歸檔)
-- myisampack big_table

InnoDB 替代方案：

1. 建立表時僅建主鍵，次要索引匯入後再 CREATE INDEX。
2. 按主鍵順序匯入資料 (InnoDB 聚簇索引)，大幅減少頁分裂。
3. 調大 innodb_buffer_pool_size (如 70% 記憶體)、innodb_log_file_size (如 1GB)、innodb_flush_log_at_trx_commit = 2 (匯入期間)。
4. 使用 innodb_bulk_load_mode (MySQL 8.0.30+) 自動優化。

4. 鎖控制與事務批次

-- 表鎖 (MyISAM) 或事務 (InnoDB)
LOCK TABLES big_table WRITE;
INSERT INTO big_table ... VALUES (...), (...), ...; -- 批次
INSERT INTO big_table ... VALUES (...), (...), ...;
UNLOCK TABLES;

-- InnoDB 事務批次
START TRANSACTION;
INSERT ...; -- 批次 1
INSERT ...; -- 批次 2
COMMIT; -- 僅提交時刷盤一次

關鍵差異：

• MyISAM：LOCK TABLES 減少索引緩衝區刷盤次數 (每批次 1 次 vs 每條 1 次)。
• InnoDB：事務提交時刷盤，innodb_flush_log_at_trx_commit=2 可延遲至每秒刷盤 (風險：崩潰丟失 1 秒資料)。

5. 並行匯入與分區表

• 分區表：按範圍/雜湊分區，不同分區可並行載入 (需 LOCK TABLES 分區級)。
• 多連線並行：將資料分割為多檔案，多線程同時 LOAD DATA (注意鎖衝突)。
• MySQL Shell：util.importTable() 內建並行匯入、進度條、錯誤處理。

實測參考 (100 萬筆、InnoDB、SSD、16GB RAM)

避坑指南

1. max_allowed_packet 設定過小導致多值 INSERT 截斷 (建議 64M+)。
2. 字元集不匹配導致亂碼：檔案、表、連線皆用 utf8mb4。
3. 日期格式錯誤：CSV 使用 YYYY-MM-DD HH:MM:SS 標準格式。
4. 主鍵衝突：使用 INSERT IGNORE、ON DUPLICATE KEY UPDATE 或 REPLACE。
5. 外鍵檢查拖慢：匯入期間 SET FOREIGN_KEY_CHECKS=0; 結束後恢復。
6. 唯一索引檢查拖慢：SET UNIQUE_CHECKS=0; (InnoDB 亦有效)。