MySQL 慢查詢根因分析與監控體系建設實戰

常見慢查詢成因

• 缺乏索引：大表全表掃描，百萬級資料秒級延遲。
• 缺少 LIMIT：無節制回傳大量資料，佔用記憶體與網路頻寬。
• Filesort 暴增：ORDER BY 無索引支援導致磁碟排序，I/O 飆升。
• 連線數過多：線上庫頻繁出現 Too many connections，阻塞正常業務。

傳統監控盲區

輕量級節點進程監控方案

核心優勢

1. 即時性：秒級採集，可依業務動態調整閾值。
2. 智慧過濾：自動排除必跑的定時任務、備份查詢等雜訊。
3. 異常即捕：發現無索引、非法查詢即刻介入，避免資料庫掛掉。
4. 連線暴增自動保存 processlist：事後根因分析鐵證。
5. Binlog 精準定位：mysqlbinlog 結合時間窗快速鎖定異常區間。

慢查詢日誌實戰配置

方法一：動態開啟與調整

mysql> SHOW VARIABLES LIKE 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 10.000000|
+-----------------+----------+

mysql> SET long_query_time = 1;          -- 1 秒即記錄
mysql> SHOW VARIABLES LIKE 'slow%';
+---------------------+-----------------+
| Variable_name       | Value           |
+---------------------+-----------------+
| slow_launch_time    | 2               |
| slow_query_log      | ON              |
| slow_query_log_file | /tmp/slow.log   |
+---------------------+-----------------+

mysql> SET GLOBAL slow_query_log = 'ON';  -- 立即生效

持久化請寫入 /etc/my.cnf：

long_query_time = 1
slow_query_log_file = /tmp/slow.log

方法二：mysqldumpslow 快速分析

# 依執行次數排序前 10
/path/mysqldumpslow -s c -t 10 /tmp/slow.log

# 依回傳行數排序前 10
/path/mysqldumpslow -s r -t 10 /tmp/slow.log

# 含 left join 的前 10 慢查詢（依時間排序）
/path/mysqldumpslow -s t -t 10 -g "left join" /tmp/slow.log

參數說明：-s c/t/l/r（次數/時間/查詢時間/回傳行數），ac/at/al/ar 為倒序，-t 為 Top N，-g 正則過濾。

查詢快取關鍵指標

Qcache_queries_in_cache   -- 快取查詢數
Qcache_inserts            -- 新增快取數
Qcache_hits               -- 命中數
Qcache_lowmem_prunes      -- 因記憶體不足被剔除數
Qcache_not_cached         -- 未快取數（不可快取或 QUERY_CACHE_TYPE 關閉）
Qcache_free_memory        -- 快取剩餘記憶體
Qcache_free_blocks        -- 空閒記憶體塊數
Qcache_total_blocks       -- 總塊數

建議：Qcache_lowmem_prunes 持續增長時擴大 query_cache_size；Qcache_hits / Qcache_inserts 比值低則評估快取效益。