07 慢查询诊断与性能监控体系

摘要： 生产环境的 MySQL 性能问题往往不是在写代码时发现的，而是在流量突增、数据量增长后才暴露。建立一套完善的监控体系，能让你在问题发生前预警、在问题发生时快速定位。本文构建从”发现问题”到”定位根因”的完整工具链：慢查询日志的配置与分析（pt-query-digest 聚合分析）、Performance Schema 的核心表与使用方法、关键性能指标的采集与告警阈值、以及一个完整的”线上突发慢查询”排查流程。掌握这套方法论，大多数 MySQL 性能问题都能在 10 分钟内完成初步定位。

第 1 章慢查询日志：最基础的性能数据源

1.1 慢查询日志的配置

慢查询日志（Slow Query Log） 是 MySQL 内置的性能记录工具，将执行时间超过阈值的 SQL 记录到日志文件中。

核心配置参数：

-- 动态开启慢查询日志（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
 
-- 设置慢查询阈值（单位：秒，支持小数）
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询记录下来
 
-- 记录未使用索引的查询（即使快于 long_query_time）
SET GLOBAL log_queries_not_using_indexes = ON;
 
-- 记录管理命令（如 ANALYZE TABLE、CHECK TABLE）
SET GLOBAL log_slow_admin_statements = ON;

同步到 my.cnf 确保重启后生效：

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

1.2 慢查询日志的内容格式

# Time: 2026-03-02T14:30:00.123456Z
# User@Host: app_user[app_user] @ 10.0.0.1 []  Id: 1234
# Query_time: 4.567890  Lock_time: 0.000123 Rows_sent: 20  Rows_examined: 8000000
# Bytes_sent: 4096
SET timestamp=1740921000;
SELECT o.order_no, u.name FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.shop_id = 8888 AND o.status IN (1,2) ORDER BY o.created_at DESC LIMIT 20;

关键字段解读：

Query_time：查询总执行时间（秒）
Lock_time：等待锁的时间
Rows_sent：返回给客户端的行数
Rows_examined：存储引擎扫描的行数

Rows_examined / Rows_sent 的比值是衡量查询效率的关键指标。比值越大，说明扫描了大量数据却只返回少量结果——索引可能有问题，或者查询需要优化。比值 = 1 是理想状态（扫描多少返回多少）；比值 > 1000 通常意味着严重的性能问题。

1.3 pt-query-digest：慢查询日志的聚合分析利器

生产环境的慢查询日志可能每天产生几 GB，手动分析是不现实的。pt-query-digest（Percona Toolkit 的一部分）可以将大量慢查询按 SQL 模式分组聚合，输出统计报告。

# 分析最近一天的慢查询日志
pt-query-digest --since=24h /var/log/mysql/slow.log > slow_report.txt
 
# 只分析 Query_time > 5 秒的查询
pt-query-digest --filter='$event->{Query_time} > 5' /var/log/mysql/slow.log

输出报告的核心部分：

# 整体统计
# Overall: 2.50k queries in 3600s, 0.69 QPS, 5x concurrency
# Time range: 2026-03-02 10:00:00 to 2026-03-02 11:00:00
# Attribute     total    min    max    avg     95%  stddev  median
# ===========  ======= ====== ====== ====== ======= ======= =======
# Exec time       18ks    1ms   32s    7s     28s     6s     4s
# Lock time         3s   0    1.2s   1ms    5ms   85ms   84us
# Rows sent        20k     0   1.0k   8.0    20.0     45   1.00
# Rows examined     1B     0   8.0M   400k   2.0M  600k  100k

# 按总执行时间排序的 Top 查询
# Query 1: 8.40 QPS, 5x concurrency, ID 0xABCDEF
# Scores: V/M = 1.23
# Time range: all
# Attribute    pct   total     min    max     avg     95%  stddev  median
# Exec time     78%    14ks    1s     32s    7s     28s   5s      4s
# Rows sent     42%   8.3k     0     1.0k    4.17   20    28     1.00
# Rows examined  95%  950M     0    8.0M   476k   2.0M  600k    90k

SELECT o.order_no, u.name FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.shop_id = ? AND o.status IN (?+) ORDER BY o.created_at DESC LIMIT ?\G

报告中的 SQL 语句中，字面量被替换为 ?，相同模式的 SQL 会被聚合在一起。pct 列显示该查询占总执行时间的百分比——重点优化占比最高的 SQL，ROI 最大。

核心概念

