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