面試官：數據庫慢查詢激增怎么辦？三步法精準定位+實戰解決
引言
我們的經典問題又來了，關于這個問題大家的想法都是不一樣的，但是有一點我們都是共鳴的，就是都不能完全地把整個流程說明白，那我們今天就來解決這個問題。
開始
一、問題定位：從告警到根因的精準狙擊
1. 快速止血：建立應急響應機制
觸發告警
通過監控平臺（如Prometheus + Grafana）捕獲數據庫QPS突增、CPU使用率超閾值（>80%）、慢查詢數量激增（如MySQL Slow_queries每分鐘超過100次）。
-- 實時監控慢查詢數量
SHOW GLOBAL STATUS LIKE 'Slow_queries';緊急限流
立即限制高危操作的并發量，防止雪崩效應：
-- 動態限制最大連接數（臨時降低至200）
SET GLOBAL max_connections = 200;
-- 使用pt-kill終止耗時超過10秒的查詢
pt-kill --busy-time 10 --kill --victims all --print h=127.0.0.12. 根因分析：工具鏈組合拳
慢日志分析
提取Top 10慢查詢，定位問題SQL：
# 按總耗時排序慢查詢
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log輸出示例：
Count: 200 Time=5.12s (1024s) Lock=0.00s (0s) Rows=100.0 (20000), user@host
SELECT * FROM orders WHERE status='pending' AND create_time > '2023-01-01';執行計劃解讀
使用EXPLAIN分析索引有效性：
EXPLAIN SELECT * FROM orders WHERE status='pending';關鍵指標：
? type: ALL → 全表掃描，需添加索引
? Extra: Using filesort → 排序邏輯需優化
資源瓶頸定位
排查服務器資源是否過載：
top -c # 查看CPU占用最高的進程
iostat -x 1 # 監控磁盤I/O（%util > 90%表示瓶頸）
dstat --tcp # 檢查網絡連接數激增二、問題解決：精準優化與架構升級
1. SQL與索引優化
索引缺失場景
添加復合索引，覆蓋高頻查詢字段：
ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);索引失效案例
? 隱式類型轉換：WHERE user_id = '123'（user_id為INT） → 移除引號
? 索引列運算：WHERE YEAR(create_time) = 2023 → 改寫為范圍查詢
SQL重寫技巧
優化復雜子查詢為JOIN操作：
-- 原語句（耗時5s）
SELECT * FROM orders WHERE status IN (SELECT status FROM config WHERE type='order');
-- 優化為JOIN（耗時0.2s）
SELECT o.* FROM orders o
JOIN config c ON o.status = c.status
WHERE c.type='order';2. 數據庫參數調優
? InnoDB引擎優化
# my.cnf調整示例
innodb_buffer_pool_size = 80G # 物理內存的70%~80%
innodb_flush_log_at_trx_commit = 2 # 平衡性能與持久化? 連接池配置
# 應用端配置（HikariCP）
maximumPoolSize: 100
connectionTimeout: 30003. 架構級解決方案
? 讀寫分離
App → ProxySQL → MySQL Master（寫）
↘ MySQL Replica1（讀）
↘ MySQL Replica2（讀）? 分庫分表
? 垂直拆分：按業務模塊拆分（訂單庫、用戶庫）
? 水平拆分：按時間或ID范圍分片（orders_2023、orders_2024）
三、團隊協作：從故障到預防的閉環
1. 故障復盤模板
階段 | 關鍵動作 | 輸出物 |
應急 | 限流、回滾、擴容 | 故障時間線記錄 |
根因 | SQL分析、資源監控、代碼Review | 根因分析報告 |
改進 | 索引優化、參數調整、架構升級 | 技術方案PRD |
預防 | 慢查詢日報、壓測流程、巡檢自動化 | 巡檢腳本+監控看板 |
2. 長效預防機制
? 慢查詢日報
-- 生成每日慢查詢TOP 10
pt-query-digest /var/log/mysql/slow.log --filter '$event->{arg} =~ m/WHERE/' --limit 10? 自動化巡檢
# 偽代碼：檢查缺失索引
for table in get_all_tables():
if not has_index(table, 'status'):
send_alert(f"表 {table} 缺少status字段索引")四、真實案例：電商大促驚魂夜
背景
某電商平臺大促期間，訂單服務響應延遲從50ms飆升至5s，數據庫CPU達到100%。
處理流程
1. 限流降級：
? 通過Sentinel將訂單查詢QPS從10k降至5k。
? 非核心功能（如用戶畫像）降級返回緩存數據。
2. 根因定位：
? 慢日志分析：SELECT * FROM orders WHERE user_id=‘xxx’ 未命中索引。
? 資源監控：磁盤IOPS達到上限（20k）。
3. 緊急優化：
? 添加user_id索引，響應時間降至20ms。
? 擴容RDS實例并啟用讀寫分離。
4.后續優化
? 架構升級：引入Elasticsearch實現訂單查詢與事務分離。
? 流程固化：將索引審核納入上線前Code Review。
