MySQL 性能優化 20+ 黃金經驗總結：查詢緩存、EXPLAIN、索引設計、架構優化與陷阱避坑

5. JOIN 欄位類型、字符集、索引三一致

-- 錯誤：DECIMAL JOIN INT、utf8 JOIN latin1
-- 正確：同類型、同字符集、雙方都有索引

6. 絕對禁止 ORDER BY RAND()

-- 災難寫法
SELECT * FROM user ORDER BY RAND() LIMIT 1;

-- 正確：應用層隨機 ID
$count = SELECT COUNT(*) FROM user;
$rand = mt_rand(0, $count-1);
SELECT * FROM user LIMIT $rand, 1;

7. 避免 SELECT *

只取需要欄位：減少 I/O、記憶體、CPU、網傳，利用索引覆蓋。

8. 每表必設 INT UNSIGNED AUTO_INCREMENT 主鍵

業務主鍵 (如 email) 設唯一索引，不做主鍵。關聯表除外 (複合主鍵 = 外鍵組合)。

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

儲存為 TINYINT，效能接近數值型。PROCEDURE ANALYSE() 會建議轉換。

10. 參考 PROCEDURE ANALYSE() 建議優化結構

phpMyAdmin「Propose table structure」功能即基於此。

11. 能用 NOT NULL 就不用 NULL

NULL 需額外位元組標記，MyISAM 每欄位 1 bit 對齊到 byte。比較邏輯更複雜。

12. 使用 Prepared Statements

• 防 SQL 注入 (參數綁定)
• 重複執行只解析一次
• 二進制協定傳輸效率高
• 注意：5.1 前不支援查詢緩存

13. 無緩衝查詢 (大結果集)

mysql_unbuffered_query()  -- PHP
-- 記憶體省、首行即可處理、但需讀完或 free_result 才能下一查詢

14. IP 存為 UNSIGNED INT

INET_ATON('192.168.1.1') → 3232235777 (插入)
INET_NTOA(3232235777) → '192.168.1.1' (查詢)
PHP: ip2long() / long2ip() (注意有號無號轉換)

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

偏移量計算 O(1)，快取重建快。垂直分割將變長欄位移出主表。

16. 垂直分割 (大表拆分)

• 低頻/大欄位 (address, bio) 移至副表
• 高頻更新欄位 (last_login) 移出避免清查詢緩存
• 慎用 JOIN 重組，否則效能指數級下降

17. 大批次 DELETE/INSERT 分批 + 休眠

while (1) {
    DELETE FROM logs WHERE log_date <= '2012-01-01' LIMIT 1000;
    if (mysql_affected_rows() == 0) break;
    usleep(50000);  -- 50ms 讓出鎖
}

18. 越小的型別越快

TINYINT < SMALLINT < MEDIUMINT < INT < BIGINT，DATE < DATETIME。預留擴展空間避免 ALTER TABLE 鎖表數小時。

19. 選對儲存引擎

20. 使用 ORM (Object Relational Mapper)

Lazy Loading、批次 SQL 打包事務、Doctrine 推薦。警惕 N+1 查詢問題。

21. 謹慎使用持久連線 (pconnect)

Apache 多進程模型下易耗盡連線數、記憶體、檔案控制代碼。現代架構建議短連線 + 連線池。