正文

MySQL数据库卡顿排查指南用这几个监控工具轻松定位慢查询与资源瓶颈

/0 浏览量
0723

你是不是也遇到过这种时刻：明明代码逻辑没变，数据库也没加什么新数据，但页面突然就转圈圈，甚至直接超时报错？那种感觉就像是你正在高速公路上开车，突然前面堵成了一锅粥，而你手里只有一张模糊不清的地图。

别慌，作为在这个领域摸爬滚打多年的“老中医”，我太懂这种焦虑了。MySQL卡顿从来不是玄学，它背后一定有迹可循。今天我不跟你扯那些晦涩的理论，咱们直接上手，用最顺手的几个工具，像剥洋葱一样把问题层层拆解。我会手把手教你怎么看、怎么查、怎么改，保证你看完就能去现场“救火”。

第一步：先让数据库“说实话”——开启慢查询日志

很多新手一遇到卡顿，第一反应是重启服务或者加内存，这其实是掩耳盗铃。真正的排查，得从MySQL自己记录的“日记”开始。这个日记就是慢查询日志（Slow Query Log）。

想象一下，如果MySQL是一个餐厅服务员，慢查询日志就是他记下来的“哪些客人点菜太慢”或者“哪些菜上得太慢”的记录。默认情况下，这个功能是关着的，因为记录所有操作会消耗性能。我们需要把它打开，并且设定一个阈值，比如超过1秒就算“慢”。

如何开启并配置？

你不需要重启MySQL，通过动态参数调整即可生效。登录到你的MySQL命令行，执行以下命令：

-- 查看当前慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';

-- 设置慢查询的时间阈值，单位是秒。这里设为1秒，意味着执行时间超过1秒的SQL都会被记录
SET GLOBAL long_query_time = 1;

-- 指定日志文件的路径，建议放在磁盘IO较好的地方
-- 注意：生产环境需要确保mysql用户对路径有写权限
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

专家提示： 在Linux服务器上，你可以直接用tail -f /var/log/mysql/slow.log实时观察日志输出。当页面再次变卡时，看看日志里是不是多了几条记录。如果有，恭喜你，你已经抓住了“凶手”的尾巴。

如果日志里没有记录怎么办？

有时候你会发现，明明页面很卡，但日志里干干净净。这通常有两个原因：

  1. 阈值设高了：你的查询可能只需要0.5秒，但你设的是1秒。这时候可以把long_query_time改成0.1甚至更小，但要小心，这会生成大量日志，影响性能，排查完记得改回去。
  2. 使用了EXPLAIN或缓存：有些查询走了缓存，或者你手动加了SELECT SQL_NO_CACHE以外的优化，导致实际执行时间极短，但前端因为网络或其他环节慢了。这时候我们需要看更底层的指标。

第二步：实时监控——Percona Monitoring and Management (PMM) 或 Prometheus + Grafana

光靠看文本日志是远远不够的，尤其是当并发量大的时候，日志滚动太快，你根本来不及看。这时候，你需要一双“透视眼”，能实时看到数据库的CPU、内存、IO以及连接数变化。

虽然Percona PMM功能强大且免费，但对于很多中小团队来说，搭建起来有点重。我这里推荐一个更轻量、更直观的替代方案思路，或者直接教你怎么看MySQL自带的状态变量。

核心监控指标：你要盯紧哪几个数？

打开你的监控面板（无论是PMM、Grafana还是简单的top命令），重点关注以下三个维度：

  1. QPS/TPS 突增或骤降：

    • QPS（Queries Per Second）每秒查询数。如果QPS突然飙升，可能是出现了热点数据访问；如果骤降，可能是锁等待导致的阻塞。
    • TPS（Transactions Per Second）每秒事务数。对于InnoDB引擎，TPS更能反映真实负载。

  2. Threads Running vs Threads Connected：

    • Threads_connected：当前建立的连接总数。如果这个数字接近max_connections，那肯定炸了。
    • Threads_running：当前正在执行的线程数。这是最关键指标！ 如果Threads_running持续很高（比如超过10-20，具体视CPU核数而定），说明数据库处理不过来，排队的人太多了。

  3. Buffer Pool Hit Rate（缓冲池命中率）：

    • 理想情况下，这个值应该在99%以上。如果低于95%，说明大部分数据都要从磁盘读，IO成为瓶颈。

