正文

网站突然变卡数据库查询越来越慢怎么办这几个MySQL性能监控工具帮你快速定位问题所在

/0 浏览量
0826

网站突然变卡数据库查询越来越慢怎么办这几个MySQL性能监控工具帮你快速定位问题所在

半夜三点，你正在追剧，突然收到运维群炸了——网站访问巨卡，用户投诉不断。你打开服务器一看，CPU飙升到90%，MySQL连接数快爆了。

别慌，这种情况我见得多了。今天就把我压箱底的MySQL性能监控工具全交给你，包教包会。

先别急着重启，搞清楚状况再说

很多人一遇到数据库变慢，第一反应是重启MySQL服务。打住！重启能解决的是”现在”，但解决不了”为什么”。你得先找到元凶，不然问题迟早还会来。

数据库变慢无非就这几类原因：

  • 查询写得烂，没走索引或者索引失效
  • 数据库配置不对，比如buffer pool设太小
  • 表数据量太大，没做分区或者归档
  • 硬件资源不够，内存、磁盘IO成了瓶颈
  • 连接数太多，把数据库拖垮了

下面这些工具，就是帮你逐一排查的。

第一道防线：SHOW 命令家族，MySQL自带的”体检报告”

SHOW PROCESSLIST —— 谁在拖后腿

这是最基础也是最常用的命令。运行它，你能看到当前所有正在执行的查询：

SHOW PROCESSLIST;

输出长这样：

+----+-------------+-----------------+--------------+---------+------+----------+-----------------------+
| Id | User        | Host            | db           | Command | Time | State    | Info                  |
+----+-------------+-----------------+--------------+---------+------+----------+-----------------------+
| 45 | app_user    | 192.168.1.10:43210 | mydb      | Sleep   | 120  |          | NULL                  |
| 46 | app_user    | 192.168.1.10:43211 | mydb      | Query   | 15   | Sending data | SELECT * FROM orders WHERE status=0 |
| 47 | root        | localhost       | NULL       | Query   | 0    | NULL     | SHOW PROCESSLIST      |
+----+-------------+-----------------+--------------+---------+------+----------+-----------------------+

重点看这几个字段：

  • Time：查询执行了多久。如果有个查询Time值特别大（比如几百秒），那基本就是它了。
  • State：查询当前状态。Sending data、Sorting result、Copying to tmp table这些都是危险信号。
  • Info：具体在跑什么SQL。

如果看到有大量Sleep状态的连接，那说明连接没释放，数据库被占着茅坑不拉屎。这时候可以用这个命令干掉休眠太久的连接：

-- 干掉休眠超过60秒的连接
KILL 45;

SHOW STATUS —— 全局健康指标

SHOW GLOBAL STATUS;

这个命令的输出有几百行，别怕，只看关键指标：

-- 慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 连接数情况
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';

-- 查询缓存命中率（如果开启了的话）
SHOW GLOBAL STATUS LIKE 'Qcache%';

-- 表扫描情况，越接近1越好
SHOW GLOBAL STATUS LIKE 'Handler_read%';

-- InnoDB缓冲池命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

我给你解释一下每个指标怎么看：

Threads_connected vs Threads_running：前者是总连接数，后者是正在执行的连接数。如果Threads_running接近Threads_connected，说明数据库忙不过来了。

Slow_queries：这个数值一直在涨？说明慢查询越来越多，得重视了。

Handler_read_next / Handler_read_rnd_next：如果这两个值特别大，说明在做全表扫描，大概率是缺索引了。

Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads：前者是缓冲池读取请求总数，后者是物理磁盘读取次数。如果后者占前者比例超过1%，说明缓冲池不够用，该调大innodb_buffer_pool_size了。

第二道防线：information_schema，查询元数据宝库

查看正在执行的长查询

SELECT 
    ID,
    USER,
    HOST,
    DB,
    COMMAND,
    TIME,
    STATE,
    LEFT(INFO, 100) AS SQL片段
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC;

这个查询能帮你筛选出所有非休眠状态的连接，并按执行时间排序，一眼就能看到谁最拖后腿。

查看表的大小和碎片情况

SELECT 
    TABLE_NAME,
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS 数据大小MB,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS 索引大小MB,
    ROUND(DATA_FREE / 1024 / 1024, 2) AS 碎片大小MB,
    TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;

输出长这样：

+-----------------+---------------+---------------+---------------+------------+
| TABLE_NAME      | 数据大小MB    | 索引大小MB    | 碎片大小MB    | TABLE_ROWS |
+-----------------+---------------+---------------+---------------+------------+
| orders          | 2560.50       | 890.20        | 45.30         | 15000000   |
| users           | 1200.00       | 320.50        | 12.10         | 5000000    |
| products        | 850.30        | 210.00        | 0.00          | 100000     |
+-----------------+---------------+---------------+---------------+------------+

