MySQL 查詢優化五大實戰技巧：從數據類型到 UNION 改寫

SQL 語義等價不代表性能等價。本文提煉 5 條高收益優化技巧：數據類型收斂、字符集隱式轉換消除、COUNT 優化、子查詢轉 JOIN、UNION ALL 替代 UNION，附原理與適用場景。

1. 優化數據類型：越小越快，拒絕 NULL

• NOT NULL 默認：NULL 需額外位圖標記、特殊索引邏輯、更複雜的優化器判斷。建表默認 NOT NULL DEFAULT 0/-1/''。
• 精準選型：TINYINT 代替 INT 存枚舉，DATE 代替 DATETIME 存生日，DECIMAL(10,2) 存金額避免浮點誤差。
• 平衡原則：預留 20% 增長空間，避免未來 ALTER TABLE 鎖表重建。

2. 消除字符集隱式轉換

• 客戶端 utf8mb4、表 latin1、列 gbk → 每次交互觸發轉換，CPU 損耗 + 索引失效風險。
• 統一全鏈路 utf8mb4，驅動 URL 顯式指定 characterEncoding=utf8mb4。
• SHOW VARIABLES LIKE 'character_set%' 定期巡檢一致性。

3. COUNT(*) 與 COUNT(col) 優化

• InnoDB 無計數緩存，**近似值** 用 SHOW TABLE STATUS Rows，**精確值** 建計數表 + 觸發器維護或定時任務刷新。
• 覆蓋索引優化 COUNT(col)：INDEX (col, pk) 讓掃描僅走二級索引。

4. 子查詢轉 JOIN：優化器偏好的執行路徑

-- 子查詢（可能物化/半連接，版本依賴）
SELECT * FROM t1 WHERE id IN (SELECT t1_id FROM t2 WHERE status=1);

-- 等價 JOIN（穩定走 Nested Loop / Hash Join）
SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.t1_id WHERE t2.status=1;

• 確保被驅動表關聯列有索引（t2.t1_id）。
• MySQL 8.0+ CTE (WITH) 可讀性更佳，執行計劃等價。

5. UNION ALL 替代 UNION：省去排序去重開銷

• UNION = UNION ALL + DISTINCT（全量排序/哈希去重）。
• 業務已知無重複或可容忍重複 → 直接 UNION ALL，性能提升 50%+。
• 應用層去重（如 Set）替代數據庫排序，將壓力前移。

持續優化閉環

1. 開啟 slow_query_log（long_query_time=0 全采集）。
2. pt-query-digest 產出 Top SQL。
3. EXPLAIN ANALYZE (8.0.18+) 觀察實際執行統計。
4. 應用上述技巧重寫，壓測驗證。
5. 基準數據入庫，建立性能回歸防線。