範本檔案結構(/etc/my.cnf 或 my.ini)
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking # 避免外部鎖定,提升並發
# 緩衝區與快取
key_buffer_size = 16M # MyISAM 索引塊緩衝區
max_allowed_packet = 1M # 單次封包最大值(大欄位需調大)
thread_stack = 64K # 執行緒堆疊大小
read_buffer_size = 1M # 循序掃描緩衝(舊名 record_buffer)
table_open_cache = 1024 # 表快取數量(舊名 table_cache)
sort_buffer_size = 4M # 排序緩衝區
net_buffer_length = 8K # 通訊緩衝區初始大小
# 連線與錯誤控制
max_connect_errors = 100 # 連線錯誤閾值,超過封鎖主機
max_connections = 1024 # 最大並發連線數
# 複制
server-id = 1 # 主從複制唯一 ID
[mysqldump]
quick # 逐行輸出,不緩衝整個結果集
max_allowed_packet = 16M # 匯出大欄位需調大
[mysql]
no-auto-rehash # 禁用 Tab 自動補全,加速啟動
# safe-updates # 新手保護:限制無 WHERE 的 UPDATE/DELETE
[isamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
[mysqlhotcopy]
interactive-timeout
[mysql.server]
user = mysql
basedir = /var/lib
[mysqld_safe] # 5.0+ 服務啟動腳本區段
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
核心參數調優方向
| 參數 | 調整建議 | 監控指標 |
innodb_buffer_pool_size | **InnoDB 最關鍵**,實體記憶體 60-80%(專用 DB 伺服器) | Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests < 1% |
innodb_log_file_size | 256M-1G,減少 Checkpoint 壓力 | Innodb_log_waits = 0 |
innodb_flush_log_at_trx_commit | 1(最安全),2/0 高效能犧牲 1 秒資料 | 業務容忍度 |
query_cache_type/size | MySQL 5.7 建議關閉(鎖爭用),8.0 已移除 | Qcache_hits / (Qcache_hits + Qcache_inserts) |
max_connections | 應用連線池上限 + 余量,避免 "Too many connections" | Threads_connected / Max_used_connections |
table_open_cache | 總表數 * 1.5,配合 open_files_limit | Opened_tables 快速增長則調大 |
調優原則
- 測量優先:
SHOW GLOBAL STATUS、SHOW ENGINE INNODB STATUS、pt-query-digest 分析慢查詢。
- 單點變更:每次調整一個參數,觀察負載測試結果。
- 硬體優先:SSD、足夠記憶體、CPU 核心數,配置再好也彌補不了硬體短板。