MySQL: 慢查询日志深度解析之配置优化与日志分析实践

慢查询日志核心机制

核心价值与磁盘开销

• MySQL慢查询日志是定位性能问题的低开销方案，主要开销来自磁盘I/O和日志存储空间
• 由于日志采用顺序写入机制，现代磁盘系统的I/O开销可忽略不计
• 核心矛盾在于：繁忙系统可能产生数百GB至TB级日志，需通过参数精准控制记录内容

关键配置参数详解

参数名	默认值	作用说明	优化建议
slow_query_log	OFF	启用/禁用日志功能	生产环境建议ON
slow_query_log_file	空	日志存储路径（默认数据目录）	分离存储路径与数据目录
long_query_time	10秒	SQL执行时间阈值（支持微秒级精度）	建议设为0.001秒(1毫秒)
log_queries_not_using_indexes	OFF	记录未使用索引的查询（无视执行时间）	索引优化关键参数

关键控制参数：

1. slow_query_log：

   • 动态开关参数，默认 OFF。
   • 启用方式：配置文件设置 slow_query_log = ON 或运行时执行 SET GLOBAL slow_query_log = ON;。
   • 建议通过定时脚本控制启停，避免长期占用磁盘空间。

2. slow_query_log_file：

   • 指定日志路径（默认存储在数据目录）。
   • 强烈建议：将日志与数据分盘存储（如 /var/log/mysql/slow.log），避免 I/O 竞争。

3. long_query_time：

   • 执行时间阈值（单位：秒，支持微秒精度）。
   • 默认值 10 秒 不适用生产环境，推荐设为 0.001（即 1 毫秒）。
   • 注意：日志会记录所有符合条件的语句（含查询/修改语句及已回滚的 SQL），用于审计时需谨慎。

4. log_queries_not_using_indexes：

   • 记录未使用索引的查询（即使执行时间未超阈值）。
   • 核心价值：提前发现潜在性能隐患（如当前数据量小但未来可能恶化的查询）。

5. log_output 参数：

   • 支持 FILE（默认）或 TABLE（保存到 mysql.slow_log 表）
   • 表存储优势：支持SQL直接分析

     SELECT * FROM mysql.slow_log WHERE query_time > 5;  
     

命令设置

-- 动态启用慢查询日志并配置阈值  
SET GLOBAL slow_query_log = ON;  
SET GLOBAL long_query_time = 0.001;  
SET GLOBAL log_queries_not_using_indexes = ON;  
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';  

配置示例（my.cnf）：

[mysqld]
slow_query_log = ON 
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.001
log_queries_not_using_indexes = ON

特别注意：

• 日志记录包含已回滚的SQL（审计时需注意）
• 未使用索引的查询即使执行快也会被记录（预防未来性能问题）

索引优化示例：

-- 针对全表扫描的优化  
ALTER TABLE orders ADD INDEX idx_status (status);  
-- 联合索引优化  
ALTER TABLE products ADD INDEX idx_category_price (category_id, price);  

日志内容结构解析

单条日志示例（已格式化）：

1. User@Host: sbtest[sbtest] @  [192.168.1.10]  Id: 17  
2. Query_time: 0.233489  Lock_time: 0.000098  
3. Rows_sent: 1  Rows_examined: 1  
4. Timestamp: 1659320000  
5. SELECT * FROM orders WHERE order_id=100;

字段释义：

1. 用户信息（含线程ID，可用于审计）
2. 执行时间（Query_time）与锁等待时间（Lock_time）
3. 返回行数（Rows_sent）与扫描行数（Rows_examined）
4. UNIX时间戳（精确到秒）
5. 原始SQL语句

对比二进制日志：慢查询日志包含完整用户信息，具备审计能力。

日志分析工具实战

1 ) 官方工具 mysqldumpslow
功能特点：

• 聚合相同模式SQL（忽略条件值差异）
• 支持按执行次数/时间/锁时间/行数排序

使用示例：

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

输出解读：

Count: 1063  Time=0.23s (244s)  Lock=0.00s (0s)  Rows=100 (106300)
  sbtest@[192.168.1.10]
  SELECT * FROM products WHERE category=?

-s t：按总时间排序 | -t 10：显示TOP10

