MySQL 優化日記：從硬件到參數的全鏈路調優實戰錄

MySQL 優化全鏈路實戰：硬件選型、參數調優、架構演進

記錄從單機 1GB 內存頻繁 OOM，到多核 4GB+ 架構穩定支撐百萬 PV 的完整優化歷程。涵蓋硬件瓶頸分析、my.cnf 關鍵參數調優邏輯、實戰案例對比、避坑指南。

一、硬件瓶頸與選型建議

二、my.cnf 關鍵參數調優邏輯 (以 4GB 內存雙核 HT 為例)

[mysqld]
# === 基礎 ===
skip-name-resolve           # 禁用 DNS，加速連接
skip-locking                # 避免外部鎖

# === 連接層 ===
back_log = 384              # 突發連接隊列
max_connections = 768       # 最大連接，配合應用連接池
thread_cache_size = 64      # 線程複用，減少創建銷毀
thread_concurrency = 8      # 邏輯 CPU 數 × 2 (4核心×2=8)

# === InnoDB 核心 ===
innodb_buffer_pool_size = 2G   # 物理內存 50-70%，核心參數
innodb_log_file_size = 256M    # 大事務/高寫入調大
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2  # 2=高性能(秒級丟失風險)，金融必須 1
innodb_file_per_table = ON
innodb_thread_concurrency = 8

# === 緩存與排序 (每連接分配，總量=連接數×單值) ===
key_buffer_size = 256M        # MyISAM 索引/系統表
sort_buffer_size = 6M         # 排序緩存
read_buffer_size = 4M         # 順序掃描
read_rnd_buffer_size = 16M    # 隨機讀/範圍排序
join_buffer_size = 8M         # 無索引 JOIN

# === 表與臨時表 ===
table_cache = 512             # 表緩存，配合 open_files_limit
tmp_table_size = 256M         # 內存臨時表上限
max_heap_table_size = 256M

# === Query Cache (視業務) ===
query_cache_type = 1
query_cache_size = 64M

# === 慢查詢與二進制日誌 ===
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

# === 其他 ===
wait_timeout = 10             # 短連接快速回收
interactive_timeout = 10

三、三大核心參數深度解析與案例

1. key_buffer_size (MyISAM/系統表索引緩存)

• 健康指標：Key_reads / Key_read_requests < 1/1000
• 案例：384MB 配置，請求 5.9 億，讀取 5.6 萬，比 1:10000 → 極健康。
• 警報：16MB 配置，同等請求下讀取 5300 萬，比 1:11 → 嚴重缺失。

2. query_cache_size

• 監控：Qcache_lowmem_prunes 大 → 擴容；Qcache_hits 小 → 關閉。
• 碎片：Qcache_free_blocks 佔比高 → 定期 FLUSH QUERY CACHE。

3. table_cache (表緩存)

• 判斷：Open_tables = table_cache 且 Opened_tables 持續增長 → 擴容。
• 案例：table_cache=64，運行 6 小時 Open_tables=64，Opened_tables=22423 → 嚴重不足，需增大。

四、常見調優誤區

1. ❌ 盲目調大 sort_buffer_size、join_buffer_size → 連接數×單值超過物理內存，導致 Swap/OOM。
2. ❌ 開啟 Query Cache 卻不監控 → 高寫入表頻繁失效，全局鎖成為瓶頸。
3. ❌ 忽略 innodb_flush_log_at_trx_commit → 金融業務設為 2 導致數據丟失風險。
4. ❌ 不做 ANALYZE TABLE → 統計信息過期，優化器選錯索引。
5. ❌ 硬件升級不調參數 → 內存從 4GB 升 16GB，innodb_buffer_pool_size 仍 2GB，資源浪費。

五、優化驗證儀表盤

-- 緩存命中率
SELECT (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100 AS hit_rate;

-- 臨時表落盤率
SELECT Created_tmp_disk_tables / Created_tmp_tables * 100 AS disk_tmp_pct;

-- 排序落盤
SELECT Sort_merge_passes;

-- 線程複用率
SELECT Threads_created / Connections * 100 AS thread_create_pct;

-- 慢查詢趨勢
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 20;