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

原创 于 2025-11-19 19:15:00 发布 · 849 阅读 · 17 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql

慢查询日志核心机制

核心价值与磁盘开销

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

关键配置参数详解


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

关键控制参数：

  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 的深度分析（含执行计划）可快速定位瓶颈
  • 日志存储分离与轮转策略是避免磁盘问题的关键，动态阈值调整进一步平衡诊断开销与存储成本
MySQL慢查询定位与优化：从日志分析到执行计划全解析
王大师企业官方博客
08-27 1136
本文详细解析了MySQL慢查询的定位与优化方法。首先介绍了慢查询的危害及记录慢查询日志的配置方式，包括临时和永久开启方法。其次讲解了如何通过mysqldumpslow和pt-query-digest等工具分析慢查询日志，重点关注执行时间、锁等待等关键指标。最后深入剖析了EXPLAIN执行计划的解读技巧，包括各项参数含义和典型问题案例分析，帮助开发者系统性地解决SQL性能问题。文章还提供了相关学习教程链接，涵盖Java、Oracle等技术的进阶内容。
优化之旅：深度解析MySQL慢查询日志
08-27
MySQL是一个流行的开源关系型数据库管理系统（RDBMS），广泛用于Web应用程序的后端数据存储。它基于结构化查询语言（SQL）来管理数据，并且是LAMP（Linux, Apache, MySQL, PHP/Python/Perl）技术栈的一部分，这个技术栈常用于构建动态网站和Web应用程序。 MySQL的特点包括： - **开放源代码**：MySQL的源代码是公开的，任何人都可以自由使用和修改。 - **跨平台**：MySQL可以在多种操作系统上运行，包括Linux、Windows、macOS等。 - **高性能**：MySQL以其快速的查询处理和良好的性能而闻名。 - **可靠性**：MySQL提供了多种机制来确保数据的完整性和可靠性，包括事务支持、备份和恢复功能。 - **易于使用**：MySQL提供了简单直观的界面和丰富的文档，便于用户学习和使用。 - **可扩展性**：MySQL支持从小型应用到大型企业级应用的扩展。 - **社区支持**：由于其广泛的使用，MySQL拥有一个活跃的开发者社区，提供大量的资源和支持。 MySQL被广泛应用于各种场景，包括在线事务处理（OL
MySQL(七)--关于慢查询
chen772209的博客
07-19 5549
MySQL(七)–关于慢查询 MySQL服务器处理查询请求的整个过程： 客户端发送SQL强求给服务器 服务器检查是否可以在查询缓存中命中该SQL 服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划 根据执行计划，调用存储引擎API来查询数据 将结果返回给客户端 查询性能低下最基本的原因是访问的数据太多。 一，如何优化慢查询？ 1，首先开启慢查询日志： 如：long_query_time（多长时间视为慢查询） slow_query_log（是否开启了慢查询日志，off表示未开启） slow_qu
MySQL慢查询
最新发布
2301_80866152的博客
09-10 1252
MySQL慢查询是指执行时间超过设定阈值的SQL语句，默认阈值为10秒，但实际业务中需根据场景调整。开启慢查询日志后，可通过mysqldumpslow或pt-query-digest工具分析慢查询日志，找出执行次数多、耗时长的SQL。优化慢查询的核心是减少数据扫描量，主要手段包括：通过explain分析执行计划、添加合适的联合索引、重构SQL语句避免使用select*和or条件、避免在索引字段上使用函数等。对于大数据量表可考虑分表分库，高频查询数据可使用缓存。这些方法能显著提升数据库查询性能。
mysql慢查询
喵酱
11-30 7874
开启慢查询⽇志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数 据库系统的性能。 顾名思义，慢查询⽇志中记录的是执⾏时间较⻓的query，也就是我们常说的slowquery，通过 设–log-slow-queries[=file_name]来打开该功能并设置记录位置和⽂件名。 慢查询⽇志采⽤的是简单的⽂本格式，可以通过各种⽂本编辑器查看其中的内容。 其中记录了语句执⾏的时刻，执 ⾏所消耗的时间，执⾏⽤户，连接主机等相关信息。 MySQL 还提供了专⻔⽤来分
MySQL慢查询及解决方案
zelxnxy的博客
09-04 3522
10）索引一般用于记录比较多的表，假如有表DBInstance，所有查询都有userID条件字段，目前已知该字段已经能够很好的区分记录，即每一个userID下记录数量不多，所以该表只需在userID上建立一个索引即可，即使有使用其他条件字段，由于每一个userID对应的记录数据不多，所以其他字段使用不用索引基本无影响，同时也可以避免建立过多的索引带来的插入和更新的性能开销；当有大量慢查询并且SQL执行得越慢，消耗的CPU资源或IO资源也会越大，因此，要解决和避免这类故障，关注慢查询本身是关键。
MySQL慢查询分析和性能优化
xiaocai
02-10 5897
模拟一个千万级别的雇员表，我们在没有做索引的字段上做一下查询看看，在500W数据中查询一个名叫LsHfFJA的员工，消耗 2.239S ，获取到一条id为4582071的数据。正确理解和计算索引字段的区分度，文中有计算规则，区分度高的索引，可以快速得定位数据，区分度太低，无法有效的利用索引，可能需要扫描大量数据页，和不使用索引没什么差别。正确理解和计算前缀索引的字段长度，文中有判断规则，合适的长度要保证高的区分度和最恰当的索引存储容量，只有达到最佳状态，才是保证高效率的索引。这个趋向于指导性作用。
mysql-slow-log-import-to-elasticsearch:将mysql慢查询日志导入elasticsearch进行分析
05-04
本项目"mysql-slow-log-import-to-elasticsearch"的目标是将MySQL的慢查询日志有效地导入到Elasticsearch，以便于进行深度分析和快速检索。 在实现这个过程中，主要涉及以下几个关键知识点： 1. **MySQL慢查询日志...
mysql：深度解析MySQL慢SQL监控与优化
weixin_43290370的博客
07-29 1181
黄金法则所有线上SQL必须通过EXPLAIN验证慢查询阈值不得超过300ms定期执行更新统计信息工具推荐：全套诊断工具：SaaS监控平台Arctype：SQL性能可视化工具架构级解决方案fill:#333;color:#333;color:#333;fill:none;CQRS事件驱动应用层查询服务命令服务读库集群写库集群全局数据同步慢SQL发生率从15%降至0.3%数据库资源消耗降低60%故障平均定位时间从2小时缩短至5分钟。
MySQL日志分析工具深度解析与性能优化
MySQL 日志分析工具 mysqlsla 是一款功能非常强大的日志分析工具，专门用于分析 MySQL 的慢查询日志（slow query log），帮助数据库管理员和开发人员深入理解数据库的查询行为，识别性能瓶颈，并优化数据库性能。...
三分钟了解MySQL慢查询
Huangjiazhen711的博客
10-18 5320
一、什么是慢查询什么是MySQL慢查询呢？其实就是查询的SQL语句耗费较长的时间。具体耗费多久算慢查询呢？这其实因人而异，有些公司慢查询的阈值是100ms，有些的阈值可能是500ms，即查询的时间超过这个阈值即视为慢查询。正常情况下，MySQL是不会自动开启慢查询的，且如果开启的话默认阈值是10秒二、慢查询的危害既然我们这么关注慢查询，那它肯定是有一些不好的地方，常见的有这几个：1、用户体验差。我们访问一个东西，或者保存一个东西，都得等好久，那不得分分钟弃坑？
sql --- 慢sql定位及优化
谬也的博客
06-15 1万+
慢sql的定位及优化
MySQL慢查询分析
m0_54187478的博客
02-29 3168
在MySQL中，慢查询定义为执行时间超过特定阈值的查询。这个阈值可以通过MySQL的配置选项来设置。默认情况下，的值是10秒，意味着任何执行时间超过10秒的查询都会被认为是慢查询。然而，这个值可以根据具体需求进行调整，以便捕捉更多或更少的查询进行分析。MySQL提供了慢查询日志（Slow Query Log）功能，用于记录那些执行时间超过阈值的查询。通过分析慢查询日志，可以识别出数据库性能瓶颈，进而对SQL查询或数据库索引进行优化。要启用慢查询日志，需要在MySQL的配置文件（通常是my.cnf或。
MySQL性能优化：慢查询优化
热门推荐
weixin_55229531的博客
06-13 5万+
这一点，也是针对联合索引来说的，前面我们反复强调过，所有记录都是按照索引列的值从小到大的顺序排好序的，而联合索引则是按创建索引时的顺序进行分组排序。由于B+树中的数据页和记录是先按insert_time列排序的，所以我们上边的查询过程其实是这样的：找到insert_time值为'2021-03-22 18:23:42' 的记录。找到insert_timee值为'2021-03-22 18:35:00'的记录。
MySQL慢查询的原因与解决方案
weixin_40381772的博客
10-13 6298
在数据库系统中，慢查询是一个常见的问题。特别是在MySQL中，由于其复杂的查询结构和大量的数据，慢查询可能会导致系统性能下降，甚至影响整个应用的运行。本文将详细介绍MySQL慢查询的原因，并提供一些有效的解决方案。
mysql查询缓慢原因和解决方案
忘川
03-16 1万+
查询速度慢的原因很多，常见如下几种：1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷) 2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足 5、网络速度慢 6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量） 7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷) 8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。 9、返回了不必要的行和列 10、查询语句不好，没有优化 可以通过如下方法来优化查询 : 1、把数据
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Wang's Blog

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值