通用核心參數 (兩引擎共用)
| 參數 | 建議值 | 說明 |
|---|
max_connections | 1000~5000 | 視記憶體與應用並發調整，避免 "Too many connections" |
query_cache_size | 0 (5.7+ 棄用) / 64M (5.6-) | 高寫入場景建議關閉 (0)，否則鎖競爭嚴重 |
table_open_cache | 2000~10000 | 配合 open_files_limit，減少開關表開銷 |
thread_cache_size | 50~200 | 減少線程建立銷毀開銷 |
sort_buffer_size | 2M~8M | 每連線獨佔，總量 = 值 × max_connections，勿設過大 |
read_buffer_size | 2M~4M | 全表掃描緩衝，每連線獨佔 |
read_rnd_buffer_size | 4M~8M | 排序後隨機讀取緩衝 |
join_buffer_size | 2M~8M | 無索引 JOIN 緩衝，每連線獨佔 |
skip_name_resolve | ON | 禁用 DNS 反解，加速連線建立，授權需用 IP |
skip_external_locking | ON | 避免外部鎖定，增強穩定性 |
MyISAM 專屬關鍵參數
| 參數 | 建議值 (4G RAM 例) | 說明 |
|---|
key_buffer_size | 256M~512M | 索引塊緩衝，**最關鍵**參數。監控 Key_reads/Key_read_requests < 0.01 |
myisam_sort_buffer_size | 64M~128M | 建立/修復索引時排序緩衡 |
myisam_max_sort_file_size | 10G | 修復索引臨時檔案上限 |
myisam_repair_threads | 1 | 平行修復索引執行緒 (1=單執行緒) |
concurrent_insert | 2 (ALWAYS) | 允許尾部並發插入，減少鎖爭用 |
InnoDB 專屬關鍵參數 (核心調優區)
| 參數 | 建議值 | 說明 |
|---|
innodb_buffer_pool_size | 物理記憶體 60~80% | **最關鍵**參數，緩存數據+索引+插入緩衝+鎖資訊。專用 DB 可達 70-80% |
innodb_buffer_pool_instances | 1~8 (≤ buffer_pool/1G) | 減少全域鎖競爭，8.0 預設自動 |
innodb_log_file_size | 256M~2G | 單檔大小，總量建議 2-4G。大檔 = 恢復慢、Checkpoint 慢；小檔 = 頻繁刷盤 |
innodb_log_files_in_group | 2~4 | 日誌組檔案數 |
innodb_log_buffer_size | 16M~64M | 事務日誌緩衝，大事務需調大 |
innodb_flush_log_at_trx_commit | 1 (核心業務) / 2 (高寫入可容忍秒級丟失) | 1=完全 ACID；2=每秒刷盤；0=每秒刷盤+提交不刷 |
innodb_flush_method | O_DIRECT | 避免雙重緩衝 (OS cache + InnoDB buffer) |
innodb_file_per_table | ON | 獨立表空間，支援單表備份、空間回收、遷移 |
innodb_open_files | 2000~10000 | 配合 table_open_cache，獨立表空間必增 |
innodb_read_io_threads / write_io_threads | 4~16 | 背景 I/O 執行緒，SSD/NVMe 可調高 |
innodb_lock_wait_timeout | 50~100 | 鎖等待超時 (秒)，避免死鎖長時間阻塞 |
innodb_deadlock_detect | ON | 死鎖偵測，高並發可關閉改用 timeout |
監控指標與調優閉環
-- 關鍵狀態觀察
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'; -- 命中率
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits'; -- 日誌等待
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%'; -- 行鎖等待
SHOW GLOBAL STATUS LIKE 'Key_read%'; -- MyISAM 索引命中
SHOW GLOBAL STATUS LIKE 'Qcache%'; -- Query Cache (舊版)
-- 慢查詢分析
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;
配置檔範例片段 (專用 16G RAM DB 伺服器)
[mysqld]
# 通用
max_connections = 2000
table_open_cache = 4000
thread_cache_size = 200
skip_name_resolve = 1
skip_external_locking = 1
# InnoDB 核心
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_open_files = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# MyISAM (若仍有)
key_buffer_size = 512M
myisam_sort_buffer_size = 128M
# 安全
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
