MySQL 大數據裝載效率優化：五大核心策略與實戰技巧

核心原理：減少索引維護、網絡往返、解析開銷

數據裝載（Data Loading）雖不如 SELECT 查詢複雜，但大批量導入時，微小優化累積效應顯著。核心邏輯三點：

1. 批量勝過單行：減少索引刷新頻率、網絡包數、SQL 解析次數
2. 無索引勝過有索引：避免每行維護多棵 B+ 樹
3. 短 SQL 勝過長 SQL：減少解析、網絡傳輸、協議開銷

策略一：優先使用 LOAD DATA INFILE

• 比 INSERT 快 **20 倍以上**（官方數據）
• 服務器直接讀取本地文件，無需客戶端傳輸
• 單語句解析，批量構建索引，僅最終刷新一次
• 需 FILE 權限，文件須在服務器本地

LOAD DATA INFILE '/data/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(order_id, user_id, amount, created_at);

策略二：LOAD DATA INFILE 優於 LOAD DATA LOCAL INFILE

• LOCAL 關鍵字意味著：客戶端讀文件 → 經網絡發送給服務器 → 服務器寫入
• 網絡傳輸成為瓶頸，且佔用客戶端內存
• 僅當文件只能在客戶端、無法上傳服務器時使用 LOCAL

策略三：若必須用 INSERT，使用多值語法

-- 慢：N 條單行 INSERT
INSERT INTO t VALUES (1); INSERT INTO t VALUES (2); ...

-- 快：單條多行 INSERT（每批 500-2000 行視行長度而定）
INSERT INTO t (a,b,c) VALUES
(1,2,3), (4,5,6), (7,8,9), ...;

-- mysqldump 導出時必加參數
mysqldump --extended-insert  # 或 --opt (含 extended-insert)
-- 禁用 --complete-insert（產生單行 INSERT，極慢）

• 減少語句數、解析次數、索引刷新次數
• 調大 bulk_insert_buffer_size（MyISAM 專用，默認 8M）可進一步加速批量插入索引構建

策略四：大表裝載時「先數據後索引」

1. 創建表結構，**不創建任何二級索引**（主鍵除外，InnoDB 必須有主鍵）
2. 批量導入數據（LOAD DATA 或多值 INSERT）
3. 數據全入庫後，**一次性創建所有二級索引**

原理：逐行插入時，每行都需隨機訪問多棵索引樹修改；批量建索引則採用排序+順序構建，I/O 為順序寫入，速度快數量級。

-- 實操步驟
CREATE TABLE big_table (id INT PRIMARY KEY, col1 INT, col2 VARCHAR(50)) -- 無二級索引
LOAD DATA INFILE 'data.txt' INTO TABLE big_table;
ALTER TABLE big_table ADD INDEX idx_col1 (col1), ADD INDEX idx_col2 (col2);

策略五：利用 INSERT DELAYED / DELAYED_KEY_WRITE（MyISAM 專用）

• INSERT DELAYED：客戶端立即返回，行入隊列，服務器空閒時批量寫入。適用於「寫入不需即時可見」場景（日志、統計）。多客戶端寫入會合併塊寫入，極大減少索引刷新。
• DELAYED_KEY_WRITE=1 表選項 / 全局 --delayed-key-write=ON：索引塊不即時刷盤，待塊被替換或 FLUSH TABLES 時才寫入。風險：崩潰導致索引損壞，需 REPAIR TABLE 恢復。

進階場景：高併發寫入與讀取衝突緩解

問題：長時間 SELECT 鎖定表/行，阻塞高頻 INSERT。

方案：臨時表緩衝 + 定期批量合併

1. 寫入端插入臨時表 tmp_logs（無索引/少索引，極快） 2. 定時任務（每分鐘/每小時）INSERT INTO main_logs SELECT * FROM tmp_logs; TRUNCATE tmp_logs; 3. 讀取端查詢主表，不受寫入衝突影響

• 減少主表鎖競爭
• 批量合併時索引僅刷新一次
• Web 訪問日志、埋點數據典型適用

事務表（InnoDB）的批量提交優化

-- 錯誤：自動提交模式下每行一提交
INSERT INTO t VALUES (1); -- 隱式 COMMIT
INSERT INTO t VALUES (2); -- 隱式 COMMIT
...

-- 正確：顯式事務包裹批量插入
START TRANSACTION;
INSERT INTO t VALUES (1),(2),(3),...; -- 批量 100-1000 行
COMMIT;
-- 重複上述批次

• 減少 Redo Log 刷盤次數（innodb_flush_log_at_trx_commit=1 時每提交必須 fsync）
• 避免使用 LOCK TABLES，InnoDB 用事務替代表鎖

網絡層優化：壓縮協議

• 客戶端啟用 --compress（mysqldump、mysql client、應用驅動均支持）
• 適用於**低帶寬/高延遲網絡**（跨機房、VPN、公網）
• 局域網高帶寬環境下，CPU 壓縮開銷可能得不償失

默認值利用：隱式列不出現在 SQL 中

-- 表定義：col3 INT DEFAULT 100
-- 插入時省略 col3
INSERT INTO t (col1, col2) VALUES (1, 'abc');
-- 好處：SQL 更短，網絡包更小，解析更快，服務端自動填充默認值

總結：大數據裝載決策樹

1. 能用 LOAD DATA INFILE（文件在服務器） → **首選**
2. 文件只在客戶端 → LOAD DATA LOCAL INFILE 或應用層分批多值 INSERT
3. 目標表有二級索引 → **先導數據，後建索引**
4. MyISAM 且可容忍崩潰風險 → DELAYED_KEY_WRITE
5. 寫入不需即時可見 → INSERT DELAYED 或 臨時表緩衝合併
6. InnoDB → 顯式事務批量提交，調大 innodb_log_file_size、innodb_buffer_pool_size

數據裝載優化的本質是**將隨機、碎片、高頻的操作，轉化為順序、批量、低頻的操作**——這正是計算機系統性能優化的普世真理。