MySQL 查詢優化五大實戰法則：資料型別選擇、字符集轉換、COUNT 優化、子查詢轉 JOIN、UNION 去重效能

MySQL 查詢優化五大核心法則與實戰技巧

同樣邏輯的 SQL，寫法不同效能可差百倍。本文從資料型別、字符集、聚合函數、子查詢、集合運算五個維度，給出可落地的優化法則與避坑指南。

法則一：優化資料型別選擇

1. 避免 NULL

• NULL 需特殊儲存（位圖）、特殊索引邏輯、特殊比較語意（IS NULL）
• 建表預設 NOT NULL，必要時用 0、-1、'' 作為語意預設值

2. 足夠小、剛好夠

• 磁碟 I/O、記憶體、CPU 快取皆受益於更小型別
• 例：TINYINT (1B) vs INT (4B)，CHAR(10) vs VARCHAR(255)
• 但留擴充空間：頻繁 ALTER TABLE 修改型別會重建表、鎖表、風險大

法則二：小心字符集隱含轉換

• 客戶端/連線/表字符集不一致 → 隱含 CONVERT() 消耗 CPU
• UTF-8/utf8mb4 多位元組佔用更多空間，規劃長度時乘 3~4
• 統一全鏈路 utf8mb4，避免轉換開銷

法則三：優化 COUNT(*) 與 COUNT(col)

進階優化

• 彙總表：觸發器/應用層/定時任務維護 summary_table，查詢 O(1)
• 覆蓋索引：WHERE 欄位 + COUNT 欄位建立複合索引，避免回表
• 近似值可接受：定時任務刷新彙總表，捨精確換效能

法則四：子查詢轉 JOIN（衍生表優化）

問題

MySQL 優化器對子查詢（特別是 IN (SELECT...)）處理較弱，常退化為全表掃描 + 臨時表。

改寫模板

-- 原：子查詢
SELECT * FROM t1 WHERE id IN (SELECT t1_id FROM t2 WHERE status = 1);

-- 優化：JOIN
SELECT t1.* FROM t1
JOIN t2 ON t1.id = t2.t1_id
WHERE t2.status = 1;

關鍵點

• 被驅動表（t2）連線欄位必須有索引
• 驅動表（t1）通常全表掃描，配合 LIMIT 或條件下推
• MySQL 8.0+ CTE (WITH) 可讀性更好，效能相當

法則五：UNION 去重效能陷阱

問題

UNION = UNION DISTINCT，需排序去重，大表極慢。

解決方案

實戰範例：置頂 + 普通文章分頁

(SELECT * FROM blog WHERE top = 1 ORDER BY created DESC)
UNION ALL
(SELECT * FROM blog WHERE top = 0 ORDER BY created DESC)
LIMIT 2, 3;

總結：優化閉環

1. 開啟 slow_query_log，定期分析 Top 10 慢查詢
2. EXPLAIN 驗證執行計畫，關注 type、key、rows、Extra
3. 在真實數據量環境壓測，對比基準點
4. 建立索引變更/ SQL 改寫 變更管理流程

優化無終點，但有法則。掌握五大法則，建立度量體系，持續迭代。