配置檔結構

[client]          # 客戶端工具 (mysql, mysqldump 等) 讀取
port = 3306
default-character-set = utf8mb4

[mysqld]          # Server 守護進程讀取 (核心調優區)
# 基礎路徑
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
server-id = 1

# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 連線與執行緒
max_connections = 1000              # 最大連線數，配合 thread_cache_size
thread_cache_size = 64              # 緩存執行緒，減少創建銷毀開銷
max_connect_errors = 100000         # 防止主機被封鎖
back_log = 500                      # TCP 連線佇列

# 查詢快取 (MySQL 5.7 移除, 8.0 刪除)
# query_cache_type = 0
# query_cache_size = 0

# 表快取
table_open_cache = 4000             # 開啟表緩存數，配合 max_connections
table_definition_cache = 2000       # 表定義緩存

# 臨時表
tmp_table_size = 64M                # 記憶體臨時表上限，超過轉 MyISAM 磁碟表
max_heap_table_size = 64M           # 配合 tmp_table_size 使用

# 排序/連接/讀取緩衝 (每連線分配，謹慎設定避免 OOM)
sort_buffer_size = 4M               # ORDER BY/GROUP BY
read_buffer_size = 2M               -- 全表掃描序列讀取
read_rnd_buffer_size = 8M           -- 排序後隨機讀取 (範圍查詢)
join_buffer_size = 4M               -- 無索引 JOIN (慎用，優先加索引)

# MyISAM 專用 (若無 MyISAM 表可設極小)
key_buffer_size = 32M               # 索引塊緩存
myisam_sort_buffer_size = 64M       # REPAIR/ALTER TABLE 索引重建
myisam_max_sort_file_size = 10G

# InnoDB 核心參數 (重中之重)
default_storage_engine = InnoDB
innodb_buffer_pool_size = 12G       # **最關鍵參數**，物理記憶體 60-80% (專用 DB 機)
innodb_buffer_pool_instances = 8    # 實例數 (1GB/實例)，減少鎖競爭 (5.6+)
innodb_log_file_size = 1G           # Redo Log 大小，總大小 = size * files (通常 2 個)，大 = 恢復慢、檢查點壓力小
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M        # 交易記錄緩衝，大交易可調大
innodb_flush_log_at_trx_commit = 1  # **1=完全 ACID (每提交刷盤)**，2=每秒刷盤(高效能/風險1秒數據), 0=僅寫OS Cache
innodb_flush_method = O_DIRECT      # 繞過 OS Cache，避免雙重緩衝 (Linux 必設)
innodb_file_per_table = ON          # 獨立表空間 (.ibd)，便於管理/回收空間
innodb_read_io_threads = 8          # 讀執行緒
innodb_write_io_threads = 8         # 寫執行緒
innodb_io_capacity = 2000           # SSD IOPS 能力值 (HDD 200, SSD 2000+, NVMe 10000+)
innodb_max_dirty_pages_pct = 75     # 髒頁比例上限，觸發積極刷盤
innodb_lock_wait_timeout = 50       # 鎖等待超時 (秒)
innodb_deadlock_detect = ON         # 死鎖檢測

# 二進位日誌 (複寫/時間點恢復必備)
log_bin = mysql-bin
binlog_format = ROW                 # ROW 格式最安全，支援複寫一致性
expire_logs_days = 7                # 自動清理舊 binlog
max_binlog_size = 1G
sync_binlog = 1                     # 1=每提交同步盤 (最安全)，0=交由OS, N=每N次
binlog_rows_query_log_events = ON   # 記錄原始 SQL (5.6+), 利於審計/解析

# 慢查詢日誌
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1                 # 超過 1 秒記錄 (微秒級: 0.05)
log_queries_not_using_indexes = ON  # 記錄無索引查詢
log_throttle_queries_not_using_indexes = 100 # 限流避免日誌爆炸

# 錯誤日誌
log_error = /var/log/mysql/error.log

# GTID (複寫主從)
gtid_mode = ON
enforce_gtid_consistency = ON

參數調優優先順序 (由高到低)

1. innodb_buffer_pool_size：記憶體允許最大，減少磁碟 I/O。
2. innodb_flush_log_at_trx_commit / sync_binlog：權衡效能與資料安全 (1/1 最安全)。
3. innodb_log_file_size：降低檢查點衝刷頻率，提升寫入吞吐。
4. max_connections / thread_cache_size：應對連線風暴。
5. table_open_cache / table_definition_cache：減少開表開銷。
6. sort/read/join buffer：針對特定慢查詢 (檢視 `EXPLAIN` 為 Using filesort/temporary) 微調，小心累積記憶體。
7. innodb_io_capacity：匹配底層儲存裝置 IOPS。

動態修改與驗證

-- 運行時動態調整 (部分需 SUPER 權限，部分唯讀)
SET GLOBAL innodb_buffer_pool_size = 16106127360; -- 15G (5.7+ 支援動態調整)
SET GLOBAL max_connections = 2000;
SET GLOBAL slow_query_log = 1;

-- 查看當前生效值
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

配置變更檢查清單

• [ ] 修改 `my.cnf` 後 `systemctl restart mysqld` (靜態參數必須重啟)。
• [ ] 監控 `error.log` 確認無啟動錯誤/警告。
• [ ] 觀察 `Innodb_buffer_pool_read_requests` / `reads` 變化趨勢。
• [ ] 壓測驗證 QPS/TPS/延遲指標提升。