当MySQL变慢时如何用监控工具快速定位原因

凌晨两点，你的手机突然响了。生产环境的MySQL响应时间从平时的50毫秒飙升到了5秒，用户投诉如潮水般涌来。你打开电脑，深吸一口气——别慌，这种情况我遇到过太多次了，今天我们就来聊聊如何用监控工具快速定位MySQL变慢的原因，让你从手忙脚乱变成胸有成竹。

先搞清楚慢在哪一层

MySQL变慢，就像人看病一样，得先搞清楚症状出在哪里。是数据库本身的问题，还是操作系统的问题，又或者是网络的问题？我们一层一层剥开来看。

系统层面的”体检报告”

首先要做的，是看看操作系统层面的指标是否正常。MySQL再厉害，也得在Linux系统上跑，系统资源不够，数据库肯定难受。

# 1. 检查CPU使用情况
top -bn1 | head -20

# 看这几个关键指标：
# - %us（用户空间CPU占用）
# - %sy（系统空间CPU占用）  
# - %wa（IO等待时间占比）
# - %si（硬件中断）
# 如果%wa超过30%，说明IO等待严重，CPU在等磁盘数据

# 2. 检查内存使用情况
free -h

# 关注available内存，如果接近0，系统可能在频繁swap
# 一旦swap，MySQL性能会断崖式下跌

# 3. 检查磁盘IO
iostat -x 1 5

# 重点看这几个指标：
# - %util（磁盘利用率，接近100%说明磁盘饱和）
# - await（平均等待时间，超过10ms就要警惕）
# - r_await/w_await（读/写平均等待时间）

我见过太多DBA一上来就查SQL，结果最后发现是磁盘IO被别的进程打满的。有一次客户那里，一个定时备份任务把磁盘IO拉满了，MySQL慢得不行，查了半天SQL才发现是系统层面的锅。

网络层面的”交通状况”

网络问题有时候也很隐蔽。你可以用这些命令来检查：

# 检查网络连接数
netstat -an | grep :3306 | wc -l

# 检查连接状态分布
netstat -an | grep :3306 | awk '{print $6}' | sort | uniq -c

# 如果出现大量TIME_WAIT或CLOSE_WAIT，说明网络层有问题
# 正常应该是大量ESTABLISHED

# 检查网络延迟
ping <数据库服务器IP> -c 20
# 如果延迟波动大或者丢包，网络问题要纳入考虑

MySQL内部的”病灶检查”

操作系统层面没问题，接下来就聚焦到MySQL内部了。这里有几把”手术刀”，帮你快速找到病因。

第一把刀：SHOW PROCESSLIST

这是最基础但最有效的命令之一。当数据库变慢时，先运行这个看看：

-- 查看所有连接
SHOW FULL PROCESSLIST;

-- 只看当前正在运行的查询
SELECT * FROM information_schema.processlist 
WHERE COMMAND != 'Sleep' AND ID != CONNECTION_ID();

看这里要注意几个关键点：

Id      User      Host        db       Command  Time  State                    Info
------  --------  ----------  -------  -------  ----  -----------------------  -------------------------
152     app_user  10.0.0.5:45231  mydb   Query    120   Sending data           SELECT * FROM orders WHERE ...
153     app_user  10.0.0.6:32145  mydb   Query    0     starting               SELECT COUNT(*) FROM ...
154     app_user  10.0.0.7:18234  mydb   Sleep    45    NULL                   NULL

看到Time字段很大的查询了吗？这就是嫌疑犯。State字段也能告诉你查询卡在哪个阶段——”Sending data”通常是数据量太大或索引没命中，”Locking”说明在等锁，”Waiting for schema metadata lock”说明在执行DDL时被元数据锁住了。

第二把刀：慢查询日志

慢查询日志是MySQL自带的”黑匣子”，记录所有执行时间超过阈值的SQL。先确认它是否开启，以及配置是否合理：

-- 查看慢查询日志状态
SHOW VARIABLES LIKE 'slow_query%';

+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | ON                               |
| slow_query_log_file       | /var/log/mysql/slow.log          |
| long_query_time           | 2                                |
| log_queries_not_using_indexes | OFF                          |
+---------------------------+----------------------------------+

这里有几个关键配置：

slow_query_log：是否开启，必须是ON
long_query_time：阈值，默认2秒，可以根据情况调低到0.5秒甚至更低
log_queries_not_using_indexes：记录没有使用索引的查询，即使执行时间很短

