MySQL 效能優化 21 條黃金法則：從入門到精通

5. JOIN 欄位類型、字符集必須一致

-- 錯誤：DECIMAL JOIN INT，字符集不同
SELECT * FROM users JOIN companies ON users.state = companies.state;

雙方欄位型別、長度、字符集完全一致才能用上索引。

6. 禁止使用 ORDER BY RAND()

-- 極差效能
SELECT * FROM user ORDER BY RAND() LIMIT 1;

-- 替代方案
SELECT COUNT(*) FROM user; -- 取得總數 N
SET @offset = FLOOR(RAND() * N);
SELECT * FROM user LIMIT @offset, 1;

7. 避免 SELECT *

只查需要的欄位，減少網路傳輸、記憶體佔用、磁碟 I/O。

8. 每張表必須有整數主鍵

推薦 INT UNSIGNED AUTO_INCREMENT。VARCHAR 主鍵效能差，除非是關聯表的複合主鍵（外鍵組合）。

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

性別、狀態、部門等有限值集合，ENUM 內部存 TINYINT，極度緊湊高效。

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

在 phpMyAdmin 點「Propose table structure」或執行：

SELECT * FROM table_name PROCEDURE ANALYSE();

11. 能用 NOT NULL 就不用 NULL

NULL 需額外位元組標記，比較邏輯複雜，索引統計也不準。除非業務真正需要「未知」語意。

12. 使用 Prepared Statements

• 防 SQL 注入（自動綁定參數檢查）
• 重複執行只解析一次，效能提升
• 二進制協議傳輸，頻寬更低

$stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?");
$stmt->bind_param("s", $state);
$stmt->execute();

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

$result = mysqli_query($link, $sql, MYSQLI_USE_RESULT); // 不緩衝
while ($row = mysqli_fetch_assoc($result)) { /* 逐行處理 */ }
mysqli_free_result($result);

節省記憶體，首行即可開始處理，但需讀完或釋放才能執行下一查詢。

14. IP 地址存為 UNSIGNED INT

-- 寫入
UPDATE users SET ip = INET_ATON('192.168.1.1') WHERE id = 1;

-- 讀出
SELECT INET_NTOA(ip) FROM users WHERE id = 1;

PHP 對應 ip2long() / long2ip()。範圍查詢 BETWEEN 極速。

15. 固定長度表（Static）更快

無 VARCHAR/TEXT/BLOB 的表為固定長度，偏移量計算快、易快取、易重建。可用「垂直分割」將變長欄位移出。

16. 垂直分割

將低頻、大欄位（如 address、last_login）拆到另一表，核心表保持精簡，提升快取命中率。注意：分割後避免頻繁 JOIN，否則得不償失。

17. 大量 DELETE/INSERT 分批執行

while (true) {
    $affected = mysqli_query($link, "DELETE FROM logs WHERE log_date <= '2023-01-01' LIMIT 1000");
    if (mysqli_affected_rows($link) == 0) break;
    usleep(50000); // 讓出 CPU、減輕鎖壓力
}

18. 越小的資料型別越快

參考 Storage Requirements 選最小足夠型別：TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT。預留擴充空間但別過度。

19. 選對儲存引擎

20. 善用 ORM（Object Relational Mapper）

• Lazy Loading 延遲載入
• 自動批次 SQL、事務管理
• 推薦：Doctrine (PHP)、Hibernate (Java)、SQLAlchemy (Python)

⚠️ 小心 N+1 查詢問題，適時用 Eager Loading。

21. 謹慎使用持久連線

mysql_pconnect() 理論上減少連線建立開銷，但 Apache 多進程模型下易導致連線耗盡、記憶體洩漏、檔案描述符耗盡。現代架構建議用連線池而非持久連線。