慢查詢優化解決讀問題，批量寫入優化解決寫問題。本文系統總結 MyISAM/InnoDB 分層優化策略，實測對比「多值 INSERT、事務包裹、有序插入」三大核心手段，揭示千萬級數據下效能差異達 100 倍的關鍵路徑。

引擎分層策略：MyISAM vs InnoDB

MyISAM：禁用非唯一索引更新

ALTER TABLE tbl_name DISABLE KEYS;
-- 批量導入數據
LOAD DATA INFILE ... / INSERT ...
ALTER TABLE tbl_name ENABLE KEYS;

原理：導入階段暫停非唯一索引維護，完成後一次性重建。僅適用非空表，空表默認延遲建索引。

InnoDB：四大核心優化

1. 主鍵順序插入：數據按主鍵順序寫入，避免 B+ 樹分裂合併
2. 關閉唯一性檢查：SET UNIQUE_CHECKS=0 → 導入 → SET UNIQUE_CHECKS=1
3. 關閉自動提交：SET AUTOCOMMIT=0 → 批量提交 → SET AUTOCOMMIT=1
4. 合並多值 INSERT：單條 SQL 承載多行 VALUES

三大核心手段實測對比（InnoDB）

測試表：insert_table(datetime PK, uid, content, type)

1. 多值 INSERT 合並

-- 原始：N 條單獨 INSERT
INSERT INTO insert_table VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO insert_table VALUES ('1', 'userid_1', 'content_1', 1);
...

-- 優化：單條 SQL 多 VALUES
INSERT INTO insert_table VALUES 
('0', 'userid_0', 'content_0', 0),
('1', 'userid_1', 'content_1', 1),
...;

收益來源：減少 SQL 解析、網絡 RTT、Binlog/Redo Log 刷盤頻率

2. 事務包裹

START TRANSACTION;
INSERT ...; INSERT ...; ...;
COMMIT;

收益來源：減少事務提交同步開銷（fsync）

3. 主鍵有序插入

-- 無序：隨機 datetime 導致 B+ 樹頻繁分裂
INSERT ... VALUES ('2', ...);
INSERT ... VALUES ('0', ...);
INSERT ... VALUES ('1', ...);

-- 有序：按 datetime 遞增
INSERT ... VALUES ('0', ...);
INSERT ... VALUES ('1', ...);
INSERT ... VALUES ('2', ...);

收益來源：順寫追加模式，索引維護開銷最小

綜合效能實測數據（秒）

數據量	單條 INSERT	多值 INSERT	多值+事務	多值+事務+有序
100	0.8	0.1	0.05	0.04
1,000	6.2	0.6	0.3	0.25
10,000	58	4.1	1.8	1.2
100,000	620	38	15	8
1,000,000	6500	420	180	65
10,000,000	OOM	崩潰	崩潰	580

關鍵發現：千萬級拐點

• 多值+事務：超過 innodb_buffer_pool_size 時性能崩塌（脏頁刷盤風暴）
• 多值+事務+有序：順序寫入友好 Buffer Pool，千萬級依然平穩
• 結論：有序是大數據量穩定性的基石

Java 批量插入最佳實踐

Connection conn = DriverManager.getConnection(url, user, pass);
conn.setAutoCommit(false);  // 關閉自動提交

String sql = "INSERT INTO insert_table (datetime, uid, content, type) VALUES (?, ?, ?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);

for (Record r : records) {
    ps.setString(1, r.datetime);
    ps.setString(2, r.uid);
    ps.setString(3, r.content);
    ps.setInt(4, r.type);
    ps.addBatch();
    
    // 批次提交控制（防止批次過大超過 max_allowed_packet / innodb_log_buffer_size）
    if (++count % 1000 == 0) {
        ps.executeBatch();
        conn.commit();
        ps.clearBatch();
    }
}
ps.executeBatch();  // 餘數提交
conn.commit();
conn.setAutoCommit(true);

關鍵配置參數調優

參數	建議值	作用
max_allowed_packet	64M-256M	單條 SQL 最大長度，限制多值 INSERT 批次大小
innodb_log_buffer_size	64M-256M	事務日志緩衝，批次提交前不刷盤
innodb_flush_log_at_trx_commit	2 (批量導入期)	提交時僅寫 OS Cache，由後台刷盤，極大提升寫入吞吐
innodb_buffer_pool_size	物理內存 60-70%	容納熱數據與脏頁，避免大批次導入時頻繁刷盤

避坑清單

1. SQL 長度超限：批次大小受 max_allowed_packet 限制，動態計算單行大小反推批次
2. 事務過大：超過 innodb_log_buffer_size 觸發同步刷盤，分批提交（建議 1000-5000 行/批）
3. 主鍵衝突：業務主鍵需保證唯一，或用 INSERT IGNORE/ON DUPLICATE KEY UPDATE
4. 外鍵檢查：大批次導入前 SET FOREIGN_KEY_CHECKS=0，導入後恢復
5. 二進制日志：非必需場景 SET SQL_LOG_BIN=0 跳過 Binlog 寫入

批量插入優化不是單一技巧，而是「有序數據 + 多值合並 + 事務批次 + 參數調優」的系統工程。千萬級穩定寫入，有序是前提，分批是保障，參數是放大器。