更多排序参数：

• -s c：总执行次数
• -s t：总耗时（-s at 按平均耗时排序）
• -s l：锁等待时间
• -s r：返回行数

2 ) 高级工具 pt-query-digest
核心优势：

• 提供执行计划分析（需--explain参数）
• 输出多维性能指标（响应时间分布、并发度等）

使用示例：

pt-query-digest \
  --explain h=127.0.0.1,u=admin,p=pass \
  /var/log/mysql/slow.log > slow_analysis.txt

报告核心内容：

Query 1: 0x3F7A9C8 (哈希指纹)
Attribute    | Value
------------ | ------
Calls        | 1063 (25.8%)
Total time   | 244s
Rows examined| 106,300
Query pattern: SELECT * FROM products WHERE category=?
 
执行计划 (EXPLAIN输出)
id | select_type | table   | type  | key
---|-------------|---------|-------|-----
1  | SIMPLE      | products| ref   | idx_category

或看另一个的分析报告：

Profile  
Rank Query ID           Response time  Calls R/Call  
==== ================== ============== ===== =======  
1 0xABCDEF123456789  100.234s       63    1.590s  
Query 1: SELECT * FROM orders WHERE status = ?  
Attribute:  
Exec_time   : 100s total, 1.59s avg  
Rows_sent   : 6300 total, 100 avg  
Rows_examine: 630000 total, 10000 avg  
EXPLAIN plan (简略):  
| id | select_type | table  | type  | key     | rows | Extra       |  
| 1  | SIMPLE      | orders | ALL   | NULL    | 10000| Using where |  

• 执行计划解读：type=ALL 表示全表扫描（需优化索引）

关键洞察：

• 响应时间占比：定位最耗资源的 SQL 类型
• 执行计划：直接暴露性能问题（如临时表、文件排序）
• 对比建议：优先使用 pt-query-digest，尤其需分析执行计划时

最佳实践：在从库执行分析避免主库负载，--explain自动关联执行计划

NestJS集成方案

1 ）方案1：实现慢查询监控告警

import { Injectable } from '@nestjs/common';
import { exec } from 'child_process';
 
@Injectable()
export class SlowLogMonitor {
  private readonly LOG_PATH = '/var/log/mysql/slow.log';
 
  // 定时解析日志
  async analyzeLog() {
    exec(`pt-query-digest ${this.LOG_PATH}`, (err, stdout) => {
      if (err) throw new Error(`分析失败: ${err.message}`);
      
      const criticalQueries = this.extractSlowQueries(stdout);
      if (criticalQueries.length > 0) {
        this.sendAlert(criticalQueries);
      }
    });
  }
 
  private extractSlowQueries(report: string): string[] {
    // 实现正则解析TOP5慢查询
    return report.match(/Query_time:\s\d+\.\d+s.*\nSELECT.*?(?=;)/g) || [];
  }
 
  private sendAlert(queries: string[]) {
    // 集成邮件/钉钉告警逻辑
    console.error('慢查询告警:', queries);
  }
}

配套调度配置（使用CronJob）：

import { Cron } from '@nestjs/schedule';
 
export class TasksService {
  constructor(private readonly logMonitor: SlowLogMonitor) {}
 
  @Cron('0 */30 * * * *') // 每30分钟执行
  handleLogAnalysis() {
    this.logMonitor.analyzeLog();
  }
}

2 ）方案2

自动记录慢查询至独立表

步骤一： 创建慢查询存储表：

CREATE TABLE slow_query_log (  
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  
  query TEXT NOT NULL,  
  duration DECIMAL(10,6) NOT NULL,  
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
);  

步骤二：NestJS 拦截器实现（TypeORM）：

import { Injectable, NestInterceptor, ExecutionContext, CallHandler } from '@nestjs/common';  
import { Observable } from 'rxjs';  
import { tap } from 'rxjs/operators';  
import { InjectRepository } from '@nestjs/typeorm';  
import { Repository } from 'typeorm';  
import { SlowQueryLog } from './slow-query.entity';  
 
@Injectable()  
export class SlowQueryInterceptor implements NestInterceptor {  
  constructor(  
    @InjectRepository(SlowQueryLog)  
    private readonly slowQueryRepo: Repository<SlowQueryLog>,  
  ) {}  
 
