MySQL 效能優化 21 條黃金法則：從查詢快取到架構設計

前言

資料庫操作往往是 Web 應用最大的效能瓶頸。本文針對 MySQL 整理 21 條實戰優化原則，涵蓋 SQL 撰寫、索引設計、架構調整三大維度，助你系統化提升資料庫吞吐量。

一、查詢層面優化

1. 善用查詢快取

避免在 WHERE 條件使用 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/range/eq_ref）、key（實際使用索引）、rows（掃描行數）、Extra（Using index/Using where/Using temporary/filesort）。

3. 明確只需一筆時加 LIMIT 1

讓優化器在找到第一筆即停止掃描：

SELECT 1 FROM user WHERE country='China' LIMIT 1;

4. 搜尋欄位建立索引

但 LIKE '%keyword%' 導致全表掃描，長文本搜尋請用全文索引或 Elasticsearch。

5. JOIN 欄位型別、字元集、長度必須一致

否則隱含轉換導致索引失效。

6. 禁用 ORDER BY RAND()

改用應用層隨機 OFFSET：

SELECT COUNT(*) FROM user; -- 假設 10000
SET @offset = FLOOR(RAND() * 10000);
SELECT * FROM user LIMIT @offset, 1;

7. 禁止 SELECT *

只取需要欄位，減少網路傳輸與記憶體佔用。

二、結構設計優化

8. 每張表必有無意義整數主鍵

INT UNSIGNED AUTO_INCREMENT，避免 UUID、VARCHAR 做主鍵造成頁面分裂與二級索引膨脹。

9. 固定值集合用 ENUM 代替 VARCHAR

儲存為 TINYINT，查詢效能提升、空間壓縮。

10. 善用 PROCEDURE ANALYSE() 獲取結構建議

僅供參考，決策仍需結合實際資料分佈。

11. 儘量 NOT NULL

NULL 需額外位圖標記，複雜化比較邏輯，MyISAM 每欄位額外 1 bit。

12. 使用 Prepared Statements

防 SQL 注入、重複解析優化、二進制協議傳輸更高效。注意 5.1 前不支援查詢快取。

13. 大結果集考慮無緩衝查詢

mysql_unbuffered_query() 逐行處理，節省記憶體，但限制較多（不可 mysql_num_rows、需讀完或釋放才能下一查詢）。

14. IP 位址存 UNSIGNED INT

INET_ATON()/INET_NTOA() 或 PHP ip2long()/long2ip() 轉換，範圍查詢 BETWEEN 極快。

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

位移計算 O(1)，易快取重建。可用垂直分割將變長欄位移出。

16. 垂直分割拆分熱冷欄位

如 last_login 頻繁更新導致快取失效，移至獨立表避免影響核心查詢快取。

三、運維與架構優化

17. 大量 DELETE/INSERT 分批執行

避免長時間鎖表導致連線堆積：

WHILE 1=1 DO
  DELETE FROM logs WHERE log_date <= '2023-01-01' LIMIT 1000;
  IF ROW_COUNT() = 0 THEN LEAVE; END IF;
  DO SLEEP(0.05);
END WHILE;

18. 選擇最小足夠的資料型別

TINYINT/SMALLINT/MEDIUMINT 替代 INT，DATE 替代 DATETIME，預留擴充空間避免未來 ALTER TABLE 鎖表數小時。

19. 正確選擇儲存引擎

• MyISAM：讀多寫少、需 COUNT(*) 極快場景
• InnoDB：行鎖、事務、外鍵、寫入密集、高併發首選

20. 引入 ORM 但警惕 N+1 問題

延遲載入便利但可能產生大量小查詢，關鍵路徑手寫 SQL 或 JOIN FETCH。

21. 謹慎使用持久連線

Apache prefork 模式下連線洩漏風險高，建議短連線 + 連線池（如 HikariCP）。

總結

優化是系統工程，無銀彈。建立 監控 → 分析 → 優化 → 驗證 閉環，結合 EXPLAIN、SHOW PROFILE、Performance Schema、慢查詢日誌，持續演進才是王道。