MySQL 狀態變量優化實戰（一）：慢查詢、連接數、Key Buffer、臨時表、Open Tables 五大核心指標深度解析

核心理念：看狀態、調參數、驗證效果

配置文件模板只能作為起點，**穩定運行一段時間後，根據 `SHOW GLOBAL STATUS` 實際負載特徵微調，才是正確優化路徑**。

一、慢查詢分析

-- 配置檢查
SHOW VARIABLES LIKE '%slow%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| log_slow_queries | ON    |
| slow_launch_time | 2     |
+------------------+-------+

-- 狀態檢查
SHOW GLOBAL STATUS LIKE '%slow%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| Slow_launch_threads | 0     |
| Slow_queries        | 4148  |
+---------------------+-------+

• 慢查詢閾值：建議 1~5 秒，過長失去意義；微秒級需打 Percona 慢查詢補丁。
• 主從架構技巧：僅在一台從庫開啟慢查詢日誌，減輕主庫 I/O 壓力。
• 分析工具：pt-query-digest / mysqldumpslow / mysqlsla。

二、連接數監控

SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 256   |
+-----------------+-------+

SHOW GLOBAL STATUS LIKE 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 245   |
+----------------------+-------+

• 健康水位：Max_used_connections / max_connections ≈ 85% 最理想。
• < 10%：配置過大，浪費內存（每連接 ~256KB+）。
• > 95%：臨近上限，需擴容或優化應用連接池。
• 報錯 1040：實際達上限，立即增大或增加讀從庫分流。

三、Key Buffer Size (MyISAM 核心)

SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| key_buffer_size | 536870912  |  -- 512MB

SHOW GLOBAL STATUS LIKE 'key_read%';
+------------------------+-------------+
| Variable_name          | Value       |
+------------------------+-------------+
| Key_read_requests      | 27813678764 |
| Key_reads              | 6798830     |
+------------------------+-------------+

• 未命中率：Key_reads / Key_read_requests = 0.0244%（極優，<0.1% 即合格）。
• 過大判斷：未命中率 < 0.01% 說明內存有浪費，可適當縮減。
• Block 視角：

  SHOW GLOBAL STATUS LIKE 'key_blocks_u%';
  +------------------------+-------------+
  | Variable_name          | Value       |
  +------------------------+-------------+
  | Key_blocks_unused      | 0           |
  | Key_blocks_used        | 413543      |
  +------------------------+-------------+

  使用率 Key_blocks_used / (Key_blocks_used + Key_blocks_unused) ≈ 100%，建議擴容或檢查是否過度索引。
• 目標：Block 使用率 ~80%。

四、臨時表監控

SHOW GLOBAL STATUS LIKE 'created_tmp%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 21197   |
| Created_tmp_files       | 58      |
| Created_tmp_tables      | 1771587 |
+-------------------------+---------+

• 磁盤臨時表比率：Created_tmp_disk_tables / Created_tmp_tables = 1.2%（優秀，<25% 合格）。
• 配置檢查：

  SHOW VARIABLES WHERE Variable_name IN ('tmp_table_size','max_heap_table_size');
  +---------------------+-----------+
  | Variable_name       | Value     |
  +---------------------+-----------+
  | max_heap_table_size | 268435456 |  -- 256MB
  | tmp_table_size      | 536870912 |  -- 512MB
  +---------------------+-----------+

  實際生效值取兩者較小者（256MB）。超過 256MB 的臨時表強制落磁盤。
• 優化方向：優化 SQL 減少臨時表（避免 GROUP BY/ORDER BY 不走索引），或增大兩參數（內存允許前提下）。

五、Open Tables 狀態

SHOW GLOBAL STATUS LIKE 'open%tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 919   |
| Opened_tables | 1951  |
+---------------+-------+

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

• 命中率：Open_tables / Opened_tables = 47%（偏低，說明表頻繁打開/關閉）。
• 目標：Open_tables / Opened_tables ≥ 85% 且 Open_tables / table_cache ≤ 95%。
• 調整：增大 table_open_cache（如 4096），注意打開文件句柄限制 ulimit -n。

總結：五大指標儀表盤

下一步預告：第二部分將覆蓋 InnoDB 核心指標（Buffer Pool、Redo Log、Row Lock、Deadlock、Undo Log）與複製狀態監控。