調優三層次（效能遞增）
- 硬體升級：CPU、記憶體、NVMe SSD、網路 — 效果有限，成本高
- MySQL 配置調優：
mysqld 參數調整 — 本文重點，投報比最高
- SQL 與架構優化：索引、查詢改寫、分庫分表、讀寫分離、快取 — 根治之道
一、慢查詢日誌：找出效能殺手
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1 # 超過 1 秒記錄
log_queries_not_using_indexes = 1 # 未用索引也記錄
log_throttle_queries_not_using_indexes = 60 # 每分鐘限制，防日誌爆炸
分析工具：mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t: 按總時間排序; -t 10: 前 10 條
# 自動去除具體參數值，同類查詢合併統計
二、查詢快取（Query Cache）— MySQL 8.0 已移除
適用版本：MySQL 5.7 及之前
[mysqld]
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M # 單次查詢結果超過 2M 不快取
監控指標
SHOW STATUS LIKE 'Qcache%';
-- 關鍵：Qcache_hits / (Qcache_hits + Qcache_inserts) = 命中率
-- Qcache_lowmem_prunes 持續增長 → 快取太小或碎片太多
-- FLUSH QUERY CACHE 整理碎片
注意：高併發寫入場景快取失效頻繁，反而拖慢效能，建議關閉 (query_cache_type=0)。
三、關鍵連線與資源限制
[mysqld]
max_connections = 500 # 最大連線數，配合應用連線池
wait_timeout = 60 # 閒置連線回收秒數
interactive_timeout = 60
max_connect_errors = 1000 # 連線錯誤鎖定閾值，避免誤封
open_files_limit = 65535 # 作業系統開檔數上限，配合 ulimit
四、緩衝區與快取核心參數（InnoDB 為主）
| 參數 | 建議值 (專用 16GB RAM) | 說明 |
|---|
| innodb_buffer_pool_size | 10G~12G (60~75% RAM) | 核心：資料+索引熱點全在記憶體 |
| innodb_buffer_pool_instances | 8~16 | 減少鎖競爭，每實例 ≥ 1GB |
| innodb_log_file_size | 1G~2G | 大紅日誌減少 checkpoint 壓力，重啟恢復稍慢 |
| innodb_log_buffer_size | 64M~128M | 大事務寫入緩衝 |
| innodb_flush_log_at_trx_commit | 1 (核心業務) / 2 (可容忍丟 1 秒) | 1=最安全，2=高效能 |
| innodb_flush_method | O_DIRECT | 避免雙重快取 (OS + InnoDB) |
| innodb_io_capacity | IOPS 上限 (NVMe 20000+) | 背景刷頁、合併速率控制 |
| innodb_read_io_threads / write_io_threads | 8~16 | 並行 I/O 執行緒 |
五、會話級緩衝（謹慎調整，乘以連線數）
| 參數 | 建議值 | 監控指標 |
|---|
| sort_buffer_size | 2M~4M | Sort_merge_passes 高時調大 |
| read_buffer_size | 1M~2M | 全表掃描多時調大 |
| read_rnd_buffer_size | 2M~4M | 排序後隨機讀取 |
| join_buffer_size | 2M~4M | 無索引 JOIN 時使用 |
| tmp_table_size / max_heap_table_size | 64M~128M | Created_tmp_disk_tables / Created_tmp_tables 比率高時調大 |
六、表與執行緒快取
[mysqld]
table_open_cache = 4000 # Open_tables 接近時調大
table_definition_cache = 2000 # 表定義快取
thread_cache_size = 200 # Threads_created 快速增長時調大
七、三大必備監控工具
| 工具 | 特色 | 適用場景 |
|---|
| mytop | 即時 top-like 介面，顯示連線、查詢、快取命中率 | 現場排查、即時觀察 |
| mysqlard | 守護程序每 5 分鐘採集，RRD 儲存，Web 圖表展示長期趨勢 | 長期容量規劃、趨勢分析 |
| mysqlreport | 一次性詳細報告，覆蓋所有狀態變數計算比率 | 深度健檢、調優基準線 |
八、調優實戰清單
- 啟用慢查詢日誌，跑 1 天收集基準
mysqldumpslow 找出 Top 10 慢查詢EXPLAIN 分析執行計畫，補索引 / 改寫 SQL- 調整
innodb_buffer_pool_size 至資料熱點全入記憶體
- 觀察
Key_reads/Key_read_requests、Created_tmp_disk_tables、Sort_merge_passes
- 微調會話級緩衝、快取參數
- 壓測驗證 (sysbench / tpcc / 實際流量回放)
- 建立基準線，定期複查
九、避坑指南
- ❌ 盲目複製網路大神配置 — 每個業務負載不同
- ❌ 開大快取以為萬能 — 寫入重場景快取是負擔
- ❌ 忽略作業系統層面：
ulimit -n、vm.swappiness=1、transparent_hugepage=never
- ❌ 不做備份就線上 DDL — 用
pt-online-schema-change / gh-ost
- ✅ 一次只改一個參數，記錄效果，可回滾