正文

MySQL数据库卡顿怎么办用这些工具实时监控找出慢查询和锁等待问题让业务不再因数据库性能差而崩溃

/0 浏览量
1009

说实话，数据库卡顿就像你平时开车感觉油门踩下去没反应，或者前方突然堵车动不了。这种时候，如果你没有一张实时的“仪表盘”，那就只能瞎猜：是网络堵了？是代码写烂了？还是DBA没吃饭？

别慌，今天咱们就把这层窗户纸捅破。我会带你像侦探一样，一步步揪出那个让业务崩溃的“幕后黑手”——慢查询和锁等待。

先别急着重启，看看“仪表盘”亮没亮

很多小伙伴遇到卡顿时，第一反应是重启MySQL服务。大哥，那叫治标不治本，下次还要卡。真正的高手，先看数据。

MySQL里有一张非常重要的视图，叫performance_schema。你可以把它想象成MySQL的行车记录仪，它记录了每一次查询、每一个锁、每一次上下文切换。但是！默认情况下，这个记录仪可能没开，或者没记录关键画面。

所以，第一步，我们要确保这些监控项是开启的。

-- 检查performance_schema是否启用
SHOW VARIABLES LIKE 'performance_schema';

-- 如果为OFF，需要在my.cnf配置文件中添加（重启生效）
-- performance_schema = ON

接下来，我们得看看哪些事件是正在被记录的。你可以把这看作是调出监控面板的开关。

-- 查看当前的事件收集状态
SELECT EVENT_NAME, COUNT_READ, COUNT_WRITE 
FROM performance_schema.setup_instruments 
WHERE ENABLED = 'YES' 
ORDER BY COUNT_READ DESC 
LIMIT 10;

如果看到statements和locks相关的仪器是YES，那就恭喜，你的记录仪已经开工了。如果全是NO，那你还得去配置里把它们打开。

揪出“慢动作”主角：慢查询

卡顿最常见的原因，就是有查询在“磨洋工”。MySQL自带了一个慢查询日志功能，但它有个毛病：它是事后诸葛亮，而且日志文件太大不好查。

这时候，我们需要请出两个神器：sys模式和performance_schema的组合拳。

1. 利用sys schema快速定位

MySQL 5.7+版本默认会创建一个sys库，里面有一堆预定义的视图，专门给DBA和开发用来查问题的，比直接查performance_schema直观一万倍。

查看最近执行最慢的Top SQL：

-- 找出执行时间最长的前10条SQL
SELECT 
    DIGEST_TEXT AS `查询语句摘要`,
    COUNT_STAR AS `执行次数`,
    ROUND(AVG_TIMER_WAIT/1000000000000, 2) AS `平均耗时(ms)`,
    ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS `总耗时(ms)`,
    FIRST_SEEN AS `首次出现时间`,
    LAST_SEEN AS `最后出现时间`
FROM sys.statements_with_runtimes_in_95th_percentile
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

这里有个小细节：DIGEST_TEXT是把SQL标准化后的结果，比如SELECT * FROM users WHERE id = ?，这样你能看出是哪类SQL慢，而不是某一条具体的SQL。

2. 实时查看正在执行的SQL

有时候慢查询已经不在了，但业务还是卡，因为可能还有查询正在跑。这时候看PROCESSLIST太落后了，我们要看sys.ps_active_statement。

-- 查看当前正在执行的、耗时超过1秒的语句
SELECT 
    db AS `数据库`,
    user AS `用户`,
    state AS `当前状态`,
    time AS `已执行时长(秒)`,
    current_db AS `当前库`,
    info AS `SQL内容`
FROM sys.ps_active_statement
WHERE time > 1
ORDER BY time DESC;

这个视图非常有用！它能告诉你，现在谁在占着资源不释放。如果看到Waiting for table lock或者Sending data，那问题基本就锁定了。

捉拿“拦路虎”：锁等待

如果说慢查询是“跑得太慢”，那锁等待就是“被堵在路上”。这是导致业务瞬时卡顿甚至超时的主要原因。

