MySQL 效能調優三板斧：SHOW STATUS、EXPLAIN、OPTIMIZE TABLE 實戰指南

一、SHOW STATUS：掌握伺服器脈搏

-- 關鍵狀態變數
SHOW GLOBAL STATUS LIKE 'Threads_connected';      -- 當前連線數
SHOW GLOBAL STATUS LIKE 'Max_used_connections';   -- 歷史峰值連線
SHOW GLOBAL STATUS LIKE 'Aborted_connects';       -- 連線失敗計數
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; -- 磁盤讀取次數
SHOW GLOBAL STATUS LIKE 'Handler_read_rnd_next';  -- 全表掃描次數
SHOW GLOBAL STATUS LIKE 'Slow_queries';           -- 慢查詢計數

搭配 SHOW ENGINE INNODB STATUS 可深入分析鎖等待、死鎖、Buffer Pool 狀態。

二、EXPLAIN：SQL 執行計劃深度解析

經典優化案例：多表 JOIN 消除 ALL type 全表掃描

原始查詢 4 表 JOIN，EXPLAIN 顯示所有表 type=ALL，預估掃描 450 億行。

優化步驟

1. 修正欄位長度不匹配：CHAR(10) vs CHAR(15) 導致索引失效

   ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
   ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), MODIFY ClientID VARCHAR(15);

2. 更新索引統計資訊：

   myisamchk --analyze /path/to/tt.MYI
   mysqladmin flush-tables

優化後 EXPLAIN 關鍵指標

預估掃描行數從 450 億降至 ~3,872，查詢時間從「永不返回」降至毫秒級。

三、OPTIMIZE TABLE：碎片整理與統計更新

-- 僅支援 MyISAM、InnoDB、BDB
OPTIMIZE TABLE tbl_name [, tbl_name] ...;

-- 等效操作（InnoDB）
ALTER TABLE tbl_name ENGINE=InnoDB;
ANALYZE TABLE tbl_name;

適用場景：大量 UPDATE/DELETE 後、變長欄位（VARCHAR/TEXT/BLOB）頻繁變更、表檔案明顯大於資料實際大小。

進階工具鏈

• pt-query-digest：慢查詢日志分析
• sys.schema_table_statistics：表級 IO 統計（MySQL 5.7+）
• Performance Schema：待機事件、鎖等待深度分析