您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 性能優化實戰：my.cnf 核心參數詳細配置模板與計算公式

編輯：MySQL 綜合教學

MySQL 性能優化實戰：my.cnf 核心參數詳細配置模板與計算公式

本文提供生產級 my.cnf 完整配置模板，每個參數附帶作用說明、計算公式與調優建議，並包含源碼編譯參數參考。

一、源碼編譯參數參考


cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
  -DMYSQL_DATADIR=/usr/local/mysql/data \
  -DTMPDIR=/usr/local/mysql/data \
  -DMYSQL_UNIX_ADDR=/usr/local/mysql/data/mysql.sock \
  -DSYSCONFDIR=/etc/my.cnf \
  -DWITH_MYISAM_STORAGE_ENGINE=1 \
  -DWITH_INNOBASE_STORAGE_ENGINE=1 \
  -DWITH_FEDERATED_STORAGE_ENGINE=1 \
  -DDEFAULT_CHARSET=utf8 \
  -DEXTRA_CHARSETS=all \
  -DDEFAULT_COLLATION=utf8_general_ci

二、記憶體佔用模型（核心原則）


Global Buffers = innodb_buffer_pool_size + innodb_additional_mem_pool_size
               + innodb_log_buffer_size + key_buffer_size + query_cache_size

Per Thread Buffers = (read_buffer_size + read_rnd_buffer_size
                    + sort_buffer_size + thread_stack + join_buffer_size
                    + binlog_cache_size) * max_connections

總佔用 = Global Buffers + Per Thread Buffers ≤ 物理記憶體 80-90%

三、生產級 my.cnf 完整模板（含詳細註釋）


[client]
port = 3306
socket = /usr/local/mysql/data/mysql.sock

[mysqld]
port = 3306
socket = /usr/local/mysql/data/mysql.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /usr/local/mysql/data

# skip-name-resolve          # 禁用 DNS 解析，加速連線
skip_external_locking
local-infile = 0
character-set-server = utf8
default-storage-engine = innodb

# 日誌
# general_log = on
# general_log_file = /usr/local/mysql/data/mysql.log
log-error = /usr/local/mysql/data/error.log
# slow_query_log = on
# long_query_time = 2
# slow_query_log_file = /usr/local/mysql/data/slow.log

server-id = 1
log-bin = mysql-bin
binlog_format = mixed
binlog_cache_size = 2M
sync_binlog = 0          # 0=最高性能風險最大，1=最安全，2=折中
expire_logs_days = 30

back_log = 500
max_connections = 1000
wait_timeout = 100
interactive_timeout = 100
max_connect_errors = 1000
connect_timeout = 20
max_allowed_packet = 16M
table_open_cache = 2000

read_buffer_size = 512K
read_rnd_buffer_size = 512K
sort_buffer_size = 512K
join_buffer_size = 512K
thread_stack = 256K
thread_cache_size = 64

explicit_defaults_for_timestamp = 1

query_cache_type = 1
query_cache_size = 32M
query_cache_limit = 1M

ft_min_word_len = 1
transaction_isolation = REPEATABLE-READ
tmp_table_size = 32M

key_buffer_size = 32M          # 純 InnoDB 可設 64M 或更小

# InnoDB 核心
innodb_file_per_table = 0      # 0=共享表空間，1=獨立表空間（建議 1）
innodb_buffer_pool_size = 256M # 單實例建議物理記憶體 70-80%
innodb_use_sys_malloc = 1
innodb_data_file_path = ibdata1:512M:autoextend
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_thread_concurrency = 0
innodb_flush_log_at_trx_commit = 2  # 2=高性能，1=最高安全，0=最高性能風險
innodb_log_buffer_size = 8M
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_now = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_load_now = 1

[mysqldump]
quick
max_allowed_packet = 4M

四、關鍵參數調優建議

參數	建議值	說明
innodb_buffer_pool_size	物理記憶體 70-80%	InnoDB 核心緩存，監控 `Innodb_buffer_pool_reads` / `Innodb_buffer_pool_read_requests` 命中率 > 99%
sync_binlog	1 (金融) / 2 (一般)	1 保證不丟 binlog，2 性能較好僅丟極少量
innodb_flush_log_at_trx_commit	1 (金融) / 2 (一般)	1 每提交刷盤，2 寫 OS cache 每秒刷盤
innodb_file_per_table	1	獨立表空間便於單表回收空間、遷移
thread_cache_size	64+	監控 `Threads_created` / `Connections` 命中率 > 90%
table_open_cache	2000+	監控 `Open_tables` / `Opened_tables`

五、監控指標速查


SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';
SHOW GLOBAL STATUS LIKE 'Key_read%';
SHOW GLOBAL STATUS LIKE 'Qcache_%';
SHOW GLOBAL STATUS LIKE 'Thread_%';
SHOW GLOBAL STATUS LIKE 'Opened_tables';

此模板為通用基線，實際上線需根據硬體（CPU 核心、記憶體、磁盤型別）、業務讀寫比例、併發量進行壓測微調。變更配置後必須重啟 MySQL 生效（部分參數支援動態修改）。