MySQL慢查询日志详细使用指南

MySQL慢查询日志详细使用指南

一、慢查询日志简介

慢查询日志是MySQL记录执行时间超过指定阈值的SQL语句的日志文件。用于帮助开发者和DBA找出性能瓶颈。

二、开启慢查询日志

1. 临时开启（重启失效）

-- 查看当前慢查询配置SHOWVARIABLESLIKE'%slow_query%';SHOWVARIABLESLIKE'%long_query%';-- 开启慢查询日志SETGLOBALslow_query_log='ON';-- 设置慢查询阈值（单位：秒）SETGLOBALlong_query_time=1;-- 执行超过1秒的SQL-- 设置慢查询日志文件路径SETGLOBALslow_query_log_file='/var/log/mysql/slow.log';-- 记录未使用索引的查询SETGLOBALlog_queries_not_using_indexes='ON';-- 记录管理语句（ALTER TABLE等）SETGLOBALlog_slow_admin_statements='ON';-- 记录慢的从库查询SETGLOBALlog_slow_slave_statements='ON';

2. 永久开启（修改配置文件）

# 编辑MySQL配置文件 my.cnf / my.ini [mysqld] # 开启慢查询日志 slow_query_log = ON # 指定日志文件路径 slow_query_log_file = /var/log/mysql/slow.log # 慢查询阈值（秒） long_query_time = 1 # 记录未使用索引的查询 log_queries_not_using_indexes = ON # 记录管理语句 log_slow_admin_statements = ON # 最小扫描行数阈值 min_examined_row_limit = 100 # 扫描行数超过100才记录 # 重启MySQL服务 service mysql restart # 或 systemctl restart mysqld

3. Docker环境开启

# docker-compose.ymlversion:'3'services: mysql: image: mysql:8.0 command: - --slow_query_log=ON - --slow_query_log_file=/var/log/mysql/slow.log - --long_query_time=1volumes: - ./mysql-slow-logs:/var/log/mysql

三、慢查询日志分析工具

1. 原生mysqldumpslow工具（MySQL自带）

# 查看帮助mysqldumpslow --help# 常用命令示例# 1. 按照平均执行时间排序，显示前10条mysqldumpslow -s at -t10/var/log/mysql/slow.log# 2. 按照总执行时间排序mysqldumpslow -s t /var/log/mysql/slow.log# 3. 按照执行次数排序mysqldumpslow -s c /var/log/mysql/slow.log# 4. 只显示包含特定字符串的查询mysqldumpslow -g"SELECT"/var/log/mysql/slow.log# 5. 详细信息格式mysqldumpslow -a /var/log/mysql/slow.log# 6. 分析多个日志文件mysqldumpslow /var/log/mysql/slow*.log# 7. 输出到文件mysqldumpslow -s at -t20/var/log/mysql/slow.log>slow_report.txt# 参数说明：# -s 排序方式：c(次数) t(时间) l(锁时间) r(返回行数) at(平均时间)# -t 显示前N条# -g 正则匹配# -a 显示完整的SQL语句（不抽象化）# -n 抽象化时至少显示多少位数字# -r 反转排序顺序

2. pt-query-digest（Percona Toolkit）

# 安装# Ubuntu/Debianapt-getinstallpercona-toolkit# CentOS/RHELyuminstallpercona-toolkit# 使用示例# 1. 基本分析pt-query-digest /var/log/mysql/slow.log# 2. 分析并输出报告pt-query-digest /var/log/mysql/slow.log --output slow_report.txt# 3. 分析指定时间段的日志pt-query-digest /var/log/mysql/slow.log --since'2024-01-01'--until'2024-01-31'# 4. 只分析特定数据库pt-query-digest /var/log/mysql/slow.log --filter'($event->{db} || "") =~ m/test_db/'# 5. 分析并发送邮件报告pt-query-digest /var/log/mysql/slow.log --outputsendmail--mail-to=dba@example.com# 6. 持续监控（每30秒分析一次）pt-query-digest /var/log/mysql/slow.log --interval30# 7. 分析binlog中的慢查询mysqlbinlog mysql-bin.000001|pt-query-digest --type=binlog# 8. 分析TCP流量tcpdump -s65535-x -nn -q -tttt -i any port3306>mysql.tcp.txt pt-query-digest --type=tcpdump mysql.tcp.txt# 9. 详细分析报告（JSON格式）pt-query-digest /var/log/mysql/slow.log --output json --no-report|python -m json.tool

3. MySQL内置分析

-- 1. 使用performance_schema分析（MySQL 5.6+）SELECT*FROMperformance_schema.events_statements_summary_by_digestWHERESUM_TIMER_WAIT>1000000000-- 大于1秒ORDERBYSUM_TIMER_WAITDESCLIMIT10;-- 2. 解析慢查询日志到表SETGLOBALslow_query_log=OFF;-- 使用mysqlslowlog工具或自己解析-- 安装mysql-utilitiesmysqlslowlog--slow /var/log/mysql/slow.log --log-output table --create-table --execute-- 3. 查询慢查询统计SELECTdb,query_time_avg,rows_examined_avg,last_seen,ts_cntasexecution_countFROMmysql.slow_logGROUPBYdb,queryORDERBYquery_time_sumDESCLIMIT10;

四、慢查询日志格式解析

日志示例：

# Time: 2024-01-26T10:30:45.123456Z # User@Host: root[root] @ localhost [] Id: 12345 # Query_time: 2.345678 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 1000000 SET timestamp=1706257845; SELECT * FROM users WHERE age > 30 ORDER BY create_time DESC LIMIT 10;

字段说明：

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

