MySQL 5.6/5.7/8.0 核心優化配置實戰手冊

MySQL 5.6 大幅改善預設值，需手動調優的參數大幅減少。本文針對專用資料庫伺服器，梳理 InnoDB、複制、字符集、SQL 模式、連線管理五大維度的關鍵配置，並提供 64GB 記憶體 + RAID 控制器的實戰配置範本。

一、InnoDB 核心參數 (效能心臟)

IOPS 估算範例

# 單顆 HDD 200 IOPS，RAID10 (10顆) = (10/2) * 200 = 1000 IOPS
# RAID 控制器快取通常能額外提升 20-50%
# SSD 單顆 10,000-100,000 IOPS

innodb_io_capacity = 2000
innodb_io_capacity_max = 6000
innodb_lru_scan_depth = 2000

二、複制與二進制日誌 (高可用基石)

# 必須配置
log-bin = mysql-bin
server-id = 123                    # 集群唯一

# 崩潰安全複制 (Crash-safe Replication)
sync_binlog = 1                    # 每提交同步 binlog 到磁碟
sync_relay_log = 1                 # Relay log 同步
relay-log-info-repository = TABLE  # 元數據存表而非檔案
master-info-repository = TABLE

# 日誌清理
expire_logs_days = 7               # 7-10 天，備份為主恢復手段

# 行基礎複制 (RBR) - 推薦
binlog_format = ROW
transaction-isolation = READ-COMMITTED
innodb_autoinc_lock_mode = 2       # 互斥鎖優化，需 ROW 格式

三、字符集與 SQL 模式 (避坑指南)

# 統一字符集 (utf8mb4 支援 Emoji、完整 Unicode)
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# skip-character-set-client-handshake  # 強制忽略客戶端字符集設定

# 嚴格 SQL 模式 - 新應用強烈建議
sql_mode = "STRICT_TRANS_TABLES,
    ERROR_FOR_DIVISION_BY_ZERO,
    NO_AUTO_CREATE_USER,
    NO_AUTO_VALUE_ON_ZERO,
    NO_ENGINE_SUBSTITUTION,
    NO_ZERO_DATE,
    NO_ZERO_IN_DATE,
    ONLY_FULL_GROUP_BY"

模式解讀：

• STRICT_TRANS_TABLES：非法數據報錯而非截斷警告
• ONLY_FULL_GROUP_BY：GROUP BY 必須包含所有非聚合欄位
• NO_ZERO_DATE：禁止 '0000-00-00' 日期

四、連線與網路優化

# 禁用 DNS 反向解析 (加快連線建立)
skip-name-resolve

# 連線錯誤計數 (配合防火牆，實質由防火牆防暴力破解)
max_connect_errors = 100000

# 最大連線數 (配合應用連線池)
max_connections = 500
# 連線池建議：應用端維持 50-100 核心連線，高峰擴至 200-300
# 避免幾千空閒連線佔用線程記憶體

# 時區統一 UTC (全球化業務必備)
default-time-zone = '+00:00'
# 或 my.cnf: timezone = GMT

五、實戰配置範本 (64GB RAM, RAID 控制器, 複制, 新應用, 防火牆, UTC)

[mysqld]
# === InnoDB ===
innodb_buffer_pool_size = 50G
innodb_log_file_size = 2G
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 6000
innodb_lru_scan_depth = 2000
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

# === Binary Log / Replication ===
log-bin = mysql-bin
sync_binlog = 1
sync_relay_log = 1
relay-log-info-repository = TABLE
master-info-repository = TABLE
expire_logs_days = 10
binlog_format = ROW
transaction-isolation = READ-COMMITTED
innodb_autoinc_lock_mode = 2

# === Character Set & SQL Mode ===
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY"

# === Network & Connection ===
skip-name-resolve
max_connect_errors = 100000
max_connections = 500
default-time-zone = '+00:00'

# === Server Identity ===
server-id = 123

遷移至 8.0 額外關注點

• innodb_dedicated_server=ON：自動根據記憶體設定 buffer_pool、log_file_size、flush_method
• 資料字典原子化、即時 DDL、隱藏索引、CTE/窗口函數
• caching_sha2_password 預設認證插件，客戶端驅動需 8.0+ 版本
• 移除查詢緩存、query_cache_type 參數