萬盛學電腦網

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

巧用 MySQL Query Cache 緊急緩解慢查詢導致的連接池耗盡

線上系統突發大量「獲取連接超時」與「超過 max_connections」報警。排查發現某慢 SQL 佔用連接池不釋放,導致後續請求排隊報錯。由於業務修改需走發布流程,無法立即生效,需資料庫層面快速止血。本文記錄一次利用 Query Cache 臨時繞過全表掃描、秒級恢復服務的實戰案例。

故障現場

-- 應用日誌
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
Could not create connection to database server. Attempted reconnect 3 times. Giving up.

-- 數據庫慢查詢
mysql> select * from user where md5(nick)='3f5950f59ddf2a0d14a44166040e348f';
Empty set (1.32 sec)

-- 執行計劃
mysql> explain select * from user where md5(nick)='3f5950f59ddf2a0d14a44166040e348f';
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 256608 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+

根因:WHERE md5(nick)=... 導致索引失效,25 萬行全表掃描,單次 1.32s。高併發下連接池瞬間被佔滿。

緊急止血:開啟 Query Cache

確認場景:讀多寫極少(用戶資料表,寫入頻率極低),符合 QC 適用條件。

mysql> show variables like '%query%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| query_cache_size       | 0        |
| query_cache_type       | ON       |
+------------------------+----------+

mysql> set global query_cache_size=1024*1024*32;  -- 32MB
Query OK, 0 rows affected (0.02 sec)

驗證效果

mysql> select * from user where md5(nick)='3f5950f59ddf2a0d14a44166040e348f';
Empty set (1.32 sec)  -- 首次執行,建立緩存

mysql> select * from user where md5(nick)='3f5950f59ddf2a0d14a44166040e348f';
Empty set (0.00 sec)  -- 命中緩存,毫秒級返回

應用層連接池積壓瞬間消失,服務恢復正常。

風險提示與後續動作

⚠️ Query Cache 是雙刃劍:
• 寫入頻繁的表開啟 QC 會導致全系統 Waiting for query cache lock,引發雪崩(見下圖故障案例)。
• 本案例屬「讀多寫少」特例,僅作緊急繞過方案。
• 根治方案:業務側移除 md5() 函數,或增加 nick_md5 列+索引,走覆蓋索引查詢。

QC 導致的鎖等待故障圖

總結

  • QC 可作為「只讀/低寫」表的臨時止血手段,但嚴禁在寫入頻繁的表/實例開啟。
  • 長期治理:SQL 改寫、索引優化、應用層緩存(Redis)、讀寫分離分流。
  • 監控補齊:慢查詢告警、連接池使用率告警、QC 命中率/鎖等待監控。
copyright © 萬盛學電腦網 all rights reserved