您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 效能診斷三大核心工具實戰：SHOW STATUS、EXPLAIN、OPTIMIZE TABLE 深度應用指南

編輯：MySQL 資料庫相關知識

MySQL 內建診斷工具是效能調優的「聽診器」。SHOW STATUS 觀測服務器運行狀態，EXPLAIN 解剖查詢執行計畫，OPTIMIZE TABLE 整理碎片回收空間。本文透過實戰案例，示範如何組合這三大工具從症狀定位到根因解決，構建標準化診斷流程。

工具一：SHOW STATUS —— 服務器健康儀表板

核心指令與關鍵指標

-- 全域狀態
SHOW GLOBAL STATUS;

-- 重點關注指標分類
-- 連線相關
Threads_connected, Threads_running, Max_used_connections, Aborted_connects, Connection_errors_max_connections

-- 查詢相關
Questions, Queries, Com_select, Com_insert, Com_update, Com_delete, Slow_queries

-- 效能相關
Innodb_buffer_pool_reads, Innodb_buffer_pool_read_requests, 
Innodb_buffer_pool_wait_free, Innodb_log_waits,
Innodb_row_lock_waits, Innodb_row_lock_time_avg,
Table_locks_immediate, Table_locks_waited,
Sort_merge_passes, Sort_range, Sort_rows, Sort_scan

-- 複寫相關
Slave_running, Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master

實戰：計算緩衝池命中率

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests = 1000000
-- Innodb_buffer_pool_reads = 5000
-- 命中率 = 1 - (5000 / 1000000) = 99.5% (優秀 > 99%)

實戰：鎖爭用分析

SHOW GLOBAL STATUS LIKE 'Table_locks%';
-- Table_locks_immediate = 500000
-- Table_locks_waited = 2000
-- 等待率 = 2000/502000 = 0.4% (MyISAM 表鎖爭用指標)

SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';
-- Innodb_row_lock_waits = 150
-- Innodb_row_lock_time_avg = 45ms
-- 平均等待 45ms，需檢查熱點行/索引缺失

工具二：EXPLAIN —— 查詢計畫解剖刀

標準輸出欄位解讀

欄位	含義	優化導向
id	SELECT 識別號	同 id 由上而下執行；id 越大越優先
select_type	查詢類型	SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION/UNION RESULT
table	存取表	關注別名、衍生表
partitions	匹配分區	分區裁剪驗證
type	存取類型 (核心)	system > const > eq_ref > ref > range > index > ALL
possible_keys	可能用到的索引	候選集
key	實際使用的索引	NULL = 全表掃描
key_len	索引鍵長度	驗證複合索引最左前綴使用長度
ref	關聯比較對象	const/func/欄位名
rows	預估掃描行數	越小越好，乘積反映總成本
filtered	條件過濾比例	100 = 無額外過濾
Extra	額外資訊 (關鍵)	Using index/Using where/Using temporary/Using filesort

經典優化案例：四表 JOIN 從 450 億行掃描降至 52 行

原始查詢：

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, ..., do.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
  AND tt.ActualPC = et.EMPLOYID
  AND tt.AssignedPC = et_1.EMPLOYID
  AND tt.ClientID = do.CUSTNMBR;

表結構與索引：

表	欄位	類型	索引
tt	ActualPC	CHAR(10)	INDEX
tt	AssignedPC	CHAR(10)	INDEX
tt	ClientID	CHAR(10)	INDEX
et	EMPLOYID	CHAR(15)	PK
do	CUSTNMBR	CHAR(15)	PK

第一輪 EXPLAIN：全表掃描地獄 (type=ALL ×4，rows 乘積 452 億)

table  type  possible_keys  key  key_len  ref  rows  Extra
et     ALL   PRIMARY        NULL NULL     NULL 74
do     ALL   PRIMARY        NULL NULL     NULL 2135
et_1   ALL   PRIMARY        NULL NULL     NULL 74
tt     ALL   AssignedPC...  NULL NULL     NULL 3872  range checked for each record

問題 1：欄位長度不匹配導致索引失效

