MySQL 數據庫優化總結：19 條核心最佳實踐（含 EXPLAIN、索引、緩存、架構）

優化清單

1. 優化查詢緩存：避免在 SQL 中使用 NOW()、RAND()、CURDATE() 等非確定性函數，改用應用層傳入固定值，使查詢可被緩存。
2. 善用 EXPLAIN：分析執行計劃，關注 type（目標 ref/range/eq_ref）、key（實際用到的索引）、rows（掃描行數）、Extra（Using index/Using where/Using filesort 等）。
3. LIMIT 1 取唯一行：明確只需一行時加 LIMIT 1，避免全表掃描。
4. 為搜尋欄位建索引：不僅主鍵，任何出現在 WHERE、JOIN、ORDER BY 的欄位都應評估建索引。
5. JOIN 條件類型一致：關聯欄位類型、字符集、長度必須完全一致，否則隱式轉換導致索引失效。
6. 避免 ORDER BY RAND()：改用應用層隨機 LIMIT offset, 1 或 WHERE id >= (SELECT FLOOR(RAND() * MAX(id)) FROM t) LIMIT 1。
7. 禁用 SELECT *：只選需要的列，減少網絡傳輸、磁盤 I/O、緩存佔用。
8. PROCEDURE ANALYSE() 獲取結構建議：根據現有數據推薦更優的列類型（如 VARCHAR→ENUM、INT→MEDIUMINT）。
9. 預處理語句：防 SQL 注入、減少解析開銷、支援二進制協議傳輸。
10. IP 存為 UNSIGNED INT：用 INET_ATON()/INET_NTOA() 或 PHP ip2long()/long2ip()，節省空間並利於範圍查詢。
11. 每表必有自增整型主鍵：業務鍵（如 email）勿作主鍵；關聯表除外（複合主鍵）。
12. ENUM 代替固定值 VARCHAR：內部存 TINYINT，查詢顯示字符串，極省空間且快。
13. 儘量 NOT NULL：NULL 需額外位圖存儲，比較邏輯複雜；無特殊語意時設 NOT NULL DEFAULT '' 或 0。
14. Prepared Statements（重複）：同第 9 條，強調安全與性能雙贏。
15. 無緩衝查詢：mysql_unbuffered_query() 適合大結果集流式處理，節省記憶體，但需手動釋放、不可 mysql_num_rows()。
16. 固定長度表更快：無 VARCHAR/TEXT/BLOB 時表為 static，偏移量計算快、易緩存；可用垂直分割分離變長列。
17. 垂直分割：將大表按訪問頻率拆分為「核心表（定長）」+「擴展表（變長）」，減少核心表行大小，提升緩存命中。
18. 水平分表/分庫：單表超 2000 萬行或 2GB 考慮分片（Range/Hash/一致性 Hash）。
19. 慢查詢日誌分析：開啟 long_query_time=0、log_queries_not_using_indexes=ON，定期用 pt-query-digest 分析 Top SQL。
20. 監控關鍵指標：QPS、緩存命中率、連接數、InnoDB 緩衝池使用率、磁盤 I/O、主從延遲。

總結

優化是系統工程：**索引設計**與**SQL 改寫**見效最快，**架構調整**（分庫分表、讀寫分離）解決擴展性。建立「慢查詢→分析→優化→驗證」閉環，持續迭代。