MySQL INSERT 語句全攻略：從基礎語法到批量插入、事務優化、參數調優的性能提升實戰

MySQL INSERT 語句全攻略：從基礎語法到批量插入、事務優化、參數調優的性能提升實戰

INSERT 是最常用的 DML 操作，但高並發寫入場景下性能差異可達數十倍。本文系統梳理語法變體、批量寫入技巧、事務邊界控制、InnoDB 關鍵參數調優，助你構建高吞吐寫入鏈路。

基礎語法與常見變體

-- 1. 標準插入（指定列，防止表結構變更導致錯誤）
INSERT INTO `table` (col1, col2, col3) VALUES (val1, val2, val3);

-- 2. 多行單語句插入（核心優化手段，減少網絡往返與解析開銷）
INSERT INTO `table` (col1, col2) VALUES
  (val1, val2), (val3, val4), (val5, val6);

-- 3. INSERT ... SELECT（表間數據遷移/複製）
INSERT INTO target_table (col1, col2)
SELECT col1, col2 FROM source_table WHERE condition;

-- 4. INSERT IGNORE / ON DUPLICATE KEY UPDATE（冲突處理）
INSERT INTO `table` (id, val) VALUES (1, 'a')
ON DUPLICATE KEY UPDATE val=VALUES(val);

PHP 腳本插入示例（參考）

<?php
$conn = new mysqli('localhost', 'root', 'pass', 'db');
$stmt = $conn->prepare("INSERT INTO runoob_tbl (title, author, date) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $title, $author, $date);
// 循環綁定執行...
$stmt->close();
$conn->close();
?>

性能優化三板斧

1. 批量插入：單條 → 多值 VALUES

原理：減少客戶端-服務端網絡往返（RTT）、SQL 解析優化器開銷、Binlog 組提交頻率。

-- 差：循環執行 10000 次 INSERT（耗時 ~10s+）
-- 優：單條 INSERT 包含 10000 行 VALUES（耗時 ~0.5s）
-- 注意：max_allowed_packet 默認 4MB/1MB，超大批次需調大或分批

2. 事務包裹：自動提交 → 手動提交

START TRANSACTION;
INSERT ...; INSERT ...; ... (批量)
COMMIT;
-- 原理：將多次 fsync 合併為一次，利用 redo log 組提交（group commit）
-- 注意：事務過大超過 innodb_log_buffer_size 會導致頻繁刷盤，反而變慢。建議每 1000-5000 行提交一次。

3. 關鍵 InnoDB 參數調優（my.cnf）

實戰壓測對比（參考數據）

場景：插入 100 萬行，每行 5 列，SSD 雲盤，16C32G 實例
--------------------------------------------------------------
單條自動提交          |  ~2,000 rows/sec  |  基準
批量 1000 行/語句     |  ~15,000 rows/sec |  7.5x
批量 1000 行 + 事務   |  ~45,000 rows/sec |  22x
批量 1000 行 + 事務 + 參數調優 (flush_log=2, buffer_pool=20G) | ~120,000 rows/sec |  60x

避坑指南

• max_allowed_packet：批量包體超過限制會報錯 Packet too large，動態調整 SET GLOBAL max_allowed_packet=64M; 或分批。
• 主鍵設計：自增主鍵連續寫入無頁分裂；UUID/雪花算法隨機寫入導致 B+ 樹頻繁分裂，性能下降 50%+。
• 二級索引過多：每個 INSERT 需維護所有二級索引，僅保留必要索引。
• 外鍵約束：高並發寫入場景建議應用層維護完整性，移除 FK。
• 觸發器：同理，觸發器增加開銷，能不用則不用。

總結：高性能 INSERT 检查清單

1. ✅ 使用多值 VALUES 批量插入（每批 1000-5000 行）。
2. ✅ 顯式事務包裹，適當批次提交。
3. ✅ 自增主鍵 + 最少二級索引 + 無 FK/觸發器。
4. ✅ 核心參數：buffer_pool 足夠大、log_file_size ≥ 1G、flush_log_at_trx_commit=2（可接受秒級丟失）。
5. ✅ 監控 Innodb_log_waits、Innodb_buffer_pool_wait_free 為 0。

掌握這些，寫入性能不再是瓶頸。