MySQL 效能優化 19 大黃金法則：從查詢改寫到架構設計全方位指南

查詢層面優化（1-7）

1. 善用查詢快取

MySQL Query Cache 可將相同查詢結果直接回傳。避坑： 含不確定函數（NOW()、RAND()、CURDATE()）的查詢不會被快取。改用應用層變數帶入固定值：

-- 不走快取
SELECT * FROM user WHERE signup_date >= CURDATE();

-- 走快取
SET @today = '2024-01-15';
SELECT * FROM user WHERE signup_date >= @today;

2. EXPLAIN 是優化起點

關注 type（目標：ref/eq_ref/const）、key（實際用到的索引）、rows（預估掃描行數）、Extra（Using index/Using where/Using temporary; Using filesort）。

3. 只需一行用 LIMIT 1

-- 低效
SELECT * FROM user WHERE country = 'China';

-- 高效（停止掃描）
SELECT 1 FROM user WHERE country = 'China' LIMIT 1;

4. 搜尋欄位必建索引

不限主鍵/唯一鍵，任何高頻 WHERE、JOIN ON、ORDER BY 欄位皆應評估建立索引。

5. JOIN 欄位類型、字符集、排序規則必須一致

DECIMAL 不能 JOIN INT；utf8mb4 不能 JOIN latin1（除非顯式轉換），否則索引失效導致全表掃描。

6. 杜絕 ORDER BY RAND()

會觸發全表掃描+臨時表+filesort。隨機抽樣改用：WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t))) 或應用層隨機 ID。

7. 拒絕 SELECT *

• 增加網路傳輸、記憶體、CPU 開銷
• 破壞覆蓋索引優化機會
• Schema 變更時易引發應用錯誤

結構設計優化（8-14）

8. 每張表必有無業務意義的 INT UNSIGNED AUTO_INCREMENT 主鍵

VARCHAR 主鍵導致索引膨脹、頁分裂、外鍵關聯效能差。InnoDB 聚簇索引以主鍵為序，單調遞增主鍵寫入效能最高。

9. 固定值集合優先用 ENUM

ENUM 內部存儲為 TINYINT（1-2 字節），比 VARCHAR 緊湊且比較快。適用：性別、狀態、類型、部門等。

10. 善用 PROCEDURE ANALYSE() 獲取型別建議

SELECT * FROM tbl PROCEDURE ANALYSE();

根據實際資料分佈建議最佳型別（如 INT → MEDIUMINT、VARCHAR → ENUM），需有代表性資料量才準確。

11. 能 NOT NULL 就 NOT NULL

NULL 需額外位圖存儲（MyISAM 每欄 1 bit），比較邏輯複雜（IS NULL/IS NOT NULL），統計函數 COUNT(col) 不計 NULL。

12. IP 地址存 UNSIGNED INT

INET_ATON('192.168.1.1') → 3232235777（4 字節），範圍查詢 BETWEEN 極快，INET_NTOA() 還原顯示。

13. 固定長度表（無 VARCHAR/TEXT/BLOB）查詢更快

定長記錄可直接計算偏移量隨機讀取，利於快取與重建。代價是空間浪費，視業務權衡。

14. 垂直拆分：大欄位/低頻欄位移出主表

• 範例：last_login 高頻更新導致主表 Query Cache 失效 → 移至獨立表
• 範例：address 可選大文本 → 移至 user_profile 表
• 警告： 拆分後若頻繁 JOIN 回主表，效能反而下降。

維運與架構優化（15-19）

15. 大量 DELETE/INSERT 必須分批執行

while true; do
  mysql -e "DELETE FROM logs WHERE log_date < '2023-01-01' LIMIT 1000;"
  [ $? -ne 0 ] && break
  sleep 0.05
done

避免長時間鎖表導致連線堆積、主機資源耗盡。

16. 越小的型別越快

參考 Storage Requirements：TINYINT(1B) < SMALLINT(2B) < MEDIUMINT(3B) < INT(4B) < BIGINT(8B)。字典/配置表用 SMALLINT 主鍵即夠，預留擴充空間但勿過度。

17. 選對儲存引擎

18. 謹慎使用持久連線

mysql_pconnect() 理論減少建連開銷，實際易導致連線洩漏、超過 max_connections、記憶體/檔案描述符耗盡。現代 PHP-FPM/連線池已妥善管理短連線，建議預設關閉持久連線。

19. 子查詢改寫為 JOIN 往往有奇效

-- 子查詢：18.87 秒
SELECT * FROM guang_deal_outs 
WHERE deal_id IN (SELECT id FROM guang_deals WHERE id = 100017151);

-- JOIN：0.01 秒
SELECT a.* FROM guang_deal_outs a 
INNER JOIN guang_deals b ON a.deal_id = b.id 
WHERE b.id = 100017151;

原因： MySQL 將 IN (subquery) 轉為 EXISTS 相關子查詢，外部表大時效能崩潰。JOIN 讓優化器可選擇小表驅動大表並用上索引。

總結：優化是系統工程

1. 建立 慢查詢日誌 + 定期 EXPLAIN 分析 機制
2. 推行 Schema 變更審核（索引、型別、拆分策略）
3. 引入 自動化 SQL 審核工具（如 Yearning、Archery、SQLAdvisor）
4. DBA 定期輸出 Top N SQL 報告 協助開發優化
5. 監控對比 同期基線，快速發現異常

核心心法： 優化無銀彈，唯有「測量→分析→改寫→驗證」的閉環迭代，才能在業務增長中持續守住效能底線。