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

本文提出了一种基于MySQL PROCESSLIST表的实时慢查询监控方案，通过动态捕获执行时间超阈值的活跃查询，替代传统慢查询日志的延迟分析。方案包含SQL监控脚本实现和NestJS工程实践两套技术路径，支持阈值动态调整与告警集成，实现生产环境下的秒级性能问题发现。核心SQL语句通过筛选TIME字段和执行状态，精准定位问题查询并揭示阻塞原因。NestJS实现方案提供定时监控服务和告警触发机制，满

Johnny丶me

1655人浏览 · 2025-11-19 21:15:00

Johnny丶me · 2025-11-19 21:15:00 发布

核心原理与实现逻辑

通过 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;

关键字段解析与技术细节

INFO：完整SQL文本，可直接定位问题语句
TIME：持续执行时间（秒），超时判断核心指标
STATE：线程状态（如 Sending data、Sorting result），揭示阻塞原因
COMMAND：操作类型（Query/Execute），区分SQL执行方式
动态阈值调整：
- 主库建议阈值：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系统  
  }  
}

关键设计：

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

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内容三个维度，结合自动化脚本快速定位性能瓶颈

松山湖开发者村综合服务平台

助力广东及东莞地区开发者，代码托管、在线学习与竞赛、技术交流与分享、资源共享、职业发展，成为松山湖开发者首选的工作与学习平台

更多推荐

魔珐星云 SDK 实战测评：告别繁复渲染，快速解锁专属数字人应用搭建新体验

松山湖开发者村综合服务平台

OneThingAI 算力云平台：告别本地卡顿，用云 GPU 流畅玩转 ComfyUI

松山湖开发者村综合服务平台

【云原生】Docker 搭建MySql 主从服务实战操作详解

松山湖开发者村综合服务平台

所有评论(0)

查看更多评论

Johnny丶me

@Tyro_java

已为社区贡献2条内容