如果某个表的碎片大小MB很大，说明需要进行OPTIMIZE TABLE来清理碎片：

OPTIMIZE TABLE orders;

查看表锁情况

SELECT 
    ENGINE,
    COUNT(*) AS 锁等待数量
FROM information_schema.INNODB_TRX
GROUP BY ENGINE;

如果有大量锁等待，说明有死锁或者长事务阻塞了其他查询。

第三道防线：慢查询日志，抓出”慢查询元凶”

这是我最常用的工具之一。MySQL自带的慢查询日志功能，能自动记录执行时间超过阈值的SQL。

开启慢查询日志

先看看当前配置：

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

输出：

+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /var/log/mysql/slow.log          |
| long_query_time           | 10.000000                        |
+---------------------------+----------------------------------+

如果slow_query_log是OFF，赶紧打开：

-- 临时开启（重启后失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 执行超过2秒的SQL才记录

或者修改配置文件/etc/mysql/mysql.conf.d/mysqld.cnf，让它永久生效：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1  -- 记录没走索引的查询

修改完记得重启MySQL：

sudo systemctl restart mysql

分析慢查询日志

慢查询日志一般位于/var/log/mysql/slow.log，你可以直接打开看：

tail -100 /var/log/mysql/slow.log

不过日志格式有点乱，用专门的工具来分析更好。推荐mysqldumpslow：

# 统计出现次数最多的慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 统计查询时间最长的
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

# 统计返回记录数最多的
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

输出示例：

Count: 150  Time=5.20s (780s)  Lock=0.00s (0s)
  SELECT * FROM orders WHERE user_id = ? AND status = ?

Count: 80  Time=3.10s (248s)  Lock=0.00s (0s)
  SELECT * FROM products WHERE category_id = ? ORDER BY create_time DESC

看到没？Count表示出现了多少次，Time表示平均执行时间和总执行时间。排在最前面的就是该优先优化的。

第四道防线：SHOW PROFILE，查询性能 dissect

如果你发现某条SQL特别慢，可以用SHOW PROFILE来逐阶段分析：

-- 开启profile功能
SET profiling = 1;

-- 执行你的慢查询
SELECT * FROM orders WHERE user_id = 12345 AND status = 0;

-- 查看profile列表
SHOW PROFILES;

-- 查看详细分析
SHOW PROFILE FOR QUERY 1;

-- 查看所有维度的分析
SHOW PROFILE ALL FOR QUERY 1;

输出长这样：

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000045 |
| checking permissions | 0.000003 |
| Opening tables       | 0.000012 |
| init                 | 0.000008 |
| System lock          | 0.000005 |
| optimizing           | 0.000002 |
| statistics           | 0.000015 |
| preparing            | 0.000008 |
| Executing            | 0.000001 |
| Sending data         | 4.852300 |  -- 大部分时间花在这！
| end                  | 0.000003 |
| query end            | 0.000002 |
| closing tables       | 0.000005 |
| freeing items        | 0.000010 |
| cleaning up          | 0.000003 |
+----------------------+----------+

看到了吗？Sending data阶段花了4.85秒，说明数据库在读取数据上卡住了。这时候就要检查是不是全表扫描了。

你可以用EXPLAIN进一步分析：

EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 0;

输出：

+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | orders | NULL       | ref   | idx_user_id   | idx_user_id | 4     | const | 1500 | 10.00   | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

关键字段解读：

  • type：ref表示用了索引等值查询，这是好的。如果是ALL就是全表扫描，要命了。
  • key：实际使用的索引。如果是NULL说明没走索引。
  • rows：预估扫描行数。越大越慢。
  • Extra：Using filesort表示需要额外排序，Using temporary表示用了临时表，都是性能杀手。

第五道防线：Percona Toolkit，专业级的工具箱

如果说前面的工具是”家常炒菜”，那Percona Toolkit就是”满汉全席”了。这是Percona公司开发的一套开源MySQL运维工具，专业DBA都在用。

安装

# Ubuntu/Debian
sudo apt-get install percona-toolkit

# CentOS/RHEL
sudo yum install percona-toolkit

pt-query-digest —— 慢查询日志分析神器

这是我最推崇的工具，没有之一。它能深度分析慢查询日志，给出详细的报告：

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

输出非常详细，包括：

# 2.18 total, 1896306125 total, 0.00 unique, 0.00 per query, 0.00 avg min, 0.00 avg max, 2.18 sum
# Query 1: 0.19 QPS, 0.00x concurrency, ID 0x1234 at byte 0
# This item is included for the report
# Percentages of summed rows: 100%
#   Severity  Total    Count  %Total  %Count  AvgMS  MinMS  MaxMS  StdDev
#           MedMS  Range  Name
#  medium    2       2    91.7   100.0   1090    980   1200   156   1090  220  SELECT * FROM orders WHERE user_id = ?
#   Overview  Total   Min    Max    Avg    Med    95%  99%  99.9%  StdDev  Env
#     Query   2     980   1200   1090   1090   1200   1200   1200    156    -
#    TimeRange 00:00:02       (never overlaps)
#    Statement  SELECT * FROM orders WHERE user_id = ?
#   Attributes select * from orders where user_id = ?

