MySQL 性能優化全攻略：SHOW、EXPLAIN、慢查詢、索引優化實戰

內建工具三板斧：SHOW、EXPLAIN、慢查詢日誌

1. SHOW：洞察服務器運行狀態

-- 全局狀態變量（重點關注）
SHOW GLOBAL STATUS;

-- 關鍵指標解讀
-- Aborted_connects      連接嘗試失敗數
-- Connections           總連接嘗試數
-- Max_used_connections  歷史峰值併發連接數
-- Threads_connected     當前打開連接數
-- Threads_running       當前活躍查詢線程數
-- Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests  緩衝池命中率
-- Created_tmp_disk_tables  磁盤臨時表數（應盡量為 0）
-- Select_scan           全表掃描次數
-- Sort_merge_passes     排序溢寫磁盤次數

其他實用 SHOW 變體

2. EXPLAIN：查詢執行計劃解剖刀

經典優化案例：多表連接從 ALL 到 ref

-- 原始查詢
EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, et.COUNTRY, do.CUSTNAME
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;

表結構與索引現狀：

EXPLAIN 初始輸出（全 ALL，笛卡爾積 450 億行）：

table  type  possible_keys      key   key_len  ref   rows    Extra
et     ALL   PRIMARY            NULL  NULL     NULL  74
do     ALL   PRIMARY            NULL  NULL     NULL  2135
et_1   ALL   PRIMARY            NULL  NULL     NULL  74
tt     ALL   AssignedPC,ClientID,ActualPC NULL NULL NULL 3872 range checked...

問題根因：列長度不匹配導致索引失效

• tt.ActualPC CHAR(10) vs et.EMPLOYID CHAR(15) → 隱式轉換，索引不可用。
• tt.ClientID CHAR(10) vs do.CUSTNMBR CHAR(15) → 同理。

優化步驟

1. 修正列定義：ALTER TABLE tt MODIFY ActualPC CHAR(15); 等，或修改被引用表列長度（視業務影響）。
2. 補充聯合索引：ALTER TABLE tt ADD INDEX idx_submit_actual (SubmitTime, ActualPC);
3. 重新 EXPLAIN 驗證：type 變為 ref/eq_ref，rows 驟降。

EXPLAIN 關鍵列速查

3. 慢查詢日誌：發現問題的雷達

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5          -- 秒，生產建議 0.5-1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

分析神器：pt-query-digest

pt-query-digest /var/log/mysql/slow.log --limit 20 --filter '$event->{fingerprint} !~ /^SET/'

# 輸出 Top SQL：執行次數、總時間、平均時間、掃描行數、指紋、樣本 SQL

索引優化實戰清單

1. 覆蓋索引：SELECT a, b FROM t WHERE c = 1 → INDEX idx_c_ab (c, a, b)，Extra: Using index。
2. 最左前綴：INDEX (a, b, c) 支持 a/a,b/a,b,c。
3. 避免函數殺索引：WHERE DATE(created) = '2023-01-01' → WHERE created >= '2023-01-01' AND created < '2023-01-02'。
4. 主鍵選擇：自增整型 > UUID > 業務鍵；InnoDB 聚簇索引順序寫入減少頁分裂。
5. 定期維護：ANALYZE TABLE 更新統計信息；OPTIMIZE TABLE 整理碎片（或 ALTER TABLE ... ENGINE=InnoDB）。

系統參數調優速查（InnoDB 核心）

總結

MySQL 性能優化遵循 「監控 → 發現 → 分析 → 優化 → 驗證」 閉環：

1. 用 SHOW GLOBAL STATUS、慢查詢日誌 發現瓶頸。
2. 用 EXPLAIN 剖析執行計劃，定位 ALL、Using temporary、filesort、索引失效。
3. 從 SQL 改寫、索引調整、表結構優化、參數調優 四個維度入手，優先 SQL 與索引（ROI 最高）。
4. 壓測驗證，觀察 Rows_examined、Query_time、Innodb_buffer_pool_reads 等指標下降。

工欲善其事，必先利其器。熟練掌握 SHOW/EXPLAIN/慢查詢三板斧，是每個 DBA 與後端工程師的必修課。