慢查询优化应该遵循帕累托法则（80/20 原则）：通常 20% 的慢 SQL 占据了 80% 的总慢查询时间。pt-query-digest 的价值就在于帮你快速找到这 20%，而不是在大量低影响的慢查询上浪费精力。

第 2 章 Performance Schema：深度性能诊断

2.1 什么是 Performance Schema

Performance Schema（P_S） 是 MySQL 的内置性能诊断框架，通过内存中的”仪表点（Instrumentation Points）“收集 MySQL 内部各种操作的详细统计数据，并通过 performance_schema 数据库中的特殊表暴露出来。

P_S 的核心理念是：在 MySQL 内部的关键位置插入计时探针，记录每个操作的等待时间、调用次数等统计信息，让你看到”MySQL 把时间花在哪里了”。

-- 查看 Performance Schema 是否开启
SHOW VARIABLES LIKE 'performance_schema';
-- 默认 ON（MySQL 5.6.6+）

生产避坑

Performance Schema 的开销在现代 MySQL 版本中已经很低（通常 < 5% 的 CPU 开销），但在 I/O 密集的极端场景下，收集等待事件的开销可能更显著。如果你的系统对延迟极度敏感，可以选择性地只开启必要的 instrument，而不是全部开启。

2.2 最有用的 P_S 查询

2.2.1 找出当前最慢的 SQL（按总执行时间排序）

SELECT 
    digest_text AS sql_pattern,
    count_star AS exec_count,
    ROUND(sum_timer_wait / 1e12, 2) AS total_exec_sec,
    ROUND(avg_timer_wait / 1e12, 4) AS avg_exec_sec,
    ROUND(max_timer_wait / 1e12, 2) AS max_exec_sec,
    sum_rows_examined AS total_rows_examined,
    sum_rows_sent AS total_rows_sent,
    ROUND(sum_rows_examined / sum_rows_sent, 0) AS examine_ratio
FROM performance_schema.events_statements_summary_by_digest
WHERE digest_text IS NOT NULL
ORDER BY sum_timer_wait DESC
LIMIT 10;

2.2.2 分析当前正在运行的 SQL 的等待事件

-- 查看当前活跃连接正在等待什么
SELECT 
    t.THREAD_ID,
    t.PROCESSLIST_ID,
    t.PROCESSLIST_USER,
    t.PROCESSLIST_HOST,
    t.PROCESSLIST_DB,
    t.PROCESSLIST_COMMAND,
    t.PROCESSLIST_TIME AS time_sec,
    t.PROCESSLIST_STATE,
    w.EVENT_NAME AS waiting_for,
    s.SQL_TEXT
FROM performance_schema.threads t
LEFT JOIN performance_schema.events_waits_current w USING (THREAD_ID)
LEFT JOIN performance_schema.events_statements_current s USING (THREAD_ID)
WHERE t.PROCESSLIST_COMMAND != 'Sleep'
ORDER BY t.PROCESSLIST_TIME DESC;

2.2.3 找出 I/O 热点表

-- 按总 I/O 等待时间排序的表
SELECT 
    object_schema AS db,
    object_name AS tbl,
    count_read,
    ROUND(sum_timer_read / 1e12, 2) AS read_sec,
    count_write,
    ROUND(sum_timer_write / 1e12, 2) AS write_sec,
    count_fetch,
    count_insert,
    count_update,
    count_delete
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
ORDER BY (sum_timer_read + sum_timer_write) DESC
LIMIT 10;

2.2.4 查找锁等待热点

-- 当前的锁等待关系
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 lw
JOIN information_schema.INNODB_TRX r ON lw.requesting_trx_id = r.trx_id
JOIN information_schema.INNODB_TRX b ON lw.blocking_trx_id = b.trx_id;

2.3 sys Schema：P_S 的便民封装

MySQL 5.7.7+ 内置了 sys Schema，它对 Performance Schema 的数据做了易读的封装视图：

-- 最耗时的 SQL（总时间排序）
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
 
-- 当前有哪些锁等待
SELECT * FROM sys.innodb_lock_waits;
 
-- 哪些用户/主机产生了最多的查询流量
SELECT * FROM sys.user_summary ORDER BY total_latency DESC LIMIT 10;
 
-- 哪些表有最多的全表扫描
SELECT * FROM sys.schema_tables_with_full_table_scans ORDER BY rows_full_scanned DESC LIMIT 10;

sys Schema 是快速诊断 MySQL 性能问题的最便捷入口，推荐作为日常巡检的首选工具。

第 3 章关键性能指标与告警阈值

