MySQL 查詢優化五大法則：資料類型、字符集、COUNT、子查詢、UNION

1. 優化資料類型

• 避免 NULL：NULL 需額外位元組標記、索引邏輯複雜、優化器處理特殊。預設 NOT NULL，用 0、-1、空字串作預設值。
• 越小越好：TINYINT < SMALLINT < INT < BIGINT；CHAR 固定長、VARCHAR 變長。小類型減少磁碟 I/O、記憶體佔用、CPU 快取命中率高。但預留擴展空間，避免日後 ALTER TABLE 重建鎖表。

2. 小心字符集轉換

• 連線字符集（character_set_client）與表字符集不一致，MySQL 會隱式轉換，導致索引失效、CPU 浪費。
• 統一使用 utf8mb4，確認 SET NAMES utf8mb4 或連線參數 charset=utf8mb4。
• 注意 utf8mb4 字元最多 4 bytes，影響 VARCHAR 最大長度、索引長度限制（767 bytes/3072 bytes）。

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

• MyISAM：無 WHERE 時 COUNT(*) 極快（直接讀表統計行數）。
• InnoDB：無 WHERE 也需全表掃描（MVCC 可見性檢查），較慢。
• COUNT(col) 若欄位 NOT NULL，等同 COUNT(*)；若可為 NULL，需掃描過濾 NULL。
• 有 WHERE 時：建立覆蓋索引（含 WHERE 欄位 + 計數欄位）才能索引掃描。
• 大表近似計數：維護摘要表（觸發器/定時任務更新），或用 EXPLAIN 估算行數。

4. 優化子查詢 → JOIN

• MySQL 優化器對子查詢（特別是 IN/EXISTS 相關子查詢）優化較弱，常退化為全表掃描。
• 改寫為 JOIN（INNER JOIN、LEFT JOIN），確保被驅動表關聯欄位有索引。
• 巢狀迴圈演算法：外層表掃描子集，內層表索引查找，效能穩定。

5. 優化 UNION

• UNION 預設去重 + 排序，資源消耗大。
• 若業務允許重複或已知無重複，用 UNION ALL 避免排序去重，速度提升數倍。
• 應用層邏輯去重後再用 UNION ALL，效果相同但更快。

綜合建議

1. 定期檢視 slow_query_log，針對高耗時 SQL 優化。
2. 用 EXPLAIN 驗證執行計畫（type、key、rows、Extra）。
3. 在真實資料量環境測試，觀察 Handler_* 狀態變數。
4. 建立基準線，持續迭代優化。

結語

查詢優化無銀彈，但遵循「小類型、統一字符集、COUNT 摘要表、子查詢轉 JOIN、UNION ALL 替代 UNION」五大法則，配合執行計畫分析與慢查詢監控，能解決 80% 以上的效能問題。