核心優化原則

1. 內存 > 磁碟：盡量讓熱數據、索引駐留內存
2. 索引 > 數據：索引緩存命中率直接決定查詢速度
3. 漸進調優：每次調整一個參數，觀察效果
4. 測試環境驗證：嚴禁生產庫直接實驗

關鍵服務器參數調優

1. 表緩存 table_open_cache (舊 table_cache)

# 檢查是否需增加
SHOW GLOBAL STATUS LIKE 'Opened_tables';
# Opened_tables 持續快速增長 → 緩存太小

# 設定建議（根據打開表數量）
table_open_cache = 4000  # 典型值，配合 open_files_limit

2. MyISAM 鍵緩存 key_buffer_size

# 預設 8MB，專用 MyISAM 庫建議 25%-50% 物理內存
key_buffer_size = 2G

# 4.1+ 支持多鍵緩存，專表專用
SET GLOBAL hot_cache.key_buffer_size = 128*1024*1024;
CACHE INDEX hot_table IN hot_cache;
LOAD INDEX INTO CACHE hot_table;

3. InnoDB 緩衝池 innodb_buffer_pool_size（最關鍵）

# 專用 DB 服務器：60-80% 物理內存
innodb_buffer_pool_size = 16G
# 多實例可分多個實例
innodb_buffer_pool_instances = 8

4. InnoDB 日誌緩衝 innodb_log_buffer_size

innodb_log_buffer_size = 64M  # 大事務場景調大

5. 查詢緩存 query_cache_*（5.7 廢用，8.0 移除）

# 僅適用「讀多寫少、重複查詢多」場景
query_cache_type = 1          # ON
query_cache_size = 64M        # 不宜過大（>256M 有鎖爭用）
query_cache_limit = 2M        # 單查詢結果超過不緩存

# 客戶端控制
SET SESSION query_cache_type = OFF;  -- 當前會話禁用
SELECT SQL_NO_CACHE * FROM huge_table; -- 單查詢禁用

注意：高併發寫入場景查詢緩存常成瓶頸，建議關閉 (query_cache_type=0)。

其他優化策略

• 禁用無用存儲引擎：編譯時 --without-innodb 或運行時 --skip-innodb（不推薦）
• 簡化權限表：清空 tables_priv、columns_priv 讓權限檢查快速路徑
• 靜態編譯：--with-mysqld-ldflags=-all-static 提升 5-10% 效能
• 優化編譯器：GCC -O3 -march=native、Intel ICC

硬件優化優先級

優先級	措施	效果
1	增加內存	擴大緩衝池、減少磁碟 I/O、支援更大排序/連接緩衝
2	NVMe SSD / RAID 10	降低延遲、提升 IOPS、並行 I/O
3	多核 CPU	並行查詢、並行索引構建、多線程刷盤
4	分離數據/日誌/備份到不同物理設備	消除 I/O 爭用
5	RAID 控制器帶 BBU/WB	寫入緩存掉電保護

檢查清單

• ✅ innodb_buffer_pool_size ≥ 數據熱集大小
• ✅ innodb_log_file_size ≥ 1GB（減少检查点刷盘）
• ✅ innodb_flush_log_at_trx_commit = 1（ACID）或 2（性能折衷）
• ✅ sync_binlog = 1（主庫數據安全）
• ✅ 關鍵表有合適索引（EXPLAIN 驗證）
• ✅ 慢查詢日誌開啟並定期分析（pt-query-digest）
• ✅ 監控：QPS、延遲、緩存命中率、磁碟 I/O、鎖等待