3.1 必须监控的核心指标

指标	获取方式	健康阈值	说明
QPS（每秒查询数）	`Questions` 状态变量的增量	根据业务定基线	突然下降可能意味着异常
TPS（每秒事务数）	`Com_commit + Com_rollback` 增量	根据业务定基线
慢查询数量	`Slow_queries` 状态变量增量	0 为目标，> 10/min 告警
Buffer Pool 命中率	`1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests`	> 99%	< 95% 需要扩大 buffer pool
Buffer Pool 脏页比例	`Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total`	< 75%	过高说明刷脏跟不上
Undo History Length	`SHOW ENGINE INNODB STATUS` 中 History list length	< 10万	过高说明有长事务
连接数	`Threads_connected`	< `max_connections` 的 80%	接近上限时告警
等待连接数	`Threads_waiting`	接近 0	> 0 说明连接池已满
Deadlock 数量	`SHOW ENGINE INNODB STATUS` 中记录	0，偶发告警	频繁死锁需要代码排查
Replication Lag	`SHOW SLAVE STATUS` 中 `Seconds_Behind_Master`	< 10s	> 30s 告警，影响读写分离

3.2 关键状态变量的采集 SQL

-- 一次性采集核心指标快照
SELECT 
    variable_name,
    variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
    'Questions',
    'Com_select',
    'Com_insert',
    'Com_update',
    'Com_delete',
    'Com_commit',
    'Com_rollback',
    'Slow_queries',
    'Innodb_buffer_pool_reads',
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_pages_dirty',
    'Innodb_buffer_pool_pages_total',
    'Innodb_row_lock_waits',
    'Innodb_row_lock_time_avg',
    'Threads_connected',
    'Threads_running',
    'Aborted_connects'
);

3.3 监控告警的层级设计

一个合理的 MySQL 监控告警体系应该分为三个级别：

P0（紧急告警，立即响应）：

实例不可用（连接失败）
主从切换发生
Binlog 停止写入
磁盘使用率 > 90%

P1（高优先告警，1 小时内响应）：

复制延迟 > 30 秒
Buffer Pool 命中率 < 95%
Threads_running 持续 > 50
磁盘 I/O 利用率持续 > 80%
慢查询数量 > 100/min

P2（一般告警，当天响应）：

慢查询数量 > 10/min
连接数 > max_connections 的 70%
History list length > 10 万
出现死锁

第 4 章线上突发慢查询的排查流程

4.1 第一步：确认是否真的是 MySQL 的问题

# 检查 MySQL 进程的 CPU 和 IO 情况
top -p $(pgrep mysqld)
iostat -x 1 5
 
# 检查系统负载
uptime

如果 CPU 很低、I/O 很低，但查询很慢——问题可能不在 MySQL，而在网络（应用到 MySQL 的网络延迟）或应用层（连接池等待、序列化/反序列化）。

4.2 第二步：快速定位慢 SQL

-- 查看当前正在执行的查询（按执行时间倒序）
SHOW PROCESSLIST;
-- 或更详细的版本
SELECT 
    id, user, host, db, command, time, state,
    SUBSTR(info, 1, 100) AS query_preview
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC
LIMIT 20;

找到长时间运行的查询后，立刻对它执行 EXPLAIN 分析执行计划（参考第 02 篇文章的方法论）。

4.3 第三步：判断是个别 SQL 还是系统性问题

个别 SQL 问题的特征：

慢查询日志中某一条 SQL 的执行时间异常高
EXPLAIN 显示全表扫描或索引选错
该 SQL 最近没有代码变更，但数据量增长导致执行计划变化

系统性问题的特征：

大量不同的 SQL 都变慢了
SHOW PROCESSLIST 显示大量等待锁的线程
Threads_running 突然飙高
服务器 CPU 或 I/O 打满

4.4 第四步：针对性处置

处置：个别 SQL 慢

-- 1. 检查执行计划
EXPLAIN FORMAT=JSON SELECT ...;
 
-- 2. 如果是索引缺失，临时可以尝试强制索引
SELECT * FROM orders USE INDEX (idx_shop_status_time) WHERE ...;
 
-- 3. 更新统计信息（可能执行计划基于过时的统计）
ANALYZE TABLE orders;

处置：锁等待/死锁

-- 找出阻塞源头
SELECT * FROM sys.innodb_lock_waits;
 
-- 如果有长时间阻塞的事务，评估后 KILL
KILL <thread_id>;

处置：系统负载过高（写入洪峰）

