MySQL 效能優化 21 大實戰經驗總結

前言

資料庫效能優化是後端開發的核心技能。本文整理 21 條針對 MySQL 的實戰優化技巧，涵蓋查詢快取、索引設計、SQL 寫法、架構選型等面向，助您大幅提升資料庫效能。

優化技巧清單

1. 為查詢快取優化 SQL

MySQL 查詢快取對含有不確定函數（如 NOW()、RAND()、CURDATE()）的查詢不生效。解決方法：先用程式語言取得固定值，再帶入 SQL。

// 不觸發快取
$r = mysql_query(\"SELECT username FROM user WHERE signup_date >= CURDATE()\");

// 觸發快取
$today = date(\"Y-m-d\");
$r = mysql_query(\"SELECT username FROM user WHERE signup_date >= '$today'\");

2. 善用 EXPLAIN 分析查詢計畫

在 SELECT 前加上 EXPLAIN，可查看索引使用情況、掃描行數、連接類型等關鍵資訊。透過 rows 欄位快速找出效能瓶頸。

3. 只需一筆資料時使用 LIMIT 1

找到第一筆符合條件的資料即停止掃描，避免全表掃描。

// 低效
$r = mysql_query(\"SELECT * FROM user WHERE country = 'China'\");

// 高效
$r = mysql_query(\"SELECT 1 FROM user WHERE country = 'China' LIMIT 1\");

4. 為搜尋欄位建立索引

非主鍵、非唯一欄位若常用於 WHERE 或 JOIN 條件，應建立索引。注意 LIKE '%keyword%' 開頭萬用字元無法使用一般索引，需改用全文索引或其他方案。

5. JOIN 欄位型別、字元集必須一致

連接欄位型別不一致（如 DECIMAL vs INT）或字元集不同，MySQL 無法使用索引優化 JOIN。

6. 絕對避免 ORDER BY RAND()

會導致全表掃描並產生臨時表排序，效能極差。替代方案：先查總筆數，再用程式產生隨機 offset。

$r = mysql_query(\"SELECT count(*) FROM user\");
$d = mysql_fetch_row($r);
$rand = mt_rand(0, $d[0] - 1);
$r = mysql_query(\"SELECT username FROM user LIMIT $rand, 1\");

7. 避免 SELECT *

只查詢需要的欄位，減少網路傳輸與 I/O 開銷。

8. 每張表務必設定 INT 型主鍵 (AUTO_INCREMENT)

避免使用 VARCHAR 做主鍵。關聯表除外（由多個外鍵組成複合主鍵）。

9. 固定值集合欄位優先用 ENUM 而非 VARCHAR

ENUM 內部儲存為 TINYINT，體積小、速度快。適用於性別、狀態、部門等固定選項。

10. 利用 PROCEDURE ANALYSE() 取得結構建議

在 phpMyAdmin 中點選「Propose table structure」可獲得欄位型別優化建議，但需以實際資料量為準。

11. 能用 NOT NULL 就不用 NULL

NULL 需額外儲存空間且增加比較複雜度。除非有特殊業務需求，否則預設設為 NOT NULL。

12. 使用 Prepared Statements

提升重複查詢效能、防止 SQL 注入。MySQL 5.1+ 已支援查詢快取。PHP 可用 mysqli 或 PDO 實作。

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

mysql_unbuffered_query() 可逐行處理結果，大幅降低記憶體佔用，但無法使用 mysql_num_rows() 等函數。

14. IP 位址儲存為 UNSIGNED INT

僅佔 4 位元組，支援範圍查詢（BETWEEN）。轉換函數：INET_ATON()/INET_NTOA() 或 PHP ip2long()/long2ip()。

15. 固定長度資料表 (Fixed-length) 效能更好

無 VARCHAR、TEXT、BLOB 欄位的表為固定長度，MySQL 可快速計算行偏移量。可透過垂直分割將不定長欄位拆出。

16. 垂直分割 (Vertical Partitioning)

將低頻存取或大型欄位（如文章內容、最後登入時間）移至獨立表，縮小主表體積，提升快取命中率。注意：拆分後避免頻繁 JOIN。

17. 大量 DELETE/INSERT 分批執行

避免長時間鎖表導致服務停擺。搭配 LIMIT 與 usleep() 間歇執行。

while (1) {
    mysql_query(\"DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000\");
    if (mysql_affected_rows() == 0) break;
    usleep(50000);
}

18. 越小的資料型別越快

依實際範圍選擇 TINYINT、SMALLINT、MEDIUMINT、DATE 等，減少磁碟 I/O。但需預留擴充空間，避免未來 ALTER TABLE 鎖表過久。

19. 選擇正確的儲存引擎

• MyISAM：適合大量讀取、少寫入；COUNT(*) 極快；表級鎖。
• InnoDB：支援行級鎖、交易、外鍵；適合高併發寫入場景。

20. 使用 ORM (Object Relational Mapper)

ORM 提供 Lazy Loading、查詢打包成交易等優化，但需警惕 N+1 查詢問題。推薦 PHP 社群流行的 Doctrine。

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

mysql_pconnect() 會重用連線，但在高併發 Apache 環境下易導致連線耗盡、記憶體洩漏。除非架構經評估確有優勢，否則建議使用一般短連線。