正文

MySQL性能变慢怎么办查看这些监控工具让数据库运行如飞

/0 浏览量
0905

嘿，朋友，我懂那种感觉。

凌晨三点，报警群炸了，生产环境的MySQL CPU直接飙到95%，用户反馈页面加载卡顿，老板在群里@你“怎么回事”。这时候，慌乱是最没用的东西。你需要的是冷静、数据、和正确的工具。

MySQL性能下降从来不是一夜之间发生的。它像慢性病，前期有征兆，只是你没看见。今天我不给你讲枯燥的理论，而是像老友聊天一样，带你实战排查。我会告诉你到底该看什么指标、用什么工具、怎么从海量数据里捞出真正的元凶。

准备好了吗？让我们把这台“老爷车”重新调校到赛车级别。

第一站：先问自己三个问题

在打开任何监控工具之前，先深呼吸，问自己：

  1. 是突然变慢，还是逐渐变慢？

    • 突然变慢：可能是某条烂SQL上线、索引被误删、或者硬件故障。
    • 逐渐变慢：可能是数据量增长导致索引失效、统计信息过时、或者配置参数不再适合当前负载。

  2. 是所有查询都慢，还是只有某些查询慢？

    • 全部慢：可能是系统资源瓶颈（CPU、内存、磁盘IO、网络）。
    • 部分慢：90%的概率是SQL写法问题或索引缺失。

  3. 是连接数爆炸，还是单连接耗时过长？

    • 连接数爆满：可能是连接泄漏或并发突发。
    • 单连接耗时：很可能是慢查询或锁等待。

这三个问题，能帮你把排查范围从“整个MySQL”缩小到“某个具体的点”。接下来，我们上工具。

第二站：实时体检——系统层面先别急着看MySQL

很多DBA一上来就SHOW PROCESSLIST，其实这是错的。如果操作系统本身已经负载过高，MySQL再优秀也无济于事。

1. CPU和内存：top 和 htop

先SSH到你的MySQL服务器，敲下：

top

或者更友好的：

htop

你要看什么？

  • %CPU 和 %MEM：MySQL进程占了多少CPU？如果us（用户态）很高，说明是SQL执行计算密集；如果sy（内核态）很高，可能是上下文切换频繁或IO等待高。
  • wa（iowait）：如果这个值超过20%，说明磁盘IO成为瓶颈。MySQL在等磁盘读写，这时候调SQL优化效果有限，得看存储层。
  • load average：看1分钟、5分钟、15分钟的负载。如果1分钟远高于15分钟，说明是突发峰值；如果三者接近且很高，说明是持续高负载。

2. 内存：free -h

free -h

关键看available列，不要只看free。free是未被使用的内存，available是可用于启动新应用的内存（包括可回收的cache）。如果available很少，系统会开始 swap，性能会暴跌。

3. 磁盘IO：iostat

iostat -xz 1 5

-x 显示扩展统计信息，-z 隐藏空设备，1 表示每秒刷新，5 表示刷新5次。

你要盯着看的是：

  • %util：设备利用率。如果持续接近100%，说明磁盘已经饱和，是严重的IO瓶颈。
  • await：平均每次IO操作的等待时间（毫秒）。正常应该<10ms，如果>50ms，说明磁盘响应很慢。
  • r_await 和 w_await：读和写的平均等待时间。分别分析是读瓶颈还是写瓶颈。

4. 网络：netstat 或 ss

ss -s

查看网络连接统计。如果TCP连接数异常高，或者SYN_RECV状态很多，可能是网络攻击或连接泄漏。

第三站：MySQL内部——最核心的监控维度

系统层面没问题后，我们进入MySQL内部。这里才是主战场。

1. 连接数监控：SHOW STATUS LIKE 'Threads%'

SHOW STATUS LIKE 'Threads%';

关键字段解读：

  • Threads_connected：当前打开的连接数。如果接近max_connections，新连接会排队甚至被拒绝。
  • Threads_running：当前正在执行的线程数。如果这个值持续大于1，说明MySQL内部有线程在排队等待执行，是明显的瓶颈信号。
  • Threads_created：历史创建过的线程总数。如果这个数字增长很快，说明连接频繁创建销毁，可能是连接池配置不当。
  • Slow_queries：慢查询总数。持续增长是危险信号。

对比配置：

SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'thread_cache_size';

如果Threads_connected经常接近max_connections，你需要考虑：

  • 增加max_connections（但不要无限增加，每个连接都消耗内存）
  • 优化应用层连接池，复用连接
  • 检查是否有连接泄漏（应用未正确关闭连接）

2. 查询性能——慢查询日志：slow_query_log

这是排查SQL问题最强大的工具，没有之一。

