步驟 1：檢查整體負載
mysql> STATUS;
-- 關鍵指標：
-- Slow_queries: 長時間非零說明有慢查詢
-- Questions: 總查詢數
-- Threads: 當前連線數
步驟 2：檢視當前執行緒
mysql> SHOW FULL PROCESSLIST;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 12 | app | 10.0.1.5 | prod | Query | 45 | Sending data | SELECT * FROM big_tbl |
+----+------+-----------+------+---------+------+-------+-----------------------+
Time：已執行秒數，超過 long_query_time 即為慢查詢State：Sending data、Copying to tmp table、Locked 等提示瓶頸Info：完整 SQL 文本（需 FULL 關鍵字）
步驟 3：分析執行計畫
mysql> EXPLAIN SELECT * FROM big_tbl WHERE col = 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | big_tbl | ALL | NULL | NULL | NULL | NULL | 10M | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
type=ALL 確認全表掃描key=NULL 無索引可用rows=10M 掃描量巨大
進階監控工具
| 工具 | 用途 | 安裝 |
|---|
| pt-query-digest | 慢查詢日誌聚合分析 TOP N | percona-toolkit |
| mysqldumpslow | 官方慢查詢摘要 | 內建 |
| innotop | 即時互動式監控 | CPAN |
| Prometheus + mysqld_exporter | 指標採集、Grafana 儀表板 | Docker/二進制 |
自動化殺掉長查詢（慎用）
-- 查詢超過 60 秒的非系統查詢
SELECT CONCAT('KILL ', id, ';') FROM information_schema.PROCESSLIST
WHERE TIME > 60 AND COMMAND = 'Query' AND USER != 'system_user'
INTO OUTFILE '/tmp/kill.sql';
SOURCE /tmp/kill.sql;
預防勝於治療
- 所有新上線 SQL 必經
EXPLAIN 審核
- 設定
long_query_time=1、log_queries_not_using_indexes=ON
- 定期
ANALYZE TABLE 更新統計
- 資料庫變更走審核流程，禁止直接生產 DDL
