MySQL 效能優化 21 條黃金法則：從查詢緩存到架構選型

查詢與索引優化

1. 善用查詢緩存：避免 NOW()、RAND() 等不確定函數，改用變數預先計算（如 $today = date('Y-m-d')）啟用緩存。
2. EXPLAIN 分析執行計畫：關注 rows、type、key 欄位，發現全表掃描或缺失索引。
3. 單行查詢加 LIMIT 1：找到第一筆即停止，避免全表掃描。
4. 為搜尋欄位建索引：LIKE 'a%' 能用索引，LIKE '%a%' 不能，長文本改用全文索引或搜尋引擎。
5. JOIN 欄位類型、字符集必須一致：DECIMAL 不能與 INT 直接 JOIN 使用索引。
6. 禁用 ORDER BY RAND()：改用 COUNT(*) 取總數 + 應用層隨機 LIMIT offset,1。
7. 避免 SELECT *：只取需要的欄位，減少網路傳輸與 I/O。

表結構設計

8. 每表必有整數主鍵：INT UNSIGNED AUTO_INCREMENT，業務鍵（如 email）勿作主鍵。關聯表除外（複合主鍵）。
9. 枚舉值用 ENUM 代替 VARCHAR：儲存為 TINYINT，效能高、空間小。
10. PROCEDURE ANALYSE() 獲取結構建議：根據實際資料推薦更精簡類型（如 INT → MEDIUMINT）。
11. 盡量 NOT NULL：NULL 需額外位元組標記，比較邏輯複雜。

進階技巧

12. Prepared Statements：防注入、重複執行只解析一次、二進制協議傳輸高效。注意 5.1 前不支援查詢緩存。
13. 無緩衝查詢 mysql_unbuffered_query()：大結果集分批處理，節省記憶體，但需手動釋放、不可用 mysql_num_rows()。
14. IP 存為 UNSIGNED INT：4 字節定長，範圍查詢 BETWEEN 極快。用 INET_ATON()/INET_NTOA() 或 PHP ip2long() 轉換。
15. 固定長度表更快：無 VARCHAR/TEXT/BLOB 即為靜態表，偏移量計算快、易緩存。副作用：浪費空間。可用「垂直分割」分離變長欄位。
16. 垂直分割：將低頻/大欄位（如地址、last_login）移至副表，主表保持瘦身。避免頻繁 JOIN 否則效能指數級下降。
17. 大批量 DML 分批執行：DELETE ... LIMIT 1000 循環 + usleep() 間隙，避免長鎖表導致服務雪崩。
18. 越小的列越快：字典表用 TINYINT/SMALLINT，無時間精度需求用 DATE 而非 DATETIME。預留擴充空間避免未來 ALTER TABLE 鎖表數小時。
19. 選對引擎：MyISAM 適合多讀少寫、COUNT(*) 極快；InnoDB 支援行鎖、事務、外鍵，寫多場景優勢明顯。
20. 考慮 ORM（如 Doctrine）：Lazy Loading、批次打包事務提升效能，但小心 N+1 查詢陷阱。
21. 慎用永久連線 mysql_pconnect()：理論減少握手，實際易耗盡連線數、記憶體、檔案句柄，Apache 多進程模型下更難控制。