  intercept(context: ExecutionContext, next: CallHandler): Observable<any> {  
    const start = Date.now();  
    return next.handle().pipe(  
      tap(() => {  
        const duration = Date.now() - start;  
        if (duration > 100) { // 阈值 100ms  
          const request = context.switchToHttp().getRequest();  
          this.slowQueryRepo.save({  
            query: request.body?.query || request.params,  
            duration,  
          });  
        }  
      }),  
    );  
  }  
}  

3 ）方案3

动态阈值调整脚本（NestJS 示例）

import { exec } from 'child_process';  
// 高峰期启用日志（如每日20:00-22:00）  
setInterval(() => {  
 const hour = new Date().getHours();  
 if (hour >= 20 && hour < 22) {  
   exec('mysql -u root -pPASSWORD -e "SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=0.001;"');  
 } else {  
   exec('mysql -u root -pPASSWORD -e "SET GLOBAL slow_query_log=OFF;"');  
 }  
}, 60_000); // 每分钟检查  

60_000 是 ES2021 引入的 数字分隔符语法（等价于 60000）
下划线 _ 仅用于提升可读性（如区分 60000 和 60_000），不影响数值

或

import { exec } from 'child_process';
import * as cron from 'node-cron';
 
// 使用 cron 表达式精准控制时段（每天 20:00-22:00）
cron.schedule('0  20-22 * * *', () => {
  exec('mysql -u root -p$DB_PASSWORD -e "SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=0.001;"');
});
 
// 每天 22:00 后关闭
cron.schedule('0  22 * * *', () => {
  exec('mysql -u root -p$DB_PASSWORD -e "SET GLOBAL slow_query_log=OFF;"');
});

• 通过 node-cron 按时间点触发，避免每分钟轮询
• 密码从环境变量 $DB_PASSWORD 读取，避免硬编码

关键技术细节补充

1 ) 阈值动态调整技巧

-- 临时修改阈值（重启失效）
SET GLOBAL long_query_time = 0.005; 

-- 持久化修改（需重启）
SET PERSIST long_query_time = 0.005;

2 ) 日志轮转配置

# /etc/logrotate.d/mysql-slow 
/var/lib/mysql/slow.log  {
    daily                      # 每天轮转一次 
    rotate 7                   # 保留最近7次切割的日志 
    compress                   # 启用gzip压缩旧日志
    delaycompress              # 延迟压缩（下次轮转时压缩本次文件）
    missingok                  # 日志不存在时不报错
    notifempty                 # 空日志不轮转（避免无效操作）
    dateext                    # 为切割文件添加日期后缀（如 .slow.log-20251118 ）
    create 640 mysql mysql     # 新日志权限及属主（需与MySQL进程用户一致）
    postrotate                 # 轮转后通知MySQL重新打开日志
        /usr/bin/mysqladmin -uroot -p密码 flush-logs 
    endscript 
}

日志轮转：使用 logrotate 分割日志，避免单文件过大

3 ) 缺失索引检测SQL

SELECT 
  query_time, 
  sql_text 
FROM mysql.slow_log 
WHERE sql_text NOT REGEXP 'USE INDEX|FORCE INDEX'
  AND query_time > 0.01;

4 ）生产建议

• 仅采样记录：通过 SET GLOBAL long_query_time 动态调整阈值，高峰期间歇性开启
• 避免全量审计：改用专用审计插件（如 audit_log）
• 结合监控：与 Prometheus + Grafana 集成，实时报警慢查询激增

总结：慢查询日志是 MySQL 性能调优的基石，需合理配置参数、选择高效工具链，并通过应用层集成实现闭环优化，重点在于 平衡诊断深度与存储开销，确保可持续的性能监控机制

总结

• 慢查询日志是MySQL性能优化的基石工具，通过精准配置 long_query_time 和 log_queries_not_using_indexes 捕捉低效SQL
• 结合 pt-query-digest 的深度分析（含执行计划）可快速定位瓶颈
• 日志存储分离与轮转策略是避免磁盘问题的关键，动态阈值调整进一步平衡诊断开销与存储成本