MySQL 核心配置參數 my.cnf 深度解析：table_cache、key_buffer、InnoDB、二進制日誌、慢查詢

獲取當前配置與狀態

# 命令列
mysqld --verbose --help | grep -A1 '^table_cache'
mysqladmin -u root -p variables extended-status

# MySQL 內部
SHOW VARIABLES;
SHOW STATUS;
SHOW VARIABLES LIKE 'table_cache';
SHOW STATUS LIKE 'Open%tables';

關鍵參數優化指南

1. table_cache (table_open_cache)

表快取大小。監控 Open_tables 與 Opened_tables：

• Open_tables = table_cache 且 Opened_tables 持續增長 → 需調大。
• 1GB 記憶體建議 128-256。
• 過大導致檔案描述符耗盡。

2. key_buffer_size

MyISAM 索引快取。監控 Key_read_requests / Key_reads：

• 比例 Key_reads / Key_read_requests 應 < 1/100，理想 < 1/1000。
• 純 InnoDB 環境建議 16-64MB（供內部臨時表）。

3. query_cache_size（5.7 廢除，8.0 移除）

• 監控 Qcache_hits、Qcache_lowmem_prunes、Qcache_free_blocks。
• 命中率低且碎片高 → 關閉或調小 query_cache_min_res_unit。

4. 二進制日誌

[mysqld]
log-bin = /var/log/mysql/mysql-bin
binlog-format = ROW
expire-logs-days = 7
max-binlog-size = 100M
# 指定庫
binlog-do-db = db1
binlog-ignore-db = test

查看狀態：SHOW MASTER STATUS;

5. 慢查詢日誌

[mysqld]
slow-query-log = 1
slow-query-log-file = /var/log/mysql/slow.log
long-query-time = 1
log-queries-not-using-indexes = 1

6. InnoDB 核心配置

[mysqld]
innodb_buffer_pool_size = 4G  # 實體記憶體 60-80%
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:1G:autoextend

擴充數據檔案：停機、修改 innodb_data_file_path 加入新檔案、啟動。

7. flush_time

系統不穩定時設定非零值（秒），定期刷新表快取降低資料遺失風險，犧牲效能。

優化檢查清單