MySQL 效能優化實戰筆記：NULL 處理、IN/OR/UNION 選擇、大 SQL 拆解、Load Data、批量更新、去重、常用函數技巧

MySQL 效能優化實戰筆記：從寫法細節到架構層面的 13 條鐵律

1. NULL 值與 SUM 陷阱

SUM(A+B) ≠ SUM(A)+SUM(B) 當 A 或 B 為 NULL 時，SUM(A+B)=NULL。
解法：SUM(COALESCE(A,0) + COALESCE(B,0)) 或 SUM(IFNULL(A,0)+IFNULL(B,0))。

2. 同欄位多值匹配：OR → IN

WHERE status=1 OR status=2 OR status=3 → WHERE status IN (1,2,3)
效能：OR O(n) 線性掃描，IN O(log n) 二分查找（優化器轉為範圍查詢）。
⚠️ IN 列表建議 < 200 個值，超過考慮臨時表 JOIN。

3. 不同欄位 OR → UNION ALL

WHERE a=1 OR b=2 通常走不了索引 → 改為：
SELECT * FROM t WHERE a=1 UNION ALL SELECT * FROM t WHERE b=2
去重需求用 UNION，無去重必用 UNION ALL（避免排序去重開銷）。

4. UNION ALL vs UNION

無去重需求 → UNION ALL。
UNION 會排序去重，開銷大；UNION ALL 直接合併結果集。

5. GROUP BY 隱含排序消除

GROUP BY col 預設按分組欄位排序。
不需排序 → GROUP BY col ORDER BY NULL 或 GROUP BY col DESC (5.7+)。

6. 字串轉數字：索引效能提升

數字型索引比字串型：空間小、比較快、CPU 友善。
設計階段優先選 INT/BIGINT，避免 VARCHAR 存純數字。

7. 拒絕大 SQL，拆解為多條簡單 SQL

• 單條大 SQL 易阻塞整庫、鎖表久 (MyISAM)
• 簡單 SQL：緩存命中率高、可並行執行、利用多核 CPU
• 一條 SQL 僅能在單核執行

8. 批量匯入：LOAD DATA 碾壓 INSERT

• LOAD DATA 比 INSERT 快 **20 倍**
• 成批裝載減少緩衝刷新、無索引裝載最快、INSERT VALUES(),(),() 減少索引刷新
• 避免 INSERT ... SELECT ...：延遲高、同步易出錯

9. 大批量更新節流策略

• 凌晨低峰執行
• 白天限流：預設 100 行/秒，特殊申請放寬
• 分批提交，避免長事務鎖住表

10. INT(M) 顯示寬度誤區

INT(3) vs INT(11)：**儲存皆 4 位元組，範圍完全相同**。
M 僅影響 ZEROFILL 時的前導零顯示：INT(3) ZEROFILL 存 10 → 顯示 010。
不加 ZEROFILL 時 M 無任何實質作用。

11. COUNT(DISTINCT col) 殺手優化

大表 COUNT(DISTINCT col) 記憶體暴漲甚至 OOM。
優化：臨時表分步驟 ——

-- 步驟 1：去重寫入臨時表
CREATE TEMPORARY TABLE tmp AS 
SELECT col FROM big_table GROUP BY col;

-- 步驟 2：計數
SELECT COUNT(*) FROM tmp;

12. 常用函數速查

13. 單表去重保留最小 ID

MySQL 禁止同表子查詢更新/刪除，用臨時表繞過：

DELETE FROM t 
WHERE id NOT IN (
  SELECT id FROM (
    SELECT MIN(id) AS id FROM t GROUP BY uid
  ) tmp
);