MySQL 性能優化核心參數：thread_cache 與 table_cache（table_open_cache）原理、監控指標與調優實戰

thread_cache_size 與 table_open_cache（5.1.3 前稱 table_cache）是 MySQL 連接層與表緩存層的兩大核心參數，直接影響高併發下的 CPU 消耗、鎖競爭與文件描述符壓力。本文深度解析原理、關鍵監控指標、計算公式與生產調優案例。

一、Thread Cache：連接線程池

1. 工作原理

• 客戶端斷開時，線程不銷毀而是放入缓存池（最多 thread_cache_size 個）。
• 新連接先從池中取，池空才創建新線程（pthread_create 開銷大）。
• 適用場景：**短連接** 應用（PHP-FPM、Java 無連接池）效果顯著；長連接（連接池）效果有限。

2. 關鍵參數

3. 監控指標與命中率計算

SHOW GLOBAL STATUS LIKE 'Thread%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Threads_cached         | 12    |  -- 當前缓存中空閒線程
| Threads_connected      | 150   |  -- 當前活躍連接
| Threads_created        | 8689  |  -- 歷史累計創建線程數
| Threads_running        | 8     |  -- 正在執行查詢的線程
+------------------------+-------+

SHOW GLOBAL STATUS LIKE 'Connections';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Connections   | 199156 |  -- 歷史累計連接嘗試
+---------------+--------+

命中率公式：

Thread_Cache_Hit_Rate = (Connections - Threads_created) / Connections * 100%
-- 例：(199156 - 8689) / 199156 = 95.6%  優秀（>90% 即可）

調優判斷：

• 命中率 < 90% → 增大 thread_cache_size。
• Threads_cached 長期接近上限 → 可能過大，適當縮小節省內存。

二、Table Open Cache：表定義與文件描述符緩存

1. 工作原理

• MySQL 多線程隔離，每線程獨立打開表文件描述符（.frm/.ibd/.MYD/.MYI）。
• 緩存打開的表實例（包含結構定義、文件句柄），避免重複 open()/close() 系統調用。
• MyISAM：索引文件描述符全局共享，數據文件描述符每線程獨享。
  InnoDB：innodb_file_per_table=ON 時每表獨立 .ibd，文件句柄佔用多。

2. 關鍵監控指標

SHOW GLOBAL STATUS LIKE 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 919   |  -- 當前緩存中打開的表數
| Opened_tables | 1951  |  -- 歷史累計打開表數（含緩存未命中）
+---------------+-------+

SHOW VARIABLES LIKE 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2048  |
+------------------+-------+

3. 健康度判斷公式

# 1. 緩存利用率
Open_tables / table_open_cache <= 0.95   -- 未滿，無需擴容

# 2. 命中率
Open_tables / Opened_tables >= 0.85      -- 命中率高，否則擴容

# 3. 文件描述符壓力
# open_files_limit 需 > table_open_cache * 2 (MyISAM) 或 * 3 (InnoDB file_per_table)

4. 調優建議

• 保守策略：觀察一周業務高峰期 Opened_tables 峰值，設 table_open_cache = 峰值 * 1.2。
• 大表多實例：單實例表數 > 5 萬時，table_open_cache 設 10-20 萬，同步增大 open_files_limit（系統 ulimit -n 也要跟上）。
• 避免過大：緩存佔用內存 = table_open_cache × (表定義內存 + 文件句柄)，超大值會擠佔 Buffer Pool。

三、生產案例：電商促銷前調優

# 調優前
thread_cache_size = 32
table_open_cache = 2048
open_files_limit = 65536

# 促銷預估：峰值連接 3000，表數 1.5 萬
# 調優後
thread_cache_size = 64          # 短連接多，適當放大
table_open_cache = 20000        # 覆蓋所有熱表
open_files_limit = 100000       # 系統 ulimit -n 1000000

# 驗證
# 促銷期間 Thread_Cache_Hit_Rate 98%+, Open_tables/Open_tables 92%+, 無 "Too many open files" 報錯

四、清空緩存操作（慎用）

FLUSH TABLES;           -- 關閉所有緩存表，釋放文件句柄
FLUSH TABLES WITH READ LOCK;  -- 兼備份用，會阻塞寫入

日常運維**無需手動清空**，MySQL 會按 LRU 自動淘汰。

五、總結速查表

將兩參數納入「每日巡檢儀表盤」，促銷/大促前預調優，是高併發 MySQL 穩定運行的基石。