21 條 MySQL 效能優化黃金法則 (2024 更新版)

彙整自 Percona、MySQL 官方文件、高併發實戰經驗的 21 條核心優化原則，涵蓋查詢緩存、索引設計、SQL 寫法、架構選型、連線管理等維度。每條附帶原理、反模式與最佳實踐。

1. 善用查詢緩存，避開不確定函數

SELECT * FROM t WHERE date > CURDATE() → 不走緩存。
改用：SET @today = CURDATE(); SELECT * FROM t WHERE date > @today; (變數固定值可命中緩存)。
原則：NOW()、RAND()、UUID()、CONNECTION_ID() 等非確定性函數會導致查詢緩存失效。

2. EXPLAIN 是調優起點

關注 type (目標：ref/range/eq_ref，避免 ALL/index)、key (實際用到的索引)、rows (預估掃描行)、Extra (Using index/Using where/Using temporary/Using filesort)。

3. 單行查詢加 LIMIT 1

SELECT 1 FROM users WHERE email = 'a@b.com' LIMIT 1; 找到即停止，避免全表掃描驗證唯一性。

4. 為搜尋欄位建索引，但認清 LIKE 陷阱

WHERE name LIKE 'abc%' 走索引；WHERE name LIKE '%abc%' **不走索引** (全文索引或應用層搜尋引擎)。

5. JOIN 條件欄位類型、字符集、長度必須一致

INT JOIN BIGINT、utf8 JOIN utf8mb4、VARCHAR(10) JOIN VARCHAR(20) 皆導致隱式轉換、索引失效。

6. 禁止 ORDER BY RAND()

全表產生隨機數、排序、取前 N。替代方案見 MySQL 隨機查詢優化、高效隨機多筆。

7. 避免 SELECT *

增加網路傳輸、記憶體佔用、緩存污染、覆蓋索引失效。明確列出需要欄位。

8. 每張表必有無業務含義的整數主鍵 (BIGINT UNSIGNED AUTO_INCREMENT)

業務鍵 (email、身分證) 變更頻率高、長度長、不利叢集索引有序插入。例外：純關聯表 (多對多) 可用複合主鍵。

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

儲存 1 byte (TINYINT)、比較快、隱含約束。適用：性別、狀態、類別、優先級。PROCEDURE ANALYSE() 會建議轉換。

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

phpMyAdmin「Propose table structure」即呼叫此函數。建議隨數據量增長而精準，**決策權在人**。

11. 儘可能 NOT NULL

NULL 需額外位圖儲存、比較語義複雜 (三值邏輯)、索引統計偏差。預設 NOT NULL DEFAULT '' 或 0。

12. 使用 Prepared Statements (預處理語句)

防 SQL 注入、解析一次多次執行、二進位協議傳輸高效。MySQL 5.1+ 支援查詢緩存。PHP 使用 mysqli::prepare 或 PDO::prepare。

13. 大結果集使用無緩衝查詢

mysql_unbuffered_query() / MYSQLI_USE_RESULT：逐行讀取、低記憶體、可邊讀邊處理。限制：需讀完或 free_result 才能執行下一查詢，不可 num_rows/data_seek。

14. IP 地址存 UNSIGNED INT (4 bytes)

INET_ATON('192.168.1.1') / INET_NTOA() / PHP ip2long()/long2ip()。範圍查詢 BETWEEN 極快。

15. 固定長度表 (無 VARCHAR/TEXT/BLOB) 更快

位移計算 O(1)、易緩存、易修復。垂直拆分將變長欄位移出核心表 (見第 16 條)。

16. 垂直拆分：將低頻、大欄位、頻繁更新欄位移出熱表

例：users (id, name, pwd, role) + user_profiles (id, address, bio, last_login)。避免 last_login 更新導致熱表查詢緩存失效。

17. 大批量 DELETE/INSERT 分批執行 (LIMIT 5000/10000)

避免長鎖表、Replication 延遲、Binlog 膨脹、主從不一致。DELETE FROM big_table WHERE created < '2023-01-01' LIMIT 10000; 迴圈。

18. 越小的資料類型越快

TINYINT(1) vs INT、DATE vs DATETIME、CHAR(32) vs VARCHAR(255) (固定長度)。預留擴展空間但勿過度。

19. 選對儲存引擎：InnoDB (預設) vs MyISAM

InnoDB：行鎖、事務、外鍵、崩潰恢復、叢集索引 → **OLTP 首選**。
MyISAM：表鎖、全文索引 (5.6 前)、COUNT(*) 極快、壓縮表 → **唯讀/分析/日誌** 適用。

20. 引入 ORM (Object Relational Mapper) 但警惕 N+1 問題

Hibernate、Entity Framework、Eloquent、SQLAlchemy。Lazy Loading 方便但易產生大量小查詢，關鍵路徑用 JOIN FETCH/load()/select_related() 預載入。事務批次提交比單條提交快數量級。

21. 謹慎使用「永久連線」 (Persistent Connections)

PHP mysql_pconnect / PDO::ATTR_PERSISTENT：減少握手但易導致連線洩漏、閒置連線佔用 max_connections、事務狀態殘留。**短連線 + 連線池 (HikariCP、ProxySQL、PHP-FPM 池化) 更可控**。

附錄：快速自查清單