MySQL 效能瓶頸排查實戰：從 OS 層到 SQL 層的完整定位與優化案例

MySQL 效能瓶頸排查實戰：從 OS 層到 SQL 層的完整定位與優化案例

場景：線上 MySQL 實例負載告警

第一階段：OS 層面確認瓶頸子系統

# w / sar -q 1  查看負載
load average: 7.20, 6.70, 6.47  -- 24 CPU 下負載偏高

# top 確認佔用 CPU 進程
PID  USER   %CPU  COMMAND
14165 mysql  162%  mysqld (port 3320)
40610 mysql  121%  mysqld
Cpu(s): 7.7%us, 3.4%sy, 11.0%wa  -- us(用戶態) + wa(IO等待) 偏高

# sar -d 1 確認磁碟 IO
dev8-0: tps 5000+, %util 100%  -- 磁碟飽和

# iotop 定位具體實例
TID  DISK READ  COMMAND
16397 8.92 M/s  mysqld --port=3320  <-- 3320 實例 IO 最高

第二階段：MySQL 層面定位慢查詢

mysqladmin pr | grep -v Sleep
Id  Time  State       Info
25  68    Sending data  SELECT max(Fvideoid) FROM (SELECT Fvideoid FROM t WHERE Fvideoid>404612 ORDER BY Fvideoid) t1
26  65    Sending data  ... Fvideoid>484915 ...
28  130   Sending data  ... Fvideoid>404641 ...
...
-- 多條相似慢查詢並發執行

慢查詢日誌分析

Rows_sent: 1
Rows_examined: 5,502,460
-- 掃描 550 萬行只為取一個最大值，極度低效

SQL 改寫優化

原寫法： 子查詢正序排序取最後一行

SELECT max(Fvideoid) FROM (SELECT Fvideoid FROM t WHERE Fvideoid>404612 ORDER BY Fvideoid) t1

優化寫法： 倒序排序取第一行

SELECT Fvideoid FROM t WHERE Fvideoid>404612 ORDER BY Fvideoid DESC LIMIT 1

效果： 從 150-180 秒降至個位數毫秒，提升數量級。

常見瓶頸成因與對策總結

1. 單次請求讀寫量過大 → 拆分批次、減少掃描行
2. 缺適當索引 → 補索引或改寫 SQL
3. 突發大量請求 → 提升配置、限流、熔斷
4. 定時任務/備份佔用資源 → 遷移至專用 Slave
5. CPU 節能降頻 → 關閉節能策略、固定高頻
6. 檔案系統 ext3/ext4 高 IO 下性能弱 → 遷移 XFS
7. I/O Scheduler cfq 不適合資料庫 → 改 deadline 或 noop