一、內存與緩沖優化

# InnoDB緩沖池（內存的60%-80%）
innodb_buffer_pool_size = 12G      # 核心參數
innodb_buffer_pool_instances = 8   # 8核CPU建議分8個實例# 日志緩沖區與Redo日志
innodb_log_buffer_size = 256M      # 事務日志緩沖區
innodb_log_file_size = 4G          # Redo日志文件大小
innodb_log_files_in_group = 3      # Redo日志組文件數量# 內存臨時表
tmp_table_size = 512M              # 內存臨時表上限
max_heap_table_size = 512M         # 內存表最大值

---

二、連接與線程優化

# 連接控制
max_connections = 1000             # 最大連接數
thread_cache_size = 100            # 線程緩存數（建議max_connections的10%）
table_open_cache = 2000            # 表緩存數量# InnoDB線程優化
innodb_thread_concurrency = 16     # 并發線程數（建議CPU核心數*2）
innodb_read_io_threads = 16        # 讀線程數（8核CPU建議16）
innodb_write_io_threads = 16       # 寫線程數（8核CPU建議16）

---

三、事務與日志優化

# 事務持久化策略
innodb_flush_log_at_trx_commit = 1 # 生產環境建議1（安全優先）/測試環境可設2
sync_binlog = 1                    # Binlog同步策略# Binlog配置
binlog_expire_logs_seconds = 2592000  # Binlog保留30天（替代expire_logs_days）
binlog_cache_size = 8M             # 事務Binlog緩存

---

四、查詢與鎖優化

# 查詢緩沖區
sort_buffer_size = 8M              # 排序緩沖區（避免過大）
read_buffer_size = 8M              # 全表掃描緩沖區
join_buffer_size = 8M              # JOIN操作緩沖區# 鎖與超時
innodb_lock_wait_timeout = 50      # 行鎖等待超時（秒）
lock_wait_timeout = 120            # 元數據鎖超時

---

五、網絡與安全

max_allowed_packet = 64M           # 最大數據包限制
skip_name_resolve = ON             # 禁用DNS解析加速連接
lower_case_table_names = 1         # 表名不區分大小寫

---

六、監控與診斷

# 慢查詢日志
slow_query_log = ON                # 啟用慢查詢日志
long_query_time = 1                # 記錄超過1秒的查詢
log_queries_not_using_indexes = ON # 記錄無索引查詢# Performance Schema
performance_schema = ON            # 啟用性能監控

---

調優驗證步驟

1. 壓力測試工具
   使用sysbench模擬業務負載（OLTP讀寫比例推薦8:2）：

   sysbench oltp_read_write --threads=16 --time=300 --mysql-host=localhost --mysql-user=root run
   

2. 監控指標

   • 通過SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'檢查緩沖池命中率（目標>95%）
   • 通過SHOW STATUS LIKE 'Threads_%'監控線程狀態
   • 使用pt-query-digest分析慢查詢日志

3. 動態調整

   SET GLOBAL innodb_flush_log_at_trx_commit=2;  -- 臨時調整事務提交策略
   SET GLOBAL tmp_table_size=1024*1024*1024;     -- 調整臨時表大小