MySQL 效能優化 21 條軍規：從查詢緩存到架構擴展全維度指南

MySQL 效能優化 21 條軍規：從查詢緩存到架構擴展全維度指南

針對 Web 應用最高頻的 MySQL，本文匯總 21 條實戰優化法則，涵蓋 SQL 寫法、索引設計、架構選型、參數調優、運維習慣，助你系統化提升資料庫效能。

SQL 層優化（1-10）

1. 善用查詢緩存：避免 NOW()、RAND() 等非確定性函數導致緩存失效；改用應用層變數傳入固定值。
2. EXPLAIN 所有 SELECT：關注 type（目標 ref/range 以上）、rows（掃描行數）、Extra（Using index/Using where/Using temporary）。
3. 單行查詢加 LIMIT 1：讓優化器找到首筆即停止掃描。
4. 為搜尋欄位建索引：LIKE 'prefix%' 可用索引，LIKE '%suffix%' 需全文索引或外部搜尋引擎。
5. JOIN 欄位類型、字符集、長度完全一致：否則無法用上索引，產生隱式轉換導致全表掃描。
6. 禁用 ORDER BY RAND()：改用 SELECT * FROM tbl WHERE id >= (SELECT FLOOR(RAND() * MAX(id)) FROM tbl) LIMIT 1 或應用層隨機 ID。
7. 避免 SELECT *：只取需要欄位，減少網傳、記憶體、磁碟 I/O。
8. 每表設無業務含義的 INT UNSIGNED AUTO_INCREMENT 主鍵：VARCHAR 主鍵導致索引膨脹、插入隨機 I/O；關聯表除外（複合主鍵）。
9. 枚舉值用 ENUM 代替 VARCHAR：儲存 1 Byte、比較速度快、隱性約束合法值。
10. 用 PROCEDURE ANALYSE() 獲取結構建議：在 phpMyAdmin 點「Propose table structure」或執行 SELECT * FROM tbl PROCEDURE ANALYSE()。

Schema 與儲存優化（11-15）

11. 盡量 NOT NULL：NULL 額外佔 1 bit（MyISAM）且比較邏輯複雜；空字串/0 替代語義空值。
12. Prepared Statement：防注入、解析一次多次執行、二進制協議傳輸更小；注意 5.1+ 才支援查詢緩存。
13. 大結果集用無緩衝查詢：mysql_unbuffered_query() 邊讀邊處理，釋放 Server 端緩衝，但客戶端需自行管理記憶體。
14. IP 存 UNSIGNED INT：INET_ATON()/INET_NTOA() 或 PHP ip2long()/long2ip()，4 Byte 定長、範圍查詢極快。
15. 定長表更快：無 VARCHAR/TEXT/BLOB 的表為靜態表，位移計算 O(1)；垂直拆分將變長欄位移出主表。

架構與運維優化（16-21）

16. 垂直拆分：將低頻、大欄位（如 last_login、address）移至擴展表，避免主表緩存頻繁失效。
17. 大批次 DELETE/INSERT 分塊 + 間隔：DELETE ... LIMIT 1000; usleep(50000); 避免長鎖表導致連接堆積雪崩。
18. 越小的列越快：字典表用 SMALLINT/TINYINT；無時間精度需求用 DATE 代替 DATETIME。
19. 選對引擎：核心業務 InnoDB（行鎖、事務、崩潰恢復）；唯讀報表 MyISAM（FullText、COUNT(*) 快）。
20. 引入 ORM（如 Doctrine）：Lazy Loading、批次寫入打包事務、自動參數綁定；警惕 N+1 問題。
21. 慎用持久連接：mysql_pconnect 易導致連接洩漏、記憶體耗盡、Max connections 打滿；短連接+連接池更可控。

落地建議

• 建立 SQL Review Checklist，上線前必過 EXPLAIN、索引覆蓋、無 SELECT *、無 RAND()。
• 啟用 slow_query_log + pt-query-digest 定期分析 Top 10 慢查詢。
• 監控關鍵指標：QPS、TPS、連接數、緩存命中率、主從延遲、磁碟 I/O wait。
• 優化是持續迭代過程，無銀彈，只有在特定負載下的最優解。

將這 21 條內化為團隊編碼規範，能從源頭消除 80% 以上的效能隱患。