如果没有开启，或者阈值得太高，可以先调整：

-- 动态调整（重启后会失效，建议同时修改配置文件）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'ON';

第三把刀：EXPLAIN分析慢查询

找到慢SQL之后，下一步就是用EXPLAIN看看执行计划：

-- 查看具体SQL的执行计划
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending';

JSON格式的输出更详细，能看到优化器的思考过程。关键看这几个字段：

{
  "query_block": {
    "select_id": 1,
    "filesort": {
      "using_filesort": true,  /* 如果有值，说明需要额外排序 */
      "sort_key": "..."
    },
    "table": {
      "table_name": "orders",
      "access_type": "ref",    /* 关键！range、ref、index都是好的，ALL表示全表扫描 */
      "possible_keys": ["idx_user_id"],
      "key": "idx_user_id",    /* 实际使用的索引 */
      "key_len": "4",
      "used_keys": ["idx_user_id"],
      "rows": 150000,          /* 预估扫描行数 */
      "filtered": 10.00        /* 过滤比例 */
    }
  }
}

access_type是”ALL”的话，问题基本就定位到了——缺索引或者索引失效。常见的原因有：

-- 1. 对索引列做了函数操作，导致索引失效
SELECT * FROM users WHERE YEAR(create_time) = 2024;  -- 索引失效
-- 应该改成范围查询：
SELECT * FROM users WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

-- 2. 隐式类型转换
SELECT * FROM orders WHERE order_no = 12345;  -- order_no是VARCHAR，传了INT
-- 应该改成字符串比较：
SELECT * FROM orders WHERE order_no = '12345';

-- 3. LIKE前缀通配符
SELECT * FROM users WHERE name LIKE '%张%';  -- 前缀通配符导致索引失效
-- 可以用全文索引或者搜索引擎解决

第四把刀：sys schema

MySQL 5.7+引入了sys schema，把Performance Schema的数据做了一层友好的封装，查询起来方便很多：

-- 1. 查看当前最耗时的SQL（近一小时）
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile 
ORDER BY avg_latency DESC 
LIMIT 10;

-- 2. 查看全表扫描的SQL
SELECT * FROM sys.statements_with_full_table_scans 
WHERE schema_name = 'mydb';

-- 3. 查看使用了临时表的SQL
SELECT * FROM sys.statements_with_temp_tables 
WHERE schema_name = 'mydb';

-- 4. 查看锁等待情况
SELECT * FROM sys.innodb_lock_waits;

-- 5. 查看当前连接的资源消耗
SELECT * FROM sys.host_summary;
SELECT * FROM sys.user_summary;

sys schema特别适合日常巡检，能让你快速发现长期存在的性能隐患。

第五把刀：Performance Schema

如果你想深入到底层，Performance Schema是最终的武器。不过它的数据比较原始，需要一些时间学习：

-- 查看当前最消耗CPU的SQL
SELECT DIGEST_TEXT, 
       SUM_CPU/1000000 AS cpu_seconds,
       COUNT_STAR AS exec_count,
       SUM_ELAPSED/1000000000 AS total_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_CPU DESC
LIMIT 10;

-- 查看当前最耗IO的SQL
SELECT DIGEST_TEXT,
       SUM_ROWS_EXAMINED,
       COUNT_STAR,
       SUM_ROWS_EXAMINED/COUNT_STAR AS avg_rows
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 10;

-- 查看当前等待事件（锁、IO等待等）
SELECT event_name, count_star, sum_timer_wait/1000000000 AS total_wait_seconds
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name != 'wait/io/file/sql/frlog'
ORDER BY sum_timer_wait DESC
LIMIT 20;

通过这些数据，你可以看到哪些SQL在消耗CPU，哪些在等待IO，哪些在等待锁，从而针对性地优化。

第六把刀：pt-query-digest

如果慢查询日志里有大量的SQL，人工分析会疯掉的。这时候Percona Toolkit里的pt-query-digest就是你的神兵利器：

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_report.txt

# 只看最近的1小时数据
pt-query-digest --since 1h /var/log/mysql/slow.log

# 按查询类型分组统计
pt-query-digest --group-by fingerprint /var/log/mysql/slow.log

# 找出最耗时的查询
pt-query-digest --limit 10% /var/log/mysql/slow.log

# 生成HTML报告（更直观）
pt-query-digest --output=html_report /var/log/mysql/slow.log > /tmp/report.html

