MySQL死锁与慢查询监控方法及Python监控网页实现

向上的车轮

975人浏览 · 2025-07-15 08:00:00

向上的车轮 · 2025-07-15 08:00:00 发布

在这里插入图片描述

一、最佳监控方法与处理方案

1. 死锁监控与处理

监控方法：

查询INNODB_LOCK_WAITS与INNODB_TRX表
通过以下SQL实时获取锁等待和事务信息：

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;

解析SHOW ENGINE INNODB STATUS输出
定期执行该命令，提取LATEST DETECTED DEADLOCK部分，记录死锁事务的SQL和资源竞争细节。
启用死锁日志
在MySQL配置中设置innodb_print_all_deadlocks = ON，将死锁信息自动记录到错误日志。

处理方案：

自动回滚与重试
在应用层捕获死锁异常（如MySQL错误码1213），回滚事务并重试操作。
优化事务逻辑
统一事务操作顺序，缩短事务执行时间，避免长事务持有锁。
调整隔离级别
将隔离级别从REPEATABLE READ降为READ COMMITTED，减少锁冲突。

在这里插入图片描述

2. 慢查询监控与处理

监控方法：

开启慢查询日志
在MySQL配置中设置：

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1  # 记录执行时间超过1秒的查询
log_queries_not_using_indexes = 1  # 记录未使用索引的查询

使用performance_schema
查询performance_schema.events_statements_summary_by_digest表，统计高频慢查询。
第三方工具分析
使用pt-query-digest解析慢日志，生成优化建议。

处理方案：

索引优化
为频繁查询的字段添加索引，避免全表扫描。
SQL重写
拆分复杂查询，避免子查询和嵌套JOIN。
分库分表
对大表进行水平拆分，减少单表压力。

在这里插入图片描述

二、Python监控网页实现

1. 功能设计

实时展示：显示当前死锁详情、慢查询统计、最新慢SQL。
数据采集：定时查询MySQL并更新数据。
告警通知：当死锁或慢查询超过阈值时触发邮件通知。

2. 代码实现

依赖库：

pip install flask mysql-connector-python apscheduler

后端代码（app.py）

from flask import Flask, jsonify, render_template
import mysql.connector
from apscheduler.schedulers.background import BackgroundScheduler
import smtplib
from email.mime.text import MIMEText

app = Flask(__name__)

# MySQL连接配置
MYSQL_CONFIG = {
    'user': 'root',
    'password': 'password',
    'host': 'localhost',
    'database': 'information_schema'
}

# 死锁与慢查询数据缓存
deadlocks = []
slow_queries = []

# 定时任务：每30秒采集数据
def collect_data():
    # 连接MySQL
    conn = mysql.connector.connect(**MYSQL_CONFIG)
    cursor = conn.cursor(dictionary=True)

    # 采集死锁信息
    cursor.execute("""
        SELECT * FROM INNODB_LOCK_WAITS
        JOIN INNODB_TRX ON INNODB_LOCK_WAITS.blocking_trx_id = INNODB_TRX.trx_id
    """)
    deadlocks = cursor.fetchall()

    # 采集慢查询统计
    cursor.execute("""
        SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT 
        FROM performance_schema.events_statements_summary_by_digest
        ORDER BY SUM_TIMER_WAIT DESC LIMIT 10
    """)
    slow_queries = cursor.fetchall()

    conn.close()

# 路由与模板渲染
@app.route('/')
def index():
    return render_template('monitor.html', deadlocks=deadlocks, slow_queries=slow_queries)

# 启动定时任务
scheduler = BackgroundScheduler()
scheduler.add_job(collect_data, 'interval', seconds=30)
scheduler.start()

if __name__ == '__main__':
    app.run(debug=True, port=5000)

前端模板（templates/monitor.html）

<!DOCTYPE html>
<html>
<head>
    <title>MySQL监控</title>
    <style>
        table { border-collapse: collapse; margin: 20px; }
        th, td { border: 1px solid #ddd; padding: 8px; }
    </style>
</head>
<body>
    <h1>MySQL死锁监控</h1>
    <h2>当前死锁</h2>
    <table>
        <tr><th>等待事务ID</th><th>阻塞事务ID</th><th>SQL语句</th></tr>
        {% for deadlock in deadlocks %}
        <tr>
            <td>{{ deadlock.requesting_trx_id }}</td>
            <td>{{ deadlock.blocking_trx_id }}</td>
            <td>{{ deadlock.blocking_query }}</td>
        </tr>
        {% endfor %}
    </table>

    <h2>高频慢查询</h2>
    <table>
        <tr><th>SQL文本</th><th>执行次数</th><th>总耗时</th></tr>
        {% for query in slow_queries %}
        <tr>
            <td>{{ query.digest_text }}</td>
            <td>{{ query.count_star }}</td>
            <td>{{ query.sum_timer_wait/1e9 }}秒</td>
        </tr>
        {% endfor %}
    </table>
</body>
</html>

3. 扩展功能

邮件告警：在collect_data中添加阈值判断，触发邮件通知。

def send_alert(subject, body):
    msg = MIMEText(body)
    msg['Subject'] = subject
    msg['From'] = 'monitor@example.com'
    msg['To'] = 'admin@example.com'
    with smtplib.SMTP('smtp.example.com') as server:
        server.login('user', 'password')
        server.sendmail('monitor@example.com', ['admin@example.com'], msg.as_string())

历史数据存储：将数据保存到SQLite或InfluxDB，支持趋势分析。

三、部署与使用

启动服务：运行python app.py，访问http://localhost:5000查看监控页。
定时任务：每30秒自动刷新数据，无需手动操作。
告警配置：根据业务需求调整阈值和邮件通知逻辑。

在这里插入图片描述

四、总结

通过上述方法，可实现对MySQL死锁和慢查询的实时监控与自动化处理。Python监控网页提供轻量级可视化界面，结合定时任务与告警机制，显著提升数据库运维效率。

如何避免 MySQL 死锁在我们理解死锁后，有一些事情可以做来消除死锁。
对应用程序进行更改。在某些情况下，通过将长事务拆分为较小的事务，可以大大减少死锁的发生频率，从而更早释放锁。在其他情况下，死锁出现是因为两个事务以不同的顺序接触到相同的数据集，无论是在一个或多个表中。然后更改它们以按相同顺序访问数据；换句话说，序列化访问。这样，当事务同时发生时，你将会有锁等待而不是死锁。
对表模式进行更改，例如删除外键约束以分离两个表，或者添加索引以最小化扫描和锁定的行。
在发生间隙锁定的情况下，可以将事务隔离级别更改为读取已提交，以避免这种情况。但此时，会话或事务的 binlog 格式必须是 ROW 或 MIXED。

点亮灵感，砥砺前行，你的创新改变世界。