印刻数据库

外观

MySQL 慢查询日志分析 ​

慢查询日志配置 ​

启用慢查询日志 ​

临时启用（会话级别） ​

sql
-- 启用慢查询日志
SET SESSION slow_query_log = 'ON';

-- 设置慢查询阈值（单位：秒）
SET SESSION long_query_time = 1;

-- 设置慢查询日志文件路径
SET SESSION slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- 记录未使用索引的查询
SET SESSION log_queries_not_using_indexes = 'ON';

-- 记录管理语句
SET SESSION log_slow_admin_statements = 'ON';

永久启用（全局级别） ​

ini
[mysqld]
# 启用慢查询日志
slow_query_log = 1

# 设置慢查询阈值
long_query_time = 0.5

# 设置慢查询日志文件路径
slow_query_log_file = /var/log/mysql/mysql-slow.log

# 记录未使用索引的查询
log_queries_not_using_indexes = 1

# 记录管理语句
log_slow_admin_statements = 1

# 记录执行时间超过阈值的查询
log_output = FILE

# 限制慢查询日志大小
max_slowlog_size = 100M

# 慢查询日志文件数量
slow_query_log_rotation = 10

配置参数详解 ​

long_query_time ​

  • 查询执行时间阈值
  • 精度可设置到微秒级别
  • 建议根据业务需求调整

log_queries_not_using_indexes ​

  • 记录未使用索引的查询
  • 有助于发现索引缺失问题
  • 可能会产生大量日志，建议谨慎启用

log_slow_admin_statements ​

  • 记录管理语句（如ALTER TABLE、ANALYZE TABLE等）
  • 这些语句可能执行时间较长

min_examined_row_limit ​

  • 只记录扫描行数超过此值的查询
  • 避免记录扫描少量行的查询

慢查询日志格式 ​

日志条目结构 ​

# Time: 2023-01-01T12:00:00.000000Z
# User@Host: root[root] @ localhost []  Id: 12345
# Query_time: 2.500000  Lock_time: 0.100000  Rows_sent: 100  Rows_examined: 10000
SET timestamp=1672574400;
SELECT * FROM users WHERE age > 30 ORDER BY name;

关键字段解析 ​

  • Time：查询执行时间
  • User@Host：执行查询的用户和主机
  • Query_time：查询执行时间（秒）
  • Lock_time：锁等待时间（秒）
  • Rows_sent：返回的行数
  • Rows_examined：扫描的行数
  • SET timestamp：查询执行的时间戳
  • SQL语句：实际执行的SQL语句

日志格式类型 ​

  • FILE：写入文件
  • TABLE：写入mysql.slow_log表
  • FILE,TABLE：同时写入文件和表

慢查询日志分析工具 ​

MySQL自带工具 ​

mysqldumpslow ​

  • MySQL自带的慢查询日志分析工具
  • 简单易用，适合快速分析
  • 支持按不同维度排序

安装 ​

  • 随MySQL一起安装
  • 位于MySQL安装目录的bin目录

使用示例 ​

bash
# 查看帮助
mysqldumpslow --help

# 按查询时间排序，显示前10条
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# 按扫描行数排序，显示前10条
mysqldumpslow -s r -t 10 /var/log/mysql/mysql-slow.log

# 显示使用了全表扫描的查询
mysqldumpslow -s t -t 10 -g "Full scan" /var/log/mysql/mysql-slow.log

# 分析指定数据库的慢查询
mysqldumpslow -s t -t 10 -d test /var/log/mysql/mysql-slow.log

排序选项 ​

  • t：按查询时间排序
  • l：按锁时间排序
  • r：按扫描行数排序
  • a：按平均查询时间排序
  • c：按查询次数排序

Percona Toolkit ​

pt-query-digest ​

  • Percona开发的专业慢查询日志分析工具
  • 功能强大，提供详细的分析报告
  • 支持多种输出格式
  • 可分析实时MySQL流量

安装 ​

bash
# Debian/Ubuntu
sudo apt-get install percona-toolkit

# CentOS/RHEL
sudo yum install percona-toolkit

使用示例 ​

bash
# 分析慢查询日志文件
pt-query-digest /var/log/mysql/mysql-slow.log

# 分析实时MySQL流量
pt-query-digest --processlist h=localhost,u=root,p=password

# 按执行时间排序，显示前20条
pt-query-digest --limit=20 /var/log/mysql/mysql-slow.log

# 将结果输出到文件
pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_report.txt

# 分析指定时间范围内的慢查询
pt-query-digest --since='2023-01-01 00:00:00' --until='2023-01-02 00:00:00' /var/log/mysql/mysql-slow.log

报告解读 ​

  • 总体统计：查询总数、总执行时间、平均执行时间等
  • 响应时间分布：不同执行时间范围的查询分布
  • 用户统计：按用户分组的查询统计
  • 主机统计：按主机分组的查询统计
  • SQL统计：按SQL语句分组的详细统计
  • 执行计划：查询的执行计划分析

其他工具 ​

MySQL Enterprise Monitor ​

  • 企业级监控工具
  • 提供图形化的慢查询分析
  • 实时监控和告警
  • 集成到MySQL Enterprise Edition

