MySQL查詢優化實戰：高效載入大量資料的七大策略

大量資料匯入時，策略選擇直接決定效能差異可達數量級。本文彙總七大高效載入策略，從SQL語法選擇到儲存引擎特性調優。

七大高效載入策略

1. 優先使用 LOAD DATA INFILE
   比INSERT快數倍，伺服器直接讀取本地檔案，避免客戶端網路傳輸開銷。
2. 避免 LOAD DATA LOCAL INFILE
LOCAL需客戶端讀檔再經網路傳送至伺服器，效率顯著較低。
3. INSERT必須批次化
   單條INSERT改為多值單語句：
   INSERT INTO t VALUES (...), (...), (...);
4. 多條 INSERT 打包進事務
   

   BEGIN;
   INSERT INTO t VALUES (...);
   INSERT INTO t VALUES (...);
   ...
   COMMIT;

   避免自動提交模式下每條語句刷新重做日誌。
   不支援事務的引擎：改用表鎖：
   LOCK TABLES t WRITE; INSERT ...; UNLOCK TABLES;
5. 啟用客戶端/伺服器壓縮協議
   mysql --compress或連線字串加Compress=true，減少網路傳輸量，**但會增加CPU開銷**，小心使用。
6. 讓MySQL填入預設值
   INSERT語句盡量省略有DEFAULT的欄位，減少網路封包大小與解析時間。
7. MyISAM/ISAM 大量載入：先建表後建索引
   

   CREATE TABLE t ( ... ) ENGINE=MyISAM;  -- 無索引
   LOAD DATA INFILE ...;
   ALTER TABLE t ADD INDEX ...;           -- 載入完再建索引

   不適用於InnoDB/BDB（聚簇索引結構決定必須逐筆維護）。

索引啟停用替代方案

n為位元遮罩：第0位對應第一個索引，三個索引則n=7 (0b111)。myisamchk --description t可查索引編號。

策略選擇決策樹

• MyISAM/ISAM + 大量匯入 → DISABLE KEYS + LOAD DATA + ENABLE KEYS
• InnoDB + 大量匯入 → 依主鍵順序排序資料檔 + LOAD DATA + 適當調大innodb_buffer_pool_size/innodb_log_file_size
• 跨網路匯入 → 壓縮協議 + 批次INSERT事務

根據資料量、引擎、網路環境靈活組合上述策略，可將TB級資料匯入時間從數天壓縮至數小時。