还支持输出多种格式：

# 输出为HTML报告
pt-query-digest --report /var/log/mysql/slow.log > report.html

# 输出为JSON
pt-query-digest --report --output json /var/log/mysql/slow.log > report.json

# 分析正在运行的查询（不需要停服务）
pt-query-digest --processlist

pt-online-schema-change —— 在线改表结构

很多情况下，数据库变慢是因为表结构需要调整（比如加索引）。传统方式需要锁表，业务不能停。pt-online-schema-change可以在线改表结构：

pt-online-schema-change 
  --alter "ADD INDEX idx_status (status)" 
  D=mydb,t=orders 
  --execute

这个工具的原理是创建一张新表，把数据同步过去，最后替换。整个过程业务不受影响。

pt-deadlock-logger —— 死锁分析

pt-deadlock-logger 
  --socket /var/run/mysqld/mysqld.sock 
  --dest D=slow_log,t=deadlocks 
  --interval 1

它会持续监控死锁，记录到表中，方便后续分析。

第六道防线：MySQL Enterprise Monitor，商业级的监控方案

如果你用MySQL企业版，有个图形化的监控平台叫MySQL Enterprise Monitor。它提供：

  • 实时性能仪表盘
  • 自动告警
  • 慢查询趋势分析
  • 服务器资源监控

虽然要花钱，但对于企业级应用来说，可视化界面真的能省去很多排查时间。

第七道防线：Prometheus + Grafana，现代化的监控方案

现在流行的做法是用Prometheus采集MySQL指标，用Grafana做可视化。

部署mysqld_exporter

# 下载mysqld_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 -u root -p -e "CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password'; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';"

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

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

Grafana配置

在Grafana中导入MySQL仪表盘模板（模板ID通常是7362），就能看到非常漂亮的监控面板：

  • QPS/TPS趋势
  • 连接数变化
  • InnoDB缓冲池命中率
  • 慢查询数量
  • 锁等待情况

遇到慢查询，怎么优化？几个实战技巧

1. 该加索引的时候别犹豫

-- 查看表上现有的索引
SHOW INDEX FROM orders;

-- 添加复合索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

记住：最左前缀原则。复合索引(user_id, status)可以优化WHERE user_id = ?和WHERE user_id = ? AND status = ?的查询，但不能优化WHERE status = ?。

2. 避免SELECT *

-- 不好的写法
SELECT * FROM orders WHERE user_id = 12345;

-- 好的写法
SELECT id, order_no, total_amount FROM orders WHERE user_id = 12345;

只取需要的字段，减少IO和内存占用。

3. 大数据量分页优化

-- 不好的写法，偏移量越大越慢
SELECT * FROM orders LIMIT 100000, 20;

-- 好的写法，先定位再回表
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders LIMIT 100000, 20
) tmp ON o.id = tmp.id;

4. 大事务拆小

长事务会占用锁和资源，尽量把大事务拆成小批次执行：

-- 不好的写法，一次性处理10万条
START TRANSACTION;
UPDATE orders SET status = 1 WHERE create_time < '2023-01-01';
COMMIT;

-- 好的写法，分批处理
UPDATE orders SET status = 1 WHERE create_time < '2023-01-01' LIMIT 1000;
-- 循环执行，直到影响行数为0

总结一下

工具 用途 适用场景
SHOW PROCESSLIST 查看当前连接 快速定位卡住的查询
SHOW STATUS 查看全局状态 了解数据库整体健康度
information_schema 查询元数据 查看表大小、锁情况等
慢查询日志 记录慢SQL 发现性能瓶颈的根源
mysqldumpslow 分析慢日志 统计慢查询规律
SHOW PROFILE 分析查询阶段 定位SQL执行卡在哪
EXPLAIN 分析执行计划 检查是否走索引
pt-query-digest 深度分析慢查询 专业级慢查询分析
pt-online-schema-change 在线改表结构 加索引不锁表
Prometheus + Grafana 实时监控 长期监控和告警

最后说几句

数据库变慢这事儿，说大不大，说小不小。用对工具，排查起来其实不难。关键是要养成习惯：

  1. 日常监控不能少：装个Prometheus + Grafana，24小时盯着关键指标
  2. 慢查询日志要开：这是发现问题的第一手资料
  3. 定期review：每周看看慢查询报告，及时优化
  4. 别等出问题才想起：预防胜于治疗

希望这些工具能帮到你。数据库这东西，用的多了就熟了。有问题随时来问，一起探讨！

-- 展开阅读全文 --