場景：問答系統統計回覆數、同步城市 ID

-- 核心表
CREATE TABLE `ask` (
  `id` INT PRIMARY KEY,
  `city_id` INT,
  `borough_id` INT,
  `answer_num` INT DEFAULT 0
);

CREATE TABLE `answer` (
  `id` INT PRIMARY KEY,
  `father_id` INT,  -- 關聯 ask.id
  `content` TEXT
);

CREATE TABLE `city` (
  `id` INT PRIMARY KEY,
  `name` VARCHAR(50)
);

CREATE TABLE `borough` (
  `id` INT PRIMARY KEY,
  `city_id` INT,
  `name` VARCHAR(50)
);

1. 統計回覆數（關聯聚合查詢）

SELECT a.id, COUNT(ans.father_id) AS cnt, a.answer_num
FROM `ask` AS a
LEFT JOIN `answer` AS ans ON a.id = ans.father_id
GROUP BY a.id
ORDER BY a.id;

LEFT JOIN 保證無回覆的問題也顯示（cnt=0）。

2. 批量更新城市 ID（JOIN UPDATE）

UPDATE `ask` AS a
JOIN `borough` AS b ON a.borough_id = b.id
SET a.city_id = b.city_id;

• 單條 SQL 完成多表關聯更新，無需應用層循環。
• MySQL 支援 UPDATE ... JOIN ... SET 語法，原子執行。

3. 批量更新回覆數（子查詢 / JOIN UPDATE 兩種寫法）

寫法 A：關聯子查詢（MySQL 支援，但需注意 LIMIT 1）

UPDATE `ask` AS a
SET a.answer_num = (
  SELECT COUNT(*) 
  FROM `answer` 
  WHERE father_id = a.id
);

⚠️ 無 WHERE 條件會全表更新，大表極慢。建議加條件：

UPDATE `ask` AS a
SET a.answer_num = (
  SELECT COUNT(*) FROM `answer` WHERE father_id = a.id
)
WHERE a.id IN (SELECT DISTINCT father_id FROM `answer`);  -- 僅更新有回覆的

寫法 B：JOIN UPDATE（推薦，可見性更好）

UPDATE `ask` AS a
JOIN (
  SELECT father_id, COUNT(*) AS cnt
  FROM `answer`
  GROUP BY father_id
) AS ans ON a.id = ans.father_id
SET a.answer_num = ans.cnt;

• 衍生表聚合後再 JOIN 更新，避免逐行子查詢，效能指數級提升。

大數據量分批處理模板

-- 存儲過程：分批更新 answer_num
DELIMITER $$
CREATE PROCEDURE batch_update_answer_num()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_max_id INT;
  DECLARE v_batch_size INT DEFAULT 1000;
  
  SELECT MAX(id) INTO v_max_id FROM `ask`;
  
  WHILE done = 0 DO
    UPDATE `ask` AS a
    JOIN (
      SELECT father_id, COUNT(*) AS cnt
      FROM `answer`
      WHERE father_id > (SELECT COALESCE(MAX(id),0) - v_batch_size FROM `ask`)  -- 簡化邏輯，實際用游標
      GROUP BY father_id
    ) AS ans ON a.id = ans.father_id
    SET a.answer_num = ans.cnt
    WHERE a.id > (SELECT COALESCE(MAX(id),0) - v_batch_size FROM `ask`)  -- 示意
    LIMIT v_batch_size;
    
    IF ROW_COUNT() = 0 THEN SET done = 1; END IF;
    -- 實際生產用游標/臨時表分批更可控
  END WHILE;
END$$
DELIMITER ;

避坑指南

1. 子查詢返回多行報錯：Subquery returns more than 1 row → 聚合加 GROUP BY 或 LIMIT 1（但邏輯需正確）。
2. UPDATE 同表自關聯：MySQL 不允許 UPDATE t SET ... WHERE id IN (SELECT ... FROM t) → 用 JOIN 或 临时表 繞過。
3. 長事務鎖表：大表批量更新必須分批，每批提交，避免 Undo Log 膨脹、鎖升級、主從延遲。
4. 統計實時性 vs 效能：極高併發下，考慮 Redis 計數器 + 定時落庫、觸發器異步更新、物化視圖定時刷新，而非實時 JOIN UPDATE。
5. 外鍵/觸發器副作用：批量 UPDATE 會觸發 ON UPDATE CASCADE、觸發器，評估影響範圍。

最佳實踐總結

• 統計查詢：LEFT JOIN + GROUP BY，被驅動表關聯列有索引。
• 批量更新：衍生表聚合 + JOIN UPDATE，單條 SQL、集合操作、避免逐行子查詢。
• 大表操作：分批、低峰期、監控主從延遲，必要時停從庫復制、單主操作、完事再啟動。
• 替代方案：應用層緩存計數、異步消息隊列更新、ClickHouse/OLAP 引擎離線統計。