如果你的线上MySQL实例最近响应变慢但又说不出慢在哪个具体环节手头还没有可靠的排查依据那我建议你先别急着调参数或加缓存第一步应该打开慢查询日志看看。这是所有MySQL性能排查里成本最低、信息量最大的一步没有之一。这篇文章我会从慢查询日志的完整配置讲起再到日志内容的逐行解读、常见误区和坑最后用一个实际案例把从发现慢SQL到定位根因的整个链路走一遍。内容适合刚接触MySQL优化的人也适合已经用过慢查询日志但想系统梳理一遍的开发者。我尽量说人话把底层逻辑讲清楚保证你看完能直接上手。1. 慢查询日志到底是什么它记录什么、不记录什么慢查询日志是MySQL官方提供的一种运行日志专门用来记录执行时间超过指定阈值的SQL语句。它的核心作用只有一个帮你回答我的数据库时间到底花在哪条SQL上了这个问题。默认情况下MySQL的慢查询日志是关闭的。很多从开发转过来的同事第一次排查性能问题时习惯性地执行SHOW VARIABLES LIKE slow_query_log发现结果是OFF这时候就需要先开启它。这里有个关键点值得强调不只是执行时间长的SELECT会被记录UPDATE、DELETE、INSERT这些写操作同样在慢查询日志的监控范围内。我实际遇到过不少案例系统的写放大问题就是靠慢查询日志里那些执行了十几秒的UPDATE语句暴露出来的。要理解慢查询日志先要搞清楚MySQL判断一条SQL慢不慢的标准是什么。它主要看三个变量long_query_time执行时间超过这个阈值单位秒默认10秒的SQL才会被记录而且这个时间是实际执行时间不包括等待锁的时间。log_queries_not_using_indexes开启后会记录所有没有走索引的查询这个选项非常有用后面我会专门展开讲。min_examined_row_limit只有当SQL扫描行数超过这个值的才会被记录通常配合上面那项使用避免把扫描行数很少但没走索引的小查询也记录下来。这三个变量组合起来决定了慢查询日志的灵敏度。默认的10秒阈值对大多数业务场景来说太宽松了线上一个INSERT事务跑个3秒可能已经是灾难级延迟但用默认配置它根本不会被记录。所以我一直建议生产环境至少把long_query_time调成1秒业务高峰期能接受的极限延迟是多少阈值就设成多少。还需要澄清一个常见误解慢查询日志记录的不仅仅是慢的SQL也包括了执行计划不合理的SQL。比如一张表只有几千行数据一条全表扫描的查询可能只跑了200毫秒从执行时间看它不算慢。但如果你开启了log_queries_not_using_indexes它同样会被记进日志里。这类SQL单独看不致命问题是当表数据量涨到几百万行时同样的执行计划很可能会从200毫秒恶化到几秒。慢查询日志在这个层面其实是执行计划质量的晴雨表。一句话总结慢查询日志的价值不在于事后追责而在于提供一条从现象到根因的侦查线索。它不告诉你SQL为什么慢但告诉你该往哪查。2. 一步步开启慢查询日志参数说明与实操命令开启慢查询日志的方式有两种一种是临时修改运行时变量服务器重启后失效另一种是写进配置文件永久生效。我建议在排查问题阶段用前者确认配置参数合适后再固化到配置文件里。先看临时开启的方式。MySQL 5.7及8.0版本默认都支持以下命令mysql SET GLOBAL slow_query_log ON; mysql SET GLOBAL long_query_time 1; mysql SET GLOBAL log_queries_not_using_indexes ON;这里有一个我在实际运维中经常看到的坑修改long_query_time后当前已经打开的会话里这个值不会自动更新只有新建立的连接才会生效。如果你改了参数之后发现日志里还是只有超过10秒的SQL先别怀疑参数没生效检查一下是不是用了老的连接在跑查询。mysql SHOW VARIABLES LIKE long_query_time; mysql SHOW VARIABLES LIKE slow_query_log_file;查看慢查询日志的存放位置和当前状态建议开启后第一时间确认日志文件路径避免之后找不到日志在哪。默认路径一般在数据目录下文件名通常是主机名-slow.log这种格式。如果你用的是Docker部署的MySQL特别容易踩这个坑——容器里的日志目录如果没有做volume映射容器一删日志就没了排查到一半才发现证据丢了非常尴尬。再把参数固化到配置文件里。MySQL的配置文件在Linux上通常是/etc/my.cnf在Windows上通常是my.ini。在[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 100关于log_queries_not_using_indexes这个选项我想多说两句。生产环境中如果直接把它设成ON并且不设置min_examined_row_limit很可能会被日志刷屏。因为很多小表查询都不走索引这些查询本身执行得很快大量记录会迅速填满磁盘。正确做法是同时设置min_examined_row_limit让它只记录扫描行数超过一定数量的无索引查询。比如min_examined_row_limit1000就表示一条查询至少要扫1000行才会被记录。配置文件修改后需要重启MySQL服务才能生效这是和临时方式最大的区别。如果你用的是云数据库RDS一般都有参数组功能直接在控制台修改对应参数并应用即可不用自己重启实例。还有一种输出方式值得了解MySQL可以把慢查询日志写到表里也就是mysql.slow_log表用log_output参数控制。mysql SET GLOBAL log_output TABLE;写入表的好处是可以用SQL直接查比如按执行时间排序找出最慢的几条SQL比读文件方便得多。但缺点是表写入本身有开销高并发场景下不建议长期使用。我自己的习惯是日常排查用文件日志偶尔需要快速统计汇总才临时切到TABLE模式。MySQL 8.0里慢日志表用的是CSV存储引擎查询统计时效率不高可以先转换成InnoDB再分析ALTER TABLE mysql.slow_log ENGINE InnoDB;不过这里要提醒一下mysql.slow_log表本身也可能被记录到慢查询日志里形成一种递归记录的现象。实际使用中需要留意避免分析日志时混入噪音数据。3. 慢查询日志内容逐行拆解从一条真实日志学起打开慢查询日志文件里面不是表格也不是JSON而是一段段连续的多行文本。先看一个我在实际业务中抓到的真实例子# Time: 2024-06-15T10:32:18.123456Z # UserHost: app_user[app_user] [192.168.1.101] Id: 891023 # Query_time: 3.812345 Lock_time: 0.000182 Rows_sent: 1 Rows_examined: 537871 SET timestamp1718447538; SELECT id, order_no, amount, status FROM orders WHERE buyer_id 123456 AND status paid ORDER BY create_time DESC LIMIT 20;逐行来看这些信息TimeSQL执行的时间戳注意这里默认是UTC时间如果你的服务器时区是东八区需要在分析时把时间加上8小时否则你可能会发现日志和业务高峰期对不上。UserHost执行SQL的用户名和客户端IP多业务共库的环境里这个字段能快速定位是哪个应用发起的请求。Id连接线程ID可以配合SHOW PROCESSLIST或者performance_schema去关联当时的会话状态。Query_time这是最核心的字段表示SQL实际执行耗时。3.812345秒这个值超过阈值所以被抓进来了。Lock_time等待锁的时间。这个时间容易被误读很多人以为它指的是行锁等待其实它统计的是MySQL层等待表锁、元数据锁的时间InnoDB的行锁等待时间主要包含在Query_time里。Rows_sent最终返回给客户端的行数。这里只有1行但实际扫描了53万行筛选效率极低。Rows_examined执行过程中扫描的行数。这个数字直接反映了SQL的体力活有多大。理想情况下Rows_examined和Rows_sent应该接近如果两者差距悬殊说明索引设计或者SQL写法有明显问题。SET timestamp执行快照时的Unix时间戳用于还原SQL执行时的上下文环境。SQL文本真正被执行的那条语句多行SQL会完整保留换行格式。分析这么多字段最该抓住的其实是三个数字Query_time、Rows_sent、Rows_examined。5秒能跑完的SQL不可怕可怕的是每条都要扫50万行才返回几行。优化思路就是在扩大扫描和缩小扫描之间做文章——通过索引、改写SQL或者调整业务逻辑把Rows_examined降下来Query_time自然就降下来了。除了这些字段慢查询日志还有一种格式上的变化。MySQL 5.7开始支持将慢查询日志记录到系统表时使用更精细的时间精度8.0版本还能看到事务提交信息。不过万变不离其宗核心字段还是上面这些分析思路不需要变。4. 用mysqldumpslow和pt-query-digest分析日志常用工具的真实对比拿到慢查询日志文件后别急着逐条读慢日志是流水账如果线上SQL量大文件能达到几百MB甚至上GB。这时候需要工具帮忙做聚合统计。MySQL自带一个mysqldumpslow工具Percona Toolkit里有一款更专业的pt-query-digest我两个都用过给你分享一下真实的使用心得。先看mysqldumpslow它是MySQL安装包自带的不需要额外安装。常用姿势是# 按平均耗时排序查看前10条最慢的SQL归一化后的形式 mysqldumpslow -s at -t 10 /var/lib/mysql/mysql-slow.log输出结果会将SQL文本中的数字参数归一化成N比如WHERE id 123456会被显示为WHERE id N这样功能相同的SQL就能被聚合到一类里。这个设计非常实用否则成千上万条只差参数值的SQL会让统计完全失去意义。mysqldumpslow支持按多种维度排序c表示计数t表示总耗时at表示平均耗时l表示锁等待时间r表示返回行数。我建建议现场排查时先看at平均耗时再看c出现次数两个维度交叉起来能找到频率高且耗时高的高优先劣化对象。再来看pt-query-digest这是Percona Toolkit的核心工具功能比mysqldumpslow强很多但需要单独安装。使用方式# 解析慢查询日志输出到报表文件 pt-query-digest /var/lib/mysql/mysql-slow.log slow_report.txt生成的报告结构大概是这样的层次第一部分是整体报告列出总查询数、耗时分布、各个时间段的活跃情况。第二部分按查询的指纹fingerprint分组排名每组会展示典型SQL、总耗时、平均耗时、出现次数、Rows_examined和Rows_sent的百分位数。pt-query-digest比mysqldumpslow强的地方在于它能识别参数化后的查询指纹聚合更准确还能关联查询出现的时间分布。如果你是第一次到客户现场排查MySQL性能问题我建议优先用pt-query-digest信息密度高很多也能省去手工去换算的时间。工具对比总结如下对比项mysqldumpslowpt-query-digest安装复杂低随MySQL自带中需安装Percona Toolkit聚合准确度中基本的参数归一化高指纹聚合识别相似查询时间分布分析不支持支持能看到一天内各时段热点输出详细度简单排行报告完整含执行计划建议适合场景快速粗筛深度定位、性能审计实际工作里我会先用mysqldumpslow快速看一眼全局如果发现需要深挖的SQL再切pt-query-digest生成报告。现场解决问题时工具次数不重要能最快定位问题最重要。5. 一个从慢查询日志到索引优化的完整排查案例讲完工具用一个贴近真实业务的案例把排查链路串起来。假设你负责订单系统最近陆续收到业务方反馈订单查询变慢你打开了慢查询日志发现有一类SQL频繁出现# Query_time: 2.867419 Lock_time: 0.000108 Rows_sent: 20 Rows_examined: 489231 SELECT id, order_no, buyer_id, status, amount, create_time FROM orders WHERE status paid ORDER BY create_time DESC LIMIT 20;第一步先分析这个SQL的特征。Rows_examined达到了48万多Rows_sent才20扫了大量数据但只返回20行典型的大范围扫描排序取头部模式。问题出在哪里接下来用EXPLAIN看一下执行计划mysql EXPLAIN SELECT id, order_no, buyer_id, status, amount, create_time FROM orders WHERE status paid ORDER BY create_time DESC LIMIT 20;执行结果会显示type是ALL或ref可能走了一个选择性很差的索引然后Extra列出现Using filesort。这说明MySQL先按status筛选出一大批记录有status索引的话再在内存或磁盘里排序最后取前20行返回。问题核心很清楚status字段的选择性太低了。订单表中绝大部分订单最终都变成paid状态用status做索引筛选出的结果集几乎等同于全表。MySQL需要把接近50万行都拉进排序缓冲区做一次完整的排序操作再丢弃后面的记录。它真正想要的是直接按create_time倒序扫描遇到statuspaid的就返回这样只要扫前几条就能拿到结果。优化方案先想到的是建立(status, create_time)复合索引。这个索引可以有效过滤status并保持create_time有序ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);建立索引后EXPLAIN的类型会变成refExtra不再出现Using filesort。因为查询只需沿着索引找到第一条statuspaid的记录连续取20条即可扫描行数会从48万骤降到几十行。不过我还想多说一种反直觉的优化思路把顺序反过来建索引也就是(create_time, status)。这种情况下查询直接按create_time倒序走索引每遇到一条记录就检查status是否为paid如果是就直接返回。因为订单更新后通常会尽快支付近期订单里paid占比很高往往扫几条就能凑齐20条扫描行数可能比(status, create_time)更少。这两种方案各有适用场景需要结合业务数据分布来决定。优化完再看实际效果。执行同样的查询耗时从2.8秒降到30毫秒Rows_examined从48万降到40行左右。这个案例说明一个朴素的道理慢查询日志里的每个数字都不是随便填的Rows_examined和Rows_sent的差距就是你在数据空间里白白付出的体力活。同类的SQL还有几种变形比如按时间范围查最近一小时未发货的订单、按状态加金额区间做筛选、分页深翻页时offset过大等等。抓到一个慢查询不要只修这一条而是抽象出模式去排查同类写法。6. 被忽略的问题慢查询日志本身的副作用和运维经验慢查询日志帮助我们揪出性能问题但日志功能本身也会引入新的性能开销和运维负担。先说性能层面日志文件本质上就是磁盘写入操作当开启了log_queries_not_using_indexes后如果业务中无索引查询数量很大慢日志可能以每秒几百条的速度增长持续写入会占用大量IO。这种IO问题在机械磁盘上尤其严重在SSD上相对好一些但也不是完全没有影响。针对这个问题有几点实操建议生产环境长期开启但阈值要合理。long_query_time至少设成1秒或2秒不建议设成0.1秒这种过低的阈值否则日志量会非常庞大。如果不是正在做专项排查不建议同时开启log_queries_not_using_indexes和很小的min_examined_row_limit。建议min_examined_row_limit至少1000起步。日志文件建议配置rotate策略。MySQL本身不提供自动轮转你需要在系统层面配置logrotate按天或按大小切割文件保留最近7-30天即可。否则日志文件越滚越大后续分析和磁盘空间都会出问题。再分享一个我踩过两次的坑慢查询日志和binlog不要放同一块磁盘。慢查询日志是持续写入的binlog也是持续写入的两个高写入量的文件放在同一个磁盘上会导致IO争用。如果条件允许把log_slow_query_log_file放到独立的磁盘或至少不同的目录挂载点上。还有权限相关问题。多人在同一套环境上排查问题时如果使用普通账号连接数据库需要确认该账号是否有权限修改全局参数特别是使用SET GLOBAL slow_query_logON时。生产环境的账号通常不建议授予SUPER或SYSTEM_VARIABLES_ADMIN权限可以考虑由DBA统一开启。在处理慢查询日志的存储时我建议对写入日志文件配置压缩或定期归档。分析完毕后文件可以gzip压缩起来节约空间同时保留证据以备后续复盘。很多团队在性能治理结束后直接把日志文件删掉等下次再需要分析时发现证据全没了容易把问题排查变成无源之水。7. 慢查询日志解决不了的问题配合performance_schema和EXPLAIN形成排查闭环熟练使用慢查询日志之后你会发现它有一个边界它告诉你哪条SQL慢但不告诉你这条SQL为什么慢。SQL慢的原因可以从几个层面分析慢查询日志只能覆盖到最外层。慢查询日志解决不了的场景大致有几类第一类是单条SQL执行时间正常比如200毫秒但每秒被调用几千次数据库整体CPU被打满。慢查询日志里全是不够慢的记录从日志入手很难看出热点。这时需要借助performance_schema的events_statements_summary_by_digest表按调用次数排序找出访问频率最高的SQL从频率维度补足慢查询日志覆盖不到的盲区。SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;第二类是SQL本身执行计划漂亮索引也走了但锁等待时间很长。慢查询日志里的Lock_time统计的是MySQL层锁InnoDB行锁的等待时间包含在Query_time里不做细分。要定位锁具体卡在哪个事务上需要配合SHOW ENGINE INNODB STATUS里的LATEST DETECTED DEADLOCK或者sys.innodb_lock_waits视图来看。第三类是复杂SQL的中间步骤。比如一条SQL里有子查询、多表JOIN、临时表操作靠慢查询日志只能看到总耗时看不到时间消耗在哪个具体步骤。这时建议在SQL前加EXPLAIN ANALYZEMySQL 8.0.18它能输出每一步的耗时和扫描行数比单纯EXPLAIN更接近真实执行情况。EXPLAIN ANALYZE SELECT ... FROM orders o JOIN order_items oi ON o.id oi.order_id WHERE o.buyer_id 123456;不同类型的问题对应不同的诊断工具我梳理一下我现场排查时的参考链路现象首选工具备选工具偶发性慢SQL慢查询日志performance_schema的events_statementsCPU持续高慢日志不明显events_statements_summary_by_digest按频率排序sys.statement_analysis锁等待导致慢查询SHOW ENGINE INNODB STATUSsys.innodb_lock_waits单条SQL内部耗时分布EXPLAIN ANALYZEoptimizer_trace磁盘IO高但SQL正常慢日志写入量评估系统级iostat这套组合打法能覆盖我日常遇到的大部分性能排查场景。慢查询日志是入口但不是终点。8. 慢查询日志相关的几个高频面试问题关于慢查询MySQL慢查询日志这个主题面试和团队内部技术分享中也常被问到。整理几个高频问题帮你同时巩固理解。第一个问题慢查询日志对数据库性能有多大影响回答思路慢查询日志本身需要额外的IO写入如果阈值设置过低或者记录了过多无索引查询会放大IO压力。但比日志本身更伤性能的是慢查询对应的SQL日志不会把数据库变慢它只是暴露了变慢的根源。第二个问题long_query_time改成1秒后当前连接不生效原因在于系统变量需要新连接才会重新读取。这个细节很多人踩过面试官问出来也是想看你是不是真的操作过。第三个问题慢查询日志里Rows_examined很大但Rows_sent很小说明什么说明SQL做了大量无效扫描通常代表索引缺失、索引选择性差、或者SQL写法导致执行计划走了不合理的路径。这是典型的索引优化信号。第四个问题如何区分一个慢SQL是IO瓶颈还是CPU瓶颈这就不能只看慢查询日志了需要结合系统层面的top、iostat看CPU和IO消耗再用EXPLAIN ANALYZE看执行计划内部耗时。慢日志告诉你什么慢系统监控告诉你资源的瓶颈在哪。第五个问题线上是否应该长期开启慢查询日志我的答案是一般建议长期开启但阈值要合理。大多数业务场景1秒这个阈值是比较合适的既不会产出过多日志又能捕获绝大多数性能劣化。至于log_queries_not_using_indexes可以定期开启一段时间做索引质量检查日常不建议长期开。这些问题背后没有标准答案核心考察的是对慢查询日志机制的理解深度和真实操作经验。9. 我给新手的执行清单从零开始建立慢查询监控最后分享一份可以照着做的执行清单帮你一步步把慢查询监控建起来避免遗漏关键环节。第一步确认当前配置状态。执行SHOW VARIABLES LIKE slow_query_log检查当前慢查询是否开启、日志路径在哪、阈值是多少。把这三项记下来。第二步开启慢查询并设置合理阈值。临时开启至少看当前效果建议long_query_time从1秒开始。开启后确认新连接生效。第三步抓取至少一天的日志。不要开启半小时就开始分析一天是基线周期能覆盖大多数业务高峰。如果接入层有明显的波峰波谷至少覆盖一个完整波峰。第四步用工具聚合分析。先mysqldumpslow粗筛再用pt-query-digest做完整报告重点看avg时长靠前、出现次数靠前的SQL用Rows_examined和Rows_sent的差距找出索引优化对象。第五步对每条目标SQL做EXPLAIN和EXPLAIN ANALYZE确认瓶颈具体在过滤、排序还是关联。不要跳过这一步直接建索引。第六步优化后重新抓日志对比优化前后Query_time、Rows_examined的数值变化。如果优化有效指标应当有数量级层面的改善。第七步把慢查询日志纳入自动化监控体系。定期分析日志把TOP SQL变化趋势作为一个常态化指标跟踪不要出了问题才想起来看。这套流程我用了很多年整理出来就是这么朴素直接。MySQL的排查工作从来没有银弹慢查询日志是最扎实的起点。把它看透彻再配合performance_schema和EXPLAIN ANALYSIS一层层往下挖绝大多数性能问题都能找到清晰的优化路径。
MySQL慢查询日志实战指南：从配置、分析到索引优化
如果你的线上MySQL实例最近响应变慢但又说不出慢在哪个具体环节手头还没有可靠的排查依据那我建议你先别急着调参数或加缓存第一步应该打开慢查询日志看看。这是所有MySQL性能排查里成本最低、信息量最大的一步没有之一。这篇文章我会从慢查询日志的完整配置讲起再到日志内容的逐行解读、常见误区和坑最后用一个实际案例把从发现慢SQL到定位根因的整个链路走一遍。内容适合刚接触MySQL优化的人也适合已经用过慢查询日志但想系统梳理一遍的开发者。我尽量说人话把底层逻辑讲清楚保证你看完能直接上手。1. 慢查询日志到底是什么它记录什么、不记录什么慢查询日志是MySQL官方提供的一种运行日志专门用来记录执行时间超过指定阈值的SQL语句。它的核心作用只有一个帮你回答我的数据库时间到底花在哪条SQL上了这个问题。默认情况下MySQL的慢查询日志是关闭的。很多从开发转过来的同事第一次排查性能问题时习惯性地执行SHOW VARIABLES LIKE slow_query_log发现结果是OFF这时候就需要先开启它。这里有个关键点值得强调不只是执行时间长的SELECT会被记录UPDATE、DELETE、INSERT这些写操作同样在慢查询日志的监控范围内。我实际遇到过不少案例系统的写放大问题就是靠慢查询日志里那些执行了十几秒的UPDATE语句暴露出来的。要理解慢查询日志先要搞清楚MySQL判断一条SQL慢不慢的标准是什么。它主要看三个变量long_query_time执行时间超过这个阈值单位秒默认10秒的SQL才会被记录而且这个时间是实际执行时间不包括等待锁的时间。log_queries_not_using_indexes开启后会记录所有没有走索引的查询这个选项非常有用后面我会专门展开讲。min_examined_row_limit只有当SQL扫描行数超过这个值的才会被记录通常配合上面那项使用避免把扫描行数很少但没走索引的小查询也记录下来。这三个变量组合起来决定了慢查询日志的灵敏度。默认的10秒阈值对大多数业务场景来说太宽松了线上一个INSERT事务跑个3秒可能已经是灾难级延迟但用默认配置它根本不会被记录。所以我一直建议生产环境至少把long_query_time调成1秒业务高峰期能接受的极限延迟是多少阈值就设成多少。还需要澄清一个常见误解慢查询日志记录的不仅仅是慢的SQL也包括了执行计划不合理的SQL。比如一张表只有几千行数据一条全表扫描的查询可能只跑了200毫秒从执行时间看它不算慢。但如果你开启了log_queries_not_using_indexes它同样会被记进日志里。这类SQL单独看不致命问题是当表数据量涨到几百万行时同样的执行计划很可能会从200毫秒恶化到几秒。慢查询日志在这个层面其实是执行计划质量的晴雨表。一句话总结慢查询日志的价值不在于事后追责而在于提供一条从现象到根因的侦查线索。它不告诉你SQL为什么慢但告诉你该往哪查。2. 一步步开启慢查询日志参数说明与实操命令开启慢查询日志的方式有两种一种是临时修改运行时变量服务器重启后失效另一种是写进配置文件永久生效。我建议在排查问题阶段用前者确认配置参数合适后再固化到配置文件里。先看临时开启的方式。MySQL 5.7及8.0版本默认都支持以下命令mysql SET GLOBAL slow_query_log ON; mysql SET GLOBAL long_query_time 1; mysql SET GLOBAL log_queries_not_using_indexes ON;这里有一个我在实际运维中经常看到的坑修改long_query_time后当前已经打开的会话里这个值不会自动更新只有新建立的连接才会生效。如果你改了参数之后发现日志里还是只有超过10秒的SQL先别怀疑参数没生效检查一下是不是用了老的连接在跑查询。mysql SHOW VARIABLES LIKE long_query_time; mysql SHOW VARIABLES LIKE slow_query_log_file;查看慢查询日志的存放位置和当前状态建议开启后第一时间确认日志文件路径避免之后找不到日志在哪。默认路径一般在数据目录下文件名通常是主机名-slow.log这种格式。如果你用的是Docker部署的MySQL特别容易踩这个坑——容器里的日志目录如果没有做volume映射容器一删日志就没了排查到一半才发现证据丢了非常尴尬。再把参数固化到配置文件里。MySQL的配置文件在Linux上通常是/etc/my.cnf在Windows上通常是my.ini。在[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 100关于log_queries_not_using_indexes这个选项我想多说两句。生产环境中如果直接把它设成ON并且不设置min_examined_row_limit很可能会被日志刷屏。因为很多小表查询都不走索引这些查询本身执行得很快大量记录会迅速填满磁盘。正确做法是同时设置min_examined_row_limit让它只记录扫描行数超过一定数量的无索引查询。比如min_examined_row_limit1000就表示一条查询至少要扫1000行才会被记录。配置文件修改后需要重启MySQL服务才能生效这是和临时方式最大的区别。如果你用的是云数据库RDS一般都有参数组功能直接在控制台修改对应参数并应用即可不用自己重启实例。还有一种输出方式值得了解MySQL可以把慢查询日志写到表里也就是mysql.slow_log表用log_output参数控制。mysql SET GLOBAL log_output TABLE;写入表的好处是可以用SQL直接查比如按执行时间排序找出最慢的几条SQL比读文件方便得多。但缺点是表写入本身有开销高并发场景下不建议长期使用。我自己的习惯是日常排查用文件日志偶尔需要快速统计汇总才临时切到TABLE模式。MySQL 8.0里慢日志表用的是CSV存储引擎查询统计时效率不高可以先转换成InnoDB再分析ALTER TABLE mysql.slow_log ENGINE InnoDB;不过这里要提醒一下mysql.slow_log表本身也可能被记录到慢查询日志里形成一种递归记录的现象。实际使用中需要留意避免分析日志时混入噪音数据。3. 慢查询日志内容逐行拆解从一条真实日志学起打开慢查询日志文件里面不是表格也不是JSON而是一段段连续的多行文本。先看一个我在实际业务中抓到的真实例子# Time: 2024-06-15T10:32:18.123456Z # UserHost: app_user[app_user] [192.168.1.101] Id: 891023 # Query_time: 3.812345 Lock_time: 0.000182 Rows_sent: 1 Rows_examined: 537871 SET timestamp1718447538; SELECT id, order_no, amount, status FROM orders WHERE buyer_id 123456 AND status paid ORDER BY create_time DESC LIMIT 20;逐行来看这些信息TimeSQL执行的时间戳注意这里默认是UTC时间如果你的服务器时区是东八区需要在分析时把时间加上8小时否则你可能会发现日志和业务高峰期对不上。UserHost执行SQL的用户名和客户端IP多业务共库的环境里这个字段能快速定位是哪个应用发起的请求。Id连接线程ID可以配合SHOW PROCESSLIST或者performance_schema去关联当时的会话状态。Query_time这是最核心的字段表示SQL实际执行耗时。3.812345秒这个值超过阈值所以被抓进来了。Lock_time等待锁的时间。这个时间容易被误读很多人以为它指的是行锁等待其实它统计的是MySQL层等待表锁、元数据锁的时间InnoDB的行锁等待时间主要包含在Query_time里。Rows_sent最终返回给客户端的行数。这里只有1行但实际扫描了53万行筛选效率极低。Rows_examined执行过程中扫描的行数。这个数字直接反映了SQL的体力活有多大。理想情况下Rows_examined和Rows_sent应该接近如果两者差距悬殊说明索引设计或者SQL写法有明显问题。SET timestamp执行快照时的Unix时间戳用于还原SQL执行时的上下文环境。SQL文本真正被执行的那条语句多行SQL会完整保留换行格式。分析这么多字段最该抓住的其实是三个数字Query_time、Rows_sent、Rows_examined。5秒能跑完的SQL不可怕可怕的是每条都要扫50万行才返回几行。优化思路就是在扩大扫描和缩小扫描之间做文章——通过索引、改写SQL或者调整业务逻辑把Rows_examined降下来Query_time自然就降下来了。除了这些字段慢查询日志还有一种格式上的变化。MySQL 5.7开始支持将慢查询日志记录到系统表时使用更精细的时间精度8.0版本还能看到事务提交信息。不过万变不离其宗核心字段还是上面这些分析思路不需要变。4. 用mysqldumpslow和pt-query-digest分析日志常用工具的真实对比拿到慢查询日志文件后别急着逐条读慢日志是流水账如果线上SQL量大文件能达到几百MB甚至上GB。这时候需要工具帮忙做聚合统计。MySQL自带一个mysqldumpslow工具Percona Toolkit里有一款更专业的pt-query-digest我两个都用过给你分享一下真实的使用心得。先看mysqldumpslow它是MySQL安装包自带的不需要额外安装。常用姿势是# 按平均耗时排序查看前10条最慢的SQL归一化后的形式 mysqldumpslow -s at -t 10 /var/lib/mysql/mysql-slow.log输出结果会将SQL文本中的数字参数归一化成N比如WHERE id 123456会被显示为WHERE id N这样功能相同的SQL就能被聚合到一类里。这个设计非常实用否则成千上万条只差参数值的SQL会让统计完全失去意义。mysqldumpslow支持按多种维度排序c表示计数t表示总耗时at表示平均耗时l表示锁等待时间r表示返回行数。我建建议现场排查时先看at平均耗时再看c出现次数两个维度交叉起来能找到频率高且耗时高的高优先劣化对象。再来看pt-query-digest这是Percona Toolkit的核心工具功能比mysqldumpslow强很多但需要单独安装。使用方式# 解析慢查询日志输出到报表文件 pt-query-digest /var/lib/mysql/mysql-slow.log slow_report.txt生成的报告结构大概是这样的层次第一部分是整体报告列出总查询数、耗时分布、各个时间段的活跃情况。第二部分按查询的指纹fingerprint分组排名每组会展示典型SQL、总耗时、平均耗时、出现次数、Rows_examined和Rows_sent的百分位数。pt-query-digest比mysqldumpslow强的地方在于它能识别参数化后的查询指纹聚合更准确还能关联查询出现的时间分布。如果你是第一次到客户现场排查MySQL性能问题我建议优先用pt-query-digest信息密度高很多也能省去手工去换算的时间。工具对比总结如下对比项mysqldumpslowpt-query-digest安装复杂低随MySQL自带中需安装Percona Toolkit聚合准确度中基本的参数归一化高指纹聚合识别相似查询时间分布分析不支持支持能看到一天内各时段热点输出详细度简单排行报告完整含执行计划建议适合场景快速粗筛深度定位、性能审计实际工作里我会先用mysqldumpslow快速看一眼全局如果发现需要深挖的SQL再切pt-query-digest生成报告。现场解决问题时工具次数不重要能最快定位问题最重要。5. 一个从慢查询日志到索引优化的完整排查案例讲完工具用一个贴近真实业务的案例把排查链路串起来。假设你负责订单系统最近陆续收到业务方反馈订单查询变慢你打开了慢查询日志发现有一类SQL频繁出现# Query_time: 2.867419 Lock_time: 0.000108 Rows_sent: 20 Rows_examined: 489231 SELECT id, order_no, buyer_id, status, amount, create_time FROM orders WHERE status paid ORDER BY create_time DESC LIMIT 20;第一步先分析这个SQL的特征。Rows_examined达到了48万多Rows_sent才20扫了大量数据但只返回20行典型的大范围扫描排序取头部模式。问题出在哪里接下来用EXPLAIN看一下执行计划mysql EXPLAIN SELECT id, order_no, buyer_id, status, amount, create_time FROM orders WHERE status paid ORDER BY create_time DESC LIMIT 20;执行结果会显示type是ALL或ref可能走了一个选择性很差的索引然后Extra列出现Using filesort。这说明MySQL先按status筛选出一大批记录有status索引的话再在内存或磁盘里排序最后取前20行返回。问题核心很清楚status字段的选择性太低了。订单表中绝大部分订单最终都变成paid状态用status做索引筛选出的结果集几乎等同于全表。MySQL需要把接近50万行都拉进排序缓冲区做一次完整的排序操作再丢弃后面的记录。它真正想要的是直接按create_time倒序扫描遇到statuspaid的就返回这样只要扫前几条就能拿到结果。优化方案先想到的是建立(status, create_time)复合索引。这个索引可以有效过滤status并保持create_time有序ALTER TABLE orders ADD INDEX idx_status_create_time (status, create_time);建立索引后EXPLAIN的类型会变成refExtra不再出现Using filesort。因为查询只需沿着索引找到第一条statuspaid的记录连续取20条即可扫描行数会从48万骤降到几十行。不过我还想多说一种反直觉的优化思路把顺序反过来建索引也就是(create_time, status)。这种情况下查询直接按create_time倒序走索引每遇到一条记录就检查status是否为paid如果是就直接返回。因为订单更新后通常会尽快支付近期订单里paid占比很高往往扫几条就能凑齐20条扫描行数可能比(status, create_time)更少。这两种方案各有适用场景需要结合业务数据分布来决定。优化完再看实际效果。执行同样的查询耗时从2.8秒降到30毫秒Rows_examined从48万降到40行左右。这个案例说明一个朴素的道理慢查询日志里的每个数字都不是随便填的Rows_examined和Rows_sent的差距就是你在数据空间里白白付出的体力活。同类的SQL还有几种变形比如按时间范围查最近一小时未发货的订单、按状态加金额区间做筛选、分页深翻页时offset过大等等。抓到一个慢查询不要只修这一条而是抽象出模式去排查同类写法。6. 被忽略的问题慢查询日志本身的副作用和运维经验慢查询日志帮助我们揪出性能问题但日志功能本身也会引入新的性能开销和运维负担。先说性能层面日志文件本质上就是磁盘写入操作当开启了log_queries_not_using_indexes后如果业务中无索引查询数量很大慢日志可能以每秒几百条的速度增长持续写入会占用大量IO。这种IO问题在机械磁盘上尤其严重在SSD上相对好一些但也不是完全没有影响。针对这个问题有几点实操建议生产环境长期开启但阈值要合理。long_query_time至少设成1秒或2秒不建议设成0.1秒这种过低的阈值否则日志量会非常庞大。如果不是正在做专项排查不建议同时开启log_queries_not_using_indexes和很小的min_examined_row_limit。建议min_examined_row_limit至少1000起步。日志文件建议配置rotate策略。MySQL本身不提供自动轮转你需要在系统层面配置logrotate按天或按大小切割文件保留最近7-30天即可。否则日志文件越滚越大后续分析和磁盘空间都会出问题。再分享一个我踩过两次的坑慢查询日志和binlog不要放同一块磁盘。慢查询日志是持续写入的binlog也是持续写入的两个高写入量的文件放在同一个磁盘上会导致IO争用。如果条件允许把log_slow_query_log_file放到独立的磁盘或至少不同的目录挂载点上。还有权限相关问题。多人在同一套环境上排查问题时如果使用普通账号连接数据库需要确认该账号是否有权限修改全局参数特别是使用SET GLOBAL slow_query_logON时。生产环境的账号通常不建议授予SUPER或SYSTEM_VARIABLES_ADMIN权限可以考虑由DBA统一开启。在处理慢查询日志的存储时我建议对写入日志文件配置压缩或定期归档。分析完毕后文件可以gzip压缩起来节约空间同时保留证据以备后续复盘。很多团队在性能治理结束后直接把日志文件删掉等下次再需要分析时发现证据全没了容易把问题排查变成无源之水。7. 慢查询日志解决不了的问题配合performance_schema和EXPLAIN形成排查闭环熟练使用慢查询日志之后你会发现它有一个边界它告诉你哪条SQL慢但不告诉你这条SQL为什么慢。SQL慢的原因可以从几个层面分析慢查询日志只能覆盖到最外层。慢查询日志解决不了的场景大致有几类第一类是单条SQL执行时间正常比如200毫秒但每秒被调用几千次数据库整体CPU被打满。慢查询日志里全是不够慢的记录从日志入手很难看出热点。这时需要借助performance_schema的events_statements_summary_by_digest表按调用次数排序找出访问频率最高的SQL从频率维度补足慢查询日志覆盖不到的盲区。SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;第二类是SQL本身执行计划漂亮索引也走了但锁等待时间很长。慢查询日志里的Lock_time统计的是MySQL层锁InnoDB行锁的等待时间包含在Query_time里不做细分。要定位锁具体卡在哪个事务上需要配合SHOW ENGINE INNODB STATUS里的LATEST DETECTED DEADLOCK或者sys.innodb_lock_waits视图来看。第三类是复杂SQL的中间步骤。比如一条SQL里有子查询、多表JOIN、临时表操作靠慢查询日志只能看到总耗时看不到时间消耗在哪个具体步骤。这时建议在SQL前加EXPLAIN ANALYZEMySQL 8.0.18它能输出每一步的耗时和扫描行数比单纯EXPLAIN更接近真实执行情况。EXPLAIN ANALYZE SELECT ... FROM orders o JOIN order_items oi ON o.id oi.order_id WHERE o.buyer_id 123456;不同类型的问题对应不同的诊断工具我梳理一下我现场排查时的参考链路现象首选工具备选工具偶发性慢SQL慢查询日志performance_schema的events_statementsCPU持续高慢日志不明显events_statements_summary_by_digest按频率排序sys.statement_analysis锁等待导致慢查询SHOW ENGINE INNODB STATUSsys.innodb_lock_waits单条SQL内部耗时分布EXPLAIN ANALYZEoptimizer_trace磁盘IO高但SQL正常慢日志写入量评估系统级iostat这套组合打法能覆盖我日常遇到的大部分性能排查场景。慢查询日志是入口但不是终点。8. 慢查询日志相关的几个高频面试问题关于慢查询MySQL慢查询日志这个主题面试和团队内部技术分享中也常被问到。整理几个高频问题帮你同时巩固理解。第一个问题慢查询日志对数据库性能有多大影响回答思路慢查询日志本身需要额外的IO写入如果阈值设置过低或者记录了过多无索引查询会放大IO压力。但比日志本身更伤性能的是慢查询对应的SQL日志不会把数据库变慢它只是暴露了变慢的根源。第二个问题long_query_time改成1秒后当前连接不生效原因在于系统变量需要新连接才会重新读取。这个细节很多人踩过面试官问出来也是想看你是不是真的操作过。第三个问题慢查询日志里Rows_examined很大但Rows_sent很小说明什么说明SQL做了大量无效扫描通常代表索引缺失、索引选择性差、或者SQL写法导致执行计划走了不合理的路径。这是典型的索引优化信号。第四个问题如何区分一个慢SQL是IO瓶颈还是CPU瓶颈这就不能只看慢查询日志了需要结合系统层面的top、iostat看CPU和IO消耗再用EXPLAIN ANALYZE看执行计划内部耗时。慢日志告诉你什么慢系统监控告诉你资源的瓶颈在哪。第五个问题线上是否应该长期开启慢查询日志我的答案是一般建议长期开启但阈值要合理。大多数业务场景1秒这个阈值是比较合适的既不会产出过多日志又能捕获绝大多数性能劣化。至于log_queries_not_using_indexes可以定期开启一段时间做索引质量检查日常不建议长期开。这些问题背后没有标准答案核心考察的是对慢查询日志机制的理解深度和真实操作经验。9. 我给新手的执行清单从零开始建立慢查询监控最后分享一份可以照着做的执行清单帮你一步步把慢查询监控建起来避免遗漏关键环节。第一步确认当前配置状态。执行SHOW VARIABLES LIKE slow_query_log检查当前慢查询是否开启、日志路径在哪、阈值是多少。把这三项记下来。第二步开启慢查询并设置合理阈值。临时开启至少看当前效果建议long_query_time从1秒开始。开启后确认新连接生效。第三步抓取至少一天的日志。不要开启半小时就开始分析一天是基线周期能覆盖大多数业务高峰。如果接入层有明显的波峰波谷至少覆盖一个完整波峰。第四步用工具聚合分析。先mysqldumpslow粗筛再用pt-query-digest做完整报告重点看avg时长靠前、出现次数靠前的SQL用Rows_examined和Rows_sent的差距找出索引优化对象。第五步对每条目标SQL做EXPLAIN和EXPLAIN ANALYZE确认瓶颈具体在过滤、排序还是关联。不要跳过这一步直接建索引。第六步优化后重新抓日志对比优化前后Query_time、Rows_examined的数值变化。如果优化有效指标应当有数量级层面的改善。第七步把慢查询日志纳入自动化监控体系。定期分析日志把TOP SQL变化趋势作为一个常态化指标跟踪不要出了问题才想起来看。这套流程我用了很多年整理出来就是这么朴素直接。MySQL的排查工作从来没有银弹慢查询日志是最扎实的起点。把它看透彻再配合performance_schema和EXPLAIN ANALYSIS一层层往下挖绝大多数性能问题都能找到清晰的优化路径。
