深入解析MySQL慢查询日志：设置、分析与优化实践

一、引言：慢查询日志的核心价值

MySQL慢查询日志是数据库性能调优的”黑匣子”，通过记录执行时间超过阈值的SQL语句，帮助开发者：

1. 精准定位性能瓶颈
2. 量化分析查询效率
3. 验证优化措施效果
4. 建立长期性能监控体系

典型应用场景包括：新系统上线前的性能基准测试、业务高峰期的异常查询排查、数据库架构升级前的性能评估等。根据Percona的调研数据，62%的数据库性能问题可通过慢查询分析解决。

二、慢查询日志设置详解

1. 参数配置

关键配置项位于my.cnf/my.ini文件：

[mysqld]
slow_query_log = ON          # 启用慢查询日志
slow_query_log_file = /var/log/mysql/mysql-slow.log  # 日志路径
long_query_time = 2          # 慢查询阈值(秒)
log_queries_not_using_indexes = ON  # 记录未使用索引的查询
min_examined_row_limit = 100 # 最小扫描行数阈值

优化建议：

• 生产环境建议long_query_time初始设为1-2秒
• 测试环境可设为0秒捕获所有查询
• 日志文件建议按日期分割(使用logrotate)

2. 动态配置方法

MySQL 5.7+支持运行时修改：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1.5;  -- 立即生效(新会话)
FLUSH SLOW LOGS;  -- 重新打开日志文件

3. 特殊场景配置

• 高并发环境：建议配合log_throttle_queries_not_using_indexes限制日志量
• 云数据库：需确认云服务商是否支持自定义路径(如AWS RDS需通过参数组配置)
• 容器化部署：建议将日志挂载到持久化卷

三、慢查询日志分析方法论

1. 基础分析工具

mysqldumpslow工具使用示例：

# 按总耗时排序(前10条)
mysqldumpslow -s t /var/log/mysql/mysql-slow.log | head -10
# 按锁定时间排序
mysqldumpslow -s l /var/log/mysql/mysql-slow.log
# 生成统计报告
mysqldumpslow -g "SELECT" /var/log/mysql/mysql-slow.log > report.txt

pt-query-digest高级分析：

pt-query-digest --review h=localhost,D=performance_schema,t=events_statements_summary_by_digest \
  /var/log/mysql/mysql-slow.log > digest_report.txt

2. 关键指标解读

• Query_time：总执行时间(含网络传输)
• Lock_time：等待表锁的时间
• Rows_sent：返回客户端的行数
• Rows_examined：扫描的行数(关键优化指标)
• Tmp_tables：临时表创建次数

3. 典型问题模式识别

• 全表扫描：Rows_examined >> Rows_sent
• 索引失效：type=ALL且key=NULL(EXPLAIN输出)
• 排序问题：Using filesort出现在大结果集
• 锁竞争：Lock_time占比过高

四、慢查询优化实战

1. 索引优化策略

案例分析：

-- 优化前(慢查询日志记录)
SELECT * FROM orders WHERE customer_id = 123 AND status = 'completed' ORDER BY order_date DESC;
-- 执行计划显示：type=ALL, Using where; Using filesort
-- 优化方案
ALTER TABLE orders ADD INDEX idx_cust_status_date (customer_id, status, order_date);
-- 优化后：type=range, Using index condition

索引设计原则：

1. 遵循最左前缀原则
2. 高选择性列放前
3. 包含排序/分组字段
4. 避免过度索引(写入性能影响)

2. SQL改写技巧

常见优化模式：

• 避免SELECT *：明确指定字段
• 拆分复杂查询：将多表JOIN拆为多个简单查询
• 使用覆盖索引：确保查询字段都在索引中
• 优化子查询：改写为JOIN或EXISTS

示例：

-- 优化前
SELECT a.*, b.name FROM table_a a 
JOIN table_b b ON a.id = b.a_id 
WHERE b.create_time > '2023-01-01';
-- 优化后(覆盖索引)
SELECT a.id, a.col1, a.col2, b.name 
FROM table_a a 
JOIN (SELECT a_id, name FROM table_b WHERE create_time > '2023-01-01') b 
ON a.id = b.a_id;

