MySQL 效能問題定位實戰手冊：從 OS 層到引擎層的系統化排查流程

排查金字塔：由外而內、由粗到細

1. OS 資源層：CPU、Memory、Disk I/O、Network
2. MySQL 服務層：連接數、QPS、鎖等待、緩存命中
3. InnoDB 引擎層：Buffer Pool、Redo Log、Undo Log、鎖資訊
4. SQL 語句層：執行計畫、索引、統計資訊

第一層：OS 資源快速定位

1. top / htop - 進程資源佔用

top -p $(pgrep -d, mysqld)
# 關鍵欄位:
# %CPU  - 計算密集 (差查詢、壓縮、加密)
# %MEM  - 記憶體壓力
# VIRT/RES/SHR - 虛擬/實體/共享記憶體
# S (D=不可中斷睡眠=I/O 等待, R=運行)

2. nmon / dstat - 系統級綜合視圖

dstat -dcnml  # 磁碟、CPU、網路、記憶體、負載
# 或 nmon 互動模式 (c=CPU, d=Disk, n=Net, m=Mem)

3. I/O 深度分析

iostat -x 2          # 裝置層面: %util, await, svctm, r/s, w/s
iotop -o             # 進程層面 I/O 讀寫 (需 root)
# 基準測試:
dd if=/dev/zero of=/data/test bs=64k count=16k conv=fsync  # 寫入速度
dd if=/data/test of=/dev/null bs=64k                       # 讀取速度

4. 網絡檢查

netstat -i 1          # 介面錯誤/丟包
iftop -P              # 連線級流量 (S=顯示源埠, D=顯示目的埠)

第二層：MySQL 服務層關鍵指標

1. 即時 QPS / 執行緒狀態

mysqladmin -uroot -p ext -ri 1 | grep -i questions
# 或
SHOW GLOBAL STATUS LIKE 'Questions';

2. 鎖等待與死鎖檢測

-- 當前鎖等待鏈 (需 5.7+ / 8.0)
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G

3. 最近死鎖詳情

SHOW ENGINE INNODB STATUS\G
-- 找 "LATEST DETECTED DEADLOCK" 區塊

第三層：InnoDB 引擎深度觀測

SHOW ENGINE INNODB STATUS\G
-- 重點關注:
-- SEMAPHORES: 信號量等待 (spin rounds, OS waits)
-- TRANSACTIONS: 活躍事務、鎖結構、Undo Log 數
-- FILE I/O: 讀寫執行緒、pending I/O
-- INSERT BUFFER AND ADAPTIVE HASH INDEX: 合併效率、AHI 命中
-- LOG: Log sequence number, flush 狀態, checkpoint age
-- BUFFER POOL AND MEMORY: 命中率、free buffers、dirty pages%

第四層：SQL 語句精準優化

1. 找出 Top SQL：mysqlsla / pt-query-digest 分析 Slow Log。
2. EXPLAIN 分析：關注 type (ALL/index/range/ref/eq_ref/const)、key、rows、Extra (Using where/index/temporary/filesort)。
3. 統計資訊：ANALYZE TABLE 更新 Cardinality，誤導優化器的常見原因。
4. 索引設計：最左前綴、覆蓋索引、避免隱式轉換、ORDER BY 消除 filesort。

歷史趨勢與告警：監控系統接入

• Zabbix / Prometheus + Grafana：採集 SHOW GLOBAL STATUS、INFORMATION_SCHEMA、OS 指標。
• 核心告警閾值：
  - Threads_connected > 80% max_connections
  - Innodb_row_lock_waits > 0 (持續增長)
  - Innodb_buffer_pool_wait_free > 0
  - Seconds_Behind_Master > 30s (從庫延遲)
  - Disk space < 20%

排查清單速查表

核心心法

「指標先行、分層驗證、最小變更、持續觀測」

建立基線、配置告警、定期演練故障演練，讓效能問題在影響業務前被發現並解決。