tt.ActualPC CHAR(10) vs et.EMPLOYID CHAR(15)
MySQL 無法利用索引比較不同長度的 CHAR/VARCHAR

修正 1：統一長度

ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
-- 再次 EXPLAIN：et 變為 eq_ref，rows 乘積除以 74

問題 2：其餘關聯欄位同樣長度不匹配

ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), MODIFY ClientID VARCHAR(15);
-- 再次 EXPLAIN：et_1、do 變為 eq_ref，tt 為 ref (ActualPC)，rows 大幅下降

問題 3：tt.ActualPC 值分布不均，優化器誤判

-- 更新統計資訊
myisamchk --analyze /path/to/mysql/data/db/tt
mysqladmin refresh
-- 或 InnoDB: ANALYZE TABLE tt;
-- 最終 EXPLAIN：tt 變為 ALL 但 rows=3872 (合理)，其餘 eq_ref=1，總成本極低

EXPLAIN 進階用法

-- 格式化輸出 (5.6+)
EXPLAIN FORMAT=JSON SELECT ...;  -- 含成本估算、索引條件下推細節

-- 分析 INSERT/UPDATE/DELETE (5.6+)
EXPLAIN INSERT INTO t SELECT * FROM s;

-- 觀察分區裁剪
EXPLAIN PARTITIONS SELECT * FROM partitioned_table WHERE dt > '2024-01-01';

工具三：OPTIMIZE TABLE —— 碎片整理與空間回收

適用場景與引擎支援

引擎	支援	操作內容	鎖行為
MyISAM	✅	整理資料檔、重建索引、更新統計	表鎖 (阻塞寫入)
InnoDB	✅ (映射為 ALTER TABLE ... ENGINE=InnoDB)	重建聚簇索引、回收碎片空間、更新統計	線上 DDL (5.6+ 大多數操作無鎖)
ARCHIVE	✅	重新壓縮	表鎖
MEMORY	❌	--	--

何時需要 OPTIMIZE

大量 DELETE/UPDATE 變長欄位 (VARCHAR, BLOB, TEXT) 後
SHOW TABLE STATUS 顯示 Data_free 佔比 > 20%
定期維護窗口 (週/月) 執行

實戰操作與監控

-- 單表
OPTIMIZE TABLE orders;

-- 批次 (需腳本)
SELECT CONCAT('OPTIMIZE TABLE ', table_schema, '.', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'biz_db'
  AND data_free > 100*1024*1024
  AND engine IN ('MyISAM','InnoDB');

-- 監控進度 (InnoDB 線上 DDL)
SHOW PROCESSLIST;
-- State: "copy to tmp table" / "Rebuilding index" / "Committing"

標準化診斷流程：從症狀到根因

發現症狀：應用回應慢、CPU 飆高、磁碟 IOPS 滿、錯誤率升高
SHOW STATUS 定位層面：
- 鎖等待高 → 檢查索引/熱點行/事務長度
- 緩衝池命中率低 → 擴充記憶體/縮小工作集/調整 innodb_buffer_pool_size
- Slow_queries 增長 → 慢查詢日誌分析
慢查詢日誌 + EXPLAIN 定位具體 SQL：pt-query-digest Top 10 → 逐個 EXPLAIN
根因分類：
- 缺索引 / 索引失效 (函數/隱式轉換/長度不匹配/最左前綴違反)
- 統計資訊過期 (ANALYZE TABLE)
- 大表無分區/無分庫分表
- SQL 寫法不佳 (SELECT*/深分頁/子查詢/OR/函數下推)
優化驗證：EXPLAIN 對比 → 壓測驗證 → 上線觀察
固化規範：將優化模式寫入團隊 SQL 開發規範、Code Review 清單

總結

SHOW STATUS 看宏觀、**EXPLAIN 解微觀**、**OPTIMIZE TABLE 治碎片**。三大工具組合拳，配合慢查詢日誌、Performance Schema、Sys Schema，構建完整的 MySQL 可觀測體系。建議將常用診斷查詢封裝為 mysql_diag.sh 腳本，納入日常巡檢與自動化告警。