《面試官別再問》MySQL 大量寫入的加速技巧

MySQL 大量寫入的加速技巧：從理論到實務

當我們需要將大量資料寫入 MySQL 時，單純的逐筆 INSERT 顯然效率不彰。一個好的優化策略不僅能大幅提升寫入速度，還能確保資料的一致性與系統穩定性。下面我將從不同場景、實務技巧、程式碼範例和風險評估四個維度，全面解析如何高效地處理大量寫入。

---

1. 根據場景選擇合適的寫入策略

選擇最佳的加速技巧取決於你的具體需求：是單純的數據遷移，還是日常的高並發寫入？

技巧	適用場景	吞吐量提升	鎖影響	重建成本
批次 INSERT	最常見，適合日常高頻、小批量的寫入。	x10～x50	低	-
關閉索引 + 約束	一次性的大量數據遷移、歷史數據匯入，對即時查詢影響不大。	x5～x20	中（重建時高）	高（重建索引）
單事務 + Buffer Pool	需保證數據原子性的一系列寫入，且不希望頻繁提交。	x2～x5	低	-
LOAD DATA INFILE	速度最快，適合從檔案（如 CSV）匯入巨量數據。	x50～x200	低	-

---

2. 核心加速技巧與細節補充

批次寫入（Batch Insert）

這是最基本且最有效的優化，能顯著減少網路通訊和事務提交的開銷。

• 原理：將多筆 INSERT 語句合併成一個語句，減少客戶端與伺服器之間的互動次數。

• 批次大小調校：批次大小並非越大越好。一般建議設定在幾百到一千筆之間最為安全。若批次過大，可能超過 max_allowed_packet 限制或耗盡伺服器內存，反而降低效能。

關閉索引與約束

索引維護是寫入操作的主要瓶頸之一。在大量寫入時，暫時關閉這些機制可以帶來巨大效能提升。

• 操作方式：

  • 外鍵約束：使用 SET foreign_key_checks = 0; 暫時停用，寫入完成後再設回 1。

  • 索引：對於 MyISAM 引擎，可以使用 ALTER TABLE ... DISABLE KEYS; 快速關閉索引；但對於 InnoDB 引擎，只能先 DROP INDEX，寫入完成後再 CREATE INDEX。重建索引是一個耗時的操作，建議先測試其時間成本。

使用 LOAD DATA INFILE

這是 MySQL 官方推薦的最快寫入方法，尤其適用於從檔案匯入數據。

• 原理：MySQL 伺服器會直接從檔案系統讀取數據，繞過了客戶端逐筆發送語句的過程，將 I/O 和網路開銷降至最低。

• 限制與注意事項：

  • secure_file_priv：在伺服器端使用時，檔案路徑必須位於這個變數設定的目錄下。

  • LOCAL 關鍵字：若在客戶端使用，需加上 LOCAL 關鍵字，並確保 MySQL 和客戶端程式都允許本地檔案載入。

---

3. 程式碼實務範例

使用 Laravel 的批次寫入

Laravel 的 chunk() 方法非常適合處理批次寫入，能自動將大型資料集分塊處理，避免單次操作過大。

PHP

use Illuminate\Support\Facades\DB;

// 假設 $largeDataset 是需要寫入的陣列
collect($largeDataset)
    ->chunk(500) // 將資料集切割成每 500 筆一塊
    ->each(function ($chunk) {
        // 使用 insert() 方法進行批次寫入
        DB::table('users')->insert($chunk->toArray());
    });

PHP 原生 PDO 搭配 LOAD DATA LOCAL INFILE

這是處理檔案匯入的最佳方案。

PHP

// 1. 將需要匯入的資料寫入臨時的 CSV 檔案
$csvContent = "John Doe,john@example.com\nJane Smith,jane@example.com\n";
file_put_contents('/tmp/users.csv', $csvContent);

// 2. 建立 PDO 連線，並確保開啟 LOCAL INFILE 選項
$pdo = new PDO(
    'mysql:host=localhost;dbname=test',
    'root',
    '',
    [PDO::MYSQL_ATTR_LOCAL_INFILE => true] // 允許本地檔案載入
);

// 3. 執行 LOAD DATA LOCAL INFILE 語句
$sql = "LOAD DATA LOCAL INFILE '/tmp/users.csv'
        INTO TABLE users
        FIELDS TERMINATED BY ','
        LINES TERMINATED BY '\n'
        (name, email)";
$pdo->exec($sql);

---

4. 優缺點與風險評估

技巧	優點	缺點／風險	落地建議
批次 INSERT	實現簡單，對多數場景通用。	批次過大有 OOM 或 packet 限制風險。	設定 500~2000 筆為一批，監控 max_allowed_packet。
關閉索引	寫入速度極快。	失去索引完整性，重建成本高，可能導致寫入期間的查詢效能下降。	僅用於離峰時段或歷史數據匯入，不影響線上服務。
單事務	確保數據原子性，降低 I/O 次數。	大事務可能導致鎖定時間過長，阻塞其他查詢。	控制事務大小，避免長時間鎖定，適合小批量寫入。
LOAD DATA	寫入速度最快，I/O 效率高。	需要處理檔案，受 secure_file_priv 路徑限制，實作較複雜。	適用於定期數據匯入或 ETL 任務，配合專門的匯入腳本。

總結來說，了解不同的寫入場景並選擇對應的優化技巧是關鍵。在實際工作中，我們通常會結合使用這些方法，例如：在一個大事務中進行批次寫入，並在匯入前關閉索引和外鍵約束。這不僅能大幅提升效能，也能在確保資料完整性的同時，將對線上服務的影響降至最低。