五、实战分析案例

案例1：找出最耗时的查询

# 使用pt-query-digest找出Top 10慢查询pt-query-digest /var/log/mysql/slow.log --limit10--report-format=query_report --filter='($event->{fingerprint}) =~ m/^SELECT/'# 输出结果示例：# Rank 1: 0.42 QPS, 12.23 concurrency, 29.1s time, 2.34M rows# Exec time: 29.1s Lock time: 12ms Rows sent: 100 Rows examined: 2.34M# Query: SELECT * FROM orders WHERE user_id=? AND status=?

案例2：分析特定时间段的慢查询

# 分析今天9:00-18:00的慢查询pt-query-digest /var/log/mysql/slow.log\--since'2024-01-26 09:00:00'\--until'2024-01-26 18:00:00'\--output slow_analysis.html# 生成HTML报告openslow_analysis.html

案例3：实时监控慢查询

# 监控最近1分钟的慢查询watch-n10"pt-query-digest /var/log/mysql/slow.log --since '1m ago' --limit 5"# 或使用tail实时查看tail-f /var/log/mysql/slow.log|grep--line-buffered"Query_time"|whilereadline;doecho"[$(date)] Slow query detected:$line"done

六、慢查询日志管理

1. 日志轮转（避免日志文件过大）

# 方法1：使用logrotate# /etc/logrotate.d/mysql-slow/var/log/mysql/slow.log{daily rotate30missingok compress delaycompress notifempty create640mysql mysql postrotate mysql -e"FLUSH SLOW LOGS;"endscript}# 方法2：手动轮转mv/var/log/mysql/slow.log /var/log/mysql/slow.log.$(date+%Y%m%d)mysql -e"FLUSH SLOW LOGS;"gzip/var/log/mysql/slow.log.$(date+%Y%m%d)

2. 清理旧日志

# 删除30天前的慢查询日志find/var/log/mysql/ -name"slow.log.*"-mtime +30 -delete# 或使用pt-query-digest归档pt-query-digest /var/log/mysql/slow.log --output slow_report_$(date+%Y%m%d).txt>/var/log/mysql/slow.log# 清空日志文件

3. 调整日志记录策略

-- 只在业务高峰期间记录-- 使用事件调度器CREATEEVENT adjust_slow_logONSCHEDULE EVERY1DAYSTARTS'2024-01-26 09:00:00'DOBEGIN-- 工作日9:00-18:00开启慢查询IFDAYOFWEEK(NOW())BETWEEN2AND6ANDHOUR(NOW())BETWEEN9AND18THENSETGLOBALslow_query_log='ON';SETGLOBALlong_query_time=1;ELSESETGLOBALslow_query_log='OFF';ENDIF;END;

七、自动化慢查询分析脚本

#!/bin/bash# slow_query_analyzer.shLOGFILE="/var/log/mysql/slow.log"REPORT_DIR="/var/log/mysql/reports"THRESHOLD=5# 只分析超过5秒的查询DAYS_TO_KEEP=30# 创建报告目录mkdir-p$REPORT_DIR# 生成每日报告DATE=$(date+%Y%m%d)REPORT_FILE="$REPORT_DIR/slow_report_$DATE.html"# 使用pt-query-digest生成HTML报告pt-query-digest$LOGFILE\--filter"\$event->{Query_time} >$THRESHOLD"\--output html\>$REPORT_FILE# 发送邮件通知（如果有超过10秒的查询）LONG_QUERIES=$(pt-query-digest $LOGFILE --filter"\$event->{Query_time} > 10"--limit1|wc-l)if[$LONG_QUERIES-gt0];thenecho"发现超过10秒的慢查询，请查看附件"|mail -s"MySQL慢查询警报$(date)"\-a$REPORT_FILE\dba@example.comfi# 轮转日志文件mv$LOGFILE"${LOGFILE}.${DATE}"mysql -e"FLUSH SLOW LOGS;"# 清理旧报告find$REPORT_DIR-name"slow_report_*"-mtime +$DAYS_TO_KEEP-deletefind/var/log/mysql -name"slow.log.*"-mtime +$DAYS_TO_KEEP-delete

八、慢查询优化流程

标准优化流程：

1. 发现慢查询：通过慢查询日志定位
2. 分析原因：使用EXPLAIN分析执行计划
3. 优化方案：添加索引、重写SQL、调整架构
4. 验证效果：对比优化前后性能
5. 监控跟进：持续监控避免回归

优化示例：

-- 原始慢查询SELECT*FROMordersWHEREDATE(create_time)='2024-01-01'ANDuser_id=123ANDstatus=1;-- 优化后SELECT*FROMordersWHEREcreate_time>='2024-01-01 00:00:00'ANDcreate_time<'2024-01-02 00:00:00'ANDuser_id=123ANDstatus=1;-- 添加索引CREATEINDEXidx_user_status_timeONorders(user_id,status,create_time);

九、注意事项

1. 性能影响：开启慢查询日志对I/O有轻微影响
2. 磁盘空间：定期清理日志文件
3. 敏感信息：日志可能包含敏感数据，注意权限管理
4. 生产环境：建议阈值设为1-2秒，开发环境可设为0.1秒
5. 版本差异：MySQL 5.6+支持微秒级精度，之前只支持秒

十、可视化工具推荐

1. Percona Monitoring and Management (PMM)- 免费
2. MySQL Enterprise Monitor- 商业
3. VividCortex- SaaS服务
4. pt-query-digest + Grafana- 自定义仪表板

通过合理使用慢查询日志，可以系统性地发现和解决数据库性能问题，是MySQL性能优化的重要工具。