您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 配置檔 my.cnf 核心參數深度解析與調優指南

編輯：MySQL 資料庫相關知識

前言

MySQL 參數調優是一項系統工程，需綜合考量業務型態（OLTP/OLAP）、併發量、資料量、硬體規格（CPU、記憶體、磁碟 I/O）、網路環境等因素。無所謂「萬能配置」，唯有持續觀測指標、壓力測試、逐步調整，才能逼近最佳效能。本文對生產環境常見的 `my.cnf` 關鍵參數進行分類解析，並給出調優建議與避坑指南。

基礎連線與安全設定

[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1

# 禁用 DNS 解析，加速連線建立，但授權表需用 IP 而非主機名
skip-name-resolve
# skip-networking  # 若啟用則僅允許本地 socket 連線

連線併發控制

# 連線等待隊列長度，突發連線時緩衝
back_log = 600

# 最大連線數，依記憶體與併發評估，過大導致 OOM
max_connections = 1000

# 同一主機連線錯誤達此值則封鎖，需 FLUSH HOSTS 解封
max_connect_errors = 6000

# 文件描述符上限，配合 ulimit -n 設定
open_files_limit = 65535

表緩存與臨時表

# 表緩存大小，建議 ≈ 併發連線數 × 單連線涉及表數
table_open_cache = 128

# 最大封包大小，防止大 SQL/結果集中斷
max_allowed_packet = 4M

# Binlog 事務緩存，提交時刷盤
binlog_cache_size = 1M

# 記憶體表大小上限
max_heap_table_size = 8M
tmp_table_size = 16M  # 超過則轉為 MyISAM 磁碟表

排序、讀取、JOIN 緩存（每連線獨享，謹防記憶體耗盡）

read_buffer_size = 2M       # 循序掃描緩存
read_rnd_buffer_size = 8M   # 隨機讀/排序緩存
sort_buffer_size = 8M       # ORDER BY 排序緩存
join_buffer_size = 8M       # JOIN 無索引時緩存

# 優先優化索引避免 Filesort，再考慮調大緩存

線程緩存

thread_cache_size = 8  # 依實體記憶體：1G→8, 2G→16, 3G→32, >3G→64
# 觀察 Threads_created / Connections 比值調整

查詢緩存（MySQL 5.7+ 已棄用，8.0 移除）

query_cache_size = 8M
query_cache_limit = 2M
# 適用「讀多寫少、重複查詢高」場景；寫入頻繁時建議關閉 (0)
# 監控 Qcache_hits / Qcache_lowmem_prunes 決定是否擴容

索引緩存 (MyISAM 專用，InnoDB 用 buffer_pool)

key_buffer_size = 4M
# 4GB 記憶體建議 384M~512M；監控 Key_reads/Key_read_requests < 1/1000

InnoDB 核心參數（重中之重）

# 事務隔離級別，預設 REPEATABLE-READ
transaction_isolation = REPEATABLE-READ

# Binlog 設定
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30

# 慢查詢日誌
log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log

# 關閉 Performance Schema 省記憶體（視監控需求）
performance_schema = 0
explicit_defaults_for_timestamp = 1
skip-external-locking = 1
default-storage-engine = InnoDB

# 獨立表空間，利於空間回收與單表遷移
innodb_file_per_table = 1
innodb_open_files = 500

# 核心：InnoDB 緩存池，獨立資料庫主機建議 60%~80% 實體記憶體
innodb_buffer_pool_size = 64M  # 生產環境須大幅調大，如 32G 記憶體設 24G

# I/O 線程，依 CPU 核心數調整 (1~64)，預設 4
innodb_write_io_threads = 4
innodb_read_io_threads = 4

# 並發控制，0 表不限制，發揮多核優勢
innodb_thread_concurrency = 0

# Purge 線程，5.5+ 建議啟用
innodb_purge_threads = 1

# Redo Log 刷盤策略：1=最安全(ACID)、2=折中、0=最高效但可能丟 1 秒數據
innodb_flush_log_at_trx_commit = 2

# Redo Log 緩存與文件大小
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3

# 髒頁刷新比例
innodb_max_dirty_pages_pct = 90

# 鎖等待超時
innodb_lock_wait_timeout = 120

MyISAM 相關參數（若仍有 MyISAM 表）

bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

連線超時控制

interactive_timeout = 28800  # 8 小時
wait_timeout = 28800         # 同步調整，配合連線池 idle 檢查
# 大量 Sleep 連線時降低此值，釋放資源

備份與維護工具參數

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

調優核心原則總結

一次只改一個參數，觀測效果再決定下一步。
核心優先：`innodb_buffer_pool_size`、`innodb_log_file_size`、`max_connections` 影響最大。
監控指標驅動：關注 `Innodb_buffer_pool_reads`、`Threads_created`、`Qcache_*`、`Key_reads` 等狀態變數。
單位謹慎：`innodb_buffer_pool_size` 單位 MB，`max_connections` 無單位，勿混淆。
避免盲目倍增：記憶體翻倍不代表所有參數翻倍。