MySQL突然变慢是排查故障里最常见的场景之一。接口响应从几十毫秒涨到几秒数据库CPU告警开发群和运维群同时炸锅。我处理过不少类似的问题最后发现绝大多数性能瓶颈不是服务器配置不够而是几条SQL会话拖了后腿走错了索引甚至全表扫描都成了常态。解决问题的第一步永远是“定位”而定位离不开两个最基础的工具——慢查询日志和EXPLAIN。这篇文章就把我实际跑工单时用到的排查方法和思路完整拆给大家从慢日志配置、输出字段解读再到用EXPLAIN验证优化效果最后配上几个真实的优化案例全是可以直接照着做的内容。1. 慢查询日志先搞清楚是谁拖慢了MySQL1.1 慢查询日志的配置与参数选择很多人一听“查慢SQL”第一反应就是打开慢查询日志。但实际上默认情况下MySQL的慢查询日志是关闭的而且默认阈值是10秒。以前线上出现过一批超跑级的慢SQL每条也就两三秒根本达不到10秒的阈值日志里全是干净得过分的内容。后来我统一把生产环境的阈值调到1秒才真正抓住了一批隐藏的慢性子查询。核心参数就四个建议直接用下面的配置slow_query_logON slow_query_log_file/var/log/mysql/mysql-slow.log long_query_time1 log_queries_not_using_indexesONslow_query_log总开关打开后才会记录慢查询。slow_query_log_file日志落盘路径确保MySQL运行用户有写权限。long_query_time超过这个时间单位秒的SQL会被记录生产环境一般设1秒刚开始排查设0.5秒也行。log_queries_not_using_indexes记录所有没走索引的SQL。这个参数很有价值因为全表扫描不一定慢但迟早会成为性能隐患。注意修改这些参数有两种方式。临时生效用SET GLOBAL重启后失效要永久生效必须写进my.cnf的[mysqld]段。我看到不少人只改了my.cnf忘了重启或者只做了SET GLOBAL结果一轮排查下来什么都没抓到。慢查询日志支持落文件也可以直接写MySQL系统表。log_outputTABLE的写法我会在排查时配合mysql.slow_log表做结构化查询方便按时间和耗时排序。但平时生产环境建议落文件性能开销更小磁盘写满也不至于把数据库拖崩。这里还有一个很容易踩的坑——慢查询日志文件过大。线上日志如果不做轮转几个月不动就能膨胀到几十GB磁盘满了数据库会直接拒绝写操作那就变成二次事故了。1.2 慢查询日志的分析方法拿到慢日志之后如果文件不大直接打开看最后几十条就行。如果文件已经上GB了千万别自己翻用内置工具mysqldumpslow来分析mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log这条命令会按平均耗时排序输出耗时最长的前10条SQL还会自动把具体参数值替换成抽象符号避免同类SQL因为参数不同被拆成多行。比如SELECT * FROM orders WHERE order_no A100和WHERE order_no A200会被合并成同一条统计记录逻辑上它们是同一类问题。如果团队环境里装了Percona Toolkitpt-query-digest是更强的分析利器。它能按指纹聚合慢查询统计每条SQL的总耗时占比、平均扫描行数和出现频率一键排出“罪魁祸首TOP10”。我通常的做法是先用pt-query-digest跑一遍定位到具体某条SQL后再用EXPLAIN分析执行计划。慢日志里每段记录都包含这几类关键信息字段含义排查价值Query_timeSQL总执行时长越长越可疑Lock_time锁等待时间过高说明存在锁竞争Rows_sent实际返回行数应该尽量小Rows_examined扫描行数远超Rows_sent说明检索效率极差Rows_examined / Rows_sent扫描/返回比比值越大说明做了大量无效工作Rows_examined和Rows_sent这两个字段对比价值极高。一条SQL如果扫描了50万行只返回20行这个比例就严重失衡了背后基本是索引缺失或者索引设计有误就算这次不慢数据涨上去也会慢。1.3 慢日志的“正确打开姿势”慢查询日志不是开了就一劳永逸它是一把双刃剑。阈值调太低会把所有小SQL都记录下来磁盘被日志塞满不说光分析日志本身就要花掉大量时间。我一般按环境分三档测试环境0.2秒、生产环境1秒、核心交易库0.5秒。阈值可以动态微调关键是找到适合自己业务负载的平衡点而不是一刀切。日志轮转也要提前安排。Linux下用logrotate每天切割一次保留7天就够了配合crontab定时把前一天的慢日志归档压缩。另外建议每周跑一次mysqldumpslow把Top 20慢SQL发到团队群。这样做的好处是慢SQL在开发阶段就被盯上而不是等线上出故障再来救火。2. EXPLAIN读懂MySQL的算路引擎2.1 先看懂EXPLAIN输出里最关键的5个字段慢查询日志告诉你“哪条SQL慢”EXPLAIN告诉你“这条SQL为什么慢”。它是MySQL的“算路引擎”会把优化器选定的执行计划直接打印给你看。执行EXPLAIN SELECT ...不会真正运行SQL所以你可以放心在生产库上跑。EXPLAIN SELECT * FROM orders WHERE order_no A100;输出结果里有十几列字段我排查时重点只看五列type、key、rows、Extra外加一个key_len。这里最直观的就是type它代表访问类型效率大致排成这样type访问方式示例场景system表只有一行极少见const主键或唯一索引等值匹配WHERE id 1eq_ref被驱动表通过唯一索引匹配JOIN场景ref普通索引等值匹配WHERE order_status 1range索引范围扫描WHERE create_time 2024-01-01index全索引扫描比全表快一点但仍有风险ALL全表扫描最危险必须避免rows是优化器估算的扫描行数它不一定精确但数量级能说明问题。如果rows从几十万降到几千优化效果就已经出来了。typeALL是高危信号如果还能看到Using filesort文件排序或Using temporary临时表基本可以在优化方案里把这条SQL标成红色了。反过来Extra里出现Using index condition、Using index就是比较理想的信号说明索引帮了大忙。key_len也有讲究。它代表索引字段的最大字节长度可以用来判断联合索引有没有用满。比如联合索引(a, b, c)如果key_len只覆盖了a字段的长度说明b和c都没被用上如果覆盖了a和b的长度说明b用上了c没用上。这个字段帮我定位过好几个联合索引失效的问题比肉眼猜靠谱得多。计算时要考虑字符集和字段类型比如utf8mb4下一个VARCHAR(50)字段长度是50×4再加2字节变长位具体依赖表结构。2.2 为什么有索引却不用EXPLAIN最常见的疑点就是“明明给字段建了索引type却还是ALL”。我总结下来索引失效的原因就那么几类排查时按顺序对照检查就行。第一函数运算包裹了索引列。最典型的就是按日期筛选却用了DATE(create_time)-- 这条SQL不会走create_time上的索引 SELECT * FROM orders WHERE DATE(create_time) 2025-02-01;因为MySQL无法直接对函数计算后的结果使用索引改写为范围查询之后就能命中索引SELECT * FROM orders WHERE create_time 2025-02-01 AND create_time 2025-02-02;函数的“锅”不止日期一种。对字段做加减乘除、字符串拼接都会让索引失效排查这类问题直接搜SQL里的函数调用最有效。第二隐式类型转换。字段是VARCHAR类型查询条件却用了数字。MySQL会把字符串字段转成数字再做比较索引同样失效。比如order_no是字符串写WHERE order_no 10086就可能不走索引正确写法是WHERE order_no 10086。数字类型字段反过来用字符串去匹配也是一样的道理。第三优化器成本误判。这是最折腾人的情况——索引明明存在优化器就是觉得全表扫描更快实际很可能是因为统计信息过期了。遇到这种情况先用ANALYZE TABLE刷新统计信息ANALYZE TABLE orders;如果刷新后仍然不走索引再考虑用FORCE INDEX强制优化器选用索引SELECT * FROM orders FORCE INDEX(idx_order_no) WHERE order_no A100;但是FORCE INDEX属于应急方案不应该长期写死在代码里。业务数据量明显增长后强制索引可能变成拖后腿的因素根本解法还是查清楚为什么优化器判断失误比如关联查询里驱动表的筛选条件选择性太差就需要从SQL改写层面处理。2.3 用EXPLAIN做优化验证的正确姿势EXPLAIN最大的价值在于“改造前”和“改造后”可以做对比。我每次优化完SQL都会把两边的执行计划放在一起看type是不是从ALL变成了refrows是不是降了一两个数量级Extra里的Using filesort是不是消失了。这三个变化清楚了优化效果基本就不会跑偏。但是千万注意EXPLAIN不是执行计划它不会真的执行SQL所以有个隐患优化器在EXPLAIN阶段的估算和实际执行时的表现可能有出入。MySQL 8.0开始提供了EXPLAIN ANALYZE它会真正执行SQL并输出每步的实际耗时和实际扫描行数结果比普通EXPLAIN准确得多EXPLAIN ANALYZE SELECT * FROM orders WHERE order_no A100;不过EXPLAIN ANALYZE会真实执行查询线上高负载时别乱用知道有这个工具就行。再补充一个容易忽略的细节EXPLAIN的输出顺序有一定解读规矩。id相同的情况下从上往下就是执行顺序id不同的情况下id值越大越先执行。多表JOIN的EXPLAIN输出里被驱动表的type通常是eq_ref或ref如果出现ALL说明关联字段没索引或者驱动顺序不合理需要重点检查。3. 慢日志 EXPLAIN 联合诊断案例实录3.1 案例一条订单查询拖垮了整个支付接口有一次线上支付接口突然变慢单次调用从200ms涨到了1.8秒而且每隔几分钟就有一个超时告警。我先去拉了慢查询日志pt-query-digest把问题锁定在一条订单查询上SELECT * FROM order_info WHERE merchant_id 23231 AND order_status PAID AND pay_time BETWEEN 2025-01-01 00:00:00 AND 2025-01-31 23:59:59;慢日志显示这条SQL的平均耗时2.3秒Rows_examined高达120万行但每次只返回几十行。紧接着用EXPLAIN验证结果typeALL直接全表扫描。表里已经有一个idx_merchant的单列索引但单独用merchant_id过滤时该商家在全表占比太大优化器觉得回表太贵宁可扫描全表也不愿意用索引。当时的解决办法是新建联合索引把商家ID和时间戳组合起来ALTER TABLE order_info ADD INDEX idx_merchant_paytime (merchant_id, pay_time);建立索引后再看EXPLAINtype变成了rangerows从120万行直接降到约2万行执行时间从2.3秒降到0.05秒。这里的关键是用联合索引把范围条件也覆盖进去让pay_time的过滤能力被充分利用而不只是靠商户ID去猜。改造后又发现一个隐藏问题SELECT *把订单表的大字段全部捞了回来回表次数很多。第二步优化是把查询字段收窄到真正需要的几列同时在联合索引里补充查询需要的列做成覆盖索引。这一步执行完接口耗时最终稳定在20ms左右。3.2 案例深分页查询引发的慢查询另一个典型场景就是后台管理系统的分页列表。某个运营系统的订单列表越翻越慢翻到几十页之后接口直接超时。慢日志抓到的问题SQL长这样SELECT id, order_no, amount, create_time FROM order_info ORDER BY create_time DESC LIMIT 100000, 20;这种写法在LIMIT的偏移量特别大时MySQL要先把前10万行全部查出来再扔掉它们只留最后20行。就算create_time有索引这个过程也一样低效因为排序加偏移导致大量无效扫描。EXPLAIN会显示Using filesortrows也是一个很大的估算值。优化方案是“延迟关联”。思路是先在子查询里用覆盖索引查出目标行的主键ID再用主键关联原表取完整数据SELECT t.id, t.order_no, t.amount, t.create_time FROM order_info t INNER JOIN ( SELECT id FROM order_info ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;改写前后的差别在于子查询里直接通过(create_time, id)的覆盖索引做排序和分页扫描范围集中在索引树上不需要回表拿完整行外层再通过主键回表只取20行数据。实测同样的分页参数查询耗时从1.5秒降到0.08秒效果明显。如果业务上不允许改SQL形式还有一个偏方记住上一页最大时间戳用时间范围条件代替OFFSET。比如上一页最后一条数据的create_time是2025-02-01 10:00:00下一页就直接查小于这个时间点的前20条SELECT id, order_no, amount, create_time FROM order_info WHERE create_time 2025-02-01 10:00:00 ORDER BY create_time DESC LIMIT 20;这种游标分页方案在数据量大的表上性能非常好缺点是需要客户端维护一个上一次翻页位置的状态。3.3 案例多表JOIN导致临时表和文件排序还有一个常见问题出在JOIN查询上。某个报表模块需要把订单表、商家表、商品表关联起来统计销量一条SQL跑了6秒。EXPLAIN里出现两个明显的红灯Using temporary和Using filesort。再细看驱动表的相关信息关联字段没有索引被驱动表每次都要全表扫描最终MySQL优化器只能建临时表帮JOIN凑数据。这种问题我一般先做三件事。第一给被驱动表的JOIN字段加索引JOIN条件里的列必须有索引否则被驱动表全表扫是必然的。第二去掉不必要的表关联很多情况下业务并不需要SELECT *只需要统计字段三张表缩成两张表效率能差出好几百毫秒。第三把JOIN改成子查询优先让大表先过滤出小结果集再参与关联。这里要特别提一下MySQL执行JOIN的顺序逻辑。优化器会选择一张表作为驱动表其他表作为被驱动表。驱动表是先被执行的那张它的访问方式会决定整体流程。经验上驱动表应该是过滤后结果集更小的那张表。如果发现优化器选错了驱动顺序可以通过STRAIGHT_JOIN强制指定顺序但这属于高级技巧初期不建议随意使用先保证索引和统计信息都是最新的让优化器自己做该做的事更稳妥。3.4 从案例中提炼的优化优先级三个案例跑下来我总结出MySQL性能优化的顺序应该是先揪出慢SQL并看懂执行计划再解决索引问题之后才是SQL改写再往后才考虑配置和架构。现实中很多人一上来就想着分库分表或者动硬件这是本末倒置的。顺序应该是慢查询日志定位到底哪条SQL有问题EXPLAIN对比前后的执行计划找出类型为ALL的或者Extra有问题的SQL补索引、调整索引顺序和长度让rows降到合理量级改写SQL消灭Using filesort和Using temporary最后才考虑增加缓存、读写分离、分库分表这些重手段实际线上90%的慢查询问题做完前四步就已经能解决掉了。等做完前四步仍然扛不住流量的时候再来谈架构升级也不迟而且那时手上的执行计划和慢SQL证据能让你更有底气去设计分片键。4. 常见问题与排查技巧实录4.1 慢查询排查的完整操作流程把前面所有内容汇总成一套可以直接照着执行的流程这套流程我在多个项目里反复验证过效率很高第一步开启慢查询日志设置阈值为1秒并打开log_queries_not_using_indexes。核心库可以先用0.5秒跑8小时收集足够样本。第二步在日志分析工具里找出Top N条慢SQL。重点看Rows_examined和Rows_sent的比值比值超过100的优先处理。第三步对每条慢SQL执行EXPLAIN记录当前type、key、rows、Extra四个值。第四步分析SQL条件列是否满足索引规则。先确认WHERE条件和JOIN条件涉及的字段有没有索引再看这些索引是单列还是联合最后检查有没有函数包裹、隐式转换等失效情况。第五步执行索引新增或SQL改写再次运行EXPLAIN对比执行计划。确认rows明显减少Extra不再有高危提醒后再用真实参数跑一遍原SQL看耗时。第六步上线后持续观察慢日志。如果这条SQL继续出现在TopN里说明优化不够需要进一步调整索引或改写逻辑。如果一个优化后的SQL连续两周不出现在慢日志里基本可以标记为闭环了。这个流程看着简单但每一个步骤背后都有门道。比如第四步里联合索引的字段顺序和查询条件的排列顺序强相关。最左侧字段必须出现在WHERE里后面的字段才可能生效这叫做最左前缀原则优化的时候要反复确认条件顺序和索引字段顺序一一对应。4.2 新手常踩的坑配置慢查询日志时最典型的坑是改了参数却看不到效果。SET GLOBAL只对后续新连接生效旧的连接拿不到新参数值可以断开重连或者直接重启应用。文件路径设了MySQL没有权限的目录也会静默失败用SHOW VARIABLES LIKE slow_query_log_file确认实际路径再用系统账号测试写入权限。EXPLAIN相关的新手坑就更多了。很多人以为EXPLAIN显示用了索引就安全了实际上还需要看rows和filtered。filtered代表满足条件行数占比如果特别低说明即使走了索引回表行数仍然很多。优先去设计能够同时过滤多个条件的联合索引或者用覆盖索引一次收齐所有查询列。另外还有一点必须反复强调EXPLAIN的结果不是最终执行SQL时的执行计划它只是优化器基于当时统计信息的估算结果。当表数据量发生大幅变化、统计信息更新后执行计划可能完全不一样。所以定期ANALYZE TABLE对保障执行计划稳定很有意义特别是那种频繁批量导入数据的表。还有个容易被忽略的问题是慢日志时间与业务时间的时区错位。MySQL慢日志默认用系统时区如果应用层用UTC时间排查“下午高峰期”的慢日志会找不到对应记录。这个坑我踩过一次后来在配置里统一了时区并设置了会话时间格式才解决。4.3 给我自己团队用的几条避坑经验排查次数多了有几条经验已经写进我们团队的例行检查清单里。第一条索引宁缺毋滥。每加一个索引数据库写入时都要额外维护一棵B树索引过多带来的写入性能损耗容易超出预期。一条SQL最理想的索引数量是一个联合索引覆盖所有常用查询条件而不是为每个字段单独建单列索引。我见过一个表被建了十几个单列索引写操作慢了一半查询也没快到哪里去。第二条慢日志阈值的设置应该结合业务特性。核心交易库用0.5秒偏低会影响磁盘空间建议使用1秒并重点观察扫描行数报表分析类数据库可以放宽到2秒因为大查询本身是常态更需要关注的是是否有无索引查询。第三条EXPLAIN出现Using filesort并不意味着一定有磁盘文件可能是因为排序缓冲区不够。sort_buffer_size调大的确可以减少磁盘排序但全局调大会吃内存必须按连接数估算合理值。更稳妥的方案是让排序字段走索引消灭排序而不是扩大缓冲区。第四条线上排查时优先做“只读操作”任何ALTER TABLE和ANALYZE TABLE都要避开业务高峰。新增索引虽然MySQL 8.0支持在线DDL但仍然会有元数据锁的开销最好放在低峰期操作并配合gh-ost之类的工具执行。我个人在这些年一直保持着每周看一次慢查询日志的习惯发现数据比想象中能说明更多问题。每一次看起来棘手的MySQL性能告警只要把EXPLAIN和慢日志结合起来变成固定动作先定位再动手优化最后大多都收敛到了索引层面。大家平时遇到最头疼的又是哪类慢SQL呢如果在索引优化上有更刁钻的场景欢迎一起交流更多反直觉的案例。 
长河君 · 2026/10/15 19:51:57 · 阅读 1.2k MySQL性能排查实战：从慢查询日志到EXPLAIN的优化指南
MySQL突然变慢是排查故障里最常见的场景之一。接口响应从几十毫秒涨到几秒数据库CPU告警开发群和运维群同时炸锅。我处理过不少类似的问题最后发现绝大多数性能瓶颈不是服务器配置不够而是几条SQL会话拖了后腿走错了索引甚至全表扫描都成了常态。解决问题的第一步永远是“定位”而定位离不开两个最基础的工具——慢查询日志和EXPLAIN。这篇文章就把我实际跑工单时用到的排查方法和思路完整拆给大家从慢日志配置、输出字段解读再到用EXPLAIN验证优化效果最后配上几个真实的优化案例全是可以直接照着做的内容。1. 慢查询日志先搞清楚是谁拖慢了MySQL1.1 慢查询日志的配置与参数选择很多人一听“查慢SQL”第一反应就是打开慢查询日志。但实际上默认情况下MySQL的慢查询日志是关闭的而且默认阈值是10秒。以前线上出现过一批超跑级的慢SQL每条也就两三秒根本达不到10秒的阈值日志里全是干净得过分的内容。后来我统一把生产环境的阈值调到1秒才真正抓住了一批隐藏的慢性子查询。核心参数就四个建议直接用下面的配置slow_query_logON slow_query_log_file/var/log/mysql/mysql-slow.log long_query_time1 log_queries_not_using_indexesONslow_query_log总开关打开后才会记录慢查询。slow_query_log_file日志落盘路径确保MySQL运行用户有写权限。long_query_time超过这个时间单位秒的SQL会被记录生产环境一般设1秒刚开始排查设0.5秒也行。log_queries_not_using_indexes记录所有没走索引的SQL。这个参数很有价值因为全表扫描不一定慢但迟早会成为性能隐患。注意修改这些参数有两种方式。临时生效用SET GLOBAL重启后失效要永久生效必须写进my.cnf的[mysqld]段。我看到不少人只改了my.cnf忘了重启或者只做了SET GLOBAL结果一轮排查下来什么都没抓到。慢查询日志支持落文件也可以直接写MySQL系统表。log_outputTABLE的写法我会在排查时配合mysql.slow_log表做结构化查询方便按时间和耗时排序。但平时生产环境建议落文件性能开销更小磁盘写满也不至于把数据库拖崩。这里还有一个很容易踩的坑——慢查询日志文件过大。线上日志如果不做轮转几个月不动就能膨胀到几十GB磁盘满了数据库会直接拒绝写操作那就变成二次事故了。1.2 慢查询日志的分析方法拿到慢日志之后如果文件不大直接打开看最后几十条就行。如果文件已经上GB了千万别自己翻用内置工具mysqldumpslow来分析mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log这条命令会按平均耗时排序输出耗时最长的前10条SQL还会自动把具体参数值替换成抽象符号避免同类SQL因为参数不同被拆成多行。比如SELECT * FROM orders WHERE order_no A100和WHERE order_no A200会被合并成同一条统计记录逻辑上它们是同一类问题。如果团队环境里装了Percona Toolkitpt-query-digest是更强的分析利器。它能按指纹聚合慢查询统计每条SQL的总耗时占比、平均扫描行数和出现频率一键排出“罪魁祸首TOP10”。我通常的做法是先用pt-query-digest跑一遍定位到具体某条SQL后再用EXPLAIN分析执行计划。慢日志里每段记录都包含这几类关键信息字段含义排查价值Query_timeSQL总执行时长越长越可疑Lock_time锁等待时间过高说明存在锁竞争Rows_sent实际返回行数应该尽量小Rows_examined扫描行数远超Rows_sent说明检索效率极差Rows_examined / Rows_sent扫描/返回比比值越大说明做了大量无效工作Rows_examined和Rows_sent这两个字段对比价值极高。一条SQL如果扫描了50万行只返回20行这个比例就严重失衡了背后基本是索引缺失或者索引设计有误就算这次不慢数据涨上去也会慢。1.3 慢日志的“正确打开姿势”慢查询日志不是开了就一劳永逸它是一把双刃剑。阈值调太低会把所有小SQL都记录下来磁盘被日志塞满不说光分析日志本身就要花掉大量时间。我一般按环境分三档测试环境0.2秒、生产环境1秒、核心交易库0.5秒。阈值可以动态微调关键是找到适合自己业务负载的平衡点而不是一刀切。日志轮转也要提前安排。Linux下用logrotate每天切割一次保留7天就够了配合crontab定时把前一天的慢日志归档压缩。另外建议每周跑一次mysqldumpslow把Top 20慢SQL发到团队群。这样做的好处是慢SQL在开发阶段就被盯上而不是等线上出故障再来救火。2. EXPLAIN读懂MySQL的算路引擎2.1 先看懂EXPLAIN输出里最关键的5个字段慢查询日志告诉你“哪条SQL慢”EXPLAIN告诉你“这条SQL为什么慢”。它是MySQL的“算路引擎”会把优化器选定的执行计划直接打印给你看。执行EXPLAIN SELECT ...不会真正运行SQL所以你可以放心在生产库上跑。EXPLAIN SELECT * FROM orders WHERE order_no A100;输出结果里有十几列字段我排查时重点只看五列type、key、rows、Extra外加一个key_len。这里最直观的就是type它代表访问类型效率大致排成这样type访问方式示例场景system表只有一行极少见const主键或唯一索引等值匹配WHERE id 1eq_ref被驱动表通过唯一索引匹配JOIN场景ref普通索引等值匹配WHERE order_status 1range索引范围扫描WHERE create_time 2024-01-01index全索引扫描比全表快一点但仍有风险ALL全表扫描最危险必须避免rows是优化器估算的扫描行数它不一定精确但数量级能说明问题。如果rows从几十万降到几千优化效果就已经出来了。typeALL是高危信号如果还能看到Using filesort文件排序或Using temporary临时表基本可以在优化方案里把这条SQL标成红色了。反过来Extra里出现Using index condition、Using index就是比较理想的信号说明索引帮了大忙。key_len也有讲究。它代表索引字段的最大字节长度可以用来判断联合索引有没有用满。比如联合索引(a, b, c)如果key_len只覆盖了a字段的长度说明b和c都没被用上如果覆盖了a和b的长度说明b用上了c没用上。这个字段帮我定位过好几个联合索引失效的问题比肉眼猜靠谱得多。计算时要考虑字符集和字段类型比如utf8mb4下一个VARCHAR(50)字段长度是50×4再加2字节变长位具体依赖表结构。2.2 为什么有索引却不用EXPLAIN最常见的疑点就是“明明给字段建了索引type却还是ALL”。我总结下来索引失效的原因就那么几类排查时按顺序对照检查就行。第一函数运算包裹了索引列。最典型的就是按日期筛选却用了DATE(create_time)-- 这条SQL不会走create_time上的索引 SELECT * FROM orders WHERE DATE(create_time) 2025-02-01;因为MySQL无法直接对函数计算后的结果使用索引改写为范围查询之后就能命中索引SELECT * FROM orders WHERE create_time 2025-02-01 AND create_time 2025-02-02;函数的“锅”不止日期一种。对字段做加减乘除、字符串拼接都会让索引失效排查这类问题直接搜SQL里的函数调用最有效。第二隐式类型转换。字段是VARCHAR类型查询条件却用了数字。MySQL会把字符串字段转成数字再做比较索引同样失效。比如order_no是字符串写WHERE order_no 10086就可能不走索引正确写法是WHERE order_no 10086。数字类型字段反过来用字符串去匹配也是一样的道理。第三优化器成本误判。这是最折腾人的情况——索引明明存在优化器就是觉得全表扫描更快实际很可能是因为统计信息过期了。遇到这种情况先用ANALYZE TABLE刷新统计信息ANALYZE TABLE orders;如果刷新后仍然不走索引再考虑用FORCE INDEX强制优化器选用索引SELECT * FROM orders FORCE INDEX(idx_order_no) WHERE order_no A100;但是FORCE INDEX属于应急方案不应该长期写死在代码里。业务数据量明显增长后强制索引可能变成拖后腿的因素根本解法还是查清楚为什么优化器判断失误比如关联查询里驱动表的筛选条件选择性太差就需要从SQL改写层面处理。2.3 用EXPLAIN做优化验证的正确姿势EXPLAIN最大的价值在于“改造前”和“改造后”可以做对比。我每次优化完SQL都会把两边的执行计划放在一起看type是不是从ALL变成了refrows是不是降了一两个数量级Extra里的Using filesort是不是消失了。这三个变化清楚了优化效果基本就不会跑偏。但是千万注意EXPLAIN不是执行计划它不会真的执行SQL所以有个隐患优化器在EXPLAIN阶段的估算和实际执行时的表现可能有出入。MySQL 8.0开始提供了EXPLAIN ANALYZE它会真正执行SQL并输出每步的实际耗时和实际扫描行数结果比普通EXPLAIN准确得多EXPLAIN ANALYZE SELECT * FROM orders WHERE order_no A100;不过EXPLAIN ANALYZE会真实执行查询线上高负载时别乱用知道有这个工具就行。再补充一个容易忽略的细节EXPLAIN的输出顺序有一定解读规矩。id相同的情况下从上往下就是执行顺序id不同的情况下id值越大越先执行。多表JOIN的EXPLAIN输出里被驱动表的type通常是eq_ref或ref如果出现ALL说明关联字段没索引或者驱动顺序不合理需要重点检查。3. 慢日志 EXPLAIN 联合诊断案例实录3.1 案例一条订单查询拖垮了整个支付接口有一次线上支付接口突然变慢单次调用从200ms涨到了1.8秒而且每隔几分钟就有一个超时告警。我先去拉了慢查询日志pt-query-digest把问题锁定在一条订单查询上SELECT * FROM order_info WHERE merchant_id 23231 AND order_status PAID AND pay_time BETWEEN 2025-01-01 00:00:00 AND 2025-01-31 23:59:59;慢日志显示这条SQL的平均耗时2.3秒Rows_examined高达120万行但每次只返回几十行。紧接着用EXPLAIN验证结果typeALL直接全表扫描。表里已经有一个idx_merchant的单列索引但单独用merchant_id过滤时该商家在全表占比太大优化器觉得回表太贵宁可扫描全表也不愿意用索引。当时的解决办法是新建联合索引把商家ID和时间戳组合起来ALTER TABLE order_info ADD INDEX idx_merchant_paytime (merchant_id, pay_time);建立索引后再看EXPLAINtype变成了rangerows从120万行直接降到约2万行执行时间从2.3秒降到0.05秒。这里的关键是用联合索引把范围条件也覆盖进去让pay_time的过滤能力被充分利用而不只是靠商户ID去猜。改造后又发现一个隐藏问题SELECT *把订单表的大字段全部捞了回来回表次数很多。第二步优化是把查询字段收窄到真正需要的几列同时在联合索引里补充查询需要的列做成覆盖索引。这一步执行完接口耗时最终稳定在20ms左右。3.2 案例深分页查询引发的慢查询另一个典型场景就是后台管理系统的分页列表。某个运营系统的订单列表越翻越慢翻到几十页之后接口直接超时。慢日志抓到的问题SQL长这样SELECT id, order_no, amount, create_time FROM order_info ORDER BY create_time DESC LIMIT 100000, 20;这种写法在LIMIT的偏移量特别大时MySQL要先把前10万行全部查出来再扔掉它们只留最后20行。就算create_time有索引这个过程也一样低效因为排序加偏移导致大量无效扫描。EXPLAIN会显示Using filesortrows也是一个很大的估算值。优化方案是“延迟关联”。思路是先在子查询里用覆盖索引查出目标行的主键ID再用主键关联原表取完整数据SELECT t.id, t.order_no, t.amount, t.create_time FROM order_info t INNER JOIN ( SELECT id FROM order_info ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;改写前后的差别在于子查询里直接通过(create_time, id)的覆盖索引做排序和分页扫描范围集中在索引树上不需要回表拿完整行外层再通过主键回表只取20行数据。实测同样的分页参数查询耗时从1.5秒降到0.08秒效果明显。如果业务上不允许改SQL形式还有一个偏方记住上一页最大时间戳用时间范围条件代替OFFSET。比如上一页最后一条数据的create_time是2025-02-01 10:00:00下一页就直接查小于这个时间点的前20条SELECT id, order_no, amount, create_time FROM order_info WHERE create_time 2025-02-01 10:00:00 ORDER BY create_time DESC LIMIT 20;这种游标分页方案在数据量大的表上性能非常好缺点是需要客户端维护一个上一次翻页位置的状态。3.3 案例多表JOIN导致临时表和文件排序还有一个常见问题出在JOIN查询上。某个报表模块需要把订单表、商家表、商品表关联起来统计销量一条SQL跑了6秒。EXPLAIN里出现两个明显的红灯Using temporary和Using filesort。再细看驱动表的相关信息关联字段没有索引被驱动表每次都要全表扫描最终MySQL优化器只能建临时表帮JOIN凑数据。这种问题我一般先做三件事。第一给被驱动表的JOIN字段加索引JOIN条件里的列必须有索引否则被驱动表全表扫是必然的。第二去掉不必要的表关联很多情况下业务并不需要SELECT *只需要统计字段三张表缩成两张表效率能差出好几百毫秒。第三把JOIN改成子查询优先让大表先过滤出小结果集再参与关联。这里要特别提一下MySQL执行JOIN的顺序逻辑。优化器会选择一张表作为驱动表其他表作为被驱动表。驱动表是先被执行的那张它的访问方式会决定整体流程。经验上驱动表应该是过滤后结果集更小的那张表。如果发现优化器选错了驱动顺序可以通过STRAIGHT_JOIN强制指定顺序但这属于高级技巧初期不建议随意使用先保证索引和统计信息都是最新的让优化器自己做该做的事更稳妥。3.4 从案例中提炼的优化优先级三个案例跑下来我总结出MySQL性能优化的顺序应该是先揪出慢SQL并看懂执行计划再解决索引问题之后才是SQL改写再往后才考虑配置和架构。现实中很多人一上来就想着分库分表或者动硬件这是本末倒置的。顺序应该是慢查询日志定位到底哪条SQL有问题EXPLAIN对比前后的执行计划找出类型为ALL的或者Extra有问题的SQL补索引、调整索引顺序和长度让rows降到合理量级改写SQL消灭Using filesort和Using temporary最后才考虑增加缓存、读写分离、分库分表这些重手段实际线上90%的慢查询问题做完前四步就已经能解决掉了。等做完前四步仍然扛不住流量的时候再来谈架构升级也不迟而且那时手上的执行计划和慢SQL证据能让你更有底气去设计分片键。4. 常见问题与排查技巧实录4.1 慢查询排查的完整操作流程把前面所有内容汇总成一套可以直接照着执行的流程这套流程我在多个项目里反复验证过效率很高第一步开启慢查询日志设置阈值为1秒并打开log_queries_not_using_indexes。核心库可以先用0.5秒跑8小时收集足够样本。第二步在日志分析工具里找出Top N条慢SQL。重点看Rows_examined和Rows_sent的比值比值超过100的优先处理。第三步对每条慢SQL执行EXPLAIN记录当前type、key、rows、Extra四个值。第四步分析SQL条件列是否满足索引规则。先确认WHERE条件和JOIN条件涉及的字段有没有索引再看这些索引是单列还是联合最后检查有没有函数包裹、隐式转换等失效情况。第五步执行索引新增或SQL改写再次运行EXPLAIN对比执行计划。确认rows明显减少Extra不再有高危提醒后再用真实参数跑一遍原SQL看耗时。第六步上线后持续观察慢日志。如果这条SQL继续出现在TopN里说明优化不够需要进一步调整索引或改写逻辑。如果一个优化后的SQL连续两周不出现在慢日志里基本可以标记为闭环了。这个流程看着简单但每一个步骤背后都有门道。比如第四步里联合索引的字段顺序和查询条件的排列顺序强相关。最左侧字段必须出现在WHERE里后面的字段才可能生效这叫做最左前缀原则优化的时候要反复确认条件顺序和索引字段顺序一一对应。4.2 新手常踩的坑配置慢查询日志时最典型的坑是改了参数却看不到效果。SET GLOBAL只对后续新连接生效旧的连接拿不到新参数值可以断开重连或者直接重启应用。文件路径设了MySQL没有权限的目录也会静默失败用SHOW VARIABLES LIKE slow_query_log_file确认实际路径再用系统账号测试写入权限。EXPLAIN相关的新手坑就更多了。很多人以为EXPLAIN显示用了索引就安全了实际上还需要看rows和filtered。filtered代表满足条件行数占比如果特别低说明即使走了索引回表行数仍然很多。优先去设计能够同时过滤多个条件的联合索引或者用覆盖索引一次收齐所有查询列。另外还有一点必须反复强调EXPLAIN的结果不是最终执行SQL时的执行计划它只是优化器基于当时统计信息的估算结果。当表数据量发生大幅变化、统计信息更新后执行计划可能完全不一样。所以定期ANALYZE TABLE对保障执行计划稳定很有意义特别是那种频繁批量导入数据的表。还有个容易被忽略的问题是慢日志时间与业务时间的时区错位。MySQL慢日志默认用系统时区如果应用层用UTC时间排查“下午高峰期”的慢日志会找不到对应记录。这个坑我踩过一次后来在配置里统一了时区并设置了会话时间格式才解决。4.3 给我自己团队用的几条避坑经验排查次数多了有几条经验已经写进我们团队的例行检查清单里。第一条索引宁缺毋滥。每加一个索引数据库写入时都要额外维护一棵B树索引过多带来的写入性能损耗容易超出预期。一条SQL最理想的索引数量是一个联合索引覆盖所有常用查询条件而不是为每个字段单独建单列索引。我见过一个表被建了十几个单列索引写操作慢了一半查询也没快到哪里去。第二条慢日志阈值的设置应该结合业务特性。核心交易库用0.5秒偏低会影响磁盘空间建议使用1秒并重点观察扫描行数报表分析类数据库可以放宽到2秒因为大查询本身是常态更需要关注的是是否有无索引查询。第三条EXPLAIN出现Using filesort并不意味着一定有磁盘文件可能是因为排序缓冲区不够。sort_buffer_size调大的确可以减少磁盘排序但全局调大会吃内存必须按连接数估算合理值。更稳妥的方案是让排序字段走索引消灭排序而不是扩大缓冲区。第四条线上排查时优先做“只读操作”任何ALTER TABLE和ANALYZE TABLE都要避开业务高峰。新增索引虽然MySQL 8.0支持在线DDL但仍然会有元数据锁的开销最好放在低峰期操作并配合gh-ost之类的工具执行。我个人在这些年一直保持着每周看一次慢查询日志的习惯发现数据比想象中能说明更多问题。每一次看起来棘手的MySQL性能告警只要把EXPLAIN和慢日志结合起来变成固定动作先定位再动手优化最后大多都收敛到了索引层面。大家平时遇到最头疼的又是哪类慢SQL呢如果在索引优化上有更刁钻的场景欢迎一起交流更多反直觉的案例。