生成的报告非常详细，包括：

Top N最耗时的查询
查询指纹分析
各属性的分布
每次执行的样本详情
优化的建议

# 示例报告片段

# Profile
 Rank ID             Query ID      Response time  Calls  R/Call  V/M   Item
 ==== ===============  ============= =============  =====  ======  ====  ===============
    1 0x9B755F94E41D... 0x9B75       1245.2345s  312    3.9911  0.00  SELECT mydb.orders
    2 0x2C8A1D43E89F... 0x2C8A       832.1234s   156    5.3341  0.00  SELECT mydb.users

# Query 1: 2.85 Qps, 4.17 Mo/7.87 Kbytes, 3.991 avg/4.000 med/3.991 max
# Runtime    % total    Max total    Max r/M   V/M
# 1245.23s  86.54%   3.99s      3.99      0.00
# Count     312
# Time range  2024-01-15 02:00:00 to 2024-01-15 03:30:00
# Items    0
# Item sizes min/avg/max = 0/0/0

看这个报告，第一眼的结论就很清晰：有一个查询占了86%的响应时间，需要立即优化。

内存和缓冲池的检查

InnoDB缓冲池是MySQL性能的关键。如果配置不当或者命中率低，数据库会频繁读写磁盘，性能自然下降。

-- 查看缓冲池配置
SHOW VARIABLES LIKE 'innodb_buffer_pool%';

+--------------------------------------+-------------+
| Variable_name                        | Value       |
+--------------------------------------+-------------+
| innodb_buffer_pool_size              | 1073741824  |  -- 默认128M，生产环境应该更大
| innodb_buffer_pool_instances         | 1           |
| innodb_buffer_pool_chunk_size        | 134217728   |
+--------------------------------------+-------------+

-- 查看缓冲池命中率（越高越好，应该大于99%）
SELECT 
  (1 - (Pages_free / (Pages_free + Pages_data + Pages_misc))) * 100 AS hit_rate
FROM (
  SELECT 
    SUM(CASE WHEN STATUS = 'free' THEN 1 ELSE 0 END) AS Pages_free,
    SUM(CASE WHEN STATUS = 'modified' OR STATUS = 'read_plain' OR STATUS = 'read_pos' THEN 1 ELSE 0 END) AS Pages_data,
    SUM(CASE WHEN STATUS = 'misc' THEN 1 ELSE 0 END) AS Pages_misc
  FROM information_schema.INNODB_BUFFER_PAGE
) t;

-- 或者用sys schema查看
SELECT * FROM sys.innodb_buffer_stats_by_schema 
ORDER BY allocated DESC;

缓冲池命中率低，可能的原因：

innodb_buffer_pool_size设置太小
查询的数据量超过了缓冲池容量
缓冲池预热不够（刚重启的数据库）

锁和事务的问题

有时候MySQL变慢不是查询慢，而是被锁住了。多个事务互相等待，排队等资源，响应时间就会暴涨。

-- 查看当前锁等待情况（MySQL 5.7+）
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 r
JOIN information_schema.innodb_trx b ON r.blocking_trx_id = b.trx_id;

-- 查看当前活跃的事务
SELECT * FROM information_schema.innodb_trx;

-- 查看最近执行的DDL（可能导致元数据锁）
SELECT * FROM performance_schema.events_statements_history 
WHERE EVENT_NAME LIKE 'statement/sql/alter%'
ORDER BY TIMER_START DESC 
LIMIT 10;

如果发现有长时间运行的事务或者阻塞严重的查询，可能需要：

杀掉阻塞的会话
优化长事务的SQL
调整隔离级别

-- 紧急情况下的操作（谨慎使用）
-- 查看会话详情
SHOW PROCESSLIST;

-- 如果某个会话确实需要终止
KILL <thread_id>;

一个真实的排查案例

让我分享一个我亲身经历的案例，让你感受下实战中的排查思路。

那是2023年冬天，某电商平台的订单系统在早上9点的高峰期突然响应变慢。用户反映下单页面经常超时，后台监控显示MySQL的平均响应时间从50ms飙升到了3秒。

第一步，我先查看了系统层面的指标。top显示CPU使用率正常，iostat显示磁盘IO也正常，排除了系统层面的问题。

第二步，登录MySQL执行SHOW PROCESSLIST，发现有几个查询的Time字段非常大，最长的已经跑了120秒。这显然不正常。

