1. 慢查询日志到底是什么为什么值得花时间折腾接触MySQL有一段时间的兄弟应该都听过“慢查询”这个词面试题里也经常出现。它的核心机制很简单MySQL会把执行时间超过你设定阈值的SQL语句按照特定格式写到日志文件里方便你事后定位“到底哪条SQL拖慢了业务”。但这玩意儿真正用起来远不止“打开开关”这么简单。我见过不少团队要么压根没开启慢查询日志要么开是开了但从没看过一眼要么看了发现全是些无意义的记录没法落地优化。慢查询日志的价值不在于“有没有”而在于你能不能从里面挖掘出真正值得优化的SQL并且用一套系统的方法把它们治理掉。这篇内容就是围绕这套系统方法展开的。我会从配置参数、日志格式、分析工具、SQL优化案例、常见坑点这几个维度逐层拆开尽量还原我在实际工作中排查慢查询、优化数据库性能的完整路径。适合刚接触MySQL优化、被慢查询困扰、以及想搭一套相对规范的慢SQL治理流程的开发者参考。2. 慢查询日志的开关与参数配置2.1 核心参数一览与动态调整先看一组最基础的参数记住它们后面所有操作都围绕这几个配置展开参数名默认值作用slow_query_logOFF总开关ON/OFFslow_query_log_file主机名-slow.log日志文件路径long_query_time10超过多少秒算慢查询单位秒支持小数log_queries_not_using_indexesOFF是否记录未走索引的查询log_slow_admin_statementsOFF是否记录ALTER TABLE等管理语句min_examined_row_limit0扫描行数超过该值的查询才会被记录log_outputFILE日志输出方式FILE或TABLE这些参数大部分是动态的意味着你不需要重启MySQL就能调整。我最常用的排查姿势是-- 临时开启慢查询日志阈值设置为2秒 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 2; SET GLOBAL log_queries_not_using_indexes ON;这里有个容易被坑的细节long_query_time设置后新建立的连接才会生效已有连接不受影响。也就是说你改完参数后可以在当前会话里先看一下-- 查看当前会话的慢查询阈值 SHOW VARIABLES LIKE long_query_time;如果还是旧值重新连接一次即可。我当时第一次排查时改完参数后一直在原会话里测试发现某条查询明明超过了阈值却没被记录折腾半天才反应过来是会话级参数没刷新。另外强调一点log_output TABLE可以把慢查询写入mysql.slow_log表适合在无法访问日志文件的场景下临时排查。但我不建议长期用这个方式因为日志量大时对mysql.slow_log的写入本身会带来性能开销而且查询这个表还可能造成元数据锁竞争。生产环境还是以文件输出为主。2.2 持久化配置的正确姿势动态修改只能管到MySQL重启前想要永久生效必须把配置写进my.cnf或my.ini。拿Linux环境下MySQL 8.0举例通常配置文件在/etc/my.cnf。在[mysqld]段落里加入[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 2 log_queries_not_using_indexes 1 log_slow_admin_statements 1 min_examined_row_limit 100这里有几个点想单独说明第一slow_query_log_file的目录必须提前创建并且MySQL的运行用户通常是mysql要有写入权限。否则启动时虽然不会报错但日志文件根本写不进去等你想看日志时才发现是空的。用chown mysql:mysql /var/log/mysql把目录属主改掉。第二min_examined_row_limit 100的意思是扫描行数超过100行的查询才会被记录。这个参数配合long_query_time一起用可以过滤掉那种“扫描行数很少但耗时偏长”的偶然波动记录让日志更聚焦。第三log_slow_admin_statements默认关闭意味着ALTER TABLE、OPTIMIZE TABLE这类管理语句不会进慢日志。但这些操作在表数据量大的时候耗时往往非常恐怖。建议在低峰期开启方便事后追溯大表结构变更带来的性能影响。不过要留意开启后日志量可能明显增大。改完配置文件后重启MySQL生效。如果你用的是云数据库RDS之类的一般控制台里就有慢查询参数模板不用直接改配置文件但参数含义是相通的。3. 慢查询日志的格式拆解与文件管理3.1 一条慢日志记录到底在说什么先看一条真实的慢查询日志记录# Time: 2024-03-18T09:32:11.123456Z # UserHost: app_user[app_user] [192.168.1.100] Id: 882345 # Query_time: 12.345678 Lock_time: 0.000123 Rows_sent: 10 Rows_examined: 4523110 SET timestamp1734341521; SELECT a.*, b.* FROM orders a LEFT JOIN order_items b ON a.order_id b.order_id WHERE a.status PAID AND a.created_at 2024-01-01 ORDER BY a.created_at DESC LIMIT 10;逐行解读一下TimeSQL执行完成的时间点注意这是UTC时间如果你的服务器设了东八区换算成北京时间要加8小时。排查业务问题时时区搞错会让你完全对不上时间线。UserHost执行SQL的用户和客户端IP。定位到具体来源方便后续找对应业务方。Id线程ID配合SHOW FULL PROCESSLIST可以追踪连接状态。Query_time执行总耗时。这是判断“慢不慢”的核心指标包含CPU、IO、锁等待等所有时间。Lock_time锁等待时间。如果这个值占比高说明问题不单纯是SQL执行慢而是有锁竞争。Rows_sent返回给客户端的行数。Rows_examined存储引擎扫描的行数。Query_time和Rows_examined的组合是判断SQL是否健康的关键。常见的坑是Query_time很大、Rows_examined也很大这是典型的全表扫描还有一种更隐蔽的情况是Query_time大、Rows_examined并不大这可能是因为锁等待、CPU资源争抢、或者事务提交时的IO压力。看到日志里的SET timestamp...;了吗下一行往往紧跟SQL文本本身SET timestamp记录的是这条SQL执行的时间戳。做日志归档时可以用这个字段来精确还原执行时间而不是用Time那行的UTC时间。3.2 日志切割与保留周期慢日志文件会一直增长如果不做切割迟早把磁盘撑爆。我在生产环境踩过一次坑有个核心库的慢日志积累了一个多月占了几十个GB的磁盘空间最后直接导致MySQL数据目录所在分区告警。而且日志文件过大之后用mysqldumpslow分析也会变得极慢。如果MySQL版本支持log_slow_log_admin之类的旋转机制可以用mysqladmin命令刷新日志。Linux环境下更通用的做法是借助系统自带的logrotate# /etc/logrotate.d/mysql-slow /var/log/mysql/mysql-slow.log { daily rotate 7 missingok compress delaycompress dateext postrotate /usr/bin/mysqladmin -uroot -p***** flush-logs endscript }这段配置的含义是每天切割一次保留7天旧日志压缩存储。关键是postrotate里的flush-logs它让MySQL关闭当前的慢日志文件句柄、重新打开一个新文件否则logrotate只是改了文件名MySQL还在往旧文件里写。如果数据库有多实例或集群每个实例的日志文件路径不同logrotate配置文件要分开写。另外flush-logs这个操作会刷新所有日志包括binlog如果你对binlog管理比较敏感要确认一下当时的binlog策略避免flush动作引发不必要的日志切换。4. 慢日志的分析工具从自带到第三方4.1 mysqldumpslow官方自带轻量级首选日志文件有了接下来就是分析。MySQL自带的mysqldumpslow工具是最容易上手的# 按平均查询时间排序查看前10条 mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log-s参数指定排序方式常用的有c按次数计数排序t按总耗时排序at按平均耗时排序l按锁等待时间排序-t指定只显示前N条。-g后跟正则比如-g orders只匹配包含orders的SQL。mysqldumpslow会把SQL文本里的具体数字替换成N把字符串替换成S这样能自动聚合结构相似的SQL。比如下面两条SELECT * FROM users WHERE id 1001; SELECT * FROM users WHERE id 2002;会被聚合成同一条记录SELECT * FROM users WHERE id N;这个聚合逻辑对统计高频慢SQL很有帮助否则每条SQL单独一行根本没法看清问题的核心。但mysqldumpslow的缺点也很明显它对复杂查询的支持比较粗暴正则替换有时会过度聚合导致不同执行计划的SQL被混在一起。功能也比较弱没有执行计划、没有占比分析适合快速瞄一眼不适合深度优化。4.2 pt-query-digest真正拿来干活的分析工具如果要做系统性的慢查询治理我强烈推荐Percona Toolkit里的pt-query-digest。它是目前社区里用得最多的慢日志分析工具能把日志按指纹聚合、按各种维度排序还能输出明细报告。安装方式CentOS/RHEL系# 从Percona官方仓库安装 yum install percona-toolkit使用方式# 直接分析文件输出到终端 pt-query-digest /var/log/mysql/mysql-slow.log # 输出到文件方便保存 pt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt # 分析最近2小时的日志通过watch持续输出 pt-query-digest --since2h /var/log/mysql/mysql-slow.log输出内容很长我一般重点看三块第一块是Overall统计它按指纹分组的SQL列表。每条SQL都会标出Query_time的占比、中位数、95分位数。你应该优先关注“总耗时占比”最高的SQL而不是单次耗时最高的SQL。第二块是用例报告它把相似的SQL归成一个uls用例。你可以点开每一组看到完整的SQL文本模板以及该模板的执行次数、平均耗时、最大耗时。第三块是LIMIT后的明细包括响应时间分布、Rows examine、Rows sent等维度。pt-query-digest还有一个实用功能是支持从多个来源读取日志还能和tcpdump配合抓取实时流量里的慢查询。不过除非你做的是流量采样分析否则生产环境直接分析慢日志文件就够了。4.3 分析慢日志时要警惕的误读慢日志分析最忌讳的是“只看单条慢SQL忽略整体趋势”。我举个例子你打开日志看到一条排序特别的SQLORDER BY xxx LIMIT 10耗时8秒于是你急着去优化这条SQL。但pt-query-digest的报告可能告诉你这类SQL每天执行几万次每次虽然不到1秒但累积起来才是数据库CPU的真正大头。所以我整理了一套分析优先级先看Query_time占比最大的Top 10 SQL指纹。再看平均耗时高的、且执行频率也很高的SQL。再看锁等待时间异常的SQL。最后才看单次耗时极值型的SQL。另外Rows_examined / Rows_sent的比例也很关键。一个理解成本很低的类比是查10条数据却翻了100万行说明索引没有起到应有的过滤作用。如果在日志里看到大量这种“扫描行数远大于返回行数”的记录优先考虑加索引或改写SQL。5. 从慢日志定位到的SQL怎么落地优化5.1 经典案例深分页问题慢日志里最常见的一类SQL是带有LIMIT offset, size的分页查询。我第一次优化这类SQL时表里有几百万条订单记录业务方要做报表翻页每页20条SELECT * FROM orders ORDER BY created_at DESC LIMIT 100000, 20;这条SQL执行时间高达8秒原因其实不复杂。MySQL执行LIMIT 100000, 20时需要先把前100020条全部查出来排好序然后丢弃前100000条只返回最后20条。页数越深扫描的无效行越多。当时的优化方案是延迟关联SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY created_at DESC LIMIT 100000, 20 ) t ON o.id t.id;思路是先在一个小范围的子查询里只查主键id快速定位到目标页的数据位置再用主键关联回原表取完整行。这样扫描的列变少了排序和回表的成本大幅下降。实测效果很立竿见影执行时间从8秒压到了0.5秒以内。这背后的道理是ORDER BY created_at DESC LIMIT offset, size实际上依赖于一个有序的索引如果created_at没有索引排序就是全文件排序filesort数据量一大自然就慢。更进一步的优化手段是“分区游标分页”也就是不传偏移量而是传上一页最后一条记录的IDSELECT * FROM orders WHERE created_at 2024-01-15 10:00:00 ORDER BY created_at DESC LIMIT 20;这种用条件代替偏移量的写法无论翻到多深扫描范围始终固定在当前页附近性能非常稳定。唯一的缺点是要业务方配合改接口设计不能无脑传page和size。5.2 经典案例隐式类型转换再来看一个很隐蔽的优化场景。慢日志里出现过一条查询SELECT * FROM user_phone WHERE phone 13800138000;phone字段明明是VARCHAR类型但条件里传的是数字。MySQL对VARCHAR和数字比较时会把VARCHAR列隐式转换成浮点数再比较。这意味着phone列上即使有索引也没法走索引只能全表扫描。解决方式是让传入的参数类型和字段类型保持一致SELECT * FROM user_phone WHERE phone 13800138000;另一个类似场景是字符集不一致导致的索引失效比如表A的字段是utf8mb4表B的字段是utf8两表关联时也可能导致索引失效。这些案例不是靠优化SQL能解决的要从表结构设计的源头去规范字段类型。慢日志里看到这类SQL光改SQL本身还不够我一般会再向前追溯一步为什么业务代码里会传入一个数字很多是ORM框架自动生成的SQL或者API接口层没有做类型校验。协调业务方把参数类型修正过来才能真正解决问题。5.3 索引设计不能凭感觉慢SQL定位出来后大部分人第一反应就是“加索引”。但乱加索引同样会埋雷写入性能下降、占用额外磁盘空间、甚至让查询优化器选错执行计划。我的习惯是拿到一条慢SQL后先做三件事看执行计划EXPLAIN一下关注type、key、rows、Extra几个关键列。看实际的数据分布比如字段里不同值的数量判断区分度。看这条SQL涉及的查询模式是等值查询多还是范围查询多要不要覆盖索引。针对WHERE status PAID AND created_at 2024-01-01 ORDER BY created_at DESC这类查询一个常见的索引设计是ALTER TABLE orders ADD INDEX idx_status_created_at (status, created_at);status是等值条件放在左边created_at是范围加排序条件放在右边。这个复合索引能让查询既走等值过滤又避免排序。如果反过来建(created_at, status)等值条件status在右侧排序优化带来的收益可能就没有了。当然索引设计还要考虑业务的实际查询组合。最理想的状态是把200种查询模式汇总找出重叠度最高的公共索引而不是每来一条慢SQL就加一个索引。类似“一个字段建5个索引”的情况优化器反而容易晕。6. 常见问题与排查技巧实录6.1 慢日志打开了但什么都没记录这是出现频率最高的问题。快速排查路径如下第一步确认开关和阈值SHOW VARIABLES LIKE slow_query_log; SHOW VARIABLES LIKE long_query_time; SHOW VARIABLES LIKE slow_query_log_file;第二步手动跑一条耗时超过阈值的SQL验证。比如用SLEEP(3)把阈值设成2秒SELECT SLEEP(3);然后看日志文件是否新增内容。第三步检查文件路径和权限。如果slow_query_log_file指定的目录不存在或不可写MySQL可能直接把记录丢弃或者写到一个奇怪的位置。用mysqld的错误日志确认一下有没有相关报错。另一个隐蔽原因是MySQL 8.0的log_error_verbosity设置。如果错误日志级别太低某些和慢日志相关的信息不会输出但通常不影响slow.log的写入。所以最核心还是先排除权限和路径问题。6.2 慢日志文件太大分析工具跑不动日志文件有几十GB时pt-query-digest也会变慢。这里有两个实用技巧用--since或--until限定时间范围只分析最近一小时或一天的日志pt-query-digest --since2024-03-18 09:00:00 --until2024-03-18 10:00:00 /var/log/mysql/mysql-slow.log或者先把慢日志按日期归档用grep提取一批SQL文本再分析。如果只是想确认有没有“新增的高频慢SQL”可以先看最近半小时的日志而不是每次都全量扫描。另外logrotate切割日志后建议把旧日志名里带上日期。dateext这个配置能让日志文件名带日期后缀分析时一目了然。6.3 主从环境下的慢日志策略在主从复制架构里从库的慢日志经常被忽略。但很多慢查询恰恰是主库一般、从库很慢因为从库的硬件配置可能偏低或者从库上跑着报表查询负载和主库不一样。MySQL提供了log_slow_slave_statements参数开启后从库上执行的中继日志中的SQL也会被记录到从库的慢日志里。这个参数默认是关闭的。如果你的从库承担着读流量建议开启并单独设置从库的long_query_time通常可以比主库宽松一些比如主库2秒就算慢从库5秒才算慢。这样可以减少从库日志的噪音又能在从库性能明显劣化时及时发现。还有一点要注意在MySQL 8.0里如果从库开启了log_slow_replica_statements而主库写入的SQL在从库回放时触发了慢查询阈值会被记录到从库慢日志。这和分析主库慢日志的思路略有不同更侧重于“回放性能”而不是“业务SQL性能”。6.4 慢查询不等于必须优化最后我想说一个心态问题。看到慢日志里出现耗时高的SQL先别急着优化先回答几个问题这条SQL是不是频繁执行的如果一天只执行两三次每次3秒其实影响有限。这条SQL是不是后台批量任务触发的如果是凌晨的统计脚本对用户无感知优先级可以往后排。这条SQL是不是锁等待导致的如果锁等待时间占了总耗时的一大半加索引解决不了问题要处理的是锁竞争和事务边界。执行计划是不是真的走了预期索引有时候优化器“抽风”换了执行计划分析起来完全是另一个方向。慢日志治理是个持续的过程不是一次性把Top 10优化完就结束了。数据量在涨业务查询模式在变索引也需要定期复查。我最开始做这部分工作时是每月把慢日志整体分析一遍输出一份报告和业务方确认每个高耗时SQL的优化优先级。坚持了几个月后慢查询的数量和总耗时明显下降团队对数据库性能的敏感度也上来了。7. 慢查询监控体系的延伸日志文件分析属于事后排查能解决大部分问题但不具备实时性。等日志积累到一定程度再分析问题往往已经影响用户一段时间了。如果想更主动一点可以把慢日志接入监控体系核心指标包括慢查询总条数Top N SQL的耗时趋势每小时/每天的慢查询分布MySQL没有内置Prometheus exporter但社区方案比较成熟比如mysqld_exporter配合Grafana能把MySQL的全局状态指标包括Slow_queries计数抓出来做可视化。再配合告警规则比如“连续5分钟慢查询数超过100条”就报警基本可以覆盖大部分告警需求。用mysqld_exporter采集SHOW GLOBAL STATUS LIKE Slow_queries这个计数器的变化率就能判断慢查询是否在突增。不过要注意这个计数是所有会话累计的如果之前一直开启慢日志计数会一直累积需要看增量而不是绝对值。Grafana里用rate()或者increase()函数处理一下即可。有了这套监控慢查询就从“等着用户投诉再排查”变成了“指标异常就能提前介入”。比如业务大促期间流量翻倍慢查询数量必然上升。如果监控里看到慢查询涨幅和流量涨幅不成比例就要赶紧打开慢日志看是不是某个新上线的功能引入了重量级查询。我个人在实际操作中的一个体会是慢查询优化最大的成本不是技术方案本身而是推动业务方配合改造。数据库加索引可能只需要几分钟但业务方改SQL写法、改接口参数、调整事务边界往往要经历需求排期。所以更现实的路径是先把数据库侧能做的优化做完比如加索引、调整配置再带着慢日志的量化数据去和业务方沟通让对方直观看到“这条SQL占了数据库总负载的30%”这样推进起来会顺利很多。最后再分享一个小技巧我自己在分析慢日志时习惯先看一眼Rows_examined / Rows_sent的倍数。如果这个倍数长期超过1000即便当前不慢也值得提前优化。因为数据量继续增长后这类SQL随时会从“不慢”变成“很慢”。慢日志不仅是救火工具更是体检报告用好了能让数据库在业务增长面前多撑很长一段时间。