配置檔案結構(my.cnf / my.ini)
[client]
port = 3306
socket = /data/mysql/mysql.sock
[mysqld]
# 基礎路徑
basedir = /usr/local/mysql
datadir = /data/mysql
tmpdir = /data/mysql
pid-file = /data/mysql/mysql.pid
socket = /data/mysql/mysql.sock
port = 3306
# 核心優化
skip-name-resolve # 禁用 DNS 反解,加速連線建立
skip-external-locking # 僅 MyISAM 需要
default-storage-engine = InnoDB
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
# 連線設定
max_connections = 20000
max_user_connections = 1000
interactive_timeout = 7200
wait_timeout = 7200
thread_cache_size = 64
back_log = 512
# 緩衝區與排序
sort_buffer_size = 2M
join_buffer_size = 256K
read_buffer_size = 2M
read_rnd_buffer_size = 4M
max_allowed_packet = 64M
# 表快取
table_open_cache = 4096
table_definition_cache = 2048
# Query Cache(MySQL 8.0 已移除,建議用 Redis 替代)
# query_cache_type = 0
# query_cache_size = 0
# Binlog 設定
log-bin = mysql-bin
binlog_format = ROW
max_binlog_size = 1G
sync_binlog = 1 # 最高資料安全,犧牲些許寫入效能
binlog_cache_size = 4M
max_binlog_cache_size = 2G
# 慢查詢日誌
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/slow.log
log_queries_not_using_indexes = 1
# InnoDB 核心參數(假設 32G 記憶體專用 DB 伺服器)
innodb_buffer_pool_size = 22G # 60-70% 物理記憶體
innodb_buffer_pool_instances = 16
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000 # SSD 建議 2000-5000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 0 # 0 表示不限制
innodb_doublewrite = 1
innodb_adaptive_hash_index = 1
# MyISAM(若仍有舊表)
key_buffer_size = 128M
myisam_sort_buffer_size = 64M
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
[mysqlhotcopy]
interactive-timeout
關鍵參數調優口訣
- Buffer Pool:越大越好,留 10-20% 給 OS 與其他緩衝區。
- Log File Size:大檔案減少 checkpoint 壓力,但恢復時間變長;配合
innodb_log_buffer_size。
- Flush Method:
O_DIRECT 避免雙重快取(OS + InnoDB),SSD 必開。
- IO Capacity:根據磁碟基準測試(fio)設定,SSD 可設 2000+。
- Thread Concurrency:MySQL 5.6+ 建議設 0 讓引擎自適應。
變更生效方式
- 動態參數:
SET GLOBAL var = value; 即時生效,但重啟失效,需同步寫入配置檔。
- 靜態參數:修改配置檔後
systemctl restart mysqld。