慢查询分析实战：从日志定位到索引优化与监控体系搭建

1. 为什么慢查询分析是数据库优化的第一入口先说说我自己的经历。早几年接手过一套线上订单系统业务量一上来每晚高峰时段接口响应时间直接从 80ms 飙到 3s 以上客服那边投诉单子堆了一沓。当时第一反应是加机器、加缓存、上读写分离折腾了一圈收效甚微。后来把数据库的慢查询日志打开一看吓一跳——某张订单表的几条 SQL 平均执行时间都在 2s 以上其中一条关联了 6 张表、跑了全表扫描单次查询扫描行数上千万。把这几条 SQL 揪出来优化之后高峰期接口耗时直接回落到 200ms 以内。这些年做过的数据库性能排查项目多了我得说一句慢查询分析是所有数据库优化工作的第一入口没有之一。原因很简单——数据库性能问题的外在表现可能千奇百怪CPU 飙升、连接数打满、锁等待严重、磁盘 IO 爆掉但九成以上追根溯源都能落到某几条“烂 SQL”上。而慢查询日志恰恰是最直接、最廉价的定位工具它把你数据库里所有“跑得慢”的 SQL 全部记录下来等于给了你一张现成的体检报告。这篇文章就以慢查询分析为主线完整走一遍“发现慢查询 → 分析原因 → 针对性优化 → 验证效果 → 建立长效机制”的实操流程。核心关键词是数据库性能监控、慢查询日志、SQL 分析、索引优化、执行计划。适用人群包括被线上数据库性能问题折磨的后端开发、刚入门但想系统掌握调优思路的 DBA、以及做性能测试和运维监控的朋友。不需要什么高深的理论基础只要你能连上数据库、会看日志、能执行几条 SQL就可以跟着这篇文章把你的数据库体检一遍。2. 先定位问题慢查询日志的开启与采集策略2.1 慢查询日志到底帮你记录了什么慢查询日志是数据库自带的功能它会把所有执行时间超过设定阈值的 SQL 语句记录到日志文件里。以 MySQL 为例核心配置项就三个slow_query_log是否开启慢查询日志ON 表示开启。slow_query_log_file日志文件的存放路径。long_query_time阈值时间单位秒执行时间超过该值的 SQL 会被记录。很多人以为把long_query_time设置成 1s 就够了我见过不少团队确实就这么干的结果上线后日志文件一天几个 GB全是些无关紧要的小查询。这里有一个关键理解慢查询日志记录的不是“绝对慢”的 SQL而是“相对你的业务需求”不够快的 SQL。一个小查询在测试环境跑 0.5s 没人在意但在线交易系统里如果每笔订单要经过 20 次这样的查询总耗时就被拖到 10s 了这时候 0.5s 也属于必须优化的范围。所以我的建议是long_query_time初期设置得宽松一些比如 2s 或 3s先把最严重的“大慢查询”捞出来等把明显的问题处理完之后再逐步下调阈值到 1s 甚至 0.5s做一轮更精细的排查。千万别一上来就设 0.1s日志量太大反而干扰判断。2.2 线上环境采集要注意的三个细节在正式环境开启慢查询日志之前有几个容易被忽略的细节这里一并说明磁盘空间预估。慢查询日志会持续写入如果事先没做好容量规划日志可能把磁盘写满导致数据库整体不可用。建议把日志单独放到一块磁盘上同时配置日志轮转或者定期清理策略。比如可以用 Linux 的logrotate工具按天切分日志保留最近 7 天的记录。不要用SET GLOBAL直接改线上配置除非你清楚自己在做什么。正确做法是先修改配置文件如 MySQL 的my.cnf再重启实例或者用SET GLOBAL动态修改后同时确认配置文件中同步了对应参数否则实例一重启之前的配置就丢了。慢查询日志不是越全越好。有些团队把log_queries_not_using_indexes也打开这个选项会把所有没走索引的查询都记录下来量非常大而且很多小表全表扫描反而不慢、没必要优化。我的建议是初期保持关闭只在专项排查索引问题时临时打开。下面给出一份标准的开启示例# 临时开启无需重启但重启后失效 mysql SET GLOBAL slow_query_log ON; mysql SET GLOBAL long_query_time 2; mysql SET GLOBAL slow_query_log_file /var/log/mysql/slow-query.log; # 永久生效写入配置文件 [mysqld] slow_query_log ON slow_query_log_file /var/log/mysql/slow-query.log long_query_time 2 log_queries_not_using_indexes OFF2.3 用 mysqldumpslow 快速浏览日志日志文件积累一段时间后里面可能躺着成百上千条 SQL逐条看肯定不现实。MySQL 自带了一个汇总工具mysqldumpslow可以按照执行次数、总耗时、平均耗时等维度把 SQL 聚合展示效果拔群。# 按平均查询时间排序显示前 10 条 mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log # 按总执行时间排序 mysqldumpslow -s t -t 20 /var/log/mysql/slow-query.log # 按执行次数排序 mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log实测下来-s at平均时间是我最常用的维度。因为它能把那些“单次执行不快但执行频率极高”的 SQL 和“单次执行极慢但执行频率极低”的 SQL 都拉到同一把尺子上比较。比如一条 SQL 单次执行 1.8s、一天执行 10 万次和另一条 SQL 单次执行 20s、一天执行 3 次前者对系统整体耗时的拖累远大于后者但只看单次时间根本发现不了。这里再补一个技巧mysqldumpslow会把 SQL 中的具体数字替换成N、字符串替换成S这样相同结构的 SQL 会被归并在一起统计不至于因为查询条件值不同而分裂成几千条独立记录。3. 从日志到执行计划一条慢 SQL 的完整解剖流程3.1 拿到慢 SQL 之后先别急着优化这是我最想强调的一点看到一条慢 SQL 的时候第一反应不应该是“改 SQL 写法”而是“理解它为什么慢”。很多新手上来就加索引加了之后发现没效果就是因为没搞懂数据库执行查询时的真实路径。我通常按以下顺序逐步排查看这条 SQL 的执行计划确认走了哪些表、用了什么索引、扫描了多少行。看 SQL 涉及的表的整体情况包括表行数、索引分布、字段类型。结合实际业务场景判断哪些条件是必须的、哪些字段是多余的、能不能改写。最后才动手优化而不是一开始就凭感觉改。3.2 EXPLAIN 执行计划关键字段解读MySQL 的EXPLAIN命令是分析慢 SQL 的核心工具一条命令就能看到 SQL 的执行路径。这里拿一条典型的慢查询举例EXPLAIN SELECT o.order_no, u.nickname, o.amount FROM orders o LEFT JOIN users u ON o.user_id u.id WHERE o.status 1 AND o.created_at 2024-01-01 ORDER BY o.created_at DESC LIMIT 20;执行完后重点关注以下几列字段关键点异常含义type访问类型从好到差依次是system const eq_ref ref range index ALL出现ALL说明全表扫描这是最需要警惕的key实际使用的索引名为NULL表示没走索引rows预估扫描行数这个值越大查询成本越高Extra额外信息Using filesort代表排序没走索引Using temporary代表用了临时表都是性能隐患回到上面的例子如果执行计划显示orders表的type是ALL、rows是 1000 万那问题就很清晰了状态过滤和创建时间过滤都没有可用索引数据库只能逐行扫描整张表再逐行关联用户表获取昵称最后还要对 1000 万行做一次完整的排序。3.3 用 Profile 看 SQL 内部的时间分布执行计划解决的是“数据库怎么查”的问题但有些时候 SQL 慢在更微观的层面比如排序花了多久、临时表创建花了多久、行锁等待了多久。这时候就用得上SHOW PROFILEMySQL 5.6 之后也推荐直接用 Performance Schema 的events_statements_history_long表。开启 profiling 后执行一条慢 SQL然后查看各阶段耗时SET profiling 1; -- 执行你的慢 SQL SELECT ...; SHOW PROFILES; SHOW PROFILE FOR QUERY 1;输出会列出starting、checking permissions、Opening tables、System lock、Sending data、sorting result、executing等阶段各自消耗的时间。如果发现Sending data阶段特别长通常意味着数据读取或全表扫描耗时如果sorting result很长说明排序操作是瓶颈如果Statistics和preparing很长可能是优化器在计算最优执行路径这时候表统计信息陈旧或数据分布严重不均往往是根因。3.4 真实案例分析一条被 6 张表 JOIN 拖垮的查询之前排查过一个工单系统里某个报表页面打开要 12s后端接口日志显示 SQL 执行了 11.4s。拿过慢查询日志一看是一条关联了 6 张表的 JOIN 查询WHERE 条件里还对其中两张表做了函数运算。执行计划出来后问题非常明显第一张主表走了主键索引但返回行数有 8 万后续 JOIN 的每张表都走了全表扫描因为关联字段要么没有索引要么类型不匹配导致索引失效最后还有一个ORDER BY触发了Using filesort对 8 万行临时结果排序。这个案例我拆成三步才优化完清理关联条件。原 SQL 对关联字段使用了LEFT JOIN但业务上根本不需要保留左表的全量数据改成INNER JOIN后优化器的选择空间大很多能先从过滤条件最严格的小表开始驱动。修复类型不一致问题。有两张表的关联字段一个是VARCHAR(32)一个是BIGINT导致索引直接失效。统一字段类型后索引才真正生效。拆分 SQL。报表页面的核心数据其实只需要主表和两张维表的字段另两张表的字段只有用户点开详情时才需要。我把一个大 JOIN 拆成一个主查询 一个详情查询前端按需调用响应时间一下降到 300ms。这个案例给我的启发是JOIN 的数量本身不是罪过罪过在于 JOIN 条件没有走索引、过滤条件没有提前缩窄数据范围。优化 JOIN 的核心思路始终是——让每一轮关联的数据量尽量小让每一步关联都走索引。4. 慢 SQL 优化的几类核心场景与实战解法4.1 没有索引或索引失效最普遍也最好解决最常见的慢查询原因就是“该走索引没走索引”。这里列几个我踩过的坑每个都是线上真实遇到过的函数运算导致的索引失效-- 慢对索引列做函数运算索引失效 SELECT * FROM orders WHERE DATE(created_at) 2024-01-15; -- 快改写为范围查询索引生效 SELECT * FROM orders WHERE created_at 2024-01-15 00:00:00 AND created_at 2024-01-16 00:00:00;数据库中索引的物理结构决定了它只能对“原始值”进行快速查找。一旦对字段施加了函数运算优化器无法预知函数的输出结果在索引中的位置只能放弃索引。上面的改写本质上把“对列做运算”变成了“对查询条件的值做运算”这是完全不同的两回事。隐式类型转换-- 假设 user_id 是 VARCHAR 类型 -- 慢用数字和字符串字段比较导致全表扫描 SELECT * FROM users WHERE user_id 12345; -- 快加上引号类型匹配索引生效 SELECT * FROM users WHERE user_id 12345;这类问题极其隐蔽。用数字和 VARCHAR 字段比较时数据库会把字段值隐式转换为数字再比较等价于对字段做了 CAST索引自然失效。排查方法很简单——看执行计划的rows是否突然变大或者注意 EXPLAIN 里有没有出现Using where却没用到索引的情况。前导模糊查询-- 慢左模糊无法走索引 SELECT * FROM products WHERE name LIKE %苹果%; -- 场景允许的情况下右模糊可以走索引 SELECT * FROM products WHERE name LIKE 苹果%;LIKE %关键词%这种写法在任何数据库里都无法走索引原因是 B 树的查找依赖前缀匹配。如果业务确实需要全文搜索可以考虑引入全文索引或专门的搜索引擎而不是在数据库里硬扛。4.2 深分页性能暴跌LIMIT 大偏移量的解法一个高频场景是管理后台的分页列表。数据量到了几十万行之后LIMIT 100000, 20这种翻页越翻越慢因为数据库需要扫描并丢弃前 10 万行才能返回最后 20 行。这里有两种常用优化方式方式一延迟关联只查主键再回表-- 慢直接把所有需要返回的字段一起 LIMIT SELECT id, order_no, amount, created_at FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 100000, 20; -- 快先只查主键走索引覆盖再关联详情 SELECT o.id, o.order_no, o.amount, o.created_at FROM ( SELECT id FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 100000, 20 ) tmp JOIN orders o ON tmp.id o.id ORDER BY o.created_at DESC;原理很容易理解内层查询只取主键和排序字段数据量小可以完全在索引里完成排序和过滤拿到 20 个主键后再回表取完整数据回表次数从 10 万次降到了 20 次。实测下来这个优化在 50 万行数据的表上从 2.3s 降到 60ms。方式二记录上一页的最后位置游标分页-- 第一页 SELECT id, order_no, amount, created_at FROM orders WHERE status 1 ORDER BY created_at DESC LIMIT 20; -- 后续翻页记住上一页最后一条的 created_at 和 id SELECT id, order_no, amount, created_at FROM orders WHERE status 1 AND (created_at 2024-01-15 10:00:00 OR (created_at 2024-01-15 10:00:00 AND id 123456)) ORDER BY created_at DESC LIMIT 20;这种方式的本质是利用 B 树索引的有序性直接从“上次的位置”继续向后扫描不再需要跳过大量数据。缺点是失去了随机跳页能力但对绝大多数业务场景瀑布流、列表加载来说完全够用而且性能极其稳定。4.3 ORDER BY 与 GROUP BY 的隐形成本ORDER BY和GROUP BY是两类特别能藏性能问题的操作。很多慢 SQL 在 SELECT 部分和 WHERE 部分看着都没问题执行计划里type是range或ref但Extra里躺着大大的Using filesort或Using temporary。Using filesort的意思是排序操作没有用到索引数据库需要把结果集加载到内存或磁盘临时文件里自行排序。当结果集超过sort_buffer_size时还会落盘速度更慢。解决思路无非两种让排序字段和 WHERE 条件字段组成联合索引让数据库直接按索引顺序读取数据省去排序过程。减少参与排序的字段和行数比如先过滤再排序、只查需要的列、延迟关联。Using temporary一般出现在GROUP BYDISTINCT 多表关联的场景。优化思路是让分组字段走索引尽可能让数据库在索引扫描过程中直接完成分组统计。实际业务中还有一种更隐蔽的情况ORDER BY RAND()。很多抽奖、随机推荐功能喜欢这么写它的本质是对全表每一行生成随机数再排序数据量稍微一大就是灾难。替代方案是先查主键列表再在应用层取随机数只回表取一条记录成本低几个数量级。4.4 数据量过大导致的“无解慢”分页采样与归档策略有些慢 SQL 不是写法问题而是数据量本身已经超出了一张表的合理承载范围。比如一张订单表积累了 5 年历史数据共 2 亿行就算所有查询条件都走到索引单次查询也可能因为要扫描的索引范围过大而变慢。这时候要解决的不是单条 SQL而是数据分布问题。常规方案有三个冷热数据分离把半年或一年前的历史订单迁移到历史库或归档表业务库只保留热数据。这是最根本、最可靠的方案。分区表按时间做 RANGE 分区让查询自动裁剪到对应分区。分区表能解决部分“扫全表”的问题但分区数量过多时也可能引入新的元数据开销需要实测评估。定期汇总如果业务方经常查报表与其每次实时扫明细不如用定时任务把结果提前汇总到统计表查询直接读汇总结果。5. 监控体系建设从被动救火到主动发现5.1 慢查询监控的闭环设计排查慢 SQL 只是第一步真正能提升团队效率和系统稳定性的是建立一套完整的“发现 → 解决 → 回归 → 预防”闭环。我的建议是至少覆盖以下四个环节采集开启慢查询日志并集中收集。告警当慢查询数量或最长执行时间超过阈值时立刻通知到相关负责人。追踪把慢 SQL 的优化状态记录下来谁在负责、进展如何。回归每次发版后对比慢查询数量和耗时的变化防止劣化。5.2 用开源监控栈搭建数据库看板如果公司还没有现成的数据库监控平台我推荐一套基于 Prometheus Grafana 的轻量方案成本低、社区资料多跑起来以后能直观看到慢查询趋势。组件作用mysqld_exporter采集 MySQL 状态变量、慢查询计数、连接数、缓冲池命中率等指标Prometheus存储时序指标配置告警规则Grafana可视化看板支持慢查询数量、执行时间分位数趋势图Loki / ELK如果需要对慢查询 SQL 内容做全文检索可以额外把日志接入 Loki 或 Elasticsearch关键告警规则示例# Prometheus 告警规则片段 groups: - name: mysql-slow-query rules: - alert: MySQLSlowQueriesHigh expr: rate(mysql_global_status_slow_queries[5m]) 0.5 for: 10m labels: severity: warning annotations: summary: MySQL 慢查询速率偏高rate(mysql_global_status_slow_queries[5m])计算的是最近 5 分钟内慢查询的增量速率。如果这个值持续大于 0.5说明每 2 秒至少产生一条慢查询系统已经处于不健康状态了。具体阈值要根据业务规模调整我的经验是交易核心库任何一条慢查询都值得告警分析型库可以放宽到 5 分钟 10 条以上再告警。5.3 慢查询日志自动分析脚本除了 Grafana 看板我自己还维护了一套简单的日志分析脚本定时扫描慢查询日志把 Top 慢 SQL 汇总推送到企业微信群。脚本核心逻辑只有三步用mysqldumpslow汇总近一小时的慢查询日志。格式化输出成 Markdown 或纯文本。调用 webhook 推送到群聊。这个脚本的价值不在于技术含量而在于“每天自动把最重要的数据库健康信息送到眼前”。很多问题如果等人发现再去查往往已经造成影响了但如果每天定时看到 Top 10 慢查询清单很多隐患能在爆发前就被处理掉。6. 优化效果验证与常见误区6.1 用对比数据和 Explain 双重验证优化完一条 SQL不能只看“我觉得快了”就结束。我一般会用两个维度验证执行时间对比在相同环境、相同数据量下分别执行优化前后版本的 SQL记录多次执行取平均。注意要先执行一次预热避免缓存干扰。执行计划对比确认type从ALL变成了range或refrows的预估扫描行数数量级下降Extra里不再出现Using filesort和Using temporary。我修复那条 6 表 JOIN 的 SQL 时执行时间从 11.4s 降到 280ms预估扫描行数从 4000 万降到 5 万执行计划从全表扫描 filesort 变成全走索引 无临时排序。数据是实打实说明问题的。6.2 我见过的三个“优化后反而更差”的经典案例案例一乱加索引导致写入变慢有个同事为了解决查询慢把一个 10 个字段的大表一口气加了 6 个单列索引。查询确实快了一点但写入和更新操作直接慢了一倍——因为每次写入都要同步维护 6 个索引 B 树。解决方法是把单列索引合并成联合索引并删除那些低频查询才用得到的索引。索引不是越多越好每个索引都是空间 写入开销的交换。案例二优化器没走你新建的索引建了索引但 SQL 还是全表扫描的情况也很常见。原因往往是数据分布问题——比如某个状态字段 90% 的行都是同一个值优化器一算走索引还要回表 90 万次还不如直接全表扫描。这时候别硬刚索引可以看看能不能改写 SQL例如把“WHERE status0”拆成“WHERE status0 AND create_time ?”缩小范围或者用FORCE INDEX强制走索引试一下但要注意这只是临时手段。案例三针对错误瓶颈优化有一个案例是 SQL 确实慢执行计划显示走了索引rows也不大但耗时始终降不下来。后来用 Profile 一看时间大量消耗在Sending data阶段。继续深挖发现是某张表的数据行特别宽一个大字段存了 JSON单行 10KB虽然只查 20 行但要读出来的行总字节数非常大导致 IO 成为瓶颈。这种场景下把大字段拆到单独的表或改成异步读取才是真正有效的优化。7. 从一次慢查询优化里沉淀出的团队规范做多了慢查询优化之后我越来越觉得让数据库不慢的最好办法是从源头不让慢 SQL 产生。光靠事后监控和优化永远在救火。我最后想分享几条经过实战检验的团队规范不一定适合所有团队但至少能帮大家少走弯路写 SQL 前先想索引。新需求涉及新查询时先看查询条件和排序字段再来设计索引而不是写完 SQL 之后等出问题再补索引。代码评审加入 SQL Review。提交的 SQL 必须附上执行计划截图重点关注type、rows、Extra三项指标。发布前做慢查询基线对比。每次发版前记录当前慢查询 Top 10发版后 24 小时再对比新增的慢 SQL 必须解释清楚原因。慢查询日志保留周期至少 30 天。这样跨版本对比时才有足够的数据而不是出了问题才发现历史日志已经被清掉了。定期做“慢 SQL 清零周”。每季度抽出一天集中处理当季度新增的慢查询。我实践下来这个动作对保持系统性能健康特别有效。数据库性能优化是一个无限迭代的过程业务在增长、数据在膨胀、SQL 模式在增加永远不存在“优化完了”的状态。但只要你把慢查询分析这个基本功练扎实把监控体系建立起来绝大部分数据库问题都能在影响用户之前被发现、被解决。个人而言我每次排查慢查询最享受的部分反而是那一步步拆解执行计划、假设验证、最终定位根因的过程——那感觉跟通关一场逻辑严密的解谜游戏很像。