| 參數 | 建議值 | 作用 |
|---|
slow_query_log | ON | 啟用慢查詢記錄 |
long_query_time | 1 (秒) | 超過此閾值記錄，建議 1s 甚至 0.5s |
slow_query_log_file | /var/lib/mysql/slow.log | 日誌文件路徑，需 MySQL 進程寫入權限 |
log_queries_not_using_indexes | ON | 記錄未使用索引的查詢 (即使很快) |
配置實操
Windows (my.ini)
[mysqld]
slow_query_log = ON
slow_query_log_file = "F:/MySQL/log/mysqlslowquery.log"
long_query_time = 2
log_queries_not_using_indexes = ON
Linux (my.cnf)
[mysqld]
slow_query_log = ON
slow_query_log_file = /data/mysqldata/slowquery.log
long_query_time = 2
log_queries_not_using_indexes = ON
修改後 systemctl restart mysqld 重啟生效。建議將日誌目錄掛載獨立磁碟，避免佔滿系統盤。
EXPLAIN 執行計畫關鍵欄位深度解讀
EXPLAIN SELECT SUM(moneys) FROM sales a, companys b
WHERE a.company_id = b.id AND a.year = 2006;
| 欄位 | 含義 | 關注重點 | 優化信號 |
|---|
id | 查詢序號 | 相同 id 從上往下執行 | 衍生表/子查詢有獨立 id |
select_type | 查詢類型 | SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION | DERIVED/SUBQUERY 易成效能瓶頸 |
table | 輸出結果集的表 | 別名或衍生表名 | 確認驅動表是否為小表 |
type | 連接類型 (最關鍵) | system > const > eq_ref > ref > range > index > ALL | ALL = 全表掃描，必須優化 |
possible_keys | 可能用到的索引 | 候選索引列表 | 為 NULL 表示無索引可用 |
key | 實際使用的索引 | 最終選中的索引 | 為 NULL 表示未用索引 |
key_len | 索引長度 (位元組) | 判斷組合索引用了幾列 | 過長可能導致索引失效 |
rows | 預估掃描行數 | 越小越好 | 與實際行數差距大 → 統計資訊過期 |
Extra | 額外資訊 | Using index、Using where、Using temporary、Using filesort | Using temporary/filesort = 需優化 |
實戰優化：從 ALL 到 REF
優化前：全表掃描
EXPLAIN SELECT SUM(moneys) FROM sales a, companys b
WHERE a.company_id = b.id AND a.year = 2006;
/*
type: a=ALL (全表掃描 12 行), b=index (全索引掃描)
rows: a=12, b=1
Extra: a=Using where
*/
建立索引
ALTER TABLE sales ADD INDEX idx_year (year);
優化後：索引命中
EXPLAIN SELECT SUM(moneys) FROM sales a, companys b
WHERE a.company_id = b.id AND a.year = 2006;
/*
type: a=ref (索引查找 3 行), b=index
rows: a=3, b=1
Extra: a=Using where
*/
常見 Extra 值對照與對策
| Extra 值 | 含義 | 對策 |
|---|
| Using index | 覆蓋索引，僅讀索引不回表 | ✅ 極佳，保持 |
| Using where | WHERE 條件過濾 (Server 層) | 檢查能否下推到引擎層 |
| Using temporary | 建立臨時表 (GROUP BY/DISTINCT/ORDER BY) | 優化索引順序、避免大臨時表 |
| Using filesort | 外部排序 (無索引支援 ORDER BY) | 建立符合排序的組合索引 |
| Using join buffer | Join Buffer (Block Nested Loop) | 加索引、調大 join_buffer_size |
| Impossible WHERE | WHERE 條件永假 | 檢查業務邏輯 |
進階工具鏈
- pt-query-digest (Percona Toolkit)：慢查詢日誌聚合分析，產出 Top N 慢查詢報告
- mysqldumpslow：官方日誌分析工具，支援排序、過濾
- Performance Schema (5.6+)：
events_statements_summary_by_digest 實時 SQL 統計
- Sys Schema：
sys.schema_table_statistics、sys.user_summary_by_statement_latency 開箱即用視圖
慢查詢日誌是「雷達」，EXPLAIN 是「顯微鏡」。建立「每日審閱慢查詢 → EXPLAIN 定位 → 加索引/改 SQL → 驗證 → 關單」的標準化流程，才能在業務高速迭代中守住資料庫效能底線。
copyright ©
萬盛學電腦網 all rights reserved