MySQL UPDATE 進階實戰：關聯更新、子查詢聚合與 Rows matched 差異深度解析

測試環境

MySQL 5.6.21
CREATE TABLE product (proID INT PRIMARY KEY AUTO_INCREMENT, price DECIMAL(10,2), type INT, dtime DATETIME);
CREATE TABLE producttype (ID INT PRIMARY KEY, amount INT);
INSERT INTO product(price,type,dtime) VALUES(10,0,NOW()),(10,1,NOW()),(10,1,NOW()),(20,2,NOW()),(30,3,NOW());
INSERT INTO producttype(ID) VALUES(1),(2),(3);

核心案例對比

案例 1：子查詢更新 (全表掃描)

UPDATE producttype SET amount = (
  SELECT IFNULL(SUM(p.price),0) FROM product p WHERE p.type = producttype.ID GROUP BY p.type
);
-- Rows matched: 4  (含 ID=3,4 無匹配行)
-- 實際更新: 3 行

**缺點**：無匹配行也參與更新，產生額外行鎖、Binlog、Undo。

案例 2：衍生表 JOIN 更新 (精準匹配)

UPDATE producttype pt
JOIN (
  SELECT type, IFNULL(SUM(price),0) AS total FROM product GROUP BY type
) p ON pt.ID = p.type
SET pt.amount = p.total;
-- Rows matched: 3  (僅匹配行)
-- 實際更新: 3 行

**優勢**：只處理有關聯的行，減少鎖競爭與日誌量。

MySQL 5.6 關聯更新語法規範

• **不支援** UPDATE t1 SET ... FROM t2 WHERE ... (SQL Server 風格)
• **必須使用** UPDATE t1 JOIN t2 ON ... SET ... WHERE ...
• 多表更新順序：UPDATE 低選擇性表 JOIN 高選擇性表 ... 通常更優

避坑與最佳實踐

效能監控指標

• Handler_read_rnd_next：全表掃描計數，關聯更新應接近 0
• Innodb_rows_updated：實際更新行數
• Innodb_row_lock_time_avg：平均鎖等待時間

選對更新模式，少鎖、少日誌、快執行。