实战演示：用一条命令快速诊断

如果你没有完善的监控平台，别担心，MySQL自带了一个非常强大的命令：SHOW PROCESSLIST 和 SHOW STATUS。

当你发现网站卡顿时，立刻执行：

SHOW FULL PROCESSLIST;

你会看到一个列表，每一行代表一个正在执行的SQL或等待锁的线程。

如何解读？

  • 看Command列：如果是Query，看Time列。如果某个SQL已经执行了几十秒甚至几分钟，这就是罪魁祸首。
  • 看State列：
    • Sending data：正在读取或发送数据，通常意味着全表扫描或大结果集。
    • Locked：正在等待锁，说明被其他事务阻塞了。
    • Sorting result：正在排序，可能缺少索引。

举个例子： 假设你看到这样一行： Id: 1234 | User: app_user | Host: 192.168.1.10 | db: mydb | Command: Query | Time: 45 | State: Sending data | Info: SELECT * FROM orders WHERE create_time > '2023-01-01'

一眼就能看出，这条SQL已经跑了45秒！而且状态是Sending data，大概率是没有走索引的全表扫描。

第三步：深入骨髓——使用 EXPLAIN 分析慢SQL

找到了慢SQL，接下来就是“解剖”它。EXPLAIN是MySQL提供的最基础也最重要的诊断工具。它能告诉你，MySQL打算怎么执行这条SQL，有没有用到索引，用了哪个索引。

关键指标解读

对EXPLAIN的结果，我们主要看以下几列：

  1. type（访问类型）：

    • 从好到坏依次是：system > const > eq_ref > ref > range > index > ALL。
    • 目标：至少达到range级别，最好能达到ref或更高。
    • 警报：如果看到ALL，那就是全表扫描，必须优化！

  2. key（实际使用的索引）：

    • 如果这里是NULL，说明没用到索引。
    • 如果这里有你预期的索引名，那就是好事。

  3. rows（扫描行数）：

    • 这是一个估算值，表示MySQL认为它需要扫描多少行才能找到结果。
    • 目标：越少越好。如果type是ref，但rows有几百万，那也很糟糕。

  4. Extra（额外信息）：

    • Using filesort：需要额外的排序操作，通常意味着索引设计不合理。
    • Using temporary：使用了临时表，常见于GROUP BY或DISTINCT，性能杀手。
    • Using index：覆盖索引，非常好，不用回表。
    • Using where：在存储引擎层进行了过滤，而不是在服务器层。

代码实战：优化前的样子

假设我们有这样一个慢查询：

SELECT * FROM users WHERE email = 'test@example.com' AND age > 25;

在users表上有100万条数据，且只有主键索引。执行EXPLAIN后，你可能得到：

  • type: ALL
  • key: NULL
  • rows: 1000000
  • Extra: Using where

这说明MySQL要遍历整张表，逐行检查email和age。这能不卡吗？

优化后的样子

我们创建一个联合索引：

ALTER TABLE users ADD INDEX idx_email_age (email, age);

再次执行EXPLAIN，结果变为：

  • type: ref
  • key: idx_email_age
  • rows: 1 (假设email唯一)
  • Extra: Using index condition

看！扫描行数从100万变成了1，速度提升了百万倍。

注意细节： 在联合索引中，最左前缀原则非常重要。如果查询条件中没有包含email，而是只查age，那么这个索引就不会生效，type又会变回ALL。

第四步：揪出幕后黑手——锁等待与死锁

有时候，SQL本身很快，但就是跑不动，因为被锁住了。这种情况在并发高的系统中非常常见。

如何检测锁等待？

在MySQL 5.7及以上版本，我们可以查询information_schema中的视图来获取实时的锁信息。

