MySQL 服務器與硬體效能優化全攻略

核心優化原則

MySQL 效能優化的三大黃金法則：

1. 記憶體優於磁碟：內存訪問速度比磁碟快數個數量級。
2. 熱數據常駐內存：盡可能延長熱點數據在緩衝區的停留時間，減少物理 I/O。
3. 索引優於數據：將索引頁面優先保留在內存中，對查詢加速效果遠超緩存數據行。

服務器層面優化：關鍵緩衝區配置

1. 表緩存

控制參數：table_open_cache（舊版 table_cache）。

監控指標：Opened_tables 狀態變量。若該值快速增長，說明表緩存不足，頻繁打開/關閉表文件。

SHOW STATUS LIKE 'Opened_tables';

2. MyISAM 索引緩存

控制參數：key_buffer_size。默認 8MB，內存充足時建議設置為物理內存的 25%~30%（僅限 MyISAM 引擎）。

3. InnoDB 緩衝池

• innodb_buffer_pool_size：核心參數，建議設置為物理內存的 60%~80%（專用 DB 服務器）。
• innodb_log_buffer_size：重做日志緩衝，默認 16MB，大事務場景可適當調大（如 64MB~256MB）。

4. BDB 緩存

參數：bdb_cache_size（BDB 引擎已棄用，現代版本可忽略）。

5. 查詢緩存

注意：MySQL 8.0 已移除 Query Cache。 適用於 5.7 及之前版本：

• query_cache_size：緩存總大小，0 表示禁用。建議 16MB~128MB，過大反而增加鎖競爭。
• query_cache_limit：單次查詢結果最大緩存體積，超過不緩存。
• query_cache_type：0=關閉，1=默認緩存（SQL_NO_CACHE 除外），2=按需緩存（SQL_CACHE 才緩存）。

客戶端可通過 SET SESSION query_cache_type = 0|1|2 動態控制。

其他服務器優化策略

• 禁用無用存儲引擎：源碼編譯時使用 --without-innodb、--without-bdb 等減少內存佔用。
• 簡化權限系統：在安全前提下減少複雜的 GRANT 層級，降低權限檢查開銷。
• 靜態編譯優化：使用靜態庫編譯 mysqld 可提升 5%~10% 效能，但無法加載 UDF（用戶自定義函數）。

硬體升級指南

實戰建議

1. 優先投資內存，其次是高速存儲，最後是 CPU 核心數。
2. 生產環境務必啟用 innodb_flush_log_at_trx_commit=1 與 sync_binlog=1 保證數據安全，配合電池備寫緩存（BBWC）緩解寫入延遲。
3. 定期運行 ANALYZE TABLE 更新統計信息，配合 OPTIMIZE TABLE 整理碎片。