MySQL: 基于INFORMATION_SCHEMA.PROCESSLIST实时捕获慢SQL的性能监控方法

核心原理与实现逻辑

通过 MySQL 的 information_schema.PROCESSLIST 表实时捕获执行时间过长的查询，动态监控性能问题， PROCESSLIST 表的核心价值在于实时展示MySQL服务器的线程执行状态
通过监控 TIME 字段（单位：秒），可精准捕获执行时间超阈值的SQL

通过 MySQL 的 information_schema.PROCESSLIST 表实时捕获执行时间过长的 SQL 语句，替代传统慢查询日志的延迟分析，该表提供当前所有连接的线程状态，可直接筛选执行时间超阈值的活跃查询

以下为优化后的SQL语句：

SELECT 
    ID AS connection_id, 
    USER AS query_user, 
    HOST AS client_host, 
    DB AS current_database, 
    COMMAND AS operation_type, 
    TIME AS execution_duration, 
    STATE AS thread_state, 
    INFO AS sql_statement 
FROM 
    INFORMATION_SCHEMA.PROCESSLIST 
WHERE 
    TIME >= 60  -- 执行时间阈值（秒），生产环境建议调低 
    AND COMMAND NOT IN ('Sleep', 'Binlog Dump')  -- 过滤非活动线程
ORDER BY 
    TIME DESC;  

关键字段解析与技术细节

1. INFO：完整SQL文本，可直接定位问题语句
2. TIME：持续执行时间（秒），超时判断核心指标
3. STATE：线程状态（如 Sending data、Sorting result），揭示阻塞原因
4. COMMAND：操作类型（Query/Execute），区分SQL执行方式
5. 动态阈值调整：
   • 主库建议阈值：5-10秒（WHERE TIME >= 5）
   • 从库/分析库：可放宽至 30-60秒

生产环境部署流程

1 ） 方案1

周期性执行脚本（每 30 秒）  
while true; do  
  mysql -uadmin -p"${PASSWORD}" -e "$MONITOR_SQL" >> /var/log/slow_queries.log  
  sleep 30  
done  

2 ） 方案2

脚本化轮询机制

#!/bin/bash 
慢SQL监控脚本 
MONITOR_INTERVAL=30  # 检测间隔(秒)
LONG_QUERY_TIME=1    # 阈值(秒)
 
while true; do 
  mysql -uadmin -p$PASSWORD -e \
  "SELECT ID,USER,HOST,DB,TIME,INFO 
   FROM information_schema.PROCESSLIST 
   WHERE TIME > $LONG_QUERY_TIME 
     AND INFO IS NOT NULL" \
  >> /var/log/slow_queries.log
  
  sleep $MONITOR_INTERVAL 
done 

NestJS 工程实践

1 ) 自动化监控方案

import { Injectable, Logger } from '@nestjs/common';
import { InjectConnection } from '@nestjs/typeorm';
import { Connection } from 'typeorm';
import { Cron, CronExpression } from '@nestjs/schedule';
 
@Injectable()
export class SlowQueryMonitorService {
  private readonly logger = new Logger(SlowQueryMonitorService.name);
 
  constructor(
    @InjectConnection() private readonly connection: Connection 
  ) {}
 
  @Cron(CronExpression.EVERY_30_SECONDS)  // 每30秒执行一次
  async captureSlowQueries() {
    const threshold = 60;  // 执行时间阈值（秒）
    
    const slowQueries = await this.connection.query(`
      SELECT 
        ID, USER, HOST, DB, 
        COMMAND, TIME, STATE, INFO 
      FROM 
        INFORMATION_SCHEMA.PROCESSLIST 
      WHERE 
        TIME >= ? 
        AND COMMAND != 'Sleep'
        AND USER NOT LIKE '%system%'
      ORDER BY TIME DESC
    `, [threshold]);
 
    if (slowQueries.length > 0) {
      this.logger.warn(`检测到慢查询: ${slowQueries.length}条`);
      slowQueries.forEach(query => {
        this.logger.error(`
          [慢查询告警] 
          连接ID: ${query.ID} 
          用户: ${query.USER}@${query.HOST} 
          数据库: ${query.DB} 
          持续时间: ${query.TIME}秒 
          状态: ${query.STATE || 'N/A'}
          SQL: ${query.INFO}
        `);
      });
      // 此处可集成邮件/钉钉告警 
    }
  }
}

2 ) 方案2

