在MySQL的性能调优里有一条最朴素也最直接的路径慢查询日志。很多做业务的同学一遇到线上数据库卡顿第一反应就是抓监控、加缓存、改各种参数忙活半天才发现真正的问题早就在日志里躺着。慢查询日志不是那种需要多高深技巧才能上手的东西它本质上就是MySQL把执行时间超过你设定阈值的SQL语句记录下来方便你事后逐个排查、分析、优化。对于刚接手数据库维护的人或者正在做JavaWeb项目、业务系统性能优化的同学来说学会看慢查询日志等于拿到了一把排查线上性能问题的基础钥匙。这篇文章我打算把这些年在实际环境里调慢查询的经验完完整整整理一遍包括怎么开启、怎么配置、怎么读日志、怎么用工具分析、怎么结合EXPLAIN去定位索引问题以及那些踩完才知道的坑。文章里出现的所有命令和SQL配置都是我在真实环境里跑过的可以直接照着用。1. 先搞清楚慢查询日志到底在解决什么问题1.1 慢查询日志的原理与定位很多人把慢查询日志想得很复杂其实它的原理特别简单MySQL在每次执行完一条SQL语句之后会统计这条语句的实际执行时间如果执行时间超过了你在配置里设定的阈值long_query_time就把这条语句连同它的一些附加信息写入日志文件或者系统表里。整个过程对业务语句是透明的不会影响正常请求的执行结果只是多了一点点的统计开销。慢查询日志在整个性能排查体系里的定位不是实时告警而是事后追溯。它不会在你数据库慢的瞬间通知你它只是忠实地把那些超过阈值的SQL记下来等你回头去看。所以正确的使用姿势是先把日志开起来让问题先被记录然后定期分析、优化、验证形成闭环。线上数据库的问题很少是单一SQL造成的大多数是某几条SQL在特定数据量、特定并发下暴露出来的慢查询日志恰好把这些肇事者原原本本地留了下来。可以类比一下慢查询日志就像体检报告里的异常指标清单。体检不会直接告诉你哪里疼但它会把超出正常范围的项列出来让你有方向去查。MySQL的慢日志做的就是这个事它只负责告诉你哪条查询超时了至于为什么超时、怎么优化得靠后面的分析手段来做。1.2 为什么阈值设置是第一步的关键慢查询日志开起来之后第一个要面对的问题就是阈值设多少合适。这个参数直接决定了日志里会记录什么实际上它比你想象的要敏感得多。我见过很多团队第一次开慢日志直接把long_query_time设成0觉得这样所有SQL都能被记录下来分析起来才全面。这个想法初衷是好的但实际操作下来基本是个灾难。因为长连接、短连接、各种框架自动发的心跳语句、甚至information_schema里的元数据查询都会被记录日志文件以肉眼可见的速度膨胀磁盘满掉只是时间问题。更关键的是当阈值设成0时MySQL需要检查每一条语句的执行时间虽然单条开销很小但在高并发场景下累积起来就是一笔不小的性能损耗等于你在用性能换日志明显得不偿失。从业务角度看阈值的设定应该取决于你对慢的定义。如果是一个面向用户的在线系统用户可感知的延迟一般在1秒以上那long_query_time设成1秒是个合理的起点如果是内部报表系统或者批量任务可以放宽到2秒甚至5秒。我个人的习惯是新项目上线初期先设1秒跑一段时间看日志量如果日志里全是无关紧要的语句再调整阈值或者加过滤条件如果日志量特别少说明业务相对健康可以适当收紧到0.5秒来提前暴露隐患。还有一个容易忽略的点long_query_time的生效范围。在MySQL里这个参数既可以在全局设置也可以在会话级别设置。如果你只用SET GLOBAL修改那么已存在的连接不会立即生效必须等新连接建立或者手动在会话里再设一次。很多人在测试环境里改了全局配置没生效就开始怀疑自己命令写错了其实只是吃了这个连接级参数的亏。2. 如何正确开启和配置慢查询日志2.1 参数说明与两种开启方式慢查询日志涉及的核心参数主要有这么几个slow_query_log负责开关slow_query_log_file指定日志文件路径long_query_time设置阈值log_output决定输出到文件还是表log_queries_not_using_indexes决定是否记录没走索引的查询。还有一个容易被忽略的log_slow_admin_statements它控制ALTER TABLE、ANALYZE TABLE这类管理语句是否也被记录默认是不记录的。开启方式有永久和临时两种。临时开启适合在排查问题的时候用直接执行SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;注意修改long_query_time之后当前会话不会立即生效需要重新连接或者单独在会话内再执行一次SET long_query_time 1;永久开启就需要改my.cnf配置文件在[mysqld]段下加[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 min_examined_row_limit 100min_examined_row_limit这个参数我单独说一下它表示只有扫描行数超过指定值的语句才可能被记录。用它来配合log_queries_not_using_indexes使用可以挡住那些虽然没走索引但只扫了几行的查询减少日志噪音。比如一个只有几十行的配置表扫全表也很快没必要每次都记下来。改完配置文件后重启MySQL或者用SET GLOBAL在线调整。我建议线上优先用在线调整等确认参数合理之后再写进配置文件避免一次重启引入额外风险。2.2 日志输出到文件和表的取舍log_output参数有两个可选值FILE和TABLE也可以同时写两个。默认是FILE也就是写到磁盘文件里这也是我推荐的做法原因很简单文件写入开销低格式稳定后续用工具分析方便。TABLE模式是把慢查询记录到mysql.slow_log表里好处是可以用SQL直接查比如按执行时间排序、按语句归类统计听着很方便。但实际用起来有几个问题第一mysql.slow_log表本身也是InnoDB表写入慢日志要走事务高并发下反而增加数据库负担第二如果慢查询很多这张表会越来越大清理由此变得麻烦第三mysqldumpslow、pt-query-digest这些分析工具都是基于文本日志设计的你用TABLE模式就得先把数据导出来才能用工具多了一道手续。我之前在一个测试环境里试过TABLE模式查起来确实直观但回到生产环境还是老老实实换回了FILE。有一条经验可以分享如果确实想用SQL分析慢查询更推荐的做法是开FILE模式然后用pt-query-digest把日志解析成可查询的汇总信息既有文本日志的稳定性和兼容性又不用牺牲查询的便利性。2.3 生产环境的参数建议根据我处理过的几个线上项目的经验生产环境的慢日志配置可以参考这样一套组合参数建议值说明slow_query_logON长期开启采集本身就是一种基础能力long_query_time1业务在线系统推荐起点可逐步收紧log_queries_not_using_indexesON捕获潜在的全表扫描但要配合min_examined_row_limitmin_examined_row_limit100过滤无关紧要的小表全扫描log_outputFILE稳定、兼容性好log_slow_admin_statementsOFF管理语句不用记避免噪音这套配置的核心思路是宁可多抓一些可疑的也不要漏掉真正的问题SQL同时通过min_examined_row_limit把明显无害的全表扫描过滤掉。很多团队的慢日志形同虚设就是因为只开了开关没有配套分析机制日志堆在那里没人看。开日志只是第一步把日志用起来才是关键。另外提一句磁盘空间的规划。慢日志文件大小在业务波动时很难预估建议把慢日志目录放在独立磁盘分区并且和binlog、数据文件分开。我见过不止一次因为慢日志写满磁盘导致数据库直接hang住的案例后面排查部分我会详细讲。3. 慢查询日志的阅读与分析3.1 日志格式逐字段拆解把慢日志打开之后一次典型的记录长这样# Time: 2025-03-14T10:23:45.123456Z # UserHost: app_user[app_user] [10.0.3.18] Id: 48217 # Query_time: 2.385400 Lock_time: 0.000122 Rows_sent: 89 Rows_examined: 450892 SET timestamp1741935225; SELECT u.id, u.username, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.create_time 2025-03-01 ORDER BY o.create_time DESC LIMIT 20;第一行Time表示语句执行的开始时间格式是UTC标准时间第二行记录的是账号、来源IP和连接ID第三行是最核心的四个指标Query_time是语句实际执行时间Lock_time是等待锁的时间Rows_sent是最终返回给客户端的行数Rows_examined是执行过程中扫描过的行数。我反复跟团队说慢日志里最有价值的三个数就是Query_time、Rows_examined和Rows_sent。Rows_examined和Rows_sent的比值是判断一条SQL效率的关键指标。如果一条查询扫描了45万行最后只返回89行说明大量行在扫描过程中被过滤掉了要么是索引缺失要么是查询条件写得不合理。反过来如果Rows_examined和Rows_sent接近说明扫描的行基本都用上了SQL本身效率可观。SET timestamp1741935225这一行也别忽略它表示这条语句在数据库内部的实际时间戳方便你把它和业务日志、监控系统的时间对上。因为日志时间显示的是UTC而业务上你通常需要本地时间两者有换算关系排查跨时区问题的时候容易踩坑。3.2 用mysqldumpslow做初步统计日志文件大了之后一条一条看是不现实的。MySQL自带了一个mysqldumpslow工具能做最基础的统计归类。它的核心能力是把结构相似、只是字面量不同的SQL归成同一种模板比如上面那条SQL里2025-03-01这个日期值会被替换成N10.0.3.18这个IP会被替换成S然后做汇总。最常用的几个命令# 按平均查询时间排序显示前10条 mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log # 按执行次数排序的top 10 mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log # 按总耗时排序的top 10 mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log其中-s参数指定排序方式-t指定输出条数。mysqldumpslow还有一个很实用的能力默认会把相似的语句归并所以你能直接看到同样一类SQL一共出现了多少次、平均耗时多少、总耗时占比多少。这个信息对判断问题优先级很有帮助。比如某个查询平均只执行0.1秒但一天执行了几十万次累计消耗可能比几条2秒的慢查询更值得关注。不过mysqldumpslow的缺点也很明显它只能做统计不能告诉你语句查询计划的问题在哪也不能直接生成优化建议。对复杂SQL的分析还是得上pt-query-digest。3.3 用pt-query-digest做深度分析pt-query-digest是Percona Toolkit套件里的核心工具可以说它是分析MySQL慢查询日志的事实标准。它在mysqldumpslow的基础上做了大量增强可以生成按照Query_time、执行次数、Rows_examined等维度的综合报告还能输出每个SQL模板的响应时间分布有详细的百分比分布比如95%的耗时、最大耗时这个对判断语句耗时是否稳定非常有帮助。安装Percona Toolkit的方式比较简单在CentOS系直接用包管理器装就行yum install percona-toolkit使用方式也很直接pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt生成的报告分两部分第一部分是整体概览告诉你日志里一共捕捉了多少种SQL模板、总查询时间、各类指标的总和第二部分是每个SQL模板的详细排名每条包含执行次数、总耗时、平均耗时、响应时间分布的百分位值以及取样出来的完整SQL文本。我一般拿到这份报告后会优先看两部分内容。第一是排在整体概览里最消耗时间的几种SQL模板它们的总耗时占比往往说明问题集中在哪几张表上这个比单看平均时间更有全局意义。第二是某个SQL模板的95%响应时间。平均耗时低的SQL未必没有性能隐患如果它的95%耗时明显大于平均耗时说明这个查询偶尔会出现大幅波动很可能跟锁等待、数据分布不均衡或者缓存命中率波动有关这类问题用EXPLAIN看不出来必须靠分布信息才能定位。4. 从慢日志定位到SQL优化的具体路径4.1 EXPLAIN怎么看慢日志告诉你哪条SQL慢EXPLAIN负责回答为什么慢。EXPLAIN是MySQL提供的查询计划解读工具在慢SQL前面加上EXPLAIN关键字MySQL会输出这条语句的执行计划包括访问哪些表、按什么顺序访问、用没用到索引、预计扫描多少行等等。EXPLAIN SELECT u.id, u.username, o.order_no FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE o.create_time 2025-03-01 ORDER BY o.create_time DESC LIMIT 20;看EXPLAIN的输出我建议优先看这几个字段字段关注点type全表扫描是ALL走索引是range或ref最理想是const/eq_refkey实际选用的索引经常出现NULL说明没走索引rowsMySQL估算的需要扫描的行数跟慢日志里的Rows_examined可以做交叉验证ExtraUsing filesort表示排序没走索引Using temporary表示用了临时表这俩通常都是优化信号type字段是最直观的性能信号。如果看到ALL意味着MySQL在扫全表这是最需要警惕的如果看到range说明走了一个范围扫描虽然能用索引但扫描范围大不也一定高效ref表示用了非唯一索引等值查询const和eq_ref通常是主键或者唯一索引命中性能最好。4.2 索引问题的典型场景从慢日志里提取出来的SQL反复出现的问题其实就那么几类。第一类是WHERE条件里的列没建索引尤其常见于关联查询的关联字段。比如上面例子里的orders.user_id和orders.create_time如果orders表上只有主键索引那么LEFT JOIN时MySQL就要对orders表做全表扫描Rows_examined自然高得离谱。解决办法就是给关联字段和外键列建复合索引。第二类是索引建了但SQL写法导致索引失效。这是最能坑人的一类。比如在WHERE条件里对索引列做函数运算WHERE DATE(o.create_time) 2025-03-14MySQL基本不会对DATE()函数处理过的列走索引范围扫描正确写法是WHERE o.create_time 2025-03-14 00:00:00 AND o.create_time 2025-03-15 00:00:00同样的问题还有隐式类型转换。如果create_time列是字符串类型你传了数字进去或者反过来都会让MySQL放弃索引。这类问题在排查时最容易让人一头雾水因为索引明明存在EXPLAIN里看到的却是全表扫描。第三类是ORDER BY和GROUP BY没吃到索引的红利。MySQL的索引天然按序存储如果ORDER BY的字段顺序和索引顺序一致排序就能直接走索引Extra里不会出现Using filesort。反之如果你写的排序字段和现有索引顺序对不上MySQL就得把数据捞出来额外排一遍数据量大时这就是慢查询的根源之一。4.3 常见的优化手法定位到问题之后优化手法大概有这么几个层次。第一优先是改SQL写法让查询能命中合适的索引能用覆盖索引最好。覆盖索引指的是索引本身包含了查询需要的所有列这样MySQL可以直接从索引里拿数据不需要回表效率提升非常明显。第二优先是调整索引结构。比如原来只有单列索引(user_id)现在发现查询经常同时过滤user_id和create_time那就建一个复合索引(user_id, create_time)注意列的顺序等值条件的列放前面范围条件的列放后面。建复合索引是慢SQL优化里收益最高、也最常用的一招。第三是分页查询的优化。慢日志里经常能看到深分页的问题比如SELECT * FROM orders WHERE user_id 123 ORDER BY id DESC LIMIT 100000, 20;LIMIT的偏移量越大MySQL需要扫描和丢弃的行就越多这也是Rows_examined偏高的常见原因。优化思路一般是改成子查询先拿主键再回表SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders WHERE user_id 123 ORDER BY id DESC LIMIT 100000, 20 ) t ON o.id t.id;改完之后内层子查询可以直接走索引覆盖外层再按主键批量取数据性能提升往往非常显著。5. 实际踩坑记录与常见问题排查5.1 日志文件写满磁盘这绝对是我见过最多的一类事故。慢日志开启之后如果遇到某个时段业务量激增或者某条SQL性能严重退化日志文件会在几小时内膨胀到几十GB直接把磁盘写满数据库随之hang住或拒写。有一次线上故障排查到最后发现根因就是慢日志把盘写满了而那天的慢查询本身问题还不算大典型的治病治出更大的病。我对策是两手准备。第一把慢日志文件放到独立磁盘分区和数据目录分开这样即使写满也只影响日志不会立刻拖垮数据库。第二做好日志轮转清理策略要跟上。mysqldumpslow这类工具只负责分析不负责清理。我习惯用crontab定期执行日志切割和压缩# 0点2分执行日志轮转 2 0 * * * mv /var/log/mysql/mysql-slow.log /var/log/mysql/mysql-slow-$(date -d yesterday %Y%m%d).log mysqladmin flush-logsflush-logs之后MySQL会重新创建新的慢日志文件旧文件就能安全压缩归档保留14天或者30天之后可以删除。如果没有日志轮转你会在某天发现慢日志文件大得连打开都费劲分析工具跑一次要几分钟反而拖累了排查效率。5.2 阈值设成0导致的全链路变慢之前提到把long_query_time设成0是个常见误区这里展开说一下后果。阈值设成0意味着每一条执行完毕的SQL都会触发慢日志写入数据库每秒钟要处理成千上万次日志写入操作文件写IO肉眼可见地上升高并发场景下系统整体吞吐直接下滑。我记得有一次帮一个团队排查接口响应变慢的问题最后发现他们测试环境把慢日志阈值改成0忘了改回来上线之后所有查询都背上了日志写入的额外开销整个服务的P99延迟从80毫秒涨到了500毫秒。排查方法也简单看到慢日志文件的增长速率和生产QPS明显相关同时数据库的IO监控里写入量异常升高就要怀疑是不是阈值设太小了。把阈值改回1秒之后再观察一段时间性能数据基本能恢复正常。这个坑的教训是慢日志阈值是给问题语句设的不是给所有语句设的。你在测试环境为了全量分析设成0切回生产之前一定要检查参数别把调试配置带上线。5.3 慢日志里的慢未必是SQL自己慢很多时候我们从慢日志里抓出一条语句发现它本身的执行时间只有几十毫秒但日志里记录的是几秒差异非常大。这种情况下问题往往不在SQL本身而在执行SQL时的环境状态。Lock_time高是比较常见的一种。如果日志里Query_time很大Lock_time也占到相当比例说明语句大部分时间花在等待锁上而不是执行本身。这时候要查的是并发事务、长事务和锁竞争。可以从information_schema.innodb_trx表看当前活跃事务SELECT trx_id, trx_state, trx_started, trx_rows_locked, trx_rows_modified FROM information_schema.innodb_trx ORDER BY trx_started;如果发现某个事务启动时间特别早、持有的行锁特别多它极有可能就是阻塞别人执行的那把大锁。找到它之后得回业务侧确认是长事务没提交还是代码里事务范围控制不当。另一种情况是磁盘IO和CPU资源紧张。当数据库主机本身负载很高即使一条普通SQL也可能在日志里被记成慢查询因为它排队的时间算进了总耗时。这种问题靠优化SQL解决不了需要从主机层面入手看CPU是否打满、IO是否抖动、内存缓冲区是否不够用。慢日志能定位慢但慢的原因需要结合系统监控综合判断。5.4 日志记录与功能开关的联动陷阱用log_queries_not_using_indexes的时候我也踩过一个坑。这个参数的本意是捕获所有没走索引的查询哪怕它执行很快也要记录。它在发现隐性问题方面很有价值但如果不加min_examined_row_limit的配合就会把大量查询效率低但业务量小的语句全部记进日志。比如一张只有几百行的配置表每次全表扫描才消耗几毫秒但架不住被高频调用日志文件照样被刷得飞快。另外还有一个细节MySQL的慢查询日志只记录成功的查询不记录因为超时被kill的查询也不记录被优化器拦下来的语法错误语句。所以慢日志并非零丢失的完整记录它只是一个抽样工具这一点在做数据分析的时候心里要有数。还有一个容易被忽略的参数是log_slow_filter它可以按Rows_examined超过多少行、扫描行数超过返回行数多少倍等条件做更精细的过滤。如果日志量太大可以在官方文档里查一下这个参数的选项它对控制日志噪音很有帮助。5.5 慢日志分析工具选择分析慢日志工具的选型也很重要。mysqldumpslow适合快速看个大概pt-query-digest适合深度分析但这两者都要求你能访问服务器和日志文件。如果你在Windows上办公日志在远程服务器上我建议先把日志拉下来再本地分析或者直接在服务器上把跑完的报告文件下载下来看比在线连数据库看效率高。还有一个比较新的趋势是把慢日志数据接入监控平台或日志分析系统比如让采集进程定时把慢日志解析成结构化数据再通过Elasticsearch或Grafana做可视化。这样做的好处是能看趋势比如某个SQL模板的耗时随时间的变化曲线。我现在的做法是每天凌晨用pt-query-digest跑一次全量分析再配合Grafana面板展示Top SQL的平均耗时和总耗时趋势。日常值班只需要看面板发现问题再去翻当天明细日志。这套流程跑下来处理慢查询的效率比之前纯看文件高了很多。至于像Navicat、DBeaver这类客户端可视化工具它们一般不会直接展示慢日志主要用来执行SQL和看执行计划在优化阶段配合使用还不错。真正定位慢查询还是得靠数据库层面的日志和分析工具这个定位要先搞清楚。6. 一套可以照抄的慢查询排查流程最后把我自己日常处理慢查询的完整流程整理成一份可执行的清单这套流程我用了很久基本覆盖了从发现到闭环的所有环节。确认慢日志开启且阈值合理生产环境一般是1秒可调整。用mysqldumpslow先看Top SQL模板确定问题集中在哪几张表、哪几个查询。用pt-query-digest查看每个SQL模板的响应时间分布重点看总耗时占比和95%分位耗时。对嫌疑SQL执行EXPLAIN检查type、key、rows、Extra定位是缺索引、索引失效还是排序回表问题。针对问题做优化改SQL写法、调整索引结构、优化分页方式优先选收益高、改动小的方案。优化上线后持续观察慢日志对比优化前后相同SQL模板的平均耗时和出现频率。定期做日志轮转和归档避免久未清理导致磁盘故障。这套流程每一步需要的工具都是开箱即用的不需要额外采购商业产品。慢查询优化本来就不是什么神秘工程只要日志开得好、分析细、优化到位绝大多数线上性能问题都能在一个工作日内找到方向。我在实际运维中最大的体会是慢查询日志真正难的不是技术而是坚持看、持续管。很多团队把日志开完就再也不碰直到线上出了问题才想起来翻一翻那就错过了慢查询日志最有价值的使用方式——在问题还没引爆之前提前把隐患处理掉。我的习惯是每周固定抽出时间跑一遍统计看看有没有新冒出来的慢SQL模板。如果一周下来日志里全是老面孔那说明这段时间的优化是有效果的如果出现了新模板就按上面的流程走一遍顺手就能解决。这个内容后续还能继续扩展的方向是把慢查询数据和业务监控打通。比如结合APM系统按用户维度看接口慢的原因或者把慢SQL模板按业务线分类然后分配给对应开发负责。慢查询日志本身只是一个起点但它背后牵引出来的数据库监控、容量规划和性能治理能力才是真正的价值所在。