萬盛學電腦網

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

SQL 優化工具箱:SHOW STATUS、EXPLAIN、OPTIMIZE TABLE 三大內建利器

MySQL 內建優化三劍客:SHOW STATUS、EXPLAIN、OPTIMIZE TABLE 實戰指南

MySQL 服務器內建三條核心優化指令,涵蓋狀態監控、執行計畫分析、碎片整理。掌握這三工具,即可解決 80% 的效能問題。

1. SHOW STATUS:資料庫健康儀表板

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 引擎核心

  • 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'
);

2. EXPLAIN:執行計畫 X 光機

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

核心輸出欄位解讀

欄位關鍵值含義優化方向
typesystem/const/eq_ref/ref/range/index/ALL連接類型,從好到壞避免 ALL (全表掃描),追求 ref/eq_ref
possible_keys索引名列表可能使用的索引無索引 → 建立索引
key實際使用索引優化器選擇的索引NULL → 無索引可用
key_len位元組數實際使用的索引長度過短可能未用完複合索引
refconst/欄位名與索引比較的值來源
rows預估掃描行數優化器預估與實際差距大 → ANALYZE TABLE
ExtraUsing index/where/temporary/filesort額外資訊Using temporary/filesort → 需優化

進階:EXPLAIN FORMAT=JSON (MySQL 5.6+)

EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 123;
-- 輸出完整成本資訊、查詢塊結構、條件下推細節

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

OPTIMIZE TABLE orders, order_items;

適用場景

  • 大量 DELETE/UPDATE** 導致資料頁碎片、空洞
  • 含 可變長度欄位 (VARCHAR, TEXT, BLOB) 的表
  • 檔案大小明顯大於實際資料量

引擎支援差異

引擎支援實際操作
MyISAM✅ 完整支援重組資料檔、索引檔、統計資料
InnoDB✅ 支援 (1.0.11+)映射為 ALTER TABLE ... ENGINE=InnoDB 重建表
MEMORY/CSV❌ 不支援報錯

線上重建 (Online DDL) - MySQL 5.6+

OPTIMIZE TABLE orders; -- 5.6+ 支援 ALGORITHM=INPLACE, LOCK=NONE
-- 實際執行:ALTER TABLE orders ENGINE=InnoDB ALGORITHM=INPLACE LOCK=NONE;

允許並發 DML,無鎖表停機窗口。

組合拳實戰:從慢查詢到優化閉環

  1. 發現:SHOW STATUS LIKE 'Slow_queries' 或慢查詢日誌
  2. 定位:EXPLAIN 分析執行計畫,找出全表掃描、臨時表、檔案排序
  3. 診斷:SHOW INDEX FROM table 檢查索引、SHOW TABLE STATUS 檢查碎片
  4. 優化:建立索引、改寫查詢、OPTIMIZE TABLE、調整參數
  5. 驗證:再次 EXPLAIN + 實際執行時間對比

總結

MySQL 優化三板斧:
🔍 SHOW STATUS — 看懂資料庫在幹什麼
🔬 EXPLAIN — 看懂查詢怎麼跑
🔧 OPTIMIZE TABLE — 整理碎片釋放空間
善用系統表 sys.dm_exec_* / information_schema 進行自動化監控,讓優化從「救火」變成「預防」。

copyright © 萬盛學電腦網 all rights reserved