MySQL 效能優化 20+ 條黃金經驗總結

MySQL 效能優化 20+ 條黃金經驗總結

資料庫操作往往是應用效能瓶頸，尤其 Web 應用。以下針對 MySQL 整理 21 條實戰優化原則，涵蓋查詢、索引、架構、配置等面向。

1. 為查詢緩存優化 SQL

MySQL 查詢緩存對確定性函數失效 (如 NOW(), RAND(), CURDATE())。改用變數預先計算：

// 不走緩存
SELECT * FROM user WHERE signup_date >= CURDATE();
// 走緩存
$today = date('Y-m-d');
SELECT * FROM user WHERE signup_date >= '$today';

2. 善用 EXPLAIN 分析執行計畫

關注 type (ALL/index/range/ref/eq_ref/const)、rows (預估掃描行)、key (實際用索引)。加索引前後對比可量化優化效果。

3. 單行查詢加 LIMIT 1

找到第一筆即停止掃描，避免全表走訪。

SELECT 1 FROM user WHERE country = 'China' LIMIT 1;

4. 為搜尋欄位建立索引

LIKE 'prefix%' 可用索引，LIKE '%suffix%' 無法用普通索引，需全文索引或外部搜尋引擎。

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

類型不符 (如 DECIMAL JOIN INT) 或字符集不同會導致索引失效。

6. 杜絕 ORDER BY RAND()

極度消耗 CPU。替代方案：先查總數，應用層產生隨機 offset，再 LIMIT offset, 1。

7. 避免 SELECT *

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

8. 每張表必設整數主鍵 (UNSIGNED INT AUTO_INCREMENT)

VARCHAR 主鍵效能差，且涉及叢集、分區等引擎機制需整數主鍵。例外：關聯表的複合外鍵主鍵。

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

ENUM 內部儲存為 TINYINT，極致緊湊高效。適用於性別、狀態、部門等固定枚舉值。

10. 參考 PROCEDURE ANALYSE() 建議

根據實際資料分析建議欄位類型優化 (如 INT → MEDIUMINT, VARCHAR → ENUM)。需有真實資料才準確。

11. 能用 NOT NULL 就不用 NULL

NULL 需額外儲存空間 (MyISAM 每列 1 bit)，比較邏輯複雜。Oracle 視 NULL 等同空字串，MySQL 不同。

12. 使用 Prepared Statements

防 SQL 注入、重複解析優化、二進制協議傳輸高效。PHP 使用 mysqli 或 PDO。

13. 無緩衝查詢 (mysql_unbuffered_query)

大結果集逐行處理，節省記憶體，首行即可開始處理。限制：需讀完或釋放結果才能下一查詢，mysql_num_rows() 不可用。

14. IP 地址存為 UNSIGNED INT

4 bytes 固定長度，支援範圍查詢 IP BETWEEN ip1 AND ip2。轉換函數：INET_ATON()/INET_NTOA()、PHP ip2long()/long2ip()。

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

易計算行偏移量，掃描緩存重建效率高。可用垂直分割將變長欄位移出。

16. 垂直分割 (Vertical Partitioning)

將低頻/大欄位 (如地址、last_login) 移至獨立表，主表保持精簡，提升查詢緩存命中率。避免頻繁 JOIN 分割表。

17. 大量 DELETE/INSERT 分批執行

避免長時間鎖表導致服務雪崩。配合 LIMIT + usleep() 間歇執行。

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

18. 越小的資料類型越快

參考 Storage Requirements 選最小足夠類型 (TINYINT/SMALLINT/MEDIUMINT/DATE)。預留擴展空間，避免後期 ALTER TABLE 巨表耗時。

19. 選擇正確儲存引擎

• MyISAM：讀多寫少、需 COUNT(*) 極快、全文索引
• InnoDB：寫多、需事務、行鎖、外鍵、高併發

20. 使用 ORM (如 Doctrine)

Lazy Loading 減少不必要查詢、批次打包事務提升效能。警惕 N+1 問題。

21. 謹慎使用永久連線 (mysql_pconnect)

理論減少連線開銷，但 Apache 多進程模型易導致連線數耗盡、記憶體洩漏、檔案句柄耗盡。需依架構評估。

這 21 條經驗涵蓋 SQL 撰寫、索引設計、架構決策、配置調優，是 MySQL 開發運維的核心功力表現。