MySQL 生產級優化配置文件 my.cnf 詳解與參數最佳實踐

MySQL 生產級優化配置文件詳解

本文提供一份經生產環境驗證的 my.cnf 完整配置模板，逐參數解析核心調優邏輯，涵蓋連接層、緩衝區、InnoDB、MyISAM、複製、慢查詢、臨時表、MyISAM 並發插入控制等維度，並附帶引擎查看與切換操作指南。

完整配置模板 /etc/my.cnf

[client]
port = 3306
socket = /tmp/mysql.sock
# password = your_password  # 不建議明文寫密碼

[mysqld]
# 基礎
port = 3306
socket = /tmp/mysql.sock
skip-locking                     # 避免外部鎖，穩定性優先
skip-name-resolve                # 禁用 DNS 解析，加速連接 (授權表需用 IP)

# 連接層
back_log = 1024                  # 監聽隊列，高併發衝擊緩衝
max_connections = 256            # 視業務峰值與記憶體調整
max_connect_errors = 30000
connect_timeout = 100
wait_timeout = 864000            # 長連接池場景調大，防過早斷開

# 緩衝區 (每連接)
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 8M
join_buffer_size = 2M
thread_stack = 256K

# 全局緩衝區
key_buffer = 2048M               # MyISAM 索引緩衝 (若無 MyISAM 可設 32M)
table_open_cache = 1024
thread_cache_size = 32
query_cache_size = 256M          # 5.7 及以下讀多寫少場景
query_cache_limit = 16M
query_cache_min_res_unit = 2K

# 臨時表
tmp_table_size = 128M
max_heap_table_size = 128M

# InnoDB 核心 (假設 32GB 內存，純 InnoDB)
innodb_buffer_pool_size = 3G
innodb_buffer_pool_instances = 3
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_thread_concurrency = 64
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_additional_mem_pool_size = 16M

# 複製 / Binlog
server-id = 1
log-bin = /var/log/mysql/mysql-bin
expire_logs_days = 21
binlog_format = ROW
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON

# 慢查詢
long_query_time = 1
log-slow-queries = /var/log/mysql/slow.log
# log-queries-not-using-indexes

# MyISAM 並發插入控制
low_priority_updates = 1         # 讀請求優先，降低寫優先級
concurrent_insert = 2            # 2=無論是否有空洞，尾部並發插入

# 線程併發
thread_concurrency = 16          # 邏輯 CPU * 2

# 目錄
tmpdir = /usr/mysql/tmp

參數分類解析

1. 連接與線程

• back_log：TCP 監聽隊列長度，ss -lnt 觀察 Recv-Q 積壓調整。
• max_connections：峰值連接數 + 20% 餘量，配合 thread_cache_size 減少線程創建。
• thread_concurrency：lscpu 邏輯核心數 × 2，現代內核設 0 由 OS 調度更佳。

2. 緩衝區策略

• 每連接緩衝區 (sort/read/join/read_rnd_buffer_size) × max_connections ≤ 物理內存 30%。
• 全局緩衝區：key_buffer (MyISAM)、innodb_buffer_pool (InnoDB) 二選一佔大頭，總和 ≤ 70% 物理內存。
• query_cache：5.7 讀多寫少啟用，寫多設 0；8.0 已移除。

3. InnoDB 核心四大金剛

1. innodb_buffer_pool_size：數據+索引熱點內存化，命中率目標 > 99%。
2. innodb_log_file_size：大日誌減少 Checkpoint 刷盤頻率，256M-1G 平衡恢復時間。
3. innodb_flush_log_at_trx_commit = 1 + sync_binlog = 1：雙 1 保證零丟失。
4. innodb_flush_method = O_DIRECT：繞過 OS 緩存，避免雙重緩衝，SSD/NVMe 效能提升 20-30%。

4. MyISAM 並發插入優化

MyISAM 表鎖導致讀寫串行。concurrent_insert 三檔：

• 0：禁用並發插入。
• 1 (默認)：無空洞 (無刪除/更新) 時尾部並發插入。
• 2：無論是否有空洞，允許尾部並發插入 (配合 low_priority_updates=1 讀優先)。

原理：MyISAM 讀鎖共享、寫鎖排他。low_priority_updates=1 降低寫鎖優先級，讓讀請求優先獲取鎖，寫請求排隊，適合讀多寫少場景。

5. 慢查詢與審計

• long_query_time = 1：秒級閾值，配合 pt-query-digest 定期分析。
• log_queries_not_using_indexes：開啟後全表掃描也記錄，噪音大，建議定期開啟採樣分析。

引擎查看與切換操作

-- 查看默認引擎
SHOW VARIABLES LIKE 'default_storage_engine';

-- 查看表引擎
SHOW CREATE TABLE tbl_name;
SHOW TABLE STATUS LIKE 'tbl_name';

-- 修改表引擎 (鎖表重建，大表用 pt-online-schema-change)
ALTER TABLE tbl_name ENGINE=InnoDB;
ALTER TABLE tbl_name ENGINE=MyISAM;

-- 選型口訣
-- MyISAM：SELECT 多、無事務、全文索引、日誌/只讀
-- InnoDB：INSERT/UPDATE 多、需事務、高併發、崩潰安全

配置驗證清單

1. mysqld --defaults-file=/etc/my.cnf --help | grep -E 'innodb_buffer_pool|key_buffer|query_cache|max_connections' 確認生效值。
2. 啟動後 SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size'; 核對。
3. 運行 SHOW ENGINE INNODB STATUS\G 觀察 Buffer Pool 使用率、日誌序列號、行鎖等待。
4. 壓測驗證 Threads_created 增長緩慢、Created_tmp_disk_tables 佔比 < 10%。

配置文件是 MySQL 效能的骨架，參數調優是血肉。沒有通用的「最佳配置」，只有「當前業務+硬體下的最優配置」。建立基準、單一變數、持續觀測、版本控制 my.cnf，是 DBA 進階的必修課。