MySQL my.cnf 緩存與效能優化核心參數配置模板

MySQL 生產環境 my.cnf 核心優化參數模板：緩存、連接、索引、慢查詢全維度調優

MySQL 效能調優遵循「硬體 → 參數 → 狀態 → 慢查詢 → 應用」閉環。本文提供針對專用資料庫伺服器的 my.cnf 核心參數配置模板，含參數解析、計算公式、避坑指南，可直接作為生產基線調整。

配置原則

1. 總記憶體佔用 ≤ 物理記憶體 75-80%
2. 全局緩衝 + (線程緩衝 × max_connections) ≤ 可用記憶體
3. 一次調一個參數，負載測試驗證
4. 區分 MyISAM/InnoDB 參數，現代以 InnoDB 為主

核心參數模板 (以 16GB 記憶體、8 核 CPU、SSD 為例)

[mysqld]
# ========== 基礎身份 ==========
server_id = 1
port = 3306
socket = /tmp/mysql.sock
datadir = /var/lib/mysql
basedir = /usr/local/mysql
pid_file = /var/run/mysqld/mysqld.pid
user = mysql
default_storage_engine = InnoDB
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
skip_name_resolve = 1                    # 關閉 DNS 反解，加速連接
skip_ssl = 1                             # 內網環境可關閉 SSL 減少握手開銷

# ========== 連接層 ==========
max_connections = 2000                   # 視應用連接池大小設定
thread_cache_size = 200                  # 減少線程創建開銷
back_log = 500                           # TCP 佇列長度，突發連接保護
max_connect_errors = 100000              # 防止主機被封鎖
wait_timeout = 600                       # 10 分鐘閒置斷開，配合應用連接池 maxLifetime
interactive_timeout = 600

# ========== InnoDB 核心 (最重要) ==========
innodb_buffer_pool_size = 10G            # 物理記憶體 60-70%，核心緩衝池
innodb_buffer_pool_instances = 8         # 核心數，減少鎖爭用 (≤ 1GB/實例)
innodb_log_file_size = 1G                # Redo Log 大，減少 Checkpoint 壓力
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1       # 1=ACID, 2=高效能(丟1秒), 0=最高效能
innodb_flush_method = O_DIRECT           # 繞過 OS Cache，避免雙重緩衝
innodb_file_per_table = 1                # 獨立表空間，支援 OPTIMIZE、可移植
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000                # SSD IOPS 能力
innodb_io_capacity_max = 4000
innodb_lru_scan_depth = 1024
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 1G

# ========== 緩存與排序 (線程私有，慎重設定) ==========
sort_buffer_size = 4M                    # ORDER BY/GROUP BY 排序緩衝
read_buffer_size = 2M                    # 全表掃描順序讀取緩衝
read_rnd_buffer_size = 4M                # 隨機讀取緩衝
join_buffer_size = 4M                    # 無索引關聯緩衝
# 以上 4 參數 × max_connections = 總線程記憶體，勿過大！
# 建議：sort/read_rnd/join 各 2-8M，read_buffer 1-4M

# ========== MyISAM 兼容 (若仍有 MyISAM 表) ==========
key_buffer_size = 256M                   # MyISAM 索引塊緩衝
myisam_sort_buffer_size = 128M           # REPAIR/OPTIMIZE 排序緩衝
# skip_innodb / skip_bdb 已廢棄，改用 default_storage_engine

# ========== 表緩存 ==========
table_open_cache = 2000                  # 開啟表緩存，> max_connections × 單查詢表數
table_definition_cache = 1000            # 表定義緩存 (5.6+)

# ========== 查詢緩存 (5.7 廢棄，8.0 移除) ==========
# query_cache_size = 0
# query_cache_type = 0
# 替代方案：應用層 Redis、ProxySQL 查詢緩存

# ========== 慢查詢日誌 (必開) ==========
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1                      # >1秒記錄
log_queries_not_using_indexes = 1        # 記錄無索引查詢
log_slow_admin_statements = 1
log_slow_slave_statements = 1
min_examined_row_limit = 100             # 忽略掃描行數 <100 的查詢

# ========== 二進制日誌 (主從/備份必需) ==========
log_bin = /var/log/mysql/binlog
binlog_format = ROW                      # ROW 格式最安全
expire_logs_days = 7
max_binlog_size = 500M
binlog_cache_size = 4M
sync_binlog = 1                          # 1=最安全, 0=最高效能

# ========== 安全與其他 ==========
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER"
max_allowed_packet = 64M                 # 大字段/批量插入
tmp_table_size = 64M                     # 內存臨時表上限
max_heap_table_size = 64M                # HEAP 表上限
bulk_insert_buffer_size = 32M            # 批量插入緩衝 (MyISAM)

參數計算驗證腳本

#!/bin/bash
# 估算總記憶體佔用
GLOBAL=$(mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';" | tail -1 | awk '{print $2}')
GLOBAL_MB=$((GLOBAL/1024/1024))
THREAD_MB=$(( (4+2+4+4) * 2000 / 1024 ))  # sort+read+read_rnd+join (MB) × max_connections
echo "Global buffers: ${GLOBAL_MB} MB"
echo "Thread buffers (max): ${THREAD_MB} MB"
echo "Estimated total: $((GLOBAL_MB + THREAD_MB)) MB"

調優順序與驗證指標

1. InnoDB Buffer Pool：命中率 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests > 99.9%
2. 連接層：Threads_created 平穩、Aborted_connects = 0
3. 慢查詢：pt-query-digest 分析 Top 10，逐個優化
4. 磁碟 IO：iostat -xmt 1 觀察 %util、await
5. 鎖爭用：Innodb_row_lock_waits、Innodb_row_lock_time_avg

版本差異提醒

• MySQL 5.7：支援 query_cache 但建議關閉
• MySQL 8.0：移除 query_cache、information_schema 性能大增、預設 utf8mb4、caching_sha2_password 認證插件
• MariaDB 10.x：columnstore、spider 引擎、thread_pool 內建

此模板為專用資料庫伺服器基線，實際部署必須依據硬體規格、業務讀寫比、併發模式、資料量級逐項微調。參數調優無銀彈，唯有持續觀測、假設驗證、迭代優化。