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