MySQL 性能優化第一步：定位問題的核心指標與慢查詢配置

MySQL 優化從「盲目調參」到「數據驅動定位」：核心指標體系與慢查詢落地

很多開發者拿到慢 SQL 就加索引、調緩衝池，卻不知系統真正瓶頸在哪。優化第一步是**建立可觀測指標體系**，精準定位「是查詢慢、鎖爭用、IO 瓶頸、還是配置不當」。本文梳理關鍵狀態變量與慢查詢配置標準流程。

一、實時健康儀表盤：SHOW GLOBAL STATUS 核心指標

執行 SHOW GLOBAL STATUS（或 SHOW STATUS 當前會話），重點關注以下累計計數器：

二、計算核心速率（每秒值）

-- 手動計算 QPS/TPS
SELECT 
    VARIABLE_VALUE INTO @qps FROM performance_schema.global_status WHERE VARIABLE_NAME='Questions';
SELECT SLEEP(10);
SELECT 
    (VARIABLE_VALUE - @qps) / 10 AS qps_10s
FROM performance_schema.global_status WHERE VARIABLE_NAME='Questions';

-- TPS = (Com_commit + Com_rollback) / 秒
-- 若用 XA 事務，加上 Com_xa_commit + Com_xa_rollback

生產環境建議接入 **Prometheus + Grafana** 或 **Datadog** 自動采集繪圖。

三、慢查詢日誌：定位具體 SQL 的「金標準」

1. 開啟慢查詢（動態生效，無需重啟）

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;           -- 超過 1 秒記錄（微秒精度可用 0.05）
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 記錄未走索引的查詢

持久化寫入 my.cnf：

[mysqld]
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

2. 分析工具鏈

• mysqldumpslow：官方自帶，快速聚合 Top N。
  mysqldumpslow -s t -t 20 /var/log/mysql/slow.log（按時間排序顯示前 20）
• pt-query-digest（Percona Toolkit）：行業標準，多維度報告。
  pt-query-digest /var/log/mysql/slow.log --limit 20 --filter '$event->{bytes} > 100000'
• MySQL 5.7+ sys schema：SELECT * FROM sys.schema_table_statistics_with_buffer ORDER BY io_read_requests DESC LIMIT 10;

3. 慢查詢日誌關鍵字段解讀

# Time: 2024-01-15T10:30:00.123456Z
# User@Host: app[app] @ 10.0.1.5
# Query_time: 2.345  Lock_time: 0.001  Rows_sent: 10  Rows_examined: 1000000
SET timestamp=1705314600;
SELECT * FROM orders WHERE user_id=123 AND status=1 ORDER BY created_at DESC LIMIT 10;

• Query_time：總耗時（含鎖等待、IO、CPU）。
• Lock_time：等待鎖時間，大於 0.1s 提示鎖爭用。
• Rows_examined：掃描行數，**遠大於 Rows_sent 表明全表掃描或索引失效**。

四、定位問題的三步走策略

1. 宏觀：Grafana 看 QPS/TPS/慢查詢數/鎖等待/緩衝池命中率 趨勢圖，判斷瓶頸類型（CPU/IO/鎖/網絡）。
2. 中觀：pt-query-digest 產出 Top 10 慢 SQL，按 Query_time * Exec_count 總耗時排序，鎖定「高頻慢」與「低頻極慢」。
3. 微觀：對目標 SQL 執行 EXPLAIN、EXPLAIN ANALYZE（8.0.18+）、OPTIMIZER_TRACE，確認訪問類型、索引使用、掃描行數、臨時表/文件排序。

五、常見誤區

總結清單

1. 建立 SHOW GLOBAL STATUS 核心指標采集（每 10~30 秒）。
2. 開啟慢查詢日誌 long_query_time=1 + log_queries_not_using_indexes。
3. 定期跑 pt-query-digest 產出優化清單。
4. 每條優化 SQL 必須經過 EXPLAIN 驗證並回歸測試。

1. 上一篇文章： MySQL 分表場景下批量添加字段：存儲過程游標動態執行方案
2. 下一篇文章： 資料庫結構重構實戰：從多索引到利用主鍵索引的極致優化

MySQL 綜合教學

前端工程師必學：MySQL 記錄操作全攻略（增刪改查）

前端工程師必學：MySQL 記錄操作全攻略（增刪改查） 作為

MySQL 複寫原理深度解析：三階段流程、線程模型與序列化限制

MySQL 複寫原理深度解析：三階段流程、線程模型與序列化限

MySQL 5.7.17 Windows x64 免安裝版完整安裝配置教程

前言 MySQL 5.7.17 Windows x64 免安

MySQL 安全加固全維度指南：權限、網絡、審計、備份與合規

MySQL 安全加固全維度指南：權限、網絡、審計、備份與合規