Java+MySQL监控自身 打造数据库SQL性能分析与慢查询日志系统

Java+MySQL监控系统的实现方案

使用JDBC连接MySQL并获取性能数据
通过Java的JDBC API连接MySQL数据库，执行SHOW STATUS和SHOW VARIABLES命令获取关键性能指标。建立定期采集机制，将数据存储到本地数据库或文件中。

// 示例：获取MySQL状态变量
Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SHOW GLOBAL STATUS");
while(rs.next()) {
    String variableName = rs.getString(1);
    String value = rs.getString(2);
    // 存储或处理数据
}

实现慢查询日志分析模块
配置MySQL的慢查询日志参数，启用日志记录：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 超过2秒的查询
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

通过Java解析慢查询日志文件，使用正则表达式提取关键信息：

Pattern pattern = Pattern.compile("Query_time: (\\d+\\.\\d+).*Lock_time: (\\d+\\.\\d+).*Rows_sent: (\\d+).*Rows_examined: (\\d+)");
Matcher matcher = pattern.matcher(logLine);
if(matcher.find()) {
    double queryTime = Double.parseDouble(matcher.group(1));
    // 分析处理
}

构建性能监控仪表盘
将采集的数据通过Spring Boot暴露为REST API，前端使用ECharts或类似库可视化：

• 查询响应时间趋势图
• 锁等待时间统计
• 慢查询TOP10排名
• 数据库连接数监控

关键优化指标监控
重点关注以下MySQL性能指标：

• 线程缓存命中率：Threads_created/Connections
• 查询缓存命中率：Qcache_hits/(Qcache_hits+Com_select)
• Innodb缓冲池命中率：(1-innodb_buffer_pool_reads/innodb_buffer_pool_read_requests)*100
• 临时表创建率：Created_tmp_disk_tables/Created_tmp_tables

预警机制实现
设置阈值触发邮件或短信报警：

if(queryTime > threshold) {
    SimpleMailMessage message = new SimpleMailMessage();
    message.setTo("admin@example.com");
    message.setSubject("慢查询警报");
    message.setText("发现执行时间超过"+threshold+"秒的查询");
    mailSender.send(message);
}

系统架构设计要点

数据采集层

• 定时任务调度：使用Quartz或Spring Scheduler
• 多数据源支持：可监控多个MySQL实例
• 连接池管理：HikariCP或Druid

数据分析层

• 慢查询指纹生成：去除参数化差异
• 执行计划分析：对高频慢查询进行EXPLAIN
• 索引建议：基于WHERE条件和JOIN类型

存储方案选择

• 近期数据：MySQL监控库存储
• 历史数据：Elasticsearch归档
• 统计汇总：时序数据库InfluxDB

部署注意事项

• 监控系统自身资源消耗需低于5%
• 采集频率设置：生产环境建议1-5分钟
• 日志轮转策略：避免磁盘空间耗尽
• 权限控制：只读账号访问生产数据库

通过以上方案可实现完整的MySQL性能监控系统，需根据实际业务规模调整各模块实现细节。对于大型分布式环境，建议考虑Prometheus+Grafana等成熟方案进行扩展。