萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 8.0 生產環境配置模板:效能最大化調優參數詳解

配置檔案結構(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。
copyright © 萬盛學電腦網 all rights reserved