MySQL 效能調校核心：my.cnf 關鍵參數配置與生效層級詳解

編譯安裝只是基礎，my.cnf 參數調校才是發揮硬體效能的關鍵。本文詳解配置檔層級、預設範本選擇、參數查詢方法與核心參數設定實戰。

配置檔生效層級（優先級由高到低）

MySQL 讀取順序：全域 → 實例 → 用戶，後者覆蓋前者同名參數。

官方預設範本選擇指南

源碼包 support-files/ 提供四種範本：

操作：cp my-large.cnf /etc/my.cnf 並根據實際硬體微調。

如何查詢所有可調參數？

/usr/local/mysql/libexec/mysqld --help --verbose 2>&1 | head -300

關鍵區塊在 Possible variables for option --set-variable (-O) are 之後，列出所有 set-variable = 參數=值 可用名稱。

配置檔寫法範例

[mysqld]
# 基礎
basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306
socket = /tmp/mysql.sock

# 連線層
max_connections = 500
max_connect_errors = 10000
wait_timeout = 300
interactive_timeout = 600

# 緩衝區核心（InnoDB 為主）
innodb_buffer_pool_size = 4G        # 物理記憶體 60-70%
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1  # ACID 嚴格
innodb_file_per_table = 1

# 查詢快取（5.7+ 已棄用，8.0 移除）
# query_cache_type = 0
# query_cache_size = 0

# 臨時表與排序
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 4M
read_rnd_buffer_size = 4M

# 日誌
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# 安全
skip-name-resolve
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

修改生效流程

1. 編輯 my.cnf。
2. systemctl restart mysqld 重啟服務（動態參數可 SET GLOBAL 但重啟後失效）。
3. 驗證：SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

調優心法

• 一次改一個參數，觀察效能指標（QPS、延遲、磁碟 I/O）。
• 重點調 innodb_buffer_pool_size、max_connections、slow_query_log 三大項。
• 參考 MySQLTuner、Percona Toolkit 自動分析建議。