检查是否有批量操作（如报表任务、数据迁移）打满了磁盘 I/O
将批量任务降速或停止
检查是否可以快速切流量到从库（读流量）
评估是否需要紧急扩容

第 5 章小结

本文构建的诊断工具链：

慢查询日志是最基础的数据源。long_query_time 设合理阈值（生产建议 1s），log_queries_not_using_indexes 帮助发现隐患
pt-query-digest 聚合分析日志，找到占总时间比例最高的 20% SQL，是优化 ROI 最高的目标
Performance Schema 提供内部操作的纳秒级统计，events_statements_summary_by_digest 是最常用的慢 SQL 发现表，sys Schema 提供便民封装视图
核心监控指标：Buffer Pool 命中率（> 99%）、History list length（< 10 万）、复制延迟（< 10s）、慢查询数量、连接数，是 MySQL 健康的晴雨表
线上排查四步法：确认瓶颈层 → 定位慢 SQL → 判断个别 vs 系统性 → 针对性处置（加索引/强制索引/更新统计信息/KILL 长事务/降速批量任务）

思考题

MySQL 最关键的监控指标包括：QPS/TPS、慢查询数量、连接数、Buffer Pool 命中率、InnoDB 行锁等待时间、复制延迟。在这些指标中，哪些应该设置告警？告警阈值如何确定（如 Buffer Pool 命中率 < 99% 告警）？

performance_schema 提供了 MySQL 内部的详细性能数据——如等待事件（events_waits_summary_*）、SQL 统计（events_statements_summary_*）。sys schema 是 performance_schema 的可读性封装——如 sys.statement_analysis 显示最耗时的 SQL。performance_schema 的开启对 MySQL 性能有多大影响？在生产环境中是否应该默认开启？

SHOW GLOBAL STATUS 中的 Threads_running（正在执行查询的线程数）持续高于 CPU 核数意味着什么？如果 Threads_running 突然从 10 跳到 200，可能的原因有哪些（如慢查询、锁等待、连接数暴增）？你如何快速定位原因？

汀的知识碎片

文章导航

07 慢查询诊断与性能监控体系

07 慢查询诊断与性能监控体系

第 1 章慢查询日志：最基础的性能数据源

1.1 慢查询日志的配置

1.2 慢查询日志的内容格式

1.3 pt-query-digest：慢查询日志的聚合分析利器

第 2 章 Performance Schema：深度性能诊断

2.1 什么是 Performance Schema

2.2 最有用的 P_S 查询

2.2.1 找出当前最慢的 SQL（按总执行时间排序）

2.2.2 分析当前正在运行的 SQL 的等待事件

2.2.3 找出 I/O 热点表

2.2.4 查找锁等待热点

2.3 sys Schema：P_S 的便民封装

第 3 章关键性能指标与告警阈值

3.1 必须监控的核心指标

3.2 关键状态变量的采集 SQL

3.3 监控告警的层级设计

第 4 章线上突发慢查询的排查流程

4.1 第一步：确认是否真的是 MySQL 的问题

4.2 第二步：快速定位慢 SQL

4.3 第三步：判断是个别 SQL 还是系统性问题

4.4 第四步：针对性处置

第 5 章小结

On this page

汀的知识碎片

文章导航

07 慢查询诊断与性能监控体系

07 慢查询诊断与性能监控体系

第 1 章 慢查询日志：最基础的性能数据源

1.1 慢查询日志的配置

1.2 慢查询日志的内容格式

1.3 pt-query-digest：慢查询日志的聚合分析利器

第 2 章 Performance Schema：深度性能诊断

2.1 什么是 Performance Schema

2.2 最有用的 P_S 查询

2.2.1 找出当前最慢的 SQL（按总执行时间排序）

2.2.2 分析当前正在运行的 SQL 的等待事件

2.2.3 找出 I/O 热点表

2.2.4 查找锁等待热点

2.3 sys Schema：P_S 的便民封装

第 3 章 关键性能指标与告警阈值

3.1 必须监控的核心指标

3.2 关键状态变量的采集 SQL

3.3 监控告警的层级设计

第 4 章 线上突发慢查询的排查流程

4.1 第一步：确认是否真的是 MySQL 的问题

4.2 第二步：快速定位慢 SQL

4.3 第三步：判断是个别 SQL 还是系统性问题

4.4 第四步：针对性处置

第 5 章 小结

On this page

第 1 章慢查询日志：最基础的性能数据源

第 3 章关键性能指标与告警阈值

第 4 章线上突发慢查询的排查流程

第 5 章小结