比如，一个大事务正在更新表A，你的业务代码也在请求更新表A，那你就要排队等待。如果这个事务很大，或者根本没提交，你的业务就卡死了。

如何发现锁等待？

方法一：看sys.schema_table_lock_waits

这个视图专门告诉你，谁在等锁，谁在持有锁。

-- 查看当前的锁等待情况
SELECT 
    object_schema AS `表所在的库`,
    object_name AS `表名`,
    requesting_processlist_id AS `等待锁的线程ID`,
    blocking_processlist_id AS `持有锁的线程ID`,
    COUNT(*) AS `等待次数`
FROM sys.schema_table_lock_waits
GROUP BY object_schema, object_name, requesting_processlist_id, blocking_processlist_id;

看到输出结果别慌，重点看blocking_processlist_id。这个ID对应的线程，就是罪魁祸首。

方法二：深挖阻塞源头

知道了谁在持锁，接下来要看看这个持锁的线程在干嘛。它可能是在跑一个巨大的UPDATE，或者是一个没提交的事务。

-- 查看阻塞者（blocking thread）的详细信息
SELECT 
    p.ID AS `线程ID`,
    p.USER AS `用户`,
    p.HOST AS `主机`,
    p.DB AS `数据库`,
    p.TIME AS `持续时间(秒)`,
    p.STATE AS `当前状态`,
    p.INFO AS `正在执行的SQL`
FROM information_schema.processlist p
JOIN sys.schema_table_lock_waits l 
ON p.ID = l.blocking_processlist_id;

这时候，你可能会发现，那个blocking_processlist_id对应的SQL是一个全表扫描的大更新，或者是一个开了事务但很久没提交的代码。

方法三：使用performance_schema.events_statements_current

如果你需要更底层的细节，比如锁的具体类型（读锁还是写锁），可以看这个表。

SELECT 
    THREAD_ID,
    EVENT_NAME,
    SQL_TEXT,
    TIMER_WAIT
FROM performance_schema.events_statements_current
WHERE THREAD_ID IN (
    SELECT blocking_thread_id 
    FROM performance_schema.metadata_locks
    WHERE OBJECT_TYPE = 'TABLE'
);

注意：metadata_locks是MySQL 5.5+引入的，用于跟踪元数据锁。如果看到大量的MDL锁等待，那可能是有人在ALTER表，或者DML操作阻塞了DDL。

除了查，我们还要能“断”

找到问题了，如果业务已经因此崩溃，我们得有办法快速止血。

紧急处理锁等待

如果确认某个线程是罪魁祸首，并且业务已经不可用，最直接的办法就是KILL掉它。

-- 假设blocking_processlist_id是12345
KILL 12345;

但是，杀线程有风险！如果这个大事务正在处理重要数据，杀掉可能导致数据不一致。所以，先看看它在干什么（SELECT上面的p.INFO），确认是无害的或者可以回滚的，再动手。

优化慢查询

找到慢SQL后，不能光靠KILL，还得根治。

  1. 加索引：大多数慢查询都是因为没走索引。用EXPLAIN分析一下你的SQL，看type是不是ALL（全表扫描）。
  2. 改写SQL：有时候SQL写得太复杂，MySQL优化器也头疼。试试简化查询，或者分步执行。
  3. 调整参数：比如innodb_buffer_pool_size够不够大？缓存命中率如何？
-- 查看缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

如果Reads远小于Read requests，说明缓冲池命中率低，需要增大innodb_buffer_pool_size。

实时监控：让问题无处遁形

上面的方法都是“事后”查看。如果要做到“事前”预警，我们需要实时监控。

