萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 效能調優利器:SHOW、EXPLAIN 與 OPTIMIZE 實戰指南

SHOW:洞察伺服器運行全貌

SHOW STATUS 可輸出數百個狀態變數,關鍵指標包括:

  • Aborted_connects:嘗試連線失敗次數,暴露網路/認證問題。
  • Max_used_connections:歷史峰值併發連線,評估 max_connections 設定是否合理。
  • Threads_connected、Threads_running:當前活躍/執行中執行緒數。
  • Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests:計算緩衝池命中率。

其他實用變體:

  • SHOW ENGINE INNODB STATUS:InnoDB 內部引擎狀態(鎖、死鎖、緩衝池、清理執行緒)。
  • SHOW PROCESSLIST / SHOW FULL PROCESSLIST:即時會話列表,鎖定長時間運行查詢。
  • SHOW VARIABLES LIKE 'innodb%':快速檢視參數配置。
  • SHOW TABLE STATUS LIKE 'tbl_name':資料表統計資訊(行數、資料長度、索引長度、碎片率、自增值)。

EXPLAIN:查詢執行計劃深度剖析

EXPLAIN SELECT ... 可揭示最佳化器決策,核心欄位解讀:

欄位含義優化關注點
id查詢序號相同 id 由上至下執行;id 越大優先級越高
select_type查詢類型SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION 等
table存取表關注衍生表、子查詢物化
partitions匹配分區分區裁剪是否生效
type存取類型ALL(全掃)最差 → index → range → ref → eq_ref → const → system → NULL(最優)
possible_keys候選索引理想情況應包含最佳索引
key實際使用索引若為 NULL 則無索引可用
key_len索引長度判斷聯合索引使用了幾列
ref比較對象const、函數、欄位名
rows預估掃描行數越小越好,乘積反映總成本
filtered過濾後保留比例低值暗示索引選擇性差
Extra額外資訊Using index(覆蓋索引)、Using where、Using temporary、Using filesort(需優化)

實戰優化演示:消除全表掃描

初始查詢四表聯結,type 全為 ALL,預估掃描 452 億行。根因:聯結欄位長度不一致(CHAR(10) vs CHAR(15)),導致索引失效。

步驟 1:統一欄位定義

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

再次 EXPLAIN,tt 表轉為 ref 存取,行數乘積減少 74 倍。

步驟 2:修正統計資訊

myisamchk --analyze /path/to/tt
mysqladmin refresh

最終計劃:驅動表 tt 全掃(3872 行),其餘三表皆 eq_ref 唯一索引查找,總成本極低。

OPTIMIZE TABLE:碎片整理與空間回收

  • 適用引擎:MyISAM、InnoDB、ARCHIVE。
  • 觸發場景:大量 DELETE、UPDATE 可變長欄位、TRUNCATE 後。
  • 實際操作:重建表 → 重排索引 → 更新統計資訊。
  • 線上操作:InnoDB 支援 ALGORITHM=INPLACE,僅短暫元資料鎖;MyISAM 需全表鎖。
OPTIMIZE TABLE orders, order_items;

調優心法

  1. 先看 SHOW GLOBAL STATUS 找瓶頸(I/O、鎖、CPU、記憶體)。
  2. 對慢查詢用 EXPLAIN 定位全掃、臨時表、檔案排序。
  3. 補索引、改寫 SQL、調參數、分表分庫、引入快取,層層推進。
  4. 定期 ANALYZE TABLE 更新統計資訊,讓最佳化器決策更精準。
copyright © 萬盛學電腦網 all rights reserved