第三步，查看慢查询日志，用pt-query-digest分析，发现有一个复杂的关联查询排在第一位，占了总慢查询量的80%以上：

SELECT o.*, u.name, p.title 
FROM orders o 
LEFT JOIN users u ON o.user_id = u.id 
LEFT JOIN products p ON o.product_id = p.id 
WHERE o.created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY o.created_at DESC 
LIMIT 20;

第四步，用EXPLAIN分析这个查询：

EXPLAIN SELECT o.*, u.name, p.title 
FROM orders o 
LEFT JOIN users u ON o.user_id = u.id 
LEFT JOIN products p ON o.product_id = p.id 
WHERE o.created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY o.created_at DESC 
LIMIT 20;

结果发现orders表扫描了150万行数据，而且是用filesort做的排序。orders表虽然有一个created_at索引，但优化器选择了全表扫描，因为过滤条件created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)返回的数据量太大了。

第五步，优化方案。在orders表上添加了一个复合索引：

-- 添加覆盖索引
ALTER TABLE orders ADD INDEX idx_created_status_user_product (created_at, status, user_id, product_id);

第六步，验证效果。优化后，同样的查询响应时间从3秒降到了50ms，问题解决了。

这个案例告诉我们，排查MySQL变慢问题要有章法：先看系统，再看数据库，然后找慢SQL，分析执行计划，最后给出优化方案。

日常巡检的习惯

最后想说的是，预防胜于治疗。建立一个日常巡检的习惯，比出问题后再去救火要轻松得多：

# 每日巡检脚本示例
#!/bin/bash

# 1. 检查连接数
CONNECTED=$(mysql -e "SHOW STATUS LIKE 'Threads_connected';" -N | awk '{print $2}')
MAX_CONN=$(mysql -e "SHOW VARIABLES LIKE 'max_connections';" -N | awk '{print $2}')
echo "连接数: $CONNECTED / $MAX_CONN"

# 2. 检查缓冲池命中率
HIT_RATE=$(mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%';" | awk '/Request/{req=$2} /Hit/{hit=$2} END{printf "%.2f%%", (1-hit/req)*100}')
echo "缓冲池命中率: $HIT_RATE"

# 3. 检查慢查询数量
SLOW_COUNT=$(mysql -e "SHOW STATUS LIKE 'Slow_queries';" -N | awk '{print $2}')
echo "慢查询数: $SLOW_COUNT"

# 4. 检查复制延迟（如果有从库）
SLAVE_STATUS=$(mysql -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')
echo "复制延迟: $SLAVE_STATUS 秒"

# 5. 检查磁盘空间
df -h | grep /var/lib/mysql

把这些巡检命令写进crontab，每天自动发送报告到你的邮箱，有问题提前发现，比用户投诉要体面得多。

MySQL变慢这个问题，说复杂也复杂，说简单也简单。关键在于掌握正确的排查思路，熟悉各种监控工具的使用方法。当问题真正来临时，不要慌，按步骤来，总能找到问题的根源。希望这篇文章能成为你排查MySQL问题时的一个有用参考。

正文

当MySQL变慢时如何用监控工具快速定位原因

当MySQL变慢时如何用监控工具快速定位原因

先搞清楚慢在哪一层

系统层面的”体检报告”

网络层面的”交通状况”

MySQL内部的”病灶检查”

第一把刀：SHOW PROCESSLIST

第二把刀：慢查询日志

第三把刀：EXPLAIN分析慢查询

第四把刀：sys schema

第五把刀：Performance Schema

第六把刀：pt-query-digest

内存和缓冲池的检查

锁和事务的问题

一个真实的排查案例

日常巡检的习惯

相关阅读

Java Web如何轻松实现数据库连接与操作技巧揭秘

Java Web开发如何高效管理数据库连接池？实战技巧与案例解析

新手必看！轻松掌握Javafx连接MySQL数据库全攻略

破解IDEA连接数据库的五大绝招，轻松实现高效编程体验

郑州大学揭秘：数据库技术在现代教育中的应用与实践

从入门到精通：揭秘数据库大师的成长之路及实战技巧

揭秘Owl数据库：如何轻松管理海量数据，助力企业高效决策

手机丢失后，如何恢复outlook数据库中的重要邮件？详解数据备份与恢复技巧

揭秘OpenStack数据库优化：提升云平台性能的五大秘诀

揭秘企业级数据库运维：OpenShift轻松上云，提升业务稳定性