工具推荐

  1. Percona Monitoring and Management (PMM)

    • 这是开源界的神器，基于Prometheus和Grafana。
    • 它可以实时监控QPS、TPS、连接数、锁等待、慢查询等。
    • 配置简单，界面友好，还能展示SQL的详细执行计划。
    • 建议：生产环境必备。

  2. MySQL Enterprise Monitor

    • 官方工具，功能强大，但收费。
    • 适合大企业，对MySQL底层支持最好。

  3. 简单的自定义脚本 + 告警

    • 如果你不想部署复杂系统，可以写个简单的Python脚本，每10秒查询一次sys.schema_table_lock_waits和sys.statements_with_runtimes_in_95th_percentile。
    • 如果检测到有锁等待超过5秒，或者有慢查询超过10秒，就发钉钉/企业微信告警。
import pymysql
import time
import requests

def check_locks():
    conn = pymysql.connect(host='127.0.0.1', user='root', password='your_password', db='sys')
    cursor = conn.cursor()
    
    # 查询是否有超过5秒的锁等待
    sql = """
    SELECT COUNT(*) 
    FROM sys.schema_table_lock_waits l
    JOIN information_schema.processlist p ON l.blocking_processlist_id = p.ID
    WHERE p.TIME > 5
    """
    cursor.execute(sql)
    count = cursor.fetchone()[0]
    
    if count > 0:
        send_alert(f"发现{count}个超过5秒的锁等待！")
    
    conn.close()

def check_slow_queries():
    conn = pymysql.connect(host='127.0.0.1', user='root', password='your_password', db='sys')
    cursor = conn.cursor()
    
    # 查询是否有超过10秒的慢查询
    sql = """
    SELECT COUNT(*) 
    FROM sys.statements_with_runtimes_in_95th_percentile
    WHERE avg_timer > 10000000000  # 10秒
    """
    cursor.execute(sql)
    count = cursor.fetchone()[0]
    
    if count > 0:
        send_alert(f"发现{count}个超过10秒的慢查询！")
    
    conn.close()

def send_alert(msg):
    # 这里替换成你的钉钉/企业微信机器人Webhook
    url = "https://oapi.dingtalk.com/robot/send?access_token=xxxx"
    data = {"msgtype": "text", "text": {"content": msg}}
    requests.post(url, json=data)

while True:
    check_locks()
    check_slow_queries()
    time.sleep(10)

真实案例：一次“假死”事故的分析

记得去年，一家电商公司的客服系统突然卡死，用户反馈订单查不到。开发同学一开始以为是代码Bug，查了半天没发现问题。

我们介入后，第一步就是看sys.schema_table_lock_waits。结果发现，有30多个线程在等待同一个表的锁。顺着blocking_processlist_id找过去，发现是一个DBA在执行一个ALTER TABLE操作，想给订单表加一个索引。

这个ALTER操作锁住了整张表，导致所有写入和读取都被阻塞。而更糟糕的是，这个操作是在业务高峰期执行的！

教训：

  1. DDL操作要在低峰期进行，并且使用pt-online-schema-change这样的工具，避免锁表。
  2. 实时监控要覆盖DDL操作，PMM里可以配置告警，当有大事务或长时间运行的ALTER时通知DBA。
  3. 业务监控要和数据库监控联动，客服系统卡死时，如果数据库监控能第一时间报警，就能缩短排查时间。

总结：建立你的“防御体系”

对付MySQL卡顿，不能靠猜，要靠数据。

  1. 开启并配置好performance_schema，这是所有监控的基础。
  2. 善用sys schema的视图，它们把复杂的performance_schema表变成了人话。
  3. 重点关注ps_active_statement和schema_table_lock_waits，这两个视图能解决80%的卡顿问题。
  4. 部署PMM等监控工具，实现可视化实时监控和告警。
  5. 制定应急预案，知道什么时候该KILL线程，什么时候该优化SQL。

记住，数据库性能优化不是一次性的工作，而是一个持续监控、持续优化的过程。就像照顾一辆车，定期保养，随时关注仪表盘，才能确保它在高速公路上跑得又快又稳。

希望这篇文章能帮你建立起一套完整的MySQL监控和故障排查体系。如果还有具体问题，欢迎随时交流，咱们一起把数据库的性能摸透！

-- 展开阅读全文 --