MySQL 效能瓶頸排查實戰：從系統層面到 SQL 優化的完整定位指南

前言

線上 MySQL 實例負載飆升，告警郵件接踵而至。面對這類突發狀況，如何快速定位瓶頸並精準施策？本文將以一個真實生產案例為主軸，完整復盤從作業系統層面切入，層層深入至 MySQL 內部，最終鎖定並解決問題的全過程，為你提供一套可落地的排查方法論。

一、作業系統層面：確認瓶頸來源

1.1 檢查系統整體負載

登入服務器後，首要任務是搞清楚：究竟是哪個進程拖垮了系統？瓶頸點在 CPU、記憶體、磁碟 I/O 還是網路？

執行 w 或 sar -q 1 觀察負載數據：

[yejr@imysql.com:~ ]# w
11:52:58 up 702 days, 56 min, 1 user, load average: 7.20, 6.70, 6.47
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
root pts/0 1.xx.xx.xx 11:51 0.00s 0.03s 0.00s w

或使用 sar -q 持續觀察：

[yejr@imysql.com:~ ]# sar -q 1
Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com) 01/13/2016 _x86_64_ (24 CPU)
02:51:18 PM runq-sz plist-sz ldavg-1 ldavg-5 ldavg-15 blocked
02:51:19 PM 4 2305 6.41 6.98 7.12 3
02:51:20 PM 2 2301 6.41 6.98 7.12 4
...

關鍵判斷： 跑資料庫的伺服器，load average 長期超過 5 即屬高負載，代表 CPU 任務佇列嚴重堆積。

1.2 定位高負載進程與資源消耗類型

執行 top 觀察進程佔用：

top - 11:53:04 up 702 days, 56 min, 1 user, load average: 7.18, 6.70, 6.47
Tasks: 576 total, 1 running, 575 sleeping, 0 stopped, 0 zombie
Cpu(s): 7.7%us, 3.4%sy, 0.0%ni, 77.6%id, 11.0%wa, 0.0%hi, 0.3%si, 0.0%st
Mem: 49374024k total, 32018844k used, 17355180k free, 115416k buffers
Swap: 16777208k total, 117612k used, 16659596k free, 5689020k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14165 mysql 20 0 8822m 3.1g 4672 S 162.3 6.6 89839:59 mysqld
40610 mysql 20 0 25.6g 14g 8336 S 121.7 31.5 282809:08 mysqld
49023 mysql 20 0 16.9g 5.1g 4772 S 4.6 10.8 34940:09 mysqld

分析結論：

• 前兩個 mysqld 進程佔用極高 CPU（%us 高）
• %wa (11.0%) 偏高，暗示存在磁碟 I/O 等待瓶頸
• 無 Swap 橫向，記憶體充足

1.3 深入磁碟 I/O 子系統分析

使用 sar -d 確認磁碟吞吐：

[yejr@imysql.com:~ ]# sar -d 1
11:54:32 AM dev8-0 5338.00 162784.00 1394.00 30.76 5.24 0.98 0.19 100.00
11:54:33 AM dev8-0 5134.00 148032.00 32365.00 35.14 6.93 1.34 0.19 100.10
...

%util 長期 100%，證實磁碟 I/O 已飽和。再用 iotop 定位具體進程：

Total DISK READ: 60.38 M/s | Total DISK WRITE: 640.34 K/s
TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
16397 be/4 mysql 8.92 M/s 0.00 B/s 0.00 % 94.77 % mysqld --port=3320
7295 be/4 mysql 10.98 M/s 0.00 B/s 0.00 % 93.59 % mysqld --port=3320
...

鎖定：3320 端口實例 為主要 I/O 消費者。

二、MySQL 層面：鎖定問題 SQL

2.1 查看正在執行的查詢

mysqladmin pr | grep -v Sleep
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| Id |User| Host | db |Command|Time | State | Info |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| 25 | x | 10.x:8519 | db | Query | 68 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404612 order by Fvideoid) t1 |
| 26 | x | 10.x:8520 | db | Query | 65 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>484915 order by Fvideoid) t1 |
| 28 | x | 10.x:8522 | db | Query | 130 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404641 order by Fvideoid) t1 |
...

多條查詢長時間處於 Sending data 狀態，慢查詢日誌顯示此類 SQL 高頻出現。

2.2 SQL 寫法缺陷分析

慢查詢日誌關鍵指標：

Rows_sent: 1   Rows_examined: 5,502,460

每次掃描 550 萬行只為取一個最大值，極度低效。原 SQL 邏輯：子查詢正序排序後，外層取 max()，導致全表掃描。

2.3 優化改寫：毫秒級提升

優化思路： 利用索引有序性，倒序取第一條即可。

-- 優化前：需全表掃描 + 臨時表 + filesort
select max(Fvideoid) from (select Fvideoid from t where Fvideoid > 404612 order by Fvideoid) t1;

-- 優化後：索引倒序掃描，Limit 1 直接返回
select Fvideoid from t where Fvideoid > 404612 order by Fvideoid desc limit 1;

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

三、總結與避坑指南

本案例瓶頸定位較清晰，但實戰中常見高負載成因更複雜：

核心排查口訣

1. OS 先行： w/top/sar/iotop 三板斧，鎖定瓶頸子系統（CPU/IO/MEM/NET）
2. 進程歸因： 定位佔用資源最高的 MySQL 實例與執行緒
3. SQL 畫像： show processlist + 慢查詢日誌，找出高頻慢查詢
4. 執行計劃： EXPLAIN 分析索引使用、掃描行數、臨時表、filesort
5. 優化驗證： 改寫 SQL、加索引、調參數，對比 SHOW PROFILE 或實際耗時

掌握這套「自上而下、分層定位」的方法論，面對 MySQL 性能疑難雜症，便能從容應對、精準出擊。