MySQL 服務器內建三條核心優化指令,涵蓋狀態監控、執行計畫分析、碎片整理。掌握這三工具,即可解決 80% 的效能問題。
SHOW STATUS;
返回 300+ 狀態變數,關鍵指標分類:
Threads_connected:目前連線數Threads_running:活躍查詢數 (非 Sleep)Max_used_connections:歷史峰值連線數Aborted_connects:失敗連線嘗試Connection_errors_max_connections:達 max_connections 拒絕次數Questions:總查詢數Slow_queries:慢查詢數 (超過 long_query_time)Qcache_hits / Qcache_inserts / Qcache_lowmem_prunes:查詢快取命中/插入/因記憶體不足淘汰Select_scan:全表掃描次數 (越低越好)Sort_scan / Sort_range:排序操作類型統計Innodb_buffer_pool_read_requests / _reads:邏輯讀/物理讀 → 計算命中率Innodb_rows_read / _inserted / _updated / _deleted:行級操作計數Innodb_buffer_pool_wait_free:等待空閒頁面次數 (應為 0)Innodb_log_waits:等待記錄檔刷新次數SHOW GLOBAL STATUS WHERE Variable_name IN (
'Threads_connected','Threads_running','Max_used_connections',
'Aborted_connects','Slow_queries','Questions',
'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',
'Innodb_buffer_pool_wait_free','Innodb_log_waits',
'Qcache_hits','Qcache_lowmem_prunes','Select_scan'
);
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';
| 欄位 | 關鍵值 | 含義 | 優化方向 |
|---|---|---|---|
| type | system/const/eq_ref/ref/range/index/ALL | 連接類型,從好到壞 | 避免 ALL (全表掃描),追求 ref/eq_ref |
| possible_keys | 索引名列表 | 可能使用的索引 | 無索引 → 建立索引 |
| key | 實際使用索引 | 優化器選擇的索引 | NULL → 無索引可用 |
| key_len | 位元組數 | 實際使用的索引長度 | 過短可能未用完複合索引 |
| ref | const/欄位名 | 與索引比較的值來源 | |
| rows | 預估掃描行數 | 優化器預估 | 與實際差距大 → ANALYZE TABLE |
| Extra | Using index/where/temporary/filesort | 額外資訊 | Using temporary/filesort → 需優化 |
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 123;
-- 輸出完整成本資訊、查詢塊結構、條件下推細節
OPTIMIZE TABLE orders, order_items;
| 引擎 | 支援 | 實際操作 |
|---|---|---|
| MyISAM | ✅ 完整支援 | 重組資料檔、索引檔、統計資料 |
| InnoDB | ✅ 支援 (1.0.11+) | 映射為 ALTER TABLE ... ENGINE=InnoDB 重建表 |
| MEMORY/CSV | ❌ 不支援 | 報錯 |
OPTIMIZE TABLE orders; -- 5.6+ 支援 ALGORITHM=INPLACE, LOCK=NONE
-- 實際執行:ALTER TABLE orders ENGINE=InnoDB ALGORITHM=INPLACE LOCK=NONE;
允許並發 DML,無鎖表停機窗口。
SHOW STATUS LIKE 'Slow_queries' 或慢查詢日誌EXPLAIN 分析執行計畫,找出全表掃描、臨時表、檔案排序SHOW INDEX FROM table 檢查索引、SHOW TABLE STATUS 檢查碎片OPTIMIZE TABLE、調整參數EXPLAIN + 實際執行時間對比MySQL 優化三板斧:
🔍 SHOW STATUS — 看懂資料庫在幹什麼
🔬 EXPLAIN — 看懂查詢怎麼跑
🔧 OPTIMIZE TABLE — 整理碎片釋放空間
善用系統表 sys.dm_exec_* / information_schema 進行自動化監控,讓優化從「救火」變成「預防」。