MySQL 百萬級數據更新性能對比：UPDATE vs REPLACE INTO vs 批量操作實測分析

測試場景

• 表結構：a(PK, varchar(20)), b, c, d, e, f
• 需更新 10,000 條記錄
• 不使用事務（模擬自動提交模式）
• 三種方案對比

方案詳解與性能預期

方案 1：單條 UPDATE

UPDATE `table` SET b=222, c=333, d=444 WHERE a='xx'; -- 執行 10,000 次

• 優點： 語義明確，僅更新存在行，索引利用率高
• 缺點： 10,000 次網絡往返、解析、優化、執行、提交開銷巨大
• 預估耗時： 5-15 秒（視網絡延遲、索引情況）

方案 2：單條 REPLACE INTO

REPLACE INTO `table` VALUES ('xx', 222, 333, 444, 'e_val', 'f_val'); -- 10,000 次

• 機制： REPLACE = DELETE (若主鍵/唯一鍵衝突) + INSERT
• 風險： 觸發 DELETE 會導致自增 ID 變更、外鍵級聯刪除、觸發器雙重觸發
• 性能： 比 UPDATE 慢（雙重操作），且無事務時數據一致性風險極大
• 不適用： 僅更新場景（題目明確「只需改，不需增」）

方案 3：批量操作（最佳實踐）

3.1 批量 UPDATE（CASE WHEN）

UPDATE `table` SET
    b = CASE a WHEN 'id1' THEN 222 WHEN 'id2' THEN 223 ... END,
    c = CASE a WHEN 'id1' THEN 333 WHEN 'id2' THEN 334 ... END,
    d = CASE a WHEN 'id1' THEN 444 WHEN 'id2' THEN 445 ... END
WHERE a IN ('id1', 'id2', ..., 'id10000');

• 單條 SQL 完成所有更新
• SQL 長度限制（max_allowed_packet，默認 4MB/64MB），10,000 條可能需分批
• 無法利用索引範圍掃描，全表掃描風險（視 WHERE IN 列表大小）

3.2 批量 REPLACE INTO / INSERT ... ON DUPLICATE KEY UPDATE

INSERT INTO `table` (a,b,c,d,e,f) VALUES
('id1', 222, 333, 444, 'e1', 'f1'),
('id2', 223, 334, 445, 'e2', 'f2'),
...
('id10000', ..., ..., ..., ..., ...)
ON DUPLICATE KEY UPDATE
    b = VALUES(b), c = VALUES(c), d = VALUES(d), e = VALUES(e), f = VALUES(f);

• 推薦： 語義清晰（有則更新，無則插入），單條 SQL，利用主鍵索引
• 性能最優，網絡往返 1 次
• 需確保 a 為主鍵或唯一索引

3.3 分批次預處理語句（PDO）

<?php
$pdo->beginTransaction();
$stmt = $pdo->prepare('UPDATE `table` SET b=?, c=?, d=? WHERE a=?');
foreach ($data as $row) {
    $stmt->execute([$row['b'], $row['c'], $row['d'], $row['a']]);
}
$pdo->commit(); // 或分批提交，每 1000 條 commit 一次
?>

• 預處理減少解析開銷
• 事務保證原子性（可選）
• 分批提交避免長事務鎖表、日志膨脹

實測數據參考（模擬環境：MySQL 8.0, SSD, 同機房網絡）

關鍵優化參數

[mysqld]
# 增加包大小限制，支持大批量 SQL
max_allowed_packet = 64M

# InnoDB 日志文件大小，減少检查点刷盘
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M

# 批量操作時暫時調大
innodb_flush_log_at_trx_commit = 2  # 事務提交不強制刷盤，性能提升數倍（掉電丟數據風險）
sync_binlog = 0                     # 同步 binlog 到磁盤策略

避坑指南

1. REPLACE INTO 會刪除舊行： 自增 ID 重置、外鍵關聯斷裂、觸發器 BEFORE DELETE/AFTER DELETE 觸發。
2. 批量 SQL 過長報錯： Packet too large，需分批或調大 max_allowed_packet。
3. WHERE IN 列表過長： 優化器可能放棄索引轉全表掃描，建議 IN 列表不超過 1000 個值，或使用臨時表 JOIN。
4. 長事務鎖等待： 批量更新時盡量縮短事務持有時間，分批提交。

總結建議

核心結論： 拒絕循環執行單條 SQL。批量化、預處理化、事務可控化，是 MySQL 大數據量寫入的鐵律。