MySQL 效能優化利器：SHOW STATUS、EXPLAIN 實戰與 OPTIMIZE TABLE 整理碎片

優化不靠猜，靠工具。MySQL 內建 SHOW STATUS 看運行狀態、EXPLAIN 分析執行計畫、OPTIMIZE TABLE 整理碎片，三板斧解決 80% 效能問題。

一、SHOW STATUS：一窺服務器健康狀態

SHOW STATUS;

關鍵指標：

二、EXPLAIN：SQL 執行計畫 X 光機

實戰案例：四表聯結查詢優化全過程。

初始狀態：全表掃描災難

EXPLAIN SELECT ... 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;

結果：四表 type=ALL，預估掃描行數 74 × 2135 × 74 × 3872 ≈ 450 億行。

優化步驟 1：修正欄位長度不一致（CHAR(10) vs CHAR(15)）

ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

結果：et 變 eq_ref，行數除以 74。

優化步驟 2：修正其餘關聯欄位長度

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

結果：tt 變 ref，et_1/do 變 eq_ref，總行數大幅下降。

優化步驟 3：分析表統計資訊

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

結果：優化器掌握真實資料分布，選擇最佳驅動表，最終 EXPLAIN 僅剩 tt 掃描 3872 行，其餘皆 eq_ref 1 行，查詢從「跑不完」變「毫秒級」。

EXPLAIN 關鍵欄位速查

三、OPTIMIZE TABLE：整理碎片回收空間

OPTIMIZE TABLE tbl_name;

• 適用：MyISAM、InnoDB、BDB 表。
• 觸發場景：大量 DELETE、UPDATE 變長欄位後，產生碎片、空洞。
• 原理：重建表 → 重排索引 → 更新統計資訊。
• 線上操作：InnoDB 5.6+ 支援 Online DDL，鎖定時間極短。

工具鏈推薦

• pt-query-digest（Percona Toolkit）分析慢查詢日誌。
• MySQLTuner.pl 自動檢查配置與狀態給建議。
• sys schema（5.7+ 內建）標準化效能視圖。