3. 数据库配置优化

关键参数调整建议：

[mysqld]
innodb_buffer_pool_size = 70%总内存  # 典型值
query_cache_size = 0                 # MySQL 8.0已移除
tmp_table_size = 32M                # 临时表内存阈值
max_heap_table_size = 32M           # 内存表最大值

五、高级监控方案

1. Performance Schema集成

启用关键监控项：

-- 开启语句摘要
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_statements%';
-- 创建慢查询监控视图
CREATE VIEW slow_queries_summary AS
SELECT digest_text, schema_name, count_star AS total_executions,
       sum_timer_wait/1000000000000 AS total_latency_sec,
       sum_rows_sent AS total_rows_sent,
       sum_rows_examined AS total_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE sum_timer_wait > 1000000000000  -- 过滤慢查询
ORDER BY sum_timer_wait DESC;

2. 实时告警机制

基于Prometheus+Grafana的监控方案：

1. 配置Node Exporter采集MySQL指标
2. 创建告警规则：
   ```yaml
   groups:

• name: mysql.slow-queries
  rules:
  • alert: HighSlowQueries
    expr: rate(mysql_global_status_slow_queries[5m]) > 5
    for: 10m
    labels:
    severity: warning
    annotations:
    summary: “High rate of slow queries ({{ $value }}/s)”
    ```

3. 自动化分析流程

Python分析脚本示例：

import re
from collections import defaultdict
def analyze_slow_log(log_path):
    query_stats = defaultdict(lambda: {'count': 0, 'total_time': 0})
    with open(log_path, 'r') as f:
        current_query = None
        for line in f:
            if line.startswith('# Query_time:'):
                parts = line.split()
                time = float(parts[2].split(':')[1])
                query_stats[current_query]['total_time'] += time
            elif line.startswith('# Time:') or line.startswith('# User@Host:'):
                continue
            elif line.strip() and not line.startswith('#'):
                current_query = line.strip()
                query_stats[current_query]['count'] += 1
    # 按总耗时排序
    sorted_queries = sorted(query_stats.items(), 
                           key=lambda x: x[1]['total_time'], 
                           reverse=True)
    return sorted_queries[:10]  # 返回TOP10慢查询

六、最佳实践总结

1. 分级监控策略：

   • 开发环境：long_query_time=0，全面监控
   • 测试环境：long_query_time=0.5，验证优化效果
   • 生产环境：long_query_time=1-2，重点监控

2. 优化闭环管理：

   graph TD
   A[捕获慢查询] --> B[分析执行计划]
   B --> C{可优化?}
   C -->|是| D[实施优化]
   C -->|否| E[扩容或架构调整]
   D --> F[验证效果]
   F --> B

3. 持续改进机制：

   • 每周生成慢查询趋势报告
   • 每月进行索引合理性审查
   • 每季度评估数据库架构

七、常见问题解答

Q1：为什么设置了long_query_time但日志未记录？
A：需同时满足三个条件：

1. slow_query_log=ON
2. 查询执行时间>long_query_time
3. 查询不是内部命令(如COM_PING)

Q2：如何分析历史慢查询数据？
A：建议使用pt-query-digest的--since和--until参数：

pt-query-digest --since "2023-10-01 00:00:00" \
               --until "2023-10-02 00:00:00" \
               /var/log/mysql/mysql-slow.log

Q3：云数据库如何获取慢查询日志？
A：不同云服务商方案：

• AWS RDS：通过参数组启用，日志存入CloudWatch
• 阿里云RDS：控制台下载或API获取
• 腾讯云CDB：日志服务集成

八、结语

MySQL慢查询日志分析是数据库性能优化的核心环节。通过科学设置日志参数、系统化分析查询模式、针对性实施优化措施，可显著提升数据库性能。建议开发者建立”设置-监控-分析-优化-验证”的完整闭环，将慢查询分析纳入日常运维流程，实现数据库性能的持续优化。

实际案例显示，某电商系统通过实施本文介绍的优化方案，将平均查询响应时间从2.3秒降至0.8秒，TPS提升40%，充分验证了慢查询日志分析的价值。建议读者结合自身业务特点，灵活应用本文介绍的方法和工具。