定时监控服务实现

import { Injectable } from '@nestjs/common';  
import { Cron, CronExpression } from '@nestjs/schedule';  
import { InjectRepository } from '@nestjs/typeorm';  
import { Repository } from 'typeorm';  
 
@Injectable()  
export class QueryMonitorService {  
  constructor(  
    @InjectRepository(YourDbEntity)  
    private readonly dbRepo: Repository<YourDbEntity>,  
  ) {}  
 
  @Cron(CronExpression.EVERY_5_MINUTES)  // 每5分钟执行一次  
  async monitorSlowQueries() {  
    const slowQueryThreshold = 60;  // 执行时间阈值（秒）  
    const query = `  
      SELECT  
        ID, USER, HOST, DB, TIME, STATE, INFO  
      FROM information_schema.PROCESSLIST  
      WHERE TIME > ? AND COMMAND = 'Query'  
    `;  
 
    try {  
      const results = await this.dbRepo.query(query, [slowQueryThreshold]);  
      if (results.length > 0) {  
        this.alertAdmin(results);  // 触发告警  
        this.logToFile(results);    // 记录日志  
      }  
    } catch (error) {  
      console.error(`监控失败: ${error.message}`);  
    }  
  }  
 
  private alertAdmin(queries: any[]) {  
    // 实现邮件/短信告警逻辑  
    console.warn('发现慢查询:', JSON.stringify(queries, null, 2));  
  }  
 
  private logToFile(queries: any[]) {  
    // 写入日志文件或ELK系统  
  }  
}  

关键设计：

1. 定时任务：通过 @Cron 注解周期性执行监控。
2. 动态阈值：可根据主库/从库负载调整 slowQueryThreshold 值。
3. 双端处理：发现慢查询时同时触发告警与日志记录。

4 ) 方案4

import { Injectable, Logger } from '@nestjs/common';  
import { setInterval } from 'timers/promises';  
import { Connection } from 'mysql2/promise';  
 
@Injectable()  
export class SqlMonitorService {  
  private readonly logger = new Logger(SqlMonitorService.name);  
 
  constructor(private dbConnection: Connection) {}  
 
  async startMonitoring(intervalMs: number, timeThreshold: number) {  
    for await (const _ of setInterval(intervalMs)) {  
      const slowQueries = await this.detectSlowQueries(timeThreshold);  
      if (slowQueries.length > 0) this.alertAdmin(slowQueries);  
    }  
  }  
 
  private async detectSlowQueries(timeThreshold: number): Promise<any[]> {  
    const [rows] = await this.dbConnection.query(`  
      SELECT ID, USER, HOST, DB, INFO, TIME, STATE  
      FROM information_schema.PROCESSLIST  
      WHERE TIME > ? AND COMMAND NOT IN ('Sleep', 'Binlog Dump')  
    `, [timeThreshold]);  
    return rows;  
  }  
 
  private alertAdmin(queries: any[]) {  
    this.logger.error('SLOW QUERY ALERT!');  
    queries.forEach(query =>  
      this.logger.warn(`[Thread ${query.ID}] ${query.INFO} (${query.TIME}s)`)  
    );  
  }  
}  

功能说明：

• 定时轮询：自定义检测间隔（如 30 秒）和执行时间阈值（如 1 秒）。
• 自动告警：发现慢查询时立即日志告警，包含线程 ID 和原始 SQL。

定时任务实现

import { Injectable } from '@nestjs/common';
import { Cron, CronExpression } from '@nestjs/schedule';
import { Connection } from 'typeorm';
 
@Injectable()
export class QueryMonitorService {
  constructor(private readonly connection: Connection) {}
 
  @Cron(CronExpression.EVERY_30_SECONDS)
  async monitorSlowQueries() {
    const LONG_QUERY_TIME = 1; // 单位：秒 
    
    const results = await this.connection.query(`
      SELECT 
        ID AS connectionId,
        USER AS queryUser,
        TIME AS executionTime,
        SUBSTRING(INFO, 1, 500) AS partialQuery 
      FROM information_schema.PROCESSLIST 
      WHERE 
        TIME > ? 
        AND COMMAND = 'Query'
        AND INFO NOT LIKE '%PROCESSLIST%'`, 
      [LONG_QUERY_TIME]
    );
 
    if (results.length > 0) {
      this.alertService.sendAlert(`
        [SQL性能告警] 发现 ${results.length} 条慢查询
        最大执行时间: ${Math.max(...results.map(r => r.executionTime))}秒 
      `);
    }
  }
}