先确认是否开启：

SHOW VARIABLES LIKE 'slow_query_log%';

你应该看到：

Variable_name Value
slow_query_log ON
slow_query_log_file /var/log/mysql/slow.log
long_query_time 1.000000

如果没开启，立刻开启：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 执行时间超过1秒的SQL记录

生产环境建议设置：

  • long_query_time 设为 0.5 或 1，不要太低，否则日志会爆炸。
  • 开启 log_queries_not_using_indexes，记录未使用索引的查询，哪怕它很快。
SET GLOBAL log_queries_not_using_indexes = ON;

分析慢查询日志：

MySQL提供了专用工具mysqldumpslow，但更推荐用pt-query-digest（Percona Toolkit的一部分）：

pt-query-digest /var/log/mysql/slow.log

这个工具会生成一份详细的报告，告诉你：

  • 最慢的查询是什么
  • 查询的平均耗时、P99耗时
  • 查询的扫描行数、返回行数
  • 查询的特征指纹（便于归类相似SQL）

举个例子：

假设pt-query-digest输出显示，有80%的慢查询都是同一个SQL指纹：

SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY create_time DESC LIMIT 20;

你会立刻意识到：这个查询大概率缺索引。去查一下这个表的索引：

SHOW INDEX FROM orders;

如果发现没有(user_id, status, create_time)的联合索引，问题就找到了。

3. InnoDB引擎状态：SHOW ENGINE INNODB STATUS

这是MySQL内置的“黑匣子”数据，包含了大量内部统计信息。

查看最新状态：

SHOW ENGINE INNODB STATUS\G

重点关注LATEST DETECTED DEADLOCK和LATEST FOREIGN KEY ERROR部分，这里会记录最近的死锁和外键约束错误，对排查锁问题至关重要。

更推荐的做法是定期采样：

-- 创建一个表来存储状态
CREATE TABLE innodb_status LIKE INFORMATION_SCHEMA.INNODB_STATUS;

-- 定时插入（可以用事件调度器）
INSERT INTO innodb_status SELECT * FROM INFORMATION_SCHEMA.INNODB_STATUS;

然后在mysql.event里创建一个定时任务，每分钟采样一次。这样你可以回溯历史状态，而不是只能在出问题时现场抓。

4. 性能模式：Performance Schema（MySQL 5.7+ 推荐）

Performance Schema是MySQL内置的性能监控框架，比SHOW STATUS更细粒度，而且开销可控。

开启必要的消费者：

UPDATE setup_consumers SET ENABLED = 'YES' 
WHERE NAME IN ('events_statements_current', 'events_statements_history', 'events_waits_current');

UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE 'statement/%' OR NAME LIKE 'wait/io/table%';

查询当前正在执行的SQL及其等待事件：

SELECT 
    PROCESSLIST_ID,
    THREAD_ID,
    EVENT_NAME,
    TIMER_WAIT/1000000000000 AS wait_seconds,
    SQL_TEXT
FROM performance_schema.events_statements_current
WHERE SQL_TEXT IS NOT NULL
ORDER BY TIMER_WAIT DESC;

查询最耗时的SQL（历史）：

SELECT 
    DIGEST_TEXT AS query,
    COUNT_STAR AS exec_count,
    SUM_TIMER_WAIT/1000000000000 AS total_latency_seconds,
    AVG_TIMER_WAIT/1000000000000 AS avg_latency_seconds,
    MAX_TIMER_WAIT/1000000000000 AS max_latency_seconds,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY total_latency_seconds DESC
LIMIT 10;

这条查询会告诉你：哪10个SQL占用了最多的总响应时间。注意，是total_latency，不是avg_latency。一个执行1000次、每次10ms的SQL，比执行1次、100ms的SQL更值得优化。

5. 锁等待监控：PROCESSLIST 和 INNODB_TRX

当用户反馈“卡住”时，很可能是锁等待。

查看当前事务和锁：

-- 查看正在运行的事务
SELECT * FROM information_schema.innodb_trx\G

-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits\G

更直观的查询：

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
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

如果查到有阻塞，先解决阻塞，再优化SQL。对于长时间阻塞的事务，可以考虑KILL掉阻塞源（谨慎操作，确认影响范围）。

KILL 12345; -- 替换为blocking_thread

6. 缓冲池命中率：INNODB_BUFFER_POOL_STATS

InnoDB缓冲池是MySQL性能的核心。如果数据能在内存中命中，就不需要磁盘IO。

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

关键指标：

  • Innodb_buffer_pool_read_requests：缓冲池读请求次数
  • Innodb_buffer_pool_reads：需要从磁盘读的次数（缓存未命中）

计算命中率：

