MySQL 效能優化實戰經驗總結：架構設計、索引策略與查詢改寫三大維度

凝練 MySQL 生產環境優化精華，從資料庫結構設計、索引建立策略、SQL 查詢改寫三個維度給出可落地的最佳實務清單。

一、資料庫結構設計原則

1. 欄位型別選擇：
   • 長度越小越好（節省儲存、索引、記憶體）。
   • 優先定長型別 (CHAR vs VARCHAR)，減少碎片。
   • 盡可能 NOT NULL，避免 NULL 判斷開銷。
   • 數值欄位避免 ZEROFILL。
   • 已知有限值集合優先用 ENUM 或 SET。
2. 索引優化黃金法則 (查詢密集場景)：
   • 被索引欄位長度越短，效率越高。
   • 欄位值重複度越低 (選擇性越高)，效率越高。
   • GROUP BY 欄位順序建立複合索引。
   • DISTINCT 欄位順序建立複合索引。
   • WHERE 多個 AND 條件，按出現順序建複合索引。
   • WHERE 多個 OR 條件，只對重複值最少的單欄建索引。
   • 連接查詢必須對「連接欄位」建索引。
   • 主鍵本身已是唯一索引，額外加 UNIQUE 無意義。
   • 被索引欄位盡量 NOT NULL。
   • 寫入密集表：減少索引，尤其複合索引與唯一索引。

二、查詢語句優化清單

1. 善用 EXPLAIN 分析索引使用情況，尋找最佳寫法。
2. 禁用 SELECT *，只取必要欄位。
3. 索引掃描行數越少、欄位越短，速度越快 (EXPLAIN 觀察 rows)。
4. 避免 MySQL 內建函數處理結果，交給應用層處理。
5. LIMIT 偏移量越小越快；深分頁優化需改用游標/延遲關聯。
6. WHERE 多欄位 AND 順序必須與複合索引順序一致。
7. LIKE 只有非前導萬用字元才用索引 (abc% 是，%abc 否)。
8. MySQL 4.1+ 避免子查詢，改用 JOIN (早期版本子查詢效能極差)。
9. 減少函數呼叫，能用簡單運算式替代就替代。
10. 拆分 OR 條件為多條 UNION ALL 查詢，各走各的索引。

附：MySQL 數值型別儲存大小速查