MySQL 慢查询日志分析：pt-query-digest 工具详解
什么是慢查询日志
MySQL慢查询日志是MySQL提供的一种日志记录功能，用来记录执行时间超过指定阈值的SQL语句。通过分析慢查询日志，我们可以找出数据库中执行效率低下的SQL语句，从而进行针对性的优化。
慢查询日志的主要作用包括：
- 发现执行效率低下的SQL语句
- 找出需要优化的查询
- 监控数据库性能问题
- 定位系统瓶颈
- 追踪数据库性能变化趋势
- 识别未使用索引的查询
启用慢查询日志
在MySQL配置文件中(my.cnf或my.ini)添加以下配置：
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
min_examined_row_limit = 100
配置说明：
slow_query_log: 1表示启用慢查询日志，0表示禁用slow_query_log_file: 指定慢查询日志文件路径long_query_time: 定义慢查询的阈值，单位为秒（支持毫秒级精度，如0.1表示100毫秒）log_queries_not_using_indexes: 记录未使用索引的查询log_slow_admin_statements: 记录管理类慢查询（如ALTER TABLE, ANALYZE TABLE等）log_slow_slave_statements: 在从库上记录慢查询min_examined_row_limit: 只记录检查行数超过此值的查询
配置完成后需要重启MySQL服务使配置生效，或动态设置：
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
pt-query-digest工具介绍
pt-query-digest是Percona Toolkit工具集中的一个强大工具，专门用于分析MySQL慢查询日志。它能够对慢查询日志进行汇总、排序和分析，生成易于理解的报告。
安装pt-query-digest
在基于Debian的系统上：
sudo apt-get update
sudo apt-get install percona-toolkit
在基于RPM的系统上：
sudo yum install epel-release
sudo yum install percona-toolkit
或者直接从Percona官网下载安装包：
wget https://www.percona.com/downloads/percona-toolkit/LATEST/binary/tarball/percona-toolkit-3.5.5_x86_64.tar.gz
tar zxvf percona-toolkit-3.5.5_x86_64.tar.gz
cd percona-toolkit-3.5.5
perl Makefile.PL
make && make install
使用pt-query-digest分析慢查询日志
基本用法：
pt-query-digest /var/log/mysql/mysql-slow.log > slow_report.log
常用选项
-
–limit: 限制输出结果数量
pt-query-digest --limit 20 /var/log/mysql/mysql-slow.log -
–filter: 使用Perl表达式过滤特定查询
pt-query-digest --filter '($event->{fingerprint} =~ m/^SELECT/i)' /var/log/mysql/mysql-slow.log -
–since和–until: 按时间范围分析（支持多种时间格式）
pt-query-digest --since '2023-01-01 08:00:00' --until '2023-01-01 18:00:00' /var/log/mysql/mysql-slow.log -
–order-by: 按特定字段排序（支持Query_time, Lock_time, Rows_sent等）
pt-query-digest --order-by 'Query_time:max' /var/log/mysql/mysql-slow.log -
–output: 指定输出格式（支持json, json-anon, report等）
pt-query-digest --output json /var/log/mysql/mysql-slow.log > slow_report.json -
–review: 将结果存入数据库以便长期跟踪
pt-query-digest --review h=localhost,D=slow_query_log,t=global_query_review /var/log/mysql/mysql-slow.log
分析报告解读
pt-query-digest生成的报告通常包含以下几个部分：
-
总体统计信息
- 日志文件总大小
- 分析时间范围
- 查询总数和唯一查询数量
- 总执行时间和平均每秒查询数(QPS)
- 并发度分析
- 查询时间分布直方图
-
查询概要
- 每个查询的执行次数和占比
- 总执行时间和占比
- 平均、最小、最大执行时间
- 95%百分位执行时间
- 锁时间统计
- 扫描和返回的行数统计
- 查询大小分析
-
详细查询分析
- 查询指纹(规范化后的SQL)
- 数据库、用户、主机来源
- 完整示例SQL
- 执行计划建议(EXPLAIN)
- 时间分布直方图
- 可能的优化建议
实际案例分析
假设我们分析得到如下慢查询：
# Query 1: 0.05 QPS, 0.02x concurrency, ID 0x123456789ABCDEF at byte 12345
# Scores: V/M = 1.23
# Time range: 2023-01-01 12:00:00 to 2023-01-01 12:30:00
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 50 100
# Exec time 60 1200s 10s 20s 12s 15s 2s 11s
# Lock time 30 30s 100ms 500ms 300ms 400ms 100ms 250ms
# Rows sent 20 20,000 100 300 200 250 50 200
# Rows examine 100 500,000 4,000 10,000 5,000 8,000 1,000 5,000
# Query size 10 100KB 1KB 2KB 1KB 1.5KB 0.5KB 1KB
# String:
# Databases test
# Hosts 192.168.1.100 (90%), 192.168.1.101 (10%)
# Users webapp_user
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+ ################################################################
SELECT * FROM orders WHERE customer_id = ? AND status = ? ORDER BY created_at DESC;
从这个分析中我们可以看出：
- 该查询执行了100次，占总查询次数的50%，平均每秒0.05次
- 总执行时间为1200秒，占总查询时间的60%，是最耗资源的查询
- 平均执行时间为12秒，最长达到20秒，95%的查询在15秒内完成
- 每次查询扫描约5000行数据，但只返回200行，效率低下
- 90%的查询来自192.168.1.100主机
- 查询时间分布显示大部分查询耗时在1-10秒之间
优化建议
基于pt-query-digest的分析结果，我们可以提出以下优化建议：
-
添加复合索引优化查询效率
ALTER TABLE orders ADD INDEX idx_customer_status_created (customer_id, status, created_at DESC); -
优化查询语句，避免SELECT *并考虑分页
SELECT id, order_date, amount, status FROM orders WHERE customer_id = ? AND status = ? ORDER BY created_at DESC LIMIT 100; -
使用查询缓存或应用层缓存
SELECT SQL_CACHE id, order_date, amount, status FROM orders WHERE customer_id = ? AND status = ? ORDER BY created_at DESC; -
考虑表分区，如果数据量很大且按customer_id查询
ALTER TABLE orders PARTITION BY HASH(customer_id) PARTITIONS 16; -
优化服务器配置，如增加sort_buffer_size
[mysqld] sort_buffer_size = 4M -
应用层优化，考虑合并多个查询或使用批量查询
高级用法
持续监控慢查询
设置cron任务定期分析并发送报告：
0 * * * * pt-query-digest /var/log/mysql/mysql-slow.log --since '1 hour ago' --output json > /var/log/mysql-slow-analysis-`date +\%Y\%m\%d\%H`.json && mail -s "Hourly MySQL Slow Query Report" dba@example.com < /var/log/mysql-slow-analysis-`date +\%Y\%m\%d\%H`.json
分析TCP流量实时查询
tcpdump -s 65535 -x -nn -q -tttt -i any -c 10000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump --watch-server '192.168.1.100:3306' mysql.tcp.txt
与Anemometer集成实现可视化
- 首先设置数据库存储慢查询分析结果：
CREATE DATABASE IF NOT EXISTS slow_query_log;
CREATE TABLE IF NOT EXISTS slow_query_log.global_query_review (
checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY,
fingerprint TEXT NOT NULL,
sample TEXT NOT NULL,
first_seen DATETIME,
last_seen DATETIME,
reviewed_by VARCHAR(20),
reviewed_on DATETIME,
comments TEXT
);
CREATE TABLE IF NOT EXISTS slow_query_log.global_query_review_history (
hostname_max VARCHAR(64),
db_max VARCHAR(64),
user_max VARCHAR(64),
checksum BIGINT UNSIGNED NOT NULL,
sample TEXT NOT NULL,
ts_min DATETIME NOT NULL,
ts_max DATETIME NOT NULL,
ts_cnt FLOAT,
query_time_sum FLOAT,
query_time_min FLOAT,
query_time_max FLOAT,
query_time_pct_95 FLOAT,
query_time_stddev FLOAT,
query_time_median FLOAT,
lock_time_sum FLOAT,
rows_sent_sum FLOAT,
rows_examined_sum FLOAT,
rows_affected_sum FLOAT,
rows_read_sum FLOAT,
merge_passes_sum FLOAT,
innodb_io_r_ops_min FLOAT,
innodb_io_r_bytes_min FLOAT,
innodb_io_r_wait_min FLOAT,
innodb_rec_lock_wait_min FLOAT,
innodb_queue_wait_min FLOAT,
innodb_pages_distinct_min FLOAT,
qc_hit_sum INT UNSIGNED,
full_scan_sum INT UNSIGNED,
full_join_sum INT UNSIGNED,
tmp_table_sum INT UNSIGNED,
tmp_table_on_disk_sum INT UNSIGNED,
filesort_sum INT UNSIGNED,
filesort_on_disk_sum INT UNSIGNED,
INDEX (checksum, ts_min)
);
- 然后定期运行pt-query-digest导入数据：
pt-query-digest \
--review h=localhost,D=slow_query_log,t=global_query_review \
--history h=localhost,D=slow_query_log,t=global_query_review_history \
--no-report --limit=0% --filter="\$event->{Bytes} = length(\$event->{arg})" \
/var/log/mysql/mysql-slow.log
- 部署Anemometer并配置连接到此数据库即可实现可视化监控。
总结
pt-query-digest是MySQL性能优化中不可或缺的工具，它能够帮助我们：
-
全面诊断性能问题：
- 快速定位最耗资源的SQL查询
- 分析查询执行模式和时间分布
- 识别全表扫描和索引缺失问题
-
提供数据驱动的优化建议：
- 基于实际负载提出索引优化建议
- 发现不合理的查询模式
- 量化优化前后的性能差异
-
建立持续监控体系：
- 长期跟踪查询性能变化
- 预警性能退化
- 评估优化效果
-
集成到DevOps流程：
- 作为CI/CD的一部分检查SQL性能
- 新版本发布的性能基准测试
- 生产环境性能监控
通过定期分析慢查询日志，我们可以持续优化数据库性能，提升应用响应速度，为用户提供更好的体验。建议将pt-query-digest集成到日常运维流程中，建立从监控到优化的完整闭环。
-
-
- . . -
-
扫一扫
489
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
