MySQL 性能優化全景指南：緩存參數、連接參數、實戰配置建議

核心原則

內存讀取：微秒級 | 磁盤讀取：毫秒級 → 差 3 個數量級。
優化本質：用內存換 I/O，用 CPU 換鎖競爭。

一、緩存參數優化

1. 表緩存 table_open_cache

• 作用：緩存打開的表結構 (frm)、文件描述符
• 監控：Open_tables = table_open_cache 且 Opened_tables 持續增長 → 需調大
• 建議：512 (視表數量調整)，過大導致文件描述符耗盡

2. 查詢緩存 query_cache_size / query_cache_type

• 適用：讀多寫少、數據變化低頻 的 SELECT 密集場景
• 致命缺陷：表任意寫入 → 關聯 QC 全部失效
• 命中率公式：Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%
• 建議：中型站點 256MB；query_cache_type：
  0=OFF、1=ON(默認用)、2=DEMAND(配合 SQL_CACHE/SQL_NO_CACHE)
• MySQL 5.7+ 已棄用，8.0 移除 → 遷移至 Redis/應用層緩存

3. Binlog 緩存 binlog_cache_size

• 事務提交前暫存 binlog 事件，避免頻繁 fsync
• 無大事務：2-4MB；大事務頻繁：適當調大，≤32MB
• 監控：Binlog_cache_disk_use > 0 表示內存不足落盤，需調大

4. 索引緩存 key_buffer_size (MyISAM 專用)

• 緩存 .MYI 索引塊，不緩存數據塊
• 建議：內存允許下設為所有 MyISAM 索引總大小 (*.MYI 總和)
• 未命中率：Key_reads / Key_read_requests * 100%，< 1% 為佳

5. 批量插入緩存 bulk_insert_buffer_size (MyISAM)

• 支援語句：INSERT ... SELECT、INSERT ... VALUES(),(),()、LOAD DATA INFILE (非空表)
• 默認 8MB，建議 ≤32MB

6. InnoDB 核心緩存

命中率：(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%，目標 > 99%。

二、連接參數優化

1. 最大連接數 max_connections / 積壓隊列 back_log

• max_connections：默認 151，建議 128-512 (視內存、併發)
• 使用率：Max_used_connections / max_connections * 100%，目標 50-80%
• back_log：連接爆發時暫存隊列，默認 80，建議 128-256

2. 連接超時 wait_timeout / interactive_timeout

• 默認 28800s (8小時)，導致大量 Sleep 連接佔用槽位
• 建議：120-300 秒，兩參數同步修改
• 排查：SHOW PROCESSLIST 看大量 Sleep → 調小

3. 禁用 DNS 反解 skip-name-resolve

• 新連接不再做反向 DNS 解析，顯著降低連接建立延遲
• 代價：連接串只能用 IP (如 127.0.0.1)，不能用 localhost

三、生產環境配置建議表 (參考值，需實測調整)

四、常用診斷命令

-- 線程/連接狀態
SHOW GLOBAL STATUS LIKE 'Thread_%';

-- 參數當前值
SHOW GLOBAL VARIABLES LIKE '%buffer%';
SHOW GLOBAL VARIABLES LIKE '%timeout%';

-- InnoDB 狀態
SHOW ENGINE INNODB STATUS\G

-- 慢查詢分析
pt-query-digest /var/log/mysql/slow.log

五、避坑指南

• ❌ 盲目調大所有緩存 → OOM Killer 殺進程
• ❌ 開啟 QC 卻不監控失效率 → 寫入風暴時性能崩塌
• ❌ 忽略 innodb_flush_log_at_trx_commit=1 的 I/O 開銷 → SSD 必須、RAID 卡 BBU 必須
• ✅ 建立基線 → 壓測 → 觀察狀態變量 → 微調單一參數 → 驗證
• ✅ 關鍵指標納入監控：QPS、TPS、緩存命中率、連接使用率、慢查詢數、主從延遲