优化SQL跑得慢DBA让你看执行计划开发同学甩过来一条EXPLAIN结果让你解释——这大概是MySQL日常运维里最常见的场景之一。EXPLAIN这个词说起来大家都不陌生但真到排查问题时能一眼从输出里看出门道的同学并不多。很多人卡在“会执行EXPLAIN但不会读结果”更不用说基于执行计划反推索引设计。这篇内容我打算把EXPLAIN的核心输出字段、索引优化的判断逻辑、以及实际排障时的一些套路从头到尾梳理一遍。内容偏实战适合正在学MySQL调优的开发者也适合被慢查询折磨的运维同学对照参考。在MySQL里EXPLAIN只是第一步真正值钱的是你读到执行计划之后能不能快速回答三个问题这条SQL为什么慢慢在哪一步换成什么样的索引能让它快起来搞清楚这三件事索引优化就成功了一大半。接下来我按实际排查时的思路来展开不按官方文档顺序讲那样太枯燥也不容易记住。1. 先搞清楚EXPLAIN到底在做什么1.1 一条慢SQL引发的排查链路假设业务反馈某个列表页打开要三秒后台一看慢查询日志定位到一条涉及三张表关联的查询。这时候你会怎么查我的习惯是先看表数据量再用EXPLAIN跑一遍执行计划最后根据执行计划决定是加索引、改SQL还是拆查询。EXPLAIN在这个链路里扮演的角色就是把MySQL优化器“心里想的那条路”摊开给你看——它选择先读哪张表、用哪个索引、预估扫多少行、需不需要回表、要不要排序。这一点特别关键EXPLAIN输出的不是SQL真实执行结果而是优化器基于成本模型推算出来的执行方案。既然是推算就存在“优化器判断失误”的可能。比如统计信息过期、索引选择性判断偏差、或者SQL写法导致优化器压根没往索引上想这些都会让执行计划不是最优的。所以读EXPLAIN的核心能力不是背字段含义而是能判断“这个计划合不合理”以及“如果不对怎么引导优化器走我们想要的路”。1.2 EXPLAIN输出字段概览与使用姿势先说说最基础的用法。MySQL 5.6以上版本建议直接看扩展信息也就是在EXPLAIN后面加FORMATJSON或者直接在EXPLAIN后执行SHOW WARNINGS看优化器改写后的SQL。这两种方式能看到比表格输出更细的成本估算排查复杂问题时更有用。日常快速排查传统表格输出就够用了。EXPLAIN表格输出核心字段大致有这些id、select_type、table、type、possible_keys、key、key_len、ref、rows、filtered、Extra。其中type描述访问类型key描述实际选中的索引rows是优化器估算的需要读取行数Extra包含了很多附加信息比如是否文件排序、是否用到覆盖索引。我自己看执行计划有个固定顺序先看type判断访问级别再看key确认索引有没有用上然后看rows和filtered估算扫描量最后看Extra找有没有隐性问题。这套顺序在同一条SQL的多个执行计划对比时特别好用能快速定位差异点。另外提醒一句EXPLAIN在MySQL 8.0里还支持EXPLAIN ANALYZE这是真正执行SQL并返回实际耗时和行数的工具比传统EXPLAIN更进一步。但EXPLAIN ANALYZE真的会跑SQL只建议在测试库或者低峰期使用生产环境谨慎。2. type列是访问类型的“体检报告”2.1 从system到ALL性能逐级递减type列是EXPLAIN结果里我最先看的一列它直接告诉你MySQL是怎么在表里找数据的。打个比方type是const就好比你直接翻字典按拼音找到了字type是ALL就相当于从第一页翻到最后一页。性能好坏一眼就能判断。从好到差常见值大概这么排system const eq_ref ref range index ALL。system和const属于极少数情况基本是主键或唯一索引精确定位命中一行。eq_ref出现在多表join时被驱动表通过主键或唯一索引关联每行只匹配一条。ref是普通索引等值匹配可能命中多行这是很常见的健康状态。range就是索引范围扫描比如between、in、大于小于这类条件。index听起来带“索引”两个字很多人误以为很快其实它是全索引扫描相当于把整棵索引树从头到尾读一遍比ALL好一点但依然是扫描全量。ALL就是全表扫描这是优化的大敌。看到一个SQL的type从ALL变成range或者ref基本可以断定加索引起效了。比如之前排查过一个订单查询WHERE条件里有user_id和status没索引时type是ALL加上联合索引后变成ref查询时间从800ms降到了20ms。这种效果是实打实的。2.2 站在优化器视角理解访问类型选择你可能会问为什么有时候明明有索引优化器还是选ALL这就是理解执行计划的进阶点——优化器不是“有索引就用”而是综合评估代价。如果一条SQL要查表中大部分行比如status字段区分度很低90%的数据都是这个状态值优化器会判断走索引还要大量回表不如直接全表扫描便宜。这种情况下你强行加索引执行计划可能纹丝不动。所以在看type时不要孤立地看一个字段要结合rows估算。如果type是ALL但rows只有几百行小表全扫描根本不算问题。反过来type是ref但rows估算几十万那这个索引的选择性就很差需要重新考虑索引设计。实践中有个经验type和rows要一起看单看type容易误判。2.3 一个糟糕执行计划的真实案例之前有朋友给我看一条他引以为傲的“优化后SQL”说是加了索引后快多了。我跑了下EXPLAINtype是ALLrows显示12万。问他这表一共多少行他说14万。也就是说这条SQL虽然用了索引但实际查询还是全表扫了。为什么因为WHERE条件里对索引列做了函数操作比如DATE(create_time) 2024-01-01索引就失效了。他加索引时是加在create_time上的但函数包裹让优化器没法用索引树做范围匹配只能老老实实全表扫。这种案例特别典型也说明一个问题加索引之前先看执行计划加完索引再看执行计划两次EXPLAIN对比才是验证索引有效性的唯一标准不能靠感觉。3. key列与索引选择的底层逻辑3.1 possible_keys、key、key_len怎么组合读possible_keys列出的是这条SQL可能用到的索引key是优化器实际选中的那个。这里有个常见的坑possible_keys里明明有索引但key是NULL说明优化器评估后认为索引帮不上忙。遇到这种情况优先考虑两个方向一是SQL写法导致索引无法使用二是统计信息不准确导致优化器误判。前者改SQL后者执行ANALYZE TABLE更新统计信息。key_len这个字段很多人忽略其实它信息量很大。key_len表示MySQL在索引里使用的字节数通过它可以反推联合索引到底用到了哪几列。比如一张表有联合索引(a, b, c)a是INT占4字节b是VARCHAR(100)按utf8mb4算占400字节c也是INT占4字节。如果key_len是4说明只用到了a列如果是408就用到了a和b412就是三列全用上。这个特性在排查“为什么联合索引没完全生效”时特别管用。3.2 联合索引的最左前缀原则联合索引是MySQL索引优化的重头戏核心规则就是最左前缀原则查询条件里必须包含联合索引的最左列索引才能生效。这和字典的目录结构很像先按首字母、再按第二个字母排跳过首字母直接查第二个字母目录就用不上。举个例子索引(a, b, c)WHERE a 1 AND c 3这时候只能用a列c列的条件索引帮不上忙WHERE b 2直接跟索引无关WHERE a 1 AND b 2 AND c 3这才是最理想的全值匹配。理解这个原则后设计联合索引时要问自己一个问题业务查询里哪个字段出现频率最高、区分度最好通常把最常作为等值条件的字段放最左把范围查询字段放后面因为范围条件之后的索引列会失效。这里有个小技巧需要范围查询又想让它后面的列继续走索引可以尝试把范围查询改写为等值IN列表。比如WHERE a 1 AND b 100改成WHERE a 1 AND b IN (101, 102, 103)在某些版本和优化器版本下能多用到一列索引。但这招不一定每次都好使需要结合EXPLAIN验证。3.3 索引失效的典型场景自查我自己维护过不少业务库总结了一份索引失效自查清单每次排查SQL都会对着过一遍对索引列使用了函数或运算比如WHERE YEAR(create_time) 2024、WHERE price 10 100。隐式类型转换导致索引失效比如phone列是VARCHAR查询用WHERE phone 13800001111数字类型会被转换成字符串很可能索引用不上。LIKE模糊查询以通配符开头WHERE name LIKE %张索引失效WHERE name LIKE 张%则可以走索引。OR条件中只要有一个字段没索引整个条件可能都走不了索引。联合索引不满足最左前缀。优化器判断全表扫描比索引扫描成本更低时自动放弃索引。这份清单看起来简单但每条背后都有真实的线上事故。最典型的是隐式类型转换曾经排查过一个用户表查询突然慢十倍的问题最终定位就是某个字段类型定义不规范VARCHAR字段存了手机号代码里却用数字类型传参。改查SQL为字符串传参后type从ALL变回ref问题秒解。4. rows与Extra列里的额外信息价值4.1 rows估算为什么不完全可信rows字段是优化器估算的需要扫描的行数不是精确值。它基于统计信息和采样估算误差在所难免。所以判断执行计划好坏时rows只作为参考量级。比如估算几千行但实际跑出来几十毫秒完全正常。估算几十万行但实际数据只有几百行那可能是统计信息太陈旧了执行ANALYZE TABLE可以解决。但rows在对比场景下价值很高。比如同一条SQL修改前后各跑一次EXPLAINrows从10万降到100这个变化比任何解释都有说服力。我自己做优化汇报时就喜欢用这种对比数据直观、可信、容易复盘。4.2 Extra列里值得警惕的几类信息Extra列是执行计划的“备注栏”里面藏了很多细节。常见有价值的信息按风险排序Using filesort是文件排序意味着MySQL需要额外的排序操作可能存在性能隐患。值得强调的是filesort不一定真的在磁盘上排序数据量小时可能在内存里完成但名字带file总归让人不放心。看到这个字段优先排查ORDER BY的字段是否在索引里联合索引能覆盖ORDER BY的话排序可以直接走索引顺序Extra就不会出现Using filesort。Using temporary表示查询用到了临时表常见于GROUP BY、DISTINCT、UNION这类操作。临时表可能落在磁盘上性能开销很大。优化方向通常是重写SQL或者调整索引让分组操作能走索引有序扫描。Using index是好事表示查询用到了覆盖索引不需要回表。覆盖索引是优化利器尤其对于统计类查询能在索引里拿到全部需要的数据速度极快。Using where表示MySQL在存储引擎层拿到数据后又做了条件过滤这种情况出现在无法完全通过索引下推完成过滤的场景。从MySQL 5.6开始有索引下推优化部分WHERE条件会下推到存储引擎层提前过滤减少回表这里涉及ICP特性后续细说。4.3 三行Extra信息优化实战复盘有一次排查报表导出慢的问题原始SQL大致长这样SELECT product_id, SUM(amount) FROM orders WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31 GROUP BY product_id ORDER BY product_id;EXPLAIN结果里出现了Using temporary和Using filesort这在GROUP BY ORDER BY组合下很常见。优化方式是改变索引设计新建联合索引(create_time, product_id, amount)让WHERE过滤和GROUP BY排序都能走索引顺序。建完索引再看执行计划Using temporary和Using filesort都消失了rows也大幅下降导出从原来的30多秒降到了3秒以内。这个案例想说明的是Extra里的每个词都对应一条可执行的优化路径学会读Extra等于拿到了一张问题清单。5. 索引优化的完整实操思路5.1 从业务SQL反推索引设计优化索引之前先收集业务的高频SQL这是最基本的一步。我有一次接手一个电商后台系统把慢查询日志、业务代码里的SQL、DBA统计的高频查询全拉出来光分析就花了两天。但这一步省不了因为索引设计必须贴近真实查询模式不能拍脑袋。拿到SQL清单后按这几步设计索引找出WHERE条件里的等值字段这些字段适合放在联合索引左侧。找出范围查询字段放在等值字段后面。找出ORDER BY、GROUP BY字段尽量让它们也走索引顺序避免filesort和临时表。考虑SELECT的字段能否全部包含在索引里能则实现覆盖索引直接跳过回表。这套思路做下来80%的查询都能设计出合理索引。剩下的复杂查询比如多表join、子查询、动态条件就需要单独用EXPLAIN验证了。5.2 一个从全表扫描到覆盖索引的完整优化案例说一个典型的案例。业务有个订单列表页筛选条件包括用户ID、订单状态、下单时间范围同时要按下单时间倒序排列。原始表结构里只有主键id查询全是全表扫描。线上数据量300万接口超时严重。第一步根据等值条件设计联合索引(user_id, status, create_time)。这条索引让WHERE三个条件都能走索引type从ALL变成range。但由于SELECT返回的字段还包括订单金额、收货地址等不在索引里每次命中都要回表整体响应在几百毫秒到一秒之间波动。第二步优化做覆盖索引。创建一个更宽的联合索引(user_id, status, create_time, amount)核心查询字段都能从索引拿到Extra出现Using index回表彻底消失。同样的查询从800ms降到50ms以内。这个案例想强调两点一是索引设计不是一步到位先解决访问类型再从回表角度二次优化二是覆盖索引不是越宽越好要权衡写入性能和存储空间尤其对于更新频繁的表索引列太多会拖累写操作。5.3 索引维护的日常操作清单索引设计完了后面还有很长的维护路。分享几个日常运维动作定期用SHOW INDEX FROM table查看索引分布检查是否有冗余索引。联合索引(a, b)和单独索引(a)同时存在时后者就是冗余的建议删除。关注索引使用率可以通过performance_schema里的统计信息分析哪些索引从来没被用过长期不用的索引果断清理。大表加索引要选低峰期用在线DDL工具或者MySQL 8.0的原生在线DDL能力减少锁表影响。表数据频繁增删改时定期执行ANALYZE TABLE更新统计信息避免优化器基于过期的统计信息做出错误判断。6. 常见问题与排查技巧实录6.1 排查问题SQL的标准化步骤遇到过太多乱糟糟的排查场景很多人一上来就各种猜改来改去没有章法。我自己沉淀了一套标准步骤分享出来拿到慢SQL后先看表结构和索引现状SHOW CREATE TABLE。执行EXPLAIN看执行计划重点关注type、key、rows、Extra四列。用FORMATJSON看更精确的cost估算定位最耗时的操作。如果怀疑统计信息有问题执行ANALYZE TABLE刷新后再看执行计划。修改SQL或者索引后重跑EXPLAIN对比观察type和rows的变化。在生产环境小流量验证真实性能提升。这套步骤看着简单但能避免90%的“瞎调优”。我见过太多人上来就直接加索引加完发现没效果又删掉来回折腾就是少了第2步和第5步的对比验证。6.2 高频问题速查表根据多年经验整理下面这份速查表基本覆盖了日常最容易踩的坑问题现象可能原因排查思路解决方案typeALL全表扫描无索引或索引失效查看key列是否为空根据WHERE条件创建合适索引key_len远小于预期联合索引未完全生效对照索引列顺序反推key_len调整条件顺序满足最左前缀Extra出现Using filesortORDER BY未走索引检查排序字段是否在索引内调整联合索引或改写SQLExtra出现Using temporary分组/去重操作走临时表检查GROUP BY、DISTINCT字段通过索引消除临时表有索引但不走统计信息过期或区分度低查看rows估算ANALYZE TABLE或SQL改写索引列用了函数索引失效查看WHERE条件写法函数改写为范围条件隐式类型转换字段类型与参数不匹配比较字段定义和传参类型统一类型避免转换数据量小全表扫描优化器认为ABC成本更低看rows是否很小数据量增长后再验证这张表我贴在公司内部技术文档里团队新同学排查慢SQL时直接对照效率提升很明显。6.3 索引优化中容易被忽略的细节几个细节问题虽然不起眼但实际影响很大字符集和排序规则必须统一。多表关联时的JOIN字段如果字符集不同MySQL可能无法使用索引导致关联查询变慢。排查时留意SHOW CREATE TABLE里各表的CHARSET是否一致。字段类型设计要克制。能用INT就别用VARCHAR存数字能用短VARCHAR就别预留太长。索引列越短每页能存放的索引条目越多查询性能越好。NULL值对索引有影响但不是网上传的“索引完全失效”。MySQL的索引本身不拒绝NULL值只是IS NULL和IS NOT NULL的优化方式有区别。设计表结构时尽量给字段设置NOT NULL DEFAULT能让索引更紧凑避免后续踩坑。6.4 大表深分页优化案例最后一个实战案例。有张日志表业务上经常要做“上一页/下一页”翻页越往后越慢。排查时发现LIMIT 100000, 20配合ORDER BY create_timeMySQL要扫描前10万行然后丢弃代价很大。传统优化方式是延迟关联也就是先只查出主键ID再用主键关联回原表获取完整数据。SQL大致长这样SELECT t.* FROM log_table t INNER JOIN ( SELECT id FROM log_table ORDER BY create_time DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;这个方案能让内层查询只扫描覆盖索引大幅减少回表次数。更进一步的方案是记录上一页最后一条数据的主键或时间戳用WHERE条件定位而不是LIMIT偏移量这叫游标分页性能最好但会改变接口语义需要业务配合。6.5 一条SQL改写解决索引失效的实例之前遇到一个案例原始SQL用了OR连接多个条件导致索引全失效SELECT * FROM users WHERE phone 13800001111 OR email testexample.com;phone和email字段各有单独索引但OR条件下优化器没法同时用两个索引只能全表扫描。改写方式是拆成两个查询用UNION连接SELECT * FROM users WHERE phone 13800001111 UNION SELECT * FROM users WHERE email testexample.com;改写后两个分支各自走索引EXPLAIN里type都是ref查询耗时从百毫秒级降到个位数毫秒。这个案例很适合用来理解优化器的局限性——有时候不是MySQL不行是SQL写法没给它发挥空间。7. 从EXPLAIN出发建立索引优化的全局思维搞了这么久的MySQL性能优化我最大的感受是EXPLAIN不是终点而是一个起点。它就像医生手里的X光片能看到骨骼结构但真正治病的还是后续的诊断和治疗方案。索引优化也一样EXPLAIN帮你看到扫描方式、扫描行数、回表情况但你要做的决策——加什么索引、怎么写SQL、怎么改表结构——都建立在对业务查询模式的深刻理解之上。所以给刚接触这块的同行一个建议别急着背命令、背参数先培养一种“漫游执行计划”的习惯。拿到任何一条SQL脑子里自动过一遍它会怎么扫表会不会回表排序能不能走索引有没有隐式的类型转换这种直觉一旦建立再看EXPLAIN的输出每条信息都会变得立体起来。我自己在实际操作里的体会是索引优化不是一招鲜的事。同一个方案在今天可能是最优的等数据量翻十倍、业务查询模式变了可能又需要重新设计。保持对执行计划的敏感定期回顾慢查询日志顺手用EXPLAIN检查一下有没有新的问题SQL冒头这套动作做下来数据库才不会成为业务发展的瓶颈。