你接手过那种“跑着跑着突然慢到怀疑人生”的MySQL实例吗打开监控面板CPU、IO、连接数全线飘红查SHOW PROCESSLIST看到一串不带索引的SELECT挂在那边数据量不大却动辄执行好几秒。这种时候第一件事永远是先搞清楚“到底哪些SQL在拖后腿”而pt-query-digest就是干这个用得最顺手的工具没有之一。这次把Percona Toolkit里的核心分析利器pt-query-digest从安装到实战完整拆一遍。不管你用的是MySQL 5.7还是8.0无论你是刚接触慢查询优化的小开发还是要对线上库做例行巡检的DBA这篇都能给你一套直接能上手的流程怎么装、日志怎么开、分析结果怎么看、哪些指标才是真该盯的最后还会把我在生产环境里踩过的几个坑一并交代清楚。看完你再到自己的库里操练一遍基本就能把慢查询分析这条路彻底走通了。1. 为什么说慢查询日志分析是性能优化的第一步很多人的第一反应是直接去看sys库里Performance Schema的统计或者用mysqldumpslow扫一眼日志。这些方案各有局限Performance Schema虽然精准但开启会增加额外开销而且排查问题时要写一堆JOIN查events_statements_summary_by_digest对不熟悉内部表的同学来说门槛不小mysqldumpslow又过于单薄只能按时间排序做个粗糙聚合没法做多维度的统计对比拿到的信息量远远不够。pt-query-digest的思路更接近“把日志当成数据来做分析”它先解析慢查询日志提取出每条SQL的摘要digest也就是去掉具体参数后的归一化文本再按摘要聚合统计总执行次数、总耗时、平均耗时、最大耗时、扫描行数、返回行数最后按最耗时或最频繁等维度排序输出一份结构化的报告。这个过程相当于把散落在日志里几百上千条零散记录自动归类成一个个有代表性的“查询指纹”让你不用逐条翻日志直接看榜单就行。它的优势还在于输入源非常灵活。除了最常见的慢查询日志文件它还能直接分析tcpdump抓包得到的网络报文或者实时从SHOW PROCESSLIST输出里抓取正在执行的SQL——什么意思呢比如你连日志都没来得及开或者慢查询日志已经轮转覆盖了你照样可以从网络流量或者当前连接里“救回”最占资源的那些语句。这一点在应急排障时特别有用。所以我把这套流程定义为采集原始数据日志/抓包/processlist → pt-query-digest聚合统计 → 找出高耗时或高扫描量查询 → 针对性优化加索引、改SQL、调参 → 复测确认。整个过程里分析工具承担的是“把问题暴露出来”的环节是优化的起点和依据。没有这一步后面加索引也好、改代码也好都是在凭感觉做事效率和命中率都低得多。2. 安装部署两种方式各有各的门道pt-query-digest是Percona Toolkit套件中的一个工具官方推荐的方式是直接安装整个percona-toolkit包。这里介绍两种我用过的安装方案覆盖最常见的在线和离线两种场景。2.1 在线安装走官方仓库最省事如果服务器能访问外网优先走Percona官方仓库。以Ubuntu/Debian为例先下载并安装官方仓库配置包wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb dpkg -i percona-release_latest.generic_all.deb apt update apt install -y percona-toolkitRHEL/CentOS系则使用yum或dnf同样需要先配置Percona仓库yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm percona-release enable tools release yum install -y percona-toolkit装完验证一下版本pt-query-digest --version我建议装完后顺手看一眼版本号因为不同版本的输出格式和参数会有细微差别。比如Percona Toolkit 3.x的--since、--until时间过滤用法和2.x就有差异后续如果拿网上老文章的命令直接跑可能会报参数不识别。2.2 离线安装内网环境的备选方案生产环境经常是隔离网段装不了在线源。这种情况我的做法是在一台能访问外网的机器上用yum download或apt download把安装包连依赖一起拉下来再拷进内网。以CentOS为例# 在有外网的机器上执行 yum install -y --downloadonly --downloaddir/tmp/pt percona-toolkit然后把/tmp/pt目录下的rpm包全部拷到内网服务器执行rpm -ivh /tmp/pt/*.rpm需要注意percona-toolkit有Perl依赖主要包括perl-DBI、perl-DBD-MySQL、perl-Time-HiRes、perl-TermReadKey等。--downloadonly会把依赖一并拉下来所以整个目录拷过去基本能装成功。万一装的时候提示缺某个Perl模块可以单独搜对应的perl-*包或者用系统的包管理器补上。装完在任意路径直接输pt-query-digest能出帮助信息就说明OK了。注意内网机器上如果MySQL是源码方式编译安装的客户端库路径可能不在默认位置。遇到“找不到libmysqlclient”之类的报错时先确认perl -MDBD::mysql -e print $DBD::mysql::VERSION能正常输出版本号不行就装一下perl-DBD-MySQL。3. 让日志先跑起来慢查询采集的正确姿势工具装好只是第一步最关键的其实是“有没有数据可分析”。很多线上库没有开慢查询日志或者阈值设得离谱导致pt-query-digest分析半天什么都分析不出来。所以先把采集这段捋顺。3.1 关键参数配置说明在MySQL里慢查询相关核心参数是下面这几个参数名推荐值作用说明slow_query_logON是否开启慢查询日志slow_query_log_file/var/lib/mysql/mysql-slow.log慢查询日志文件路径long_query_time1超过多少秒的查询记入日志单位秒支持小数如0.5log_queries_not_using_indexesON即使没超过阈值但只要没用索引的查询也记录min_examined_row_limit1000可选扫描行数超过该值的查询才记录配合上面参数过滤噪声这里重点说下long_query_time。生产环境我一般建议从1秒起步不要一开始就设0.1秒或更小——那样会把大量正常查询刷进日志日志瞬间膨胀不说分析时噪声也大。先把1秒作为分界线跑几天观察高耗时查询的分布情况后再决定要不要调低阈值深入排查。还有一个容易忽略的点log_queries_not_using_indexes开起来后很多扫描行数极小的“无索引查询”也会进入日志比如一张只有几十行的小表全表扫描也就几十微秒这类查询其实对性能几乎没影响但会占满日志空间。配合min_examined_row_limit一起用可以过滤掉扫描行数很少的无索引查询让日志里的记录更贴近真实性能问题。3.2 动态开启与持久化MySQL 8.0支持动态设置不用重启实例SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON; SET GLOBAL min_examined_row_limit 1000;但注意动态设置只在当前实例生命周期内有效重启后会被my.cnf里的配置覆盖。所以确认参数合适后记得写进/etc/my.cnf的[mysqld]段落[mysqld] slow_query_log ON slow_query_log_file /var/lib/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes ON min_examined_row_limit 1000改完配置文件后需要重启MySQL才生效或者用SET GLOBAL先顶上去下次重启自然永久生效。我个人的操作习惯是先动态开启确认参数和日志写入正常再写进配置文件避免改了配置一重启才发现路径写错、权限不对这类尴尬。3.3 日志权限与轮转排雷日志文件写入权限是新手最容易踩的坑。MySQL进程要能写慢查询日志文件常见报错就是日志文件属主不对导致根本写不进去。排查时直接看日志文件ls -lh /var/lib/mysql/mysql-slow.log chown mysql:mysql /var/lib/mysql/mysql-slow.log日志越跑越大是必然的一定要做轮转。我常用的方案是logrotate最小化配置长这样/var/lib/mysql/mysql-slow.log { daily rotate 14 compress delaycompress missingok notifempty create 660 mysql mysql }轮转完记得让MySQL重新打开日志文件可以在logrotate配置里加上postrotate调用mysqladmin flush-logs或者直接kill -USR1对应MySQL进程注意这一步只在确认信号语义的前提下使用。否则日志轮转后MySQL还握着旧文件的句柄慢查询日志会继续写进已经被移走的文件里新日志文件反而是空的——这个现象我遇到好几次一度以为工具分析不出东西是格式问题。4. 核心分析命令与输出解读看懂每一行的意思数据准备好了终于轮到主角登场。这里从最基本的命令讲起再逐块拆解输出报告让你拿到一份分析结果后知道该从哪一行开始下手。4.1 五分钟跑出第一份报告最简单的用法一条命令pt-query-digest /var/lib/mysql/mysql-slow.log slow_report_$(date %Y%m%d).txt执行完会在当前目录生成一份报告文件。如果你只想看最近7天的记录加个时间过滤pt-query-digest --since 7 days ago /var/lib/mysql/mysql-slow.log如果日志特别大比如几个GB先加--limit 30只输出Top 30的查询类型或者用--outliers只看那些执行时间远高于平均水平的异常查询快速定位最严重的问题再决定要不要全量分析。4.2 报告四大块的阅读顺序一份标准的慢查询报告通常包含以下几个部分按“总到分”的结构展开第一部分整体概览Profile它会列出所有查询摘要的排名表每一行表示一种类型的查询按总执行时间或次数排序包含Rank、Query ID、Response time、Calls、R/Call、V/M、Item。其中Response time这类查询累计消耗的总时间Calls执行次数R/Call平均每次执行耗时V/M方差与均值的比值比值越大说明执行时间波动越大越值得警惕可能偶尔有慢查询拖后腿比如一条SELECT * FROM orders WHERE status?的聚合行Calls是1200R/Call是0.3秒V/M是8那说明大部分时间执行很快但偶发有执行好几秒的情况。这种情况下光是看均值不够得找那些异常波动的场景。第二部分查询类型明细每个查询ID对应一段详细的报告格式大致如下Query 1: 8.31% of total, 5.7k x, 4.83 mavg, 12.0s max, 10ms max (avg)这里给出了总占比、总次数、平均耗时、单次最大耗时等信息。往下是SQL原文格式化后的归一化语句、时间分布百分比表示法、以及各项指标的最小/平均/中位数/最大/95百分位。重点看两个数字Rows_sent实际返回的行数和Rows_examine扫描的行数。如果Rows_examine比Rows_sent高出几个数量级比如扫描了5万行只返回20行这条SQL大概率就是在全表扫描或者索引命中率很低是典型的优化目标。第三部分分组聚合视图如果你已经加上了--group-by参数比如按库名、按用户分组这里会展示各分组下的查询数量与耗时分布。这个对多业务共用一个实例的场景特别有用可以快速判断慢查询集中在哪个业务、哪个账号上。第四部分过滤后的单独查询这段就是把Top N逐条展示每一类查询的详细指标配合注释和代码块方便人工查看。实际排查时我一般先扫Profile锁定排名靠前或V/M异常的查询ID再跳到明细段看具体SQL文本和扫描行数。4.3 比日志更精准的两个分析源头有时候想查的SQL不一定落在慢查询日志里——比如某条查询每次只跑800毫秒没到1秒阈值又或者你想看“当前正在执行的查询”那慢查询日志就帮不上忙了。pt-query-digest这两个模式就是为这种场景准备的。抓取网络报文用tcpdump在MySQL端口抓包再交给工具分析tcpdump -i eth0 port 3306 -s 65535 -c 200000 -w mysql.pcap pt-query-digest --type tcpdump mysql.pcap这个方案不依赖MySQL的任何日志配置抓到什么分析什么特别适合排查“全局慢”但日志却相对干净的诡异场景。需要注意的是抓包文件体积膨胀非常快-c限制包数量上限实际使用建议配合定时任务和文件滚动。抓取当前processlistpt-query-digest --processlist hostname它会连到本机MySQL周期性采集SHOW FULL PROCESSLIST输出聚合出当前正在执行的SQL类型。这个模式适合在线应急压测或故障期间你不需要等日志落盘直接就能看到当下正在消耗资源的语句。当然采样周期要配置合理否则采集本身也会给数据库叠加额外负载。5. 高阶用法从日志分析到优化落地工具跑通、报告看得懂这只是第一步。真正拉开差距的地方在于怎么把分析结果转化为有效的优化动作。这里聊聊我的几个实用招数。5.1 用--review实现“只看增量问题”线上巡检时每次把整个日志重新分析一遍输出的报告动辄几百行看久了容易麻木。我的做法是配合--review参数把已经处理过的查询指纹存到一张MySQL表里下次分析时只输出新出现的、或者状态未解决的查询。思路是预先在库里建好工具用的review表结构Percona官方提供了pt-query-digest --create-review-table参数来创建然后执行pt-query-digest --review hlocalhost,Dpercona,tquery_review \ --review-history hlocalhost,Dpercona,tquery_review_history \ /var/lib/mysql/mysql-slow.log这样每次跑完分析工具会把查询摘要、指纹、首次/最近出现时间、累计执行次数等信息写进review表。下次再跑时相同指纹的查询不会重复输出历史表中则保留了每一次分析的快照。时间久了还能对比同一查询在不同时间段的执行表现看优化前后是否有改善。5.2 从“什么慢”到“为什么慢”三条检查线拿到慢查询报告后的行动路径我总结成一个三层检查清单先看扫描行数。Rows_examine很高而Rows_sent很低基本说明索引没吃到或者走了全表扫描。打开执行计划确认会不会走索引、有没有隐式类型转换、函数包裹索引列等典型问题。比如WHERE date(create_time) 2024-01-01这种写法哪怕create_time上有索引也用不上应该改成范围条件。再看锁等待和CPU耗时。报告中的Lock_time如果异常高说明瓶颈不在SQL本身而在并发锁竞争上。这时候优化方向不是给SQL加索引而是去查业务逻辑里事务是否过长、是否有大批量更新堵塞了读写。Rows_examine都不高但Query_time持续高企的情况十有八九是锁在作祟。最后对齐业务场景。有些慢查询其实“合理”——比如后台凌晨跑的大报表扫描几百万行本来就是需求的一部分。这时候与其改SQL不如考虑迁移到离线库、做成异步任务或者接受它能跑完就行。优化不是把所有SQL都压到毫秒级而是把影响用户主链路的慢查询优先干掉。5.3 定时巡检的实用脚本思路最后分享一个适合做例行巡检的脚本思路。核心逻辑是每天凌晨执行pt-query-digest处理前一天的日志报告输出到固定目录并把Top N查询写进一张汇总表方便后续看趋势。配合--since和--until限定时间窗口只分析一天的数据。#!/bin/bash LOG_DIR/var/log/mysql-slow-analysis REPORT_FILE$LOG_DIR/report_$(date %F).txt pt-query-digest --since 1 day ago --until now \ /var/lib/mysql/mysql-slow.log $REPORT_FILE脚本本身没什么技术含量但坚持每天跑下来你会积累一份很有价值的历史档案哪些查询是新出现的、哪些查询的耗时在逐日上升、哪类业务在某个时段集中变慢都能从中看出苗头。6. 实战避坑排查清单与个人经验笔记再靠谱的工具也顶不住环境里的细节坑。下面这些全是我实际用过踩过的单独整理出来省得你走弯路。6.1 常见问题速查表现象可能原因解决办法分析结果为空慢查询日志未开启或路径配置错误执行SHOW VARIABLES LIKE slow_query_log%确认状态和路径报错Could not parse某行SQL日志文件包含非标准格式内容如mysqldump输出混入检查日志文件是否被截断、被其他工具追加写入时间过滤不生效--since/--until参数格式不对或版本不支持使用--since 2024-01-01 00:00:00格式确认版本为3.x连接MySQL失败缺少Perl DBD驱动安装perl-DBD-MySQL并确认客户端库路径输出中看不到具体SQL文本日志中查询默认被摘要化加--report-format full展开完整信息字符集乱码或特殊字符解析异常日志中SQL包含非UTF8字符设置default-character-setutf8mb4后重新分析6.2 时间过滤的版本差异pt-query-digest的时间过滤在不同版本间差异很大。2.x版本对时间文本的解析能力较弱常见写法是--since 2024-01-01 00:00:003.x版本支持更口语化的写法--since 7 days ago直接可用。如果跑了命令发现一条数据都没出先怀疑是不是时间语法没被解析成功把参数换成明确的日期再试一次基本就能定位。6.3 大小写和规范化对聚合的影响慢查询日志里的SQL被归一化处理时工具默认只把具体数值替换掉大小写和空格等不敏感差异会统一处理。但要注意如果你自己修改了pt-query-digest的配置或用了--filter自定义过滤逻辑可能会影响聚合结果。生产环境我建议保持默认聚合逻辑不要在过滤上做太复杂的自定义不然同类查询容易被拆成多个指纹榜单就失真了。6.4 mysqldumpslow、Performance Schema与pt-query-digest怎么选一个小对比方便你根据场景选对工具工具适用场景缺点mysqldumpslow快速用一条命令扫一眼慢日志聚合维度单一输出信息少不支持网络抓包Performance Schema精细化分析当前和历史语句统计配置复杂内存消耗高排查门槛较高pt-query-digest慢日志/抓包/processlist多源分析需额外安装Percona Toolkit无图形界面实际工作中我更喜欢组合打法日常巡检用pt-query-digest扫日志遇到线上突发问题用--processlist模式应急需要确认某条语句内部执行细节再用Performance Schema跟进。工具之间不冲突关键是清楚每个工具在什么环节最省力。6.5 一份“分析完日志之后”的执行清单分析报告拿到手完整优化动作可以参考这条路径每天查看Top 5新增或最慢查询优先处理总耗时占比最高的。对每条目标SQLEXPLAIN确认是否走了全表扫描、是否用错索引。优化索引实测验证执行计划变化。回到pt-query-digest报告里对比优化前后同一查询的Rows_examine和耗时变化。把处理过的查询ID记录进--review表下次巡检自动跳过。我个人在实际操作中最深的体会是pt-query-digest不是优化银弹它最大的价值是帮你在海量日志和繁杂指标里用最短的时间圈出真正值得动手的几十条SQL。把分析环节做扎实后续的索引优化、SQL改写、参数调整才有明确抓手。手上正好有慢日志要分析的话现在就可以把工具跑起来先产出一份报告再对照这篇的思路去筛效果会比空读一遍好得多。
MySQL慢查询分析实战：pt-query-digest从安装到避坑指南
你接手过那种“跑着跑着突然慢到怀疑人生”的MySQL实例吗打开监控面板CPU、IO、连接数全线飘红查SHOW PROCESSLIST看到一串不带索引的SELECT挂在那边数据量不大却动辄执行好几秒。这种时候第一件事永远是先搞清楚“到底哪些SQL在拖后腿”而pt-query-digest就是干这个用得最顺手的工具没有之一。这次把Percona Toolkit里的核心分析利器pt-query-digest从安装到实战完整拆一遍。不管你用的是MySQL 5.7还是8.0无论你是刚接触慢查询优化的小开发还是要对线上库做例行巡检的DBA这篇都能给你一套直接能上手的流程怎么装、日志怎么开、分析结果怎么看、哪些指标才是真该盯的最后还会把我在生产环境里踩过的几个坑一并交代清楚。看完你再到自己的库里操练一遍基本就能把慢查询分析这条路彻底走通了。1. 为什么说慢查询日志分析是性能优化的第一步很多人的第一反应是直接去看sys库里Performance Schema的统计或者用mysqldumpslow扫一眼日志。这些方案各有局限Performance Schema虽然精准但开启会增加额外开销而且排查问题时要写一堆JOIN查events_statements_summary_by_digest对不熟悉内部表的同学来说门槛不小mysqldumpslow又过于单薄只能按时间排序做个粗糙聚合没法做多维度的统计对比拿到的信息量远远不够。pt-query-digest的思路更接近“把日志当成数据来做分析”它先解析慢查询日志提取出每条SQL的摘要digest也就是去掉具体参数后的归一化文本再按摘要聚合统计总执行次数、总耗时、平均耗时、最大耗时、扫描行数、返回行数最后按最耗时或最频繁等维度排序输出一份结构化的报告。这个过程相当于把散落在日志里几百上千条零散记录自动归类成一个个有代表性的“查询指纹”让你不用逐条翻日志直接看榜单就行。它的优势还在于输入源非常灵活。除了最常见的慢查询日志文件它还能直接分析tcpdump抓包得到的网络报文或者实时从SHOW PROCESSLIST输出里抓取正在执行的SQL——什么意思呢比如你连日志都没来得及开或者慢查询日志已经轮转覆盖了你照样可以从网络流量或者当前连接里“救回”最占资源的那些语句。这一点在应急排障时特别有用。所以我把这套流程定义为采集原始数据日志/抓包/processlist → pt-query-digest聚合统计 → 找出高耗时或高扫描量查询 → 针对性优化加索引、改SQL、调参 → 复测确认。整个过程里分析工具承担的是“把问题暴露出来”的环节是优化的起点和依据。没有这一步后面加索引也好、改代码也好都是在凭感觉做事效率和命中率都低得多。2. 安装部署两种方式各有各的门道pt-query-digest是Percona Toolkit套件中的一个工具官方推荐的方式是直接安装整个percona-toolkit包。这里介绍两种我用过的安装方案覆盖最常见的在线和离线两种场景。2.1 在线安装走官方仓库最省事如果服务器能访问外网优先走Percona官方仓库。以Ubuntu/Debian为例先下载并安装官方仓库配置包wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb dpkg -i percona-release_latest.generic_all.deb apt update apt install -y percona-toolkitRHEL/CentOS系则使用yum或dnf同样需要先配置Percona仓库yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm percona-release enable tools release yum install -y percona-toolkit装完验证一下版本pt-query-digest --version我建议装完后顺手看一眼版本号因为不同版本的输出格式和参数会有细微差别。比如Percona Toolkit 3.x的--since、--until时间过滤用法和2.x就有差异后续如果拿网上老文章的命令直接跑可能会报参数不识别。2.2 离线安装内网环境的备选方案生产环境经常是隔离网段装不了在线源。这种情况我的做法是在一台能访问外网的机器上用yum download或apt download把安装包连依赖一起拉下来再拷进内网。以CentOS为例# 在有外网的机器上执行 yum install -y --downloadonly --downloaddir/tmp/pt percona-toolkit然后把/tmp/pt目录下的rpm包全部拷到内网服务器执行rpm -ivh /tmp/pt/*.rpm需要注意percona-toolkit有Perl依赖主要包括perl-DBI、perl-DBD-MySQL、perl-Time-HiRes、perl-TermReadKey等。--downloadonly会把依赖一并拉下来所以整个目录拷过去基本能装成功。万一装的时候提示缺某个Perl模块可以单独搜对应的perl-*包或者用系统的包管理器补上。装完在任意路径直接输pt-query-digest能出帮助信息就说明OK了。注意内网机器上如果MySQL是源码方式编译安装的客户端库路径可能不在默认位置。遇到“找不到libmysqlclient”之类的报错时先确认perl -MDBD::mysql -e print $DBD::mysql::VERSION能正常输出版本号不行就装一下perl-DBD-MySQL。3. 让日志先跑起来慢查询采集的正确姿势工具装好只是第一步最关键的其实是“有没有数据可分析”。很多线上库没有开慢查询日志或者阈值设得离谱导致pt-query-digest分析半天什么都分析不出来。所以先把采集这段捋顺。3.1 关键参数配置说明在MySQL里慢查询相关核心参数是下面这几个参数名推荐值作用说明slow_query_logON是否开启慢查询日志slow_query_log_file/var/lib/mysql/mysql-slow.log慢查询日志文件路径long_query_time1超过多少秒的查询记入日志单位秒支持小数如0.5log_queries_not_using_indexesON即使没超过阈值但只要没用索引的查询也记录min_examined_row_limit1000可选扫描行数超过该值的查询才记录配合上面参数过滤噪声这里重点说下long_query_time。生产环境我一般建议从1秒起步不要一开始就设0.1秒或更小——那样会把大量正常查询刷进日志日志瞬间膨胀不说分析时噪声也大。先把1秒作为分界线跑几天观察高耗时查询的分布情况后再决定要不要调低阈值深入排查。还有一个容易忽略的点log_queries_not_using_indexes开起来后很多扫描行数极小的“无索引查询”也会进入日志比如一张只有几十行的小表全表扫描也就几十微秒这类查询其实对性能几乎没影响但会占满日志空间。配合min_examined_row_limit一起用可以过滤掉扫描行数很少的无索引查询让日志里的记录更贴近真实性能问题。3.2 动态开启与持久化MySQL 8.0支持动态设置不用重启实例SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON; SET GLOBAL min_examined_row_limit 1000;但注意动态设置只在当前实例生命周期内有效重启后会被my.cnf里的配置覆盖。所以确认参数合适后记得写进/etc/my.cnf的[mysqld]段落[mysqld] slow_query_log ON slow_query_log_file /var/lib/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes ON min_examined_row_limit 1000改完配置文件后需要重启MySQL才生效或者用SET GLOBAL先顶上去下次重启自然永久生效。我个人的操作习惯是先动态开启确认参数和日志写入正常再写进配置文件避免改了配置一重启才发现路径写错、权限不对这类尴尬。3.3 日志权限与轮转排雷日志文件写入权限是新手最容易踩的坑。MySQL进程要能写慢查询日志文件常见报错就是日志文件属主不对导致根本写不进去。排查时直接看日志文件ls -lh /var/lib/mysql/mysql-slow.log chown mysql:mysql /var/lib/mysql/mysql-slow.log日志越跑越大是必然的一定要做轮转。我常用的方案是logrotate最小化配置长这样/var/lib/mysql/mysql-slow.log { daily rotate 14 compress delaycompress missingok notifempty create 660 mysql mysql }轮转完记得让MySQL重新打开日志文件可以在logrotate配置里加上postrotate调用mysqladmin flush-logs或者直接kill -USR1对应MySQL进程注意这一步只在确认信号语义的前提下使用。否则日志轮转后MySQL还握着旧文件的句柄慢查询日志会继续写进已经被移走的文件里新日志文件反而是空的——这个现象我遇到好几次一度以为工具分析不出东西是格式问题。4. 核心分析命令与输出解读看懂每一行的意思数据准备好了终于轮到主角登场。这里从最基本的命令讲起再逐块拆解输出报告让你拿到一份分析结果后知道该从哪一行开始下手。4.1 五分钟跑出第一份报告最简单的用法一条命令pt-query-digest /var/lib/mysql/mysql-slow.log slow_report_$(date %Y%m%d).txt执行完会在当前目录生成一份报告文件。如果你只想看最近7天的记录加个时间过滤pt-query-digest --since 7 days ago /var/lib/mysql/mysql-slow.log如果日志特别大比如几个GB先加--limit 30只输出Top 30的查询类型或者用--outliers只看那些执行时间远高于平均水平的异常查询快速定位最严重的问题再决定要不要全量分析。4.2 报告四大块的阅读顺序一份标准的慢查询报告通常包含以下几个部分按“总到分”的结构展开第一部分整体概览Profile它会列出所有查询摘要的排名表每一行表示一种类型的查询按总执行时间或次数排序包含Rank、Query ID、Response time、Calls、R/Call、V/M、Item。其中Response time这类查询累计消耗的总时间Calls执行次数R/Call平均每次执行耗时V/M方差与均值的比值比值越大说明执行时间波动越大越值得警惕可能偶尔有慢查询拖后腿比如一条SELECT * FROM orders WHERE status?的聚合行Calls是1200R/Call是0.3秒V/M是8那说明大部分时间执行很快但偶发有执行好几秒的情况。这种情况下光是看均值不够得找那些异常波动的场景。第二部分查询类型明细每个查询ID对应一段详细的报告格式大致如下Query 1: 8.31% of total, 5.7k x, 4.83 mavg, 12.0s max, 10ms max (avg)这里给出了总占比、总次数、平均耗时、单次最大耗时等信息。往下是SQL原文格式化后的归一化语句、时间分布百分比表示法、以及各项指标的最小/平均/中位数/最大/95百分位。重点看两个数字Rows_sent实际返回的行数和Rows_examine扫描的行数。如果Rows_examine比Rows_sent高出几个数量级比如扫描了5万行只返回20行这条SQL大概率就是在全表扫描或者索引命中率很低是典型的优化目标。第三部分分组聚合视图如果你已经加上了--group-by参数比如按库名、按用户分组这里会展示各分组下的查询数量与耗时分布。这个对多业务共用一个实例的场景特别有用可以快速判断慢查询集中在哪个业务、哪个账号上。第四部分过滤后的单独查询这段就是把Top N逐条展示每一类查询的详细指标配合注释和代码块方便人工查看。实际排查时我一般先扫Profile锁定排名靠前或V/M异常的查询ID再跳到明细段看具体SQL文本和扫描行数。4.3 比日志更精准的两个分析源头有时候想查的SQL不一定落在慢查询日志里——比如某条查询每次只跑800毫秒没到1秒阈值又或者你想看“当前正在执行的查询”那慢查询日志就帮不上忙了。pt-query-digest这两个模式就是为这种场景准备的。抓取网络报文用tcpdump在MySQL端口抓包再交给工具分析tcpdump -i eth0 port 3306 -s 65535 -c 200000 -w mysql.pcap pt-query-digest --type tcpdump mysql.pcap这个方案不依赖MySQL的任何日志配置抓到什么分析什么特别适合排查“全局慢”但日志却相对干净的诡异场景。需要注意的是抓包文件体积膨胀非常快-c限制包数量上限实际使用建议配合定时任务和文件滚动。抓取当前processlistpt-query-digest --processlist hostname它会连到本机MySQL周期性采集SHOW FULL PROCESSLIST输出聚合出当前正在执行的SQL类型。这个模式适合在线应急压测或故障期间你不需要等日志落盘直接就能看到当下正在消耗资源的语句。当然采样周期要配置合理否则采集本身也会给数据库叠加额外负载。5. 高阶用法从日志分析到优化落地工具跑通、报告看得懂这只是第一步。真正拉开差距的地方在于怎么把分析结果转化为有效的优化动作。这里聊聊我的几个实用招数。5.1 用--review实现“只看增量问题”线上巡检时每次把整个日志重新分析一遍输出的报告动辄几百行看久了容易麻木。我的做法是配合--review参数把已经处理过的查询指纹存到一张MySQL表里下次分析时只输出新出现的、或者状态未解决的查询。思路是预先在库里建好工具用的review表结构Percona官方提供了pt-query-digest --create-review-table参数来创建然后执行pt-query-digest --review hlocalhost,Dpercona,tquery_review \ --review-history hlocalhost,Dpercona,tquery_review_history \ /var/lib/mysql/mysql-slow.log这样每次跑完分析工具会把查询摘要、指纹、首次/最近出现时间、累计执行次数等信息写进review表。下次再跑时相同指纹的查询不会重复输出历史表中则保留了每一次分析的快照。时间久了还能对比同一查询在不同时间段的执行表现看优化前后是否有改善。5.2 从“什么慢”到“为什么慢”三条检查线拿到慢查询报告后的行动路径我总结成一个三层检查清单先看扫描行数。Rows_examine很高而Rows_sent很低基本说明索引没吃到或者走了全表扫描。打开执行计划确认会不会走索引、有没有隐式类型转换、函数包裹索引列等典型问题。比如WHERE date(create_time) 2024-01-01这种写法哪怕create_time上有索引也用不上应该改成范围条件。再看锁等待和CPU耗时。报告中的Lock_time如果异常高说明瓶颈不在SQL本身而在并发锁竞争上。这时候优化方向不是给SQL加索引而是去查业务逻辑里事务是否过长、是否有大批量更新堵塞了读写。Rows_examine都不高但Query_time持续高企的情况十有八九是锁在作祟。最后对齐业务场景。有些慢查询其实“合理”——比如后台凌晨跑的大报表扫描几百万行本来就是需求的一部分。这时候与其改SQL不如考虑迁移到离线库、做成异步任务或者接受它能跑完就行。优化不是把所有SQL都压到毫秒级而是把影响用户主链路的慢查询优先干掉。5.3 定时巡检的实用脚本思路最后分享一个适合做例行巡检的脚本思路。核心逻辑是每天凌晨执行pt-query-digest处理前一天的日志报告输出到固定目录并把Top N查询写进一张汇总表方便后续看趋势。配合--since和--until限定时间窗口只分析一天的数据。#!/bin/bash LOG_DIR/var/log/mysql-slow-analysis REPORT_FILE$LOG_DIR/report_$(date %F).txt pt-query-digest --since 1 day ago --until now \ /var/lib/mysql/mysql-slow.log $REPORT_FILE脚本本身没什么技术含量但坚持每天跑下来你会积累一份很有价值的历史档案哪些查询是新出现的、哪些查询的耗时在逐日上升、哪类业务在某个时段集中变慢都能从中看出苗头。6. 实战避坑排查清单与个人经验笔记再靠谱的工具也顶不住环境里的细节坑。下面这些全是我实际用过踩过的单独整理出来省得你走弯路。6.1 常见问题速查表现象可能原因解决办法分析结果为空慢查询日志未开启或路径配置错误执行SHOW VARIABLES LIKE slow_query_log%确认状态和路径报错Could not parse某行SQL日志文件包含非标准格式内容如mysqldump输出混入检查日志文件是否被截断、被其他工具追加写入时间过滤不生效--since/--until参数格式不对或版本不支持使用--since 2024-01-01 00:00:00格式确认版本为3.x连接MySQL失败缺少Perl DBD驱动安装perl-DBD-MySQL并确认客户端库路径输出中看不到具体SQL文本日志中查询默认被摘要化加--report-format full展开完整信息字符集乱码或特殊字符解析异常日志中SQL包含非UTF8字符设置default-character-setutf8mb4后重新分析6.2 时间过滤的版本差异pt-query-digest的时间过滤在不同版本间差异很大。2.x版本对时间文本的解析能力较弱常见写法是--since 2024-01-01 00:00:003.x版本支持更口语化的写法--since 7 days ago直接可用。如果跑了命令发现一条数据都没出先怀疑是不是时间语法没被解析成功把参数换成明确的日期再试一次基本就能定位。6.3 大小写和规范化对聚合的影响慢查询日志里的SQL被归一化处理时工具默认只把具体数值替换掉大小写和空格等不敏感差异会统一处理。但要注意如果你自己修改了pt-query-digest的配置或用了--filter自定义过滤逻辑可能会影响聚合结果。生产环境我建议保持默认聚合逻辑不要在过滤上做太复杂的自定义不然同类查询容易被拆成多个指纹榜单就失真了。6.4 mysqldumpslow、Performance Schema与pt-query-digest怎么选一个小对比方便你根据场景选对工具工具适用场景缺点mysqldumpslow快速用一条命令扫一眼慢日志聚合维度单一输出信息少不支持网络抓包Performance Schema精细化分析当前和历史语句统计配置复杂内存消耗高排查门槛较高pt-query-digest慢日志/抓包/processlist多源分析需额外安装Percona Toolkit无图形界面实际工作中我更喜欢组合打法日常巡检用pt-query-digest扫日志遇到线上突发问题用--processlist模式应急需要确认某条语句内部执行细节再用Performance Schema跟进。工具之间不冲突关键是清楚每个工具在什么环节最省力。6.5 一份“分析完日志之后”的执行清单分析报告拿到手完整优化动作可以参考这条路径每天查看Top 5新增或最慢查询优先处理总耗时占比最高的。对每条目标SQLEXPLAIN确认是否走了全表扫描、是否用错索引。优化索引实测验证执行计划变化。回到pt-query-digest报告里对比优化前后同一查询的Rows_examine和耗时变化。把处理过的查询ID记录进--review表下次巡检自动跳过。我个人在实际操作中最深的体会是pt-query-digest不是优化银弹它最大的价值是帮你在海量日志和繁杂指标里用最短的时间圈出真正值得动手的几十条SQL。把分析环节做扎实后续的索引优化、SQL改写、参数调整才有明确抓手。手上正好有慢日志要分析的话现在就可以把工具跑起来先产出一份报告再对照这篇的思路去筛效果会比空读一遍好得多。