MySQL Workbench ​

  • MySQL官方提供的GUI工具
  • 包含性能分析功能
  • 可视化执行计划
  • 适合开发人员使用

第三方工具 ​

  • Neor ProfileSQL：开源的SQL分析工具
  • Jet Profiler：商业SQL性能分析工具
  • EverSQL：在线SQL优化工具

慢查询分析方法 ​

基本分析步骤 ​

  1. 启用慢查询日志
  2. 收集足够的慢查询数据
  3. 使用分析工具处理日志
  4. 识别慢查询模式
  5. 分析执行计划
  6. 实施优化措施
  7. 验证优化效果

关键指标分析 ​

查询执行时间 ​

  • 总执行时间
  • 平均执行时间
  • 执行时间分布

锁等待时间 ​

  • 锁等待时间占比
  • 锁类型分析
  • 锁等待原因

扫描行数与返回行数 ​

  • 扫描行数与返回行数的比例
  • 全表扫描情况
  • 索引使用效率

索引使用情况 ​

  • 未使用索引的查询
  • 索引使用不当的查询
  • 缺失索引的情况

常见慢查询模式 ​

全表扫描 ​

  • 未使用索引
  • WHERE条件不适合使用索引
  • 索引选择性差

多表连接 ​

  • 连接表过多
  • 连接条件未使用索引
  • 连接顺序不合理

复杂子查询 ​

  • 相关子查询
  • 子查询嵌套过深
  • 子查询未优化

排序和分组 ​

  • 大量数据排序
  • ORDER BY未使用索引
  • GROUP BY操作效率低

函数操作 ​

  • 在WHERE条件中使用函数
  • 函数导致索引失效
  • 复杂表达式计算

慢查询优化技巧 ​

索引优化 ​

  • 添加合适的索引
  • 优化复合索引顺序
  • 避免过度索引
  • 定期重建碎片化索引

SQL语句优化 ​

  • 减少SELECT *，只选择需要的列
  • 优化WHERE条件，使用索引友好的表达式
  • 避免在WHERE条件中使用函数
  • 优化JOIN操作，小表驱动大表
  • 分解复杂查询为简单查询

数据库结构优化 ​

  • 合理设计表结构
  • 规范化与反规范化平衡
  • 使用分区表
  • 适当使用视图和存储过程

配置优化 ​

  • 调整内存参数
  • 优化InnoDB缓冲池
  • 调整连接参数
  • 优化查询缓存（MySQL 5.7及以下）

应用层面优化 ​

  • 使用缓存，减少数据库访问
  • 批量操作替代单条操作
  • 异步处理非关键操作
  • 实现读写分离

慢查询监控与管理 ​

实时监控 ​

  • 配置慢查询日志实时分析
  • 使用监控工具实时监控慢查询
  • 设置慢查询告警阈值

定期分析 ​

  • 建立慢查询分析周期
  • 生成定期分析报告
  • 跟踪慢查询趋势

日志管理 ​

  • 配置日志轮转
  • 控制日志大小
  • 归档历史日志
  • 清理过期日志

自动化处理 ​

  • 编写脚本自动分析慢查询
  • 自动识别新出现的慢查询
  • 自动生成优化建议
  • 集成到CI/CD流程

常见问题（FAQ） ​

Q1: 如何确定合适的慢查询阈值？ ​

A1: 确定慢查询阈值应考虑：

  • 业务需求：不同业务对响应时间的要求不同
  • 系统配置：硬件性能影响查询执行时间
  • 高峰期与低谷期：可设置不同的阈值
  • 历史数据：分析历史查询执行时间分布
  • 建议：从小值开始（如0.1秒），根据实际情况调整

Q2: 慢查询日志会影响MySQL性能吗？ ​

A2: 启用慢查询日志会对MySQL性能产生一定影响：

  • 额外的I/O操作：写入日志文件
  • 额外的CPU开销：记录和处理慢查询
  • 影响程度取决于：
    • 慢查询数量
    • 日志文件写入速度
    • 存储设备性能
  • 建议：
    • 在生产环境中合理设置阈值
    • 考虑使用FILE,TABLE双格式
    • 定期轮换和清理日志

Q3: 如何处理大量的慢查询日志？ ​

A3: 处理大量慢查询日志的方法：

  • 启用日志轮转，控制单个文件大小
  • 使用pt-query-digest等工具进行聚合分析
  • 提取关键信息，忽略重复查询
  • 建立慢查询归档策略
  • 考虑使用外部存储系统存储历史日志

Q4: 为什么有些快查询也会出现在慢查询日志中？ ​

A4: 可能的原因：

  • 锁等待时间：查询本身执行快，但等待锁的时间长
  • 系统负载：系统负载高时，查询执行时间变长
  • 缓存失效：首次执行时缓存未命中
  • 统计信息过时：执行计划选择不当
  • 参数设置：某些参数影响查询执行时间

Q5: 如何区分慢查询和正常查询的边界？ ​

A5: 区分方法：

  • 基于业务SLA：根据服务级别协议确定
  • 基于历史数据：分析查询执行时间分布
  • 基于资源使用：考虑CPU、I/O等资源消耗
  • 基于用户体验：考虑用户感知的响应时间
  • 动态调整：根据系统负载和业务情况动态调整阈值