1. 从一条日志到一次深夜救火慢查询日志到底在记录什么我在一线摸爬滚打这么多年有个很深的体会数据库的问题往往不是突然爆炸的而是慢慢变卡的。你凌晨三点被电话叫醒心里骂骂咧咧打开监控发现订单表某条SQL跑了八秒钟拖垮了整个服务。这时候你翻出慢查询日志看到那条令人血压升高的SQL顺着它找到该死的全表扫描顺手补一个索引世界瞬间清净了。所以我的习惯是接手任何一套MySQL第一件事就是先把慢查询日志打开它就是你给数据库装的黑匣子。简单说慢查询日志是MySQL记录执行时间超过指定阈值的SQL语句的日志文件。它记录的不只是语句文本还包括执行时间、锁等待时间、扫描行数这些关键信息。打开它你就能回答三个业务最关心的问题数据库哪里慢了、为什么会慢、优化之后有没有真的变快。这篇文章适合谁如果你是后端开发、DBA、运维工程师或者正在自己折腾个人项目的全栈开发者这篇内容能解决你在配置、分析、优化慢查询日志过程中遇到的绝大部分问题。我会从配置参数讲起带你读懂日志文件本身的内容再把整个“日志分析 → 问题定位 → 索引优化 → 效果验证”的闭环流程盘一遍。最后还会分享一些你在官方文档里很难一次性找到的坑和技巧这些是我实践里真金白银换来的。2. 正式动手前的准备理解慢查询日志的底细2.1 慢查询日志能抓到什么抓不到什么很多人把这玩意儿想得太简单以为开了就万事大吉。实际上慢查询日志的抓取规则有几个维度理解清楚才能用好它。慢查询日志主要记录三类内容记录类型判定条件举个例子慢SQL执行时长 ≥ long_query_time默认10秒SELECT * FROM orders WHERE statusPAID 跑了15秒未走索引查询log_queries_not_using_indexesON 时触发对2亿行的大表做全表扫描的 DELETE管理语句log_slow_admin_statementsON 时触发ALTER TABLE、OPTIMIZE TABLE 等DDL操作这里有个容易忽略的点慢查询日志只会记录执行完成的语句。如果你的SQL因为锁等待超时被kill掉了它可能不会被记录但这恰恰说明你的系统存在严重的锁竞争问题需要从别的手段去排查。还有一点如果SQL压根没执行比如语法错误它同样不会出现在慢查询日志里。另外参数 min_examined_row_limit 可以设置一个最小扫描行数只有扫描行数超过这个值的查询才会被记录。这个参数和 long_query_time 配合使用很有效能过滤掉那些执行时间虚高但其实只扫了几行的语句避免日志被垃圾淹没。2.2 打开它需要多大的代价很多人不敢开慢查询日志担心影响性能。说实话在MySQL 5.1之后的版本慢查询日志的开销已经非常小了。日志落盘一般都在10%以内对于绝大多数业务来说完全可接受。真正影响性能的往往是同时打开 log_queries_not_using_indexes 之后的日志膨胀因为很多业务都有少量不走索引的查询每执行一次就记一条日志文件几个小时后就能膨胀到GB级别。我见过一个运维哥们儿开了未走索引记录之后没设阈值第二天早上磁盘直接爆了。所以配置的时候要考虑清楚日志文件的旋转策略、磁盘空间预留、以及是否需要对日志做定期清理这些都要提前想好。3. 从参数到实践一步步把慢查询日志配好3.1 最核心的配置参数一览折腾慢查询日志其实就是摆弄下面这几个参数。我按使用频率排了个序每个都标注了默认值和推荐配置参数名默认值推荐配置含义slow_query_logOFFON开关总闸slow_query_log_file主机名-slow.log/var/log/mysql/mysql-slow.log日志文件路径long_query_time10.0000001 或 0.5阈值秒超过即记录log_queries_not_using_indexesOFFON记录未利用索引的查询log_slow_admin_statementsOFFOFF是否记录管理语句min_examined_row_limit0100或1000扫描行数下限log_outputFILEFILE,TABLE日志输出方式每个参数背后都有设计意图。long_query_time 从10秒改到1秒你会立刻发现慢查询日志的“产量”暴增几倍所以在生产环境调整这个值要循序渐进看到日志量激增不用慌这是正常的。log_output 设置成 TABLE 会把日志写入 mysql.slow_log 表方便用SQL查询但写入本身有开销遇到高并发场景反而让数据库更吃力所以生产环境我一般建议用 FILE分析时再导入到分析工具中。MySQL 8.0 还新增了 log_slow_extra 参数会在日志中附加更细的耗时信息比如数据访问时间、写入时间等。在排查复杂场景时非常有用建议开启。3.2 几步搞定配置与重启直接上实操。我以 MySQL 8.0 在 CentOS 环境下举例其他版本大同小异。第一步查看当前的慢查询配置SHOW VARIABLES LIKE slow_query_log%; SHOW VARIABLES LIKE long_query_time; SHOW VARIABLES LIKE log_queries_not_using_indexes;第二步动态开启无需重启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 100;这里有个经典坑位long_query_time 设置后当前会话的变量值不会变需要新建会话才会看到效果。因为你改的是GLOBAL作用域当前连接里 LONG QUERY TIME 仍然保持着旧值。我当年排查了半天以为参数没生效实际上新查询已经按新阈值记录了只是我“看”到的值骗了我。第三步写进配置文件让重启后仍然生效[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_slow_extra 1注意配置文件里 long_query_time 不需要加引号写成整数或小数都可以。路径目录要让 mysqld 进程有写权限否则启动时会报错或者日志文件默默生成在别的位置。第四步验证是否生效SHOW VARIABLES LIKE long_query_time; SELECT SLEEP(2);然后看日志文件尾部正常情况下会出现一条记录了 SLEEP(2) 的慢查询记录。这一步实测一下比我嘴说一百句都管用。3.3 日志文件要不要定时清理慢查询日志不会自动切割所以一定要配合日志轮转。最简单的方式是用 logrotate配置一个按天切割的规则保留30天左右的日志。示例配置/var/log/mysql/mysql-slow.log { daily rotate 30 compress missingok notifempty }也可以考虑用 MySQL 自身的定时任务定期清理但更推荐用系统层面的 logrotate因为它不依赖MySQL实例的状态就算数据库挂了你日志切割仍然正常跑。切割完成后记得执行 FLUSH LOGS 让MySQL重新打开日志文件否则MySQL还是往旧文件里写文件句柄没换过来。4. 日志文件打开了可它到底在说什么4.1 读懂一条慢查询记录的每个字段配置好之后慢查询日志会不断累积内容。我挑一个典型记录拆开来看看# Time: 2025-01-10T08:30:15.123456Z # UserHost: app_user[app_user] localhost [127.0.0.1] Id: 12345 # Query_time: 3.567000 Lock_time: 0.012000 Rows_sent: 100 Rows_examined: 345678 SET timestamp1736483415; SELECT id, order_no, user_id, amount FROM orders WHERE status PAID AND create_time 2025-01-01 ORDER BY create_time DESC LIMIT 100;逐行解读Time语句执行的时间点注意这里带了时区信息默认是UTC如果你需要本地时间可以在分析的时候做偏移转换。UserHost哪个账号从哪个主机发起的连接。这里可以快速定位到是应用连接池的问题还是某个运维手工操作的语句。Query_time语句总耗时包含所有执行阶段。这是最重要的指标超过阈值的核心依据。Lock_time锁等待时间指等待表锁或行锁的时间。注意它不包含在 Query_time 里是两个独立维度。Rows_sent最终返回给客户端的行数。Rows_examined为了返回结果实际扫描了多少行。这是性能问题的第一信号——扫描20万行只返回100行说明索引设计或者SQL写法有问题。我常跟团队里的人讲一个粗口径的指标扫描行数和返回行数的比值如果超过100:1基本就要考虑优化了。这不是绝对的标准但作为快速判断的依据非常有效。SET timestamp...这一行也很有用它可以帮你还原SQL执行时具体的数据状态。在做基于时间段的对比分析时比如双十一那天的慢日志和平时对比它会给你非常有价值的线索。4.2 附带字段日志里的彩蛋如果开启了 log_slow_extraMySQL 8.0会记录更多字段。比如# Query_time: 3.567000 Lock_time: 0.012000 Rows_sent: 100 Rows_examined: 345678 # Thread_id: 12345 Schema: shop Info: SELECT ... # Full_scan: YES Full_join: NO Tmp_table: YES Tmp_disk_table: NO # Filesort: YES Filesort_on_disk: NOFull_scan 和 Full_join 是MySQL判断执行计划时是否发生了全表扫描或全量join这个信息比你自己去 EXPLAIN 一条条看要直观得多。Tmp_table 和 Filesort 表示语句是否使用了临时表和文件排序如果有意味着这条SQL的内存消耗可能比较大。举个例子一条需要 filesort 的SQL在做 ORDER BY 的时候如果排序的数据量超过 sort_buffer_size就会落盘性能立刻打折。在慢日志里能看到这些标记你就能快速判定是否需要调整 sort_buffer_size 或优化索引来消除排序。4.3 用命令和工具批量分析日志单条日志你肉眼看没问题但一晚上积累几千条慢查询你总不能一条条读。这时候用到两类工具。官方自带的 mysqldumpslowmysqldumpslow -t 10 -s c /var/log/mysql/mysql-slow.log这条命令按次数排序返回执行次数最多的前10条语句。还可以按平均执行时间排序mysqldumpslow -t 10 -s at /var/log/mysql/mysql-slow.logmysqldumpslow 的定位是“快速概览”适合你想在五分钟内搞清楚“瓶颈是不是集中在某几条SQL上”的时候用。它的缺点是会把类似SQL的变量替换成N导致某些统计不够精确但对大多数场景足够。Percona Toolkit 的 pt-query-digestpt-query-digest /var/log/mysql/mysql-slow.log slow_report.txt这个工具会生成一份结构化的报告整体概况、Top SQL排行榜、每个SQL的响应时间分布、执行计划样本等。它对慢日志的解析要精细得多还能把相似SQL按指纹聚合统计每个指纹的总耗时占比。排查线上问题时我第一个打开的就是这份报告它能快速告诉我“到底应该先优化哪条SQL”。如果日志量特别大还可以先用 grep 过滤时间范围awk $0 ~ /^# Time:/ {flag($0 # Time: 2025-01-10T00:00:00Z $0 # Time: 2025-01-11T00:00:00Z)} flag /var/log/mysql/mysql-slow.log day.log然后再分析 day.log效率会高很多。5. 从日志到优化一次完整的慢SQL排查实战5.1 案例背景订单查询为何慢到怀疑人生我拿一个实际案例走一遍完整流程。假设有一个电商系统的订单表 orders包含字段 id、order_no、user_id、status、amount、create_time数据量大约 2000 万行。某一天慢查询日志出现了大量如下记录# Query_time: 4.234000 Lock_time: 0.003000 Rows_sent: 20 Rows_examined: 1287653 SELECT order_no, user_id, amount, status, create_time FROM orders WHERE user_id 10086 AND create_time 2025-03-01 ORDER BY create_time DESC LIMIT 20;这个场景太典型了返回20条数据却扫描了128万行。出现这种问题的概率最高的原因就是索引缺失或者索引选择错误。我们直接去数据库确认一下执行计划EXPLAIN SELECT order_no, user_id, amount, status, create_time FROM orders WHERE user_id 10086 AND create_time 2025-03-01 ORDER BY create_time DESC LIMIT 20;结果令人窒息idtypekeyrowsExtra1ALLNULL20340001Using where; Using filesortuid没有索引direct全表扫描这不是找数据这是在翻整个仓库找一根针。5.2 针对性优化的完整操作过程第一步添加复合索引ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time DESC);这里有讲究。查询条件是 user_id 等值 create_time 范围排序也是 create_time 倒序。所以建一个 (user_id, create_time DESC) 的复合索引等值条件走第一列排序直接走索引顺序既能过滤又能去掉 filesort。MySQL 8.0 支持降序索引如果你用的是5.7或更老版本不需要写DESC也可以MySQL会在索引上反向扫描性能差别不大但8.0建议按实际排序方向建能减少一些边界情况的性能损失。第二步再次验证执行计划EXPLAIN SELECT order_no, user_id, amount, status, create_time FROM orders WHERE user_id 10086 AND create_time 2025-03-01 ORDER BY create_time DESC LIMIT 20;关键变化idtypekeyrowsExtra1refidx_user_create3650Using index conditionrows 从 2000万骤降到 3650Using filesort 也消失了。这个变化肉眼可见说明索引生效了。第三步再跑一遍原始SQL确认耗时SELECT order_no, user_id, amount, status, create_time FROM orders WHERE user_id 10086 AND create_time 2025-03-01 ORDER BY create_time DESC LIMIT 20;耗时从4.2秒降到了0.015秒提升了差不多300倍。这个数我说实话大家可能觉得很夸张但我真做过的优化里这种效果不是个例。索引对于数据库的性能提升就是这种量级的前提是你的SQL写法能命中索引。第四步观察后续慢查询日志优化后我一般会持续观察几天慢查询日志确认这条SQL不再出现。同时也证明了一件事加了索引这个问题就被根治了。如果过几天又出现就得考虑是不是选择性不够、数据分布变化或者有别的SQL也在吃同样的索引。5.3 不只是加索引针对不同问题的优化策略加索引是解决慢SQL最常见的手段但不是全部。我把遇到过的慢查询日志问题大概分了几类每类的解法侧重点不同问题特征根因优化策略Rows_examined 巨大Rows_sent 小缺少索引或未命中索引针对性加复合索引改写SQL避免无谓的范围扫描Lock_time 很高锁竞争或长事务调整事务隔离级别检查是否有未提交的长事务优化更新顺序减少锁持有时间Filesort 标记频繁出现排序字段未走索引索引字段与ORDER BY顺序对齐或调整 sort_buffer_sizeTmp_disk_table 标记出现临时表过大落盘了优化GROUP BY/DISTINCT增大 tmp_table_size/max_heap_table_size同样的SQL执行时间不稳定数据分布不均或缓存命中率低调整缓冲池大小分析统计信息是否过期必要时用 FORCE INDEX 或重新收集统计信息我印象很深的另一个案例一条 UPDATE 语句在慢查询日志里频繁出现Rows_examined 很小但 Lock_time 每次都超过 1 秒。最后定位到是另一个服务在频繁地以互斥顺序更新同一批行导致行锁等待严重。这种问题光加索引没用得调整业务逻辑把更新顺序统一成主键递增锁竞争立刻缓解。所以慢查询日志给你的只是一个起点你要结合业务去推导和验证根因。5.4 加索引之后索引会不会白加还是看日志说话很多人加完索引就不管了这是不对的。我见过一个兄弟给一个三年来没有任何写操作的统计表加了好几个索引搞得维护成本上升、写入性能受损其实业务上根本没有慢查询。所以加索引前一定要先用慢查询日志和数据统计确认“痛点SQL”真实存在加索引后持续观察一段时间的日志判断问题是否已消失而不是凭感觉“优化一下比较安心”。还有一种情况是索引加了但SQL根本不走它这时候要往回查是否因为函数运算、隐式类型转换、前导通配符等原因导致索引失效。比如SELECT * FROM orders WHERE DATE(create_time) 2025-03-01;这个写法即使 create_time 上有索引也不会走因为函数让索引失去作用。正确的写法是范围查询SELECT * FROM orders WHERE create_time 2025-03-01 00:00:00 AND create_time 2025-03-02 00:00:00;这些坑在慢查询日志里都会有迹可循多积累经验你看到一条SQL基本就能猜到执行计划长什么样。6. 常见问题与排查技巧实录6.1 日志没生成可能踩了这几个雷我接手过很多业务系统的排查光“慢查询日志没生效”这个问题就见过好几种原因雷区一参数类型写错了。slow_query_log 在命令行用 ON/OFF配置文件里写 1/0 或 ON/OFF 都可以但有的人在动态设置时写了SET GLOBAL slow_query_log TRUE;MySQL会直接报错。写对了参数才能正确生效。雷区二long_query_time 设置后当前连接不生效。这个前面提过这里再强调一次改完GLOBAL值一定记得重新连接否则你查询当前值总会觉得“没生效”。雷区三日志文件权限不对。我遇过一次MySQL日志文件是root拥有的mysqld进程以mysql用户跑导致一直写入失败但服务正常。排查技巧是看SHOW VARIABLES LIKE slow_query_log_file;确认路径再用ls -l查看文件属主和权限如果不对直接 chown 即可。雷区四日志输出到了TABLE而不是FILE。如果你把 log_output 设置成 TABLE文件里自然是啥也没有。要看 mysql.slow_log 表里有没有数据SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;雷区五采样阈值太低日志文件过大导致轮转不及时磁盘写满。这个问题虽然不在“日志未生成”范畴但与之相关。建议把 log_queries_not_using_indexes 打开之后一定搭配 min_examined_row_limit 一起用比如设置成1000可以过滤掉很多只扫几十行的语句。6.2 有个查询已经优化好了为什么日志里还频繁出现我在实际生产环境排查时经常遇到这类问题明明加了索引明明执行时间已经降到50毫秒了但慢查询日志里仍然有它的身影而且次数还不少。排查思路是这样的——先确认这条语句是不是真的没走索引。用EXPLAIN看一下如果已经走了索引执行时间也明显低于 long_query_time那就只有一个解释你看到的日志是旧记录还没被轮转清理。如果日志文件很大里面可能攒了几天甚至几周的记录需要按时间过滤来看最近的情况。还有一种可能你把 long_query_time 配得很低比如0.5秒而这条SQL在高并发下平均40毫秒但偶尔因为缓存失效、buffer pool冷启动等原因执行到了600毫秒仍然会被记录。这种情况不算异常属于正常抖动。一方面可以适度调高阈值另一方面要结合整体监控指标来判断系统是否真的健康。6.3 不要被慢查询日志骗了识别“假慢”场景慢查询日志记录的是执行耗时但执行耗时不等于“性能问题”。有几个典型的“假慢”场景我遇到过多次场景一刚启动的系统buffer pool是冷的。一条SQL平时跑20毫秒冷启动后第一次跑可能要2秒被记录进日志。这种慢是无害的第二次跑就正常了。你要是拿冷启动的日志去优化很容易白忙活。所以看慢日志时不妨关注同一指纹SQL的多次出现情况和耗时分布如果只有零星一两次不用太紧张。场景二大表DDL或统计信息更新导致瞬时锁表。这类操作会引起后续查询排队表现为大量平时正常的SQL突然变慢。慢日志里能看到同一时间段内不同SQL集体变慢但它们的SQL本身都没问题。这时候要复盘是不是有同事在执行大表DDL或者备份脚本在跑。场景三服务器底层IO抖动的连锁反应。云厂商的磁盘性能在某些时段会波动or整机CPU被其他实例抢占这时候你的慢查询日志会无差别产出大量“受害者SQL”。如果看到不同SQL的耗时集体上升先看宿主机负载和IO延迟别急着对SQL下手。识别假慢的能力很重要它决定了你是否会浪费精力在错误的优化方向上。我的习惯是如果一条SQL只在慢日志里出现一两次且四周的SQL没有呼应性的变慢先观察如果它反复出现才介入深入排查。6.4 慢日志最佳实践的几条军规说了这么多最后沉淀几条我自己的规矩第一上线即开慢查询日志。任何一个新项目或新数据库实例上线第一天就把慢查询日志开起来。阈值开始可以设置为1秒后续根据业务情况调整。很多问题只有事后的日志能回答事后没有日志就只能拍脑袋了。第二定期归档和分析。每周或者每两周用 pt-query-digest 生成一份慢查询分析报告沉淀到固定的地方。这样做的好处是形成趋势视图比如某个SQL的耗时逐周增长你就能提前发现数据量上升带来的索引失效风险而不是等它真变成事故。第三慢日志与监控系统联动。慢查询日志只是数据库性能数据的一部分要结合CPU、内存、磁盘IO、锁等待等监控指标一起看。我在实际运维中经常干的一件事是从慢日志里提取 Top 5 SQL再去看对应时间段的系统指标通过交叉对比找到真正的根因。第四对每次优化做记录。我开始做数据库优化后养成了一个习惯每次做优化都记录下来包括问题SQL、执行计划变化、优化前后耗时对比、慢日志变化、上线时间。时间一长这套数据就成了团队里价值最高的数据库知识库。新成员接手任何一套系统先看这些记录比看十本理论书都管用。7. 写在最后的经验谈慢查询日志这个东西入门门槛很低开个开关就行但想要用好它却需要理解参数背后的设计逻辑、日志字段的隐含信息以及数据库底层的索引和锁机制。我见过太多人把慢查询日志当成“事后追责工具”出了问题才翻出来看。其实它对预防问题更有价值——通过定期观察慢SQL的变化趋势你可以在业务还没有明显感知的时候提前发现性能瓶颈、提前优化而不是等用户先发现“这个页面打不开”你才手忙脚乱地去查。最后再分享一个小技巧建索引之前先用EXPLAIN看执行计划建完索引之后再用EXPLAIN对照一次把前后的type、rows、Extra在笔记里留下。时间久了你会发现你对SQL性能的感觉越来越准很多时候看一眼慢日志里的SQL脑子里就已经能浮现出它大概的执行计划了。这种直觉才是慢查询日志带给你最值钱的东西。