SELECT 
    (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100 AS hit_rate
FROM (
    SELECT 
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS Innodb_buffer_pool_read_requests,
        (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS Innodb_buffer_pool_reads
) t;

理想命中率应该 > 99%。如果低于95%，说明缓冲池太小或数据访问模式不合理。

检查缓冲池配置：

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

生产环境建议：innodb_buffer_pool_size 设置为物理内存的 50%-70%（独享MySQL服务器）。

第四站：可视化监控——让数据自己说话

命令行看数据很强大，但不够直观。你需要一个实时仪表盘，一眼看出异常。

推荐工具组合：

1. Prometheus + Grafana（当前主流）

架构：

MySQL --> mysqld_exporter --> Prometheus --> Grafana

步骤：

第一步：部署mysqld_exporter

# 下载exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz
tar xvf mysqld_exporter-0.14.0.linux-amd64.tar.gz
cd mysqld_exporter-0.14.0.linux-amd64

# 创建MySQL监控用户
mysql -u root -p -e "CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password'; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost'; FLUSH PRIVILEGES;"

# 创建配置文件 ~/.my.cnf
cat > ~/.my.cnf <<EOF
[client]
user=exporter
password=password
EOF

# 启动exporter
./mysqld_exporter --config.my-cnf=~/.my.cnf &

第二步：配置Prometheus抓取

# prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']

systemctl restart prometheus

第三步：Grafana导入仪表板

Grafana官方有现成的MySQL仪表板，ID是 7362。导入后，你会看到：

  • QPS/TPS趋势
  • 连接数趋势
  • 缓冲池命中率
  • 慢查询数量
  • 锁等待情况
  • 主从延迟（如果有）

2. Percona Monitoring and Management (PMM)

如果你不想自己搭建，PMM是开箱即用的最佳选择。

# Docker一键部署
docker run -d \
  -p 443:443 \
  -v /opt/prometheus/data:/prometheus \
  -v /opt/graphite/data:/var/lib/graphite \
  -v /opt/query-log-data:/var/lib/percona/qm \
  --name pmm-server \
  --restart always \
  percona/pmm-server:2

访问 https://localhost，添加MySQL服务，PMM会自动采集：

  • System metrics（CPU、内存、磁盘、网络）
  • MySQL metrics（来自mysqld_exporter）
  • Query metrics（来自pt-query-digest）
  • OS metrics

PMM的最大优势是Query Analytics，它能自动分析慢查询日志，给出索引优化建议，甚至告诉你“这个查询缺少索引，建议添加”。

3. MySQL Enterprise Monitor（商业版）

如果预算充足，MySQL官方企业版监控是最省心的，但成本高。

第五站：常见性能瓶颈及解决方案

监控是为了发现问题，但更重要的是知道怎么解决。以下是80%的性能问题类型：

瓶颈1：缺索引或索引失效

现象： 慢查询日志里大量全表扫描，rows_examined 远大于 rows_sent。

排查：

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

看type列，如果是ALL，说明全表扫描。看key列，如果是NULL，说明没用到索引。

解决：

-- 添加联合索引
ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);

注意： 索引不是越多越好。每个索引都会增加写操作开销。遵循最左前缀原则，区分度高的字段放前面。

瓶颈2：大事务和长事务

现象： innodb_trx 表里有trx_started时间很早的事务，rows_locked很大。

解决：

  • 拆分大事务为小事务
  • 避免在事务中执行网络请求
  • 设置innodb_lock_wait_timeout，防止锁等待时间过长

瓶颈3：临键锁（Next-Key Lock）导致的锁竞争

现象： 高并发更新同一范围内的数据，锁等待队列很长。

排查：

-- 查看当前锁等待
SELECT * FROM performance_schema.data_lock_waits;

解决：

  • 将REPEATABLE READ隔离级别改为READ COMMITTED（如果业务允许）
  • 优化SQL，减少锁范围
  • 使用行级锁而非表级锁

瓶颈4：Buffer Pool命中率低

现象： Innodb_buffer_pool_reads 持续增长，await IO等待高。

解决：

  • 增大innodb_buffer_pool_size（最大到物理内存的70%）
  • 增大innodb_buffer_pool_instances（减少并发访问热点）
  • 检查是否有大查询一次性加载过多数据到Buffer Pool，导致LRU抖动

瓶颈5：连接数过多

现象： Threads_connected接近max_connections，新连接拒绝。

解决：

  • 优化应用层连接池（HikariCP、Druid等），设置合理的maximumPoolSize
  • 检查连接泄漏（应用未关闭连接）
  • 适当增加max_connections，但不要超过thread_cache_size太多

瓶颈6：磁盘IO成为瓶颈

现象： iostat显示`

-- 展开阅读全文 --