-- 查看当前正在等待锁的事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看当前的锁等待情况
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看当前持有的锁（更详细的视图，需MySQL 8.0+或使用sys schema）
SELECT * FROM sys.innodb_lock_waits;

简单判断法： 如果你发现某个事务的状态是LOCK WAIT，并且trx_wait_started显示它已经等了很久，那么它就是受害者。你需要找到持有锁的那个事务（通过trx_id关联），然后杀掉那个持有锁的事务，或者让业务方停止相关操作。

死锁排查

死锁是两个或多个事务互相等待对方释放锁，导致谁都无法继续。MySQL会自动检测死锁并回滚其中一个事务。

你可以查看错误日志（error log），里面会有类似这样的记录：

LATEST DETECTED DEADLOCK
------------------------
2023-10-27T10:00:00.000000+08:00 0x7f8b0c000000
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
...
*** WE ROLL BACK TRANSACTION (1)

解决方案：

  1. 缩短事务：尽量让事务里的操作少而精，不要在一个长事务里做大量IO操作或调用外部接口。
  2. 统一锁顺序：如果多个事务都要更新表A和表B，确保它们总是以相同的顺序（比如先A后B）进行操作，这样可以避免循环等待。
  3. 使用SELECT ... FOR UPDATE要小心：只在确实需要排他锁的地方使用，并且尽快提交。

第五步：系统资源瓶颈——CPU、IO和网络

最后，我们要跳出MySQL，看看操作系统层面。有时候，MySQL没问题，是服务器本身扛不住了。

CPU飙高

如果MySQL进程占用CPU很高，通常是计算密集型任务，比如复杂的JOIN、大量的排序（filesort）、或者正则表达式匹配。

排查工具：

  • top -c：查看MySQL进程的CPU占用率。
  • perf top：更深入地分析CPU热点函数。

建议：

  • 检查是否有未索引的查询导致了大量的临时表创建和排序。
  • 考虑升级硬件，或者将读操作分离到从库。

IO瓶颈

如果CPU不高，但IOWait很高，说明磁盘读写成了瓶颈。

排查工具：

  • iostat -x 1：查看磁盘的利用率（%util）和服务时间（await）。如果%util接近100%，且await很大，说明磁盘饱和。
  • iotop：查看是哪个进程在疯狂读写磁盘。

建议：

  • 增加SSD磁盘，提升随机读写能力。
  • 调整innodb_io_capacity参数，告诉MySQL你磁盘的承受能力，让它合理安排刷脏页的速度。
  • 优化SQL，减少不必要的磁盘IO，比如增加Buffer Pool大小，让更多数据留在内存里。

网络连接

如果CPU和IO都正常，但查询依然慢，可能是网络延迟或连接数耗尽。

排查工具：

  • netstat -an | grep :3306 | wc -l：查看当前连接数。
  • tcpdump：抓包分析网络延迟。

建议：

  • 检查应用端的连接池配置，是否频繁创建和销毁连接。
  • 确保MySQL的max_connections设置合理，既不能太小导致拒绝服务，也不能太大导致上下文切换开销过大。

总结：建立你的“体检”习惯

排查MySQL卡顿，其实就像医生看病，讲究望闻问切。

  • 望：看监控大盘，看QPS、TPS、Threads_running。
  • 闻：听慢查询日志，听错误日志里的锁等待和死锁警告。
  • 问：问业务方，最近有没有发版，有没有新增流量。
  • 切：用EXPLAIN和SHOW PROCESSLIST深入内部，切中要害。

不要等到线上出事了才想起来去查。最好的策略是预防。

  1. 开启慢查询日志，并定期review。
  2. 建立监控报警，当Threads_running超过阈值时，立刻发短信或钉钉通知你。
  3. 代码审查，在Code Review阶段，强制要求带上EXPLAIN截图，杜绝无索引查询上线。

记住，数据库优化不是一蹴而就的，它是一个持续的过程。希望这篇指南能成为你工具箱里最锋利的那把手术刀，帮你轻松应对各种卡顿难题。如果你在排查过程中遇到具体的奇怪现象，欢迎随时回来讨论，我们一起“会诊”。

-- 展开阅读全文 --