您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 效能優化實戰：從硬體基準測試到 SQL 級問題定位的四步法

編輯：MySQL 綜合教學

MySQL 效能優化實戰：從硬體基準測試到 SQL 級問題定位的四步法

面對資料庫效能問題，切忌上來就調 SQL 或加索引。資深專家燕師傅教學的標準化四步定位法：硬體基準 → 宏觀狀態監控 → 進程狀態分析 → SQL 剖析優化。本文完整複現實戰流程與腳本。

步驟一：硬體基準測試 (sysbench) — 確認物理上限

若硬體 IOPS 僅 100MB/s，軟體優化再強也無法支撐 200MB/s 需求。

# 1. CPU 測試 (素數運算)
sysbench --test=cpu --cpu-max-prime=2000000 run

# 2. IO 測試 (隨機讀寫 3GB)
sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw prepare
sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw run
sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw cleanup

# 3. OLTP 測試 (模擬交易負載)
sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 \
  --mysql-socket=/tmp/mysql.sock --mysql-user=test --mysql-host=localhost --mysql-password=test prepare
sysbench --test=oltp ... run

步驟二：宏觀狀態監控 — 抓取關鍵指標時間序列

核心三指標：Queries (累計查詢數)、Threads_connected (當前連接數)、Threads_running (活躍執行緒數)。

# 監控腳本 (每秒採樣)
#!/bin/bash
while true; do
  mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Queries','Threads_connected','Threads_running');" \
  | awk 'NR>1 {print $2}' | xargs | sed 's/ /,/g' >> mysql_status.csv
  sleep 1
done

壓測工具 ab 模擬併發：

ab -c 50 -n 2000 https://target/page.php

結果分析兩大模式

模式	QPS 曲線	優化方向
平穩型	穩定水平線	SQL/索引/配置/引擎/架構優化
週期性尖峰	鋸齒狀/定時暴增	緩存失效策略 (隨機 TTL/錯峰)、業務分批 (分省/分時段)、尖峰期開啟慢查詢日誌

步驟三：進程狀態分析 — 找出 MySQL 花時間在幹什麼

# 採樣腳本
while true; do
  mysql -e "SHOW FULL PROCESSLIST\G" | grep -E '^ *State:' | sort | uniq -c >> mysql_process.csv
  sleep 1
done

重點關注的 State 狀態

State	含義	優化方向
Creating tmp table	建立臨時表 (如 ORDER BY/GROUP BY/DISTINCT)	優化索引覆蓋、避免不必要排序
Sending data	傳輸結果集	減少 SELECT *、限制 LIMIT、分頁優化
Sorting for group	分組排序	複合索引支援排序
Copying to tmp table on disk	內存臨時表溢寫磁碟	增加 tmp_table_size、優化 JOIN、避免大結果集
Locked	等待鎖	縮短交易、降低隔離級別、避免長交易
Converting HEAP to MyISAM	記憶體表轉磁碟表	同 Copying to tmp table

步驟四：SQL 級剖析 — 定位具體語句瓶頸

# 開啟 Profile
SET profiling = 1;

-- 執行目標 SQL
SELECT * FROM t1 JOIN t2 JOIN t3 ORDER BY title LIMIT 10;

# 查看 Profile 記錄
SHOW PROFILES;
-- 找到 Query_ID (如 27)
SHOW PROFILE FOR QUERY 27;