INSERT 是寫入頻率最高的 SQL，性能直接決定業務吞吐上限。本文匯總官方文檔最佳實踐、引擎層原理、實測數據對比，提煉 8 大核心優化策略與 Java 落地代碼。

單行插入性能剖析

插入一行耗時分解（比例概估）：

結論：解析、網絡、索引維護是優化主戰場。

8 大核心優化策略

1. 多值 INSERT 合並（單條 SQL 承載多行）

-- 10 行合為 1 條
INSERT INTO t (a,b,c) VALUES 
(1,2,3), (4,5,6), (7,8,9), ...;

✅ 減少解析/網絡/日志刷盤開銷 | ⚠️ 受 max_allowed_packet 限制

2. INSERT DELAYED（MyISAM/MEMORY/Archive/Blackhole）

INSERT DELAYED INTO t VALUES (...);

✅ 客戶端即時返回，數據入隊列異步寫盤 | ❌ InnoDB 不支援 | ⚠️ 異常關機數據丟失

3. 索引/數據文件分盤（建表選項）

CREATE TABLE t (...) 
INDEX DIRECTORY = '/ssd/index', 
DATA DIRECTORY = '/hdd/data';

✅ I/O 隔離 | ⚠️ 僅 MyISAM 支援，InnoDB 用表空間管理

4. bulk_insert_buffer_size（MyISAM 專用）

SET GLOBAL bulk_insert_buffer_size = 67108864;  -- 64MB

✅ 優化批量插入索引樹構建 | ❌ InnoDB 無效

5. LOAD DATA INFILE（文本導入王者）

LOAD DATA INFILE '/path/data.txt' INTO TABLE t 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

✅ 官方稱比 INSERT 快 20 倍 | 💡 先刪索引導入後重建，減少 Disk Seek

6. REPLACE / INSERT IGNORE 語義替代

• REPLACE INTO：主鍵/唯一鍵衝突時刪除舊行插入新行
• INSERT IGNORE：衝突靜默忽略，不報錯
• 根據業務語義選擇，避免應用層 SELECT → IF EXISTS → INSERT/UPDATE 往返

7. 表鎖批量提交（MyISAM）

LOCK TABLES t WRITE;
INSERT INTO t VALUES (...), (...), ...;
INSERT INTO t VALUES (...), (...);
UNLOCK TABLES;

✅ 索引緩衝區僅刷盤一次 | ⚠️ InnoDB 用 START TRANSACTION / COMMIT 替代

8. 事務批量提交（InnoDB 核心）

START TRANSACTION;
INSERT ...; INSERT ...; ...;  -- 批次內多行
COMMIT;

✅ 減少 Redo Log fsync | 💡 批次大小受 innodb_log_buffer_size 制約

實測數據對比（MyISAM 表，單/多值/鎖/事務）

階段	佔比	說明
Connecting	3%	建立連接
Sending query	2%	網絡傳輸
Parsing query	2%	SQL 解析
Inserting row	1×行大小	數據寫入
Inserting indexes	1×索引數	二級索引維護，O(log N)
Closing	1%	清理上下文

方案	1千行	5千行	1萬行	5萬行	10萬行
單條 INSERT	24s	160s	277s	--	--
多值 INSERT (1000/批)	2s	6s	11s	51s	99s
10線程並發多值 (1000/批)	5-10s	27-49s	49-84s	275-377s	552-648s
10線程並發 (表已有100萬，再插900萬)	499s/10萬	~	~	~	4200s/900萬

關鍵洞見

1. 多值 INSERT 是銀彈：1000 行/批提升 10-20 倍，邊際收益遞減
2. 並發寫入有上限：鎖競爭、Buffer Pool 爭用導致擴展性非線性
3. 大表寫入衰退不可逆：索引深度增長、碎片累積，需定期 OPTIMIZE TABLE 或分區/分表
4. InnoDB 必須「有序+事務+多值」三位一體：缺一不可達到峰值吞吐

Java 批量寫入模板

public void batchInsert(List records) {
    String sql = "INSERT INTO t (a,b,c) VALUES (?,?,?)";
    try (Connection conn = ds.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {
        
        conn.setAutoCommit(false);
        int batchSize = 1000;
        int count = 0;
        
        for (Record r : records) {
            ps.setObject(1, r.getA());
            ps.setObject(2, r.getB());
            ps.setObject(3, r.getC());
            ps.addBatch();
            
            if (++count % batchSize == 0) {
                ps.executeBatch();
                conn.commit();
                ps.clearBatch();
            }
        }
        ps.executeBatch();
        conn.commit();
    }
}

INSERT 優化的本質是將離散的隨機寫入轉化為連續的順序批次。掌握 8 大策略，根據引擎、業務、數據量靈活組合，才能榨乾磁盤 IOPS 的每一分價值。