执行结果分析示例

字段	示例值	说明
connection_id	183	连接会话ID
query_user	app_user@192.168.1.5	发起查询的用户及IP
database_name	orders_db	访问的数据库名
execution_seconds	74	已执行时间（秒）
query_status	Sending data	当前查询状态
sql_statement	SELECT * FROM large_table WHERE ...	完整SQL语句
thread_id	42	线程 ID
db_user	admin	数据库用户
client_host	192.168.1.5:55324	客户端 IP 和端口
database	orders_db	当前数据库
execution_seconds	78	已执行时间（秒）
current_state	Sending data	当前操作状态

诊断重点：

• 高执行时间：execution_seconds > 60 的查询需优先优化
• 阻塞操作：STATE 为 Locked 或 Waiting for table lock 时提示锁竞争
• 大表扫描：INFO 中出现全表扫描（如无索引查询）需紧急处理

关键知识点补充

1 ） PROCESSLIST 表字段详解：

• COMMAND：线程执行命令类型（Query/Execute 表示活跃 SQL）
• STATE：性能诊断核心字段（Copying to tmp table 暗示磁盘临时表问题）

2 ） 性能优化关联技术：

• 索引缺失检测：结合 EXPLAIN 解析 INFO 中的 SQL
• 锁争用分析：当 STATE 为 Waiting for table metadata lock 时，需检查长事务

3 ）生产实践建议：

• 阈值动态调整：

  -- 根据负载自动调整阈值  
  SET @threshold = (SELECT MAX(TIME) FROM PROCESSLIST) * 0.7;  
  

• 集成 APM 工具：将慢查询日志推送至 Prometheus+Grafana 可视化。

通过 PROCESSLIST 实时监控 + 自动化脚本/NestJS 服务，实现 秒级慢 SQL 捕获，精准定位性能瓶颈，避免传统慢日志分析的滞后性

生产环境最佳实践

1 ) 权限配置：监控账号需有 PROCESS 权限

GRANT PROCESS ON *.* TO 'monitor_user'@'%';

2 ) 增强过滤（避免误报）：

WHERE 
  TIME >= 5 
  AND COMMAND NOT IN ('Sleep', 'Binlog Dump')  
  AND INFO NOT LIKE '%PROCESSLIST%'  -- 排除监控自身

结果过滤优化

-- 排除监控程序自身查询 
AND INFO NOT LIKE '%PROCESSLIST%'  
-- 忽略特定管理语句 
AND INFO NOT REGEXP '^(SHOW|EXPLAIN)'

性能数据关联分析

-- 关联性能模式数据(MySQL 5.7+)
SELECT * FROM sys.session 
WHERE time_ms > 1000;

3 ) 锁机制关联分析：

结合 SHOW ENGINE INNODB STATUS 检查行锁等待

4 ) 扩展监控维度：

• 内存消耗：MAX_STATEMENT_TIME（需启用 performance_schema）
• 执行计划：EXPLAIN 实时解析 INFO 字段中的SQL

与传统慢查询日志的对比优势

特性	PROCESSLIST 实时监控	慢查询日志
时效性	秒级捕获	分钟级（需轮询日志）
开销	可控（低频采样）	磁盘I/O持续写入
线程状态可见性	实时展示 STATE 字段	仅记录完成请求
阻塞操作捕获	支持（显示未完成请求）	仅记录已完成请求

5 ) 阈值动态化：

// 根据服务器负载自动调整阈值  
const getDynamicThreshold = () => {  
  const load = os.loadavg()[0];  
  return load > 5 ? 30 : 60;  // 高负载时降低阈值  
};  

阈值动态调整：OLTP系统建议 0.5-2秒，OLAP系统可放宽至 30-60秒

6 ） 索引缺失检测：

在日志分析阶段，对 INFO 中的SQL进行EXPLAIN解析，识别全表扫描语句。

7 ）可视化监控：

集成Prometheus+Grafana，将慢查询数量、平均执行时间纳入监控看板。

8 ）关键结论：

• PROCESSLIST 监控与慢查询日志形成互补，实时性+历史分析双轨制是MySQL性能监控的黄金标准
• 通过 PROCESSLIST 表实现轻量级实时监控，重点把握执行时间、查询状态、SQL内容三个维度，结合自动化脚本快速定位性能瓶颈