MySQL 優化兩大核心參數：table_open_cache (原 table_cache) 與 key_buffer_size 深度調優

table_open_cache (舊名 table_cache)

作用

緩存 **表結構定義 (.frm) 和文件句柄**，避免頻繁打開/關閉表文件開銷。MyISAM 每表 2 個句柄 (.MYD/.MYI)，InnoDB 共享表空間 1 個，獨立表空間 2 個。

監控指標

SHOW GLOBAL STATUS LIKE 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 400   -- 當前打開表數
| Opened_tables | 5000  -- 歷史累計打開表數 (含緩存未命中)
+---------------+-------+

• Open_tables 接近 table_open_cache 且 Opened_tables 快速增長 → **緩存不足，需調大**。
• 比值 Opened_tables / Uptime (每秒打開表數) > 1 → 調大。

調大副作用與對策

• 消耗文件描述符：table_open_cache * 2 (MyISAM) 或 * 1~2 (InnoDB)。
• 必須同步調大系統級 open_files_limit (my.cnf [mysqld] open_files_limit=65535) 及 OS ulimit -n。
• 過大導致表定義佔用過多內存，5.6+ 引入 table_definition_cache 分離定義緩存。

建議值

key_buffer_size (MyISAM 專用)

監控指標

SHOW GLOBAL STATUS LIKE 'Key_read%';
+-------------------+-----------+
| Variable_name     | Value     |
+-------------------+-----------+
| Key_read_requests | 10000000  -- 索引讀請求
| Key_reads         | 500       -- 磁碟讀
+-------------------+-----------+

• 未命中率 = Key_reads / Key_read_requests
• 目標 **< 0.1% (1:1000)**，理想 **< 0.01% (1:10000)**。
• 實測優良庫常達 **1:20000**。

塊利用率

SHOW GLOBAL STATUS LIKE 'Key_blocks_u%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_blocks_unused | 1000  -- 剩餘塊
| Key_blocks_used   | 8000  -- 峰值使用
+-------------------+-------+

• 利用率 = Key_blocks_used / (Key_blocks_used + Key_blocks_unused) ≈ **80~90% 理想**。
• 100% 且未命中率高 → 擴容；長期 100% 利用率低 → 可能有大量無用索引或臨時表佔用。

綜合調優流程

1. 收集高峰期 30 分鐘 Open_tables, Opened_tables, Key_read_requests, Key_reads, Key_blocks_used, Key_blocks_unused。
2. 計算未命中率、利用率、每秒打開表數。 3. 根據業務引擎比例分配內存：
• InnoDB 為主：innodb_buffer_pool_size 佔 60~80% 內存，**key_buffer_size 僅 16~32M** (滿足 mysql 系統表/內部臨時表)。
• MyISAM 為主：key_buffer_size 佔 20~30% 內存，table_open_cache 配合 open_files_limit 調大。
4. 修改 my.cnf，重啟驗證，持續觀察 24h。 5. 納入監控告警：Opened_tables 突增、Key_reads 突增、Table_open_cache_overflows (5.6+ 新增) > 0。

避坑提示

• 參數名變更：5.1.3+ 將 table_cache 重命名為 table_open_cache，舊名兼容但建議用新名。
• table_open_cache 過大導致 FLUSH TABLES / 遷移表耗時極長。
• MyISAM 大量 REPAIR/OPTIMIZE 臨時佔用 key buffer，預留 20% 餘量。