线上MySQL慢查询报警最常用的排查手段就是EXPLAIN。这个命令本身不复杂但用好了能直接定位一条SQL为什么慢慢在哪个环节是该建索引还是该改写法。索引优化说白了就是跟执行计划打交道而EXPLAIN恰好是MySQL优化器把执行计划摊开给你看的那扇窗。无论是刚写业务没多久的开发者还是专职负责数据库稳定性的同学把它吃透都特别值。先说一个我实际遇到过的场景。某天凌晨业务方反馈一个查询接口突然超时我上去一看监控有一条SELECT平时跑几十毫秒现在要两三秒。用EXPLAIN一看type列是ALLrows显示扫描了上千万行而possible_keys里面明明列着可用索引key却是NULL。看到这一眼基本就有数了索引失效。后来从SQL里删掉一个包裹在条件字段上的函数执行计划立刻从全表扫描变成了索引范围扫描接口直接恢复。整个过程不到十分钟靠的就是对EXPLAIN几个关键字段的快速解读。所以这篇我把EXPLAIN的完整用法、每个字段背后的含义、以及结合它做索引优化的思路逐步拆开讲最后附上几个真实优化案例。内容偏实战看的时候可以顺手把你自己库里的慢SQL拿出来对照。1. 为什么慢SQL排查要从EXPLAIN开始1.1 执行计划是你和MySQL优化器沟通的唯一窗口MySQL收到一条SQL后不会说“我打算这么干”但EXPLAIN会原原本本告诉你它想怎么干。优化器会根据表结构、索引分布、统计信息等因素选择它认为成本最低的执行路径而这个路径就体现在EXPLAIN输出的一行行数据里。你可以把优化器想成一个导航软件同样的目的地它能规划出多条路线EXPLAIN展示的就是它当前选择的路线以及为什么要走这条路的部分理由比如估计要经过多少个路口、预估耗时多少。这也是为什么我强调做查询优化时第一步永远是EXPLAIN而不是凭经验猜。很多新手碰到慢SQL第一反应是“加索引”但不加分析直接加很可能加错字段、建错顺序甚至因为新增了冗余索引拖慢写入。而EXPLAIN能先告诉你要不要加、加在哪个字段、加完效果如何。注意EXPLAIN默认只是执行计划的预估不会真正执行SQL。如果你需要看实际执行时间和真实行数可以用MySQL 8.0.18引入的EXPLAIN ANALYZE它会真的跑一遍SQL。但生产环境建议慎重使用尤其是写操作和大查询。1.2 EXPLAIN能帮你回答的三个核心问题拿到一条慢SQL使用EXPLAIN能看到三个维度的情况是否走了索引type字段直接体现访问类型从最好到最差大致是 system const eq_ref ref range index ALL。大致扫描多少行rows字段是优化器估算的扫描行数不是精确值但对成本判断很有参考意义。还有没有额外的排序/临时表开销Extra列会出现 Using filesort、Using temporary 这类字样它们往往是查询变慢的隐形杀手。把这三点看明白一条SQL为什么慢基本就有了答案。全表扫描、扫数百万行、还要文件排序这三点加起来不快才奇怪。反之如果能通过改变索引策略让type从ALL变成range/ref、rows大幅降低、Extra里的 filesort 和 temporary 消失性能往往立竿见影。EXPLAIN还有一个容易被忽略的用法它可以作用于多条SQL也可以用来对比优化前后的执行计划。我习惯把一条慢SQL优化前的执行计划和优化后的打包存在笔记里后续同类问题直接拿来做对照省很多重新分析的时间。2. EXPLAIN输出字段逐项拆解2.1 type字段一次扫到底还是走索引全看它type字段是最直观的访问类型标识也是大多数人判断SQL是否高效的第一参考。MySQL 8.0中常见类型按照性能从优到劣排列如下type含义典型场景system表只有一行系统表或派生表的特殊场景几乎见不到const通过主键或唯一索引查一行WHERE id 1且列是主键或唯一索引eq_ref多表连接时内表通过主键/唯一索引关联join查询中被驱动表走主键等值匹配ref通过普通二级索引等值匹配WHERE user_id 123user_id有普通索引range索引范围扫描WHERE id 100、BETWEEN、LIKE abc%index全索引扫描扫描整个索引树但不用回表的情况ALL全表扫描没有可用索引或优化器认为走索引更差看到ALL的时候要区分两种可能一种是真的没有可用索引属于索引缺失另一种是明明有索引但优化器算了一下觉得全表扫更快这常见于数据量不大的小表或者是统计信息偏差大导致误判。我见过不少人一看到ALL就疯狂建索引结果把小表建成了索引大全写入变慢不说查询也没提升多少。2.2 key、rows、filtered组合起来才是完整故事type看大方向key和key_len看具体用到了哪个索引以及用到多少前缀列rows和filtered用来估算查询代价。key字段表示优化器最终选择的索引名称possible_keys则列出所有可能被用到的索引。注意possible_keys有值不代表一定会用优化器是从候选索引里挑一个它认为成本最低的。所以当possible_keys列了索引、key却是NULL时就是在提醒你可能存在索引失效常见诱因包括对索引列做了函数运算、隐式类型转换、使用了前导模糊查询等。key_len也非常有信息量。它是MySQL根据索引列定义计算出的字节长度能侧面反映联合索引究竟用到了哪几列。举个例子一张表的索引是idx_user (user_id, created_at)user_id是INT长度为4字节created_at是DATETIME长度为8字节。如果EXPLAIN出来key_len只有4说明只用了user_id这一列作为匹配条件created_at还没被用上。提示key_len的计算可以自己推导一遍加深理解。比如一个非空的VARCHAR(64)字段如果表是utf8mb4字符集每个字符最多4字节VARCHAR还需要额外的1到2个字节记录长度64字符最坏情况是64*42258所以这条索引的key_len很可能就是258。看到这个数字你就能反推出联合索引用到了哪个位置比单看key名细致得多。rows字段是优化器估算的需要扫描的行数。它和filtered连起来看才有价值filtered表示经过条件过滤后预计剩余行数的百分比。比如rows10000、filtered10优化器预计过滤后还剩1000行。虽然这是基于统计信息的估算经常不准但如果估算值和实际执行结果相差极大通常意味着表的统计信息过期了可以试试执行ANALYZE TABLE重新收集统计信息。2.3 Extra字段里那些容易忽视的告警信号Extra这一列经常出现 Using filesort、Using temporary、Using index 等字样它们直接决定SQL在索引之外还要付出多少额外成本。Using filesort不是真的用磁盘文件排序而是说需要额外执行一次排序操作也就意味着没有直接利用索引的有序性。排序成本会随结果集快速增长是慢查询的常见来源。Using temporary排序或分组时使用了临时表数据量大的场景下会非常吃力。Using index说明是覆盖索引扫描查询需要的列都在索引里不需要回表这是比较理想的扫描方式。Using where表示存储引擎返回行后Server层再用WHERE条件过滤。注意它和索引扫描是两层事件。Using index condition出现了索引下推也就是部分WHERE条件在存储引擎层就被过滤掉了减少了回表次数这是MySQL 5.6以后比较好的优化。Extra里面出现 Using filesort 或者 Using temporary 时不要急着改SQL先想想能不能通过调整索引让排序字段和分组字段直接走索引。树形索引天然有序排序字段如果能作为联合索引的一部分并且符合最左前缀原则EXPLAIN里的 filesort 就会消失查询速度往往能提升一个量级。3. 索引设计与日常优化实操3.1 索引失效的几种高频场景把EXPLAIN看懂之后下一步就是用它的反馈来校验索引设计。索引失效的问题在真实环境里出现频率极高我整理几个典型的场景原因对策WHERE DATE(create_time) 2024-01-01对索引列套函数索引树无法快速定位改写为范围条件和WHERE phone 13800138000phone是VARCHAR隐式类型转换MySQL会把字符列统一转成数值比较写成字符串形式或确保类型一致WHERE name LIKE %张%前导模糊查询无法从索引树起点定位改成右模糊LIKE 张%或使用全文索引、倒排方案联合索引但未满足最左前缀比如索引是(a,b,c)条件只用了b调整查询条件顺序或按最左前缀重新设计索引很多人觉得“我建了索引查询就会走索引”其实这是最容易踩的坑。索引是否生效取决于条件表达式是否保持列本身的纯净性。只要在索引列上做了任何运算包括函数、隐式转换、表达式计算优化器几乎都会放弃索引。3.2 联合索引的设计原则顺序比数量更重要单列索引设计相对简单更考验功力的是联合索引。设计联合索引时我常用的思路有几点等值条件放前面查询里经常出现等值匹配的字段优先放在联合索引前面。比如订单查询通常带 user_id 等值条件那就让 user_id 做联合索引的第一列。把范围条件放在后面范围查询的字段如果放在前面后面列的索引利用率会受限。排序字段作为索引尾巴ORDER BY、GROUP BY 字段参与排序时利用索引有序性可以消除 filesort。区别度大的字段优先先放区分度高的列能更快定位到目标行。但具体情况还是结合查询模式来看不要机械套用。举个例子一张订单表常见的查询是WHERE user_id ? ORDER BY created_at DESC LIMIT 20。假设表上只有一个 user_id 单列索引那么EXPLAIN大概率会显示Using filesort因为索引只帮我们定位了user_id排序还得另做。这种情况下最适合的做法是把索引设计成(user_id, created_at)一次解决过滤和排序两个问题。3.3 覆盖索引与回表成本InnoDB的二级索引叶子节点保存的是索引列加主键值查询如果需要的列在索引中已经存在就不需要再根据主键去聚簇索引回表取其他列这就是覆盖索引。覆盖索引的效果在EXPLAIN里表现为Extra列出现Using index。比如业务上常见的SELECT user_id, order_no FROM orders WHERE user_id 123如果存在(user_id, order_no)联合索引这个查询就能完全走索引完成。反之如果你写成了SELECT *即使走索引定位到行也要把每一行的完整数据回表取出来回表次数多了性能自然下降。注意不要为了覆盖索引盲目把所有查询列都塞进索引。索引列越多写入和存储成本越高。正确做法是优先覆盖高频查询的列同时注意不要让索引过分臃肿。我在实际项目中见过一个表建了五六个联合索引写入慢得离谱就是因为过度优化读路径反而拖累了写路径。3.4 索引下推、MRR等优化机制怎么配合使用MySQL 5.6以后引入了索引条件下推Index Condition PushdownICP简单说就是部分WHERE条件从Server层下推到存储引擎层在读取索引记录时就对索引列做条件过滤减少回表次数。比如联合索引(age, city)查询WHERE age 20 AND city LIKE 杭%如果没有ICPInnoDB会先根据age定位数据再回表过滤city有了ICP后city的条件在引擎层就能过滤掉回表量大减。EXPLAIN里出现Using index condition时就是ICP生效的表现。MRRMulti-Range Read则是把回表操作从随机读优化成顺序读把主键值先排序再批量回表。它和ICP常常搭配使用。这些机制本身是透明的大部分情况下不需要手动干预只需要保证索引设计合理让优化器有足够的空间去使用它们。4. 实战案例一条慢查询从全表扫描到索引命中的完整过程4.1 建表、造数据和原始SQL为了更直观地展示EXPLAIN的用法我构造一个简化的订单表场景。下面的建表语句可以照抄到测试环境。CREATE TABLE orders ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;造数据时如果数据量不够EXPLAIN很难看出变化。我会写一个简单的存储过程快速插入百万行顺便说一句存储过程在造数这个场景里确实比手动一条条INSERT高效得多。DELIMITER $$ CREATE PROCEDURE generate_orders() BEGIN DECLARE i INT DEFAULT 0; WHILE i 1000000 DO INSERT INTO orders (user_id, order_no, amount, status, created_at) VALUES (FLOOR(RAND() * 5000), CONCAT(NO, LPAD(i, 8, 0)), ROUND(RAND() * 1000, 2), FLOOR(RAND() * 5), DATE_ADD(2023-01-01, INTERVAL FLOOR(RAND() * 365) DAY)); SET i i 1; END WHILE; END$$ DELIMITER ; CALL generate_orders();业务SQL是查某用户最近的订单实际线上很常见SELECT order_no, amount, status, created_at FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 20;4.2 用EXPLAIN定位瓶颈执行EXPLAIN看原始状态EXPLAIN SELECT order_no, amount, status, created_at FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 20\G输出大概是*************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders type: ALL possible_keys: idx_user_id key: NULL key_len: NULL ref: NULL rows: 1000000 filtered: 0.02 Extra: Using where; Using filesort两个关键报警type是ALL说明直接全表扫描了一百万行虽然possible_keys里有 idx_user_id但优化器没用它。Extra里有Using filesort说明排序是在索引外额外做的扫完全表还要再排一次序。再想一下为什么不走idx_user_id这条SQL的WHERE条件只有user_id等值按理说应该能走上索引。但ORDER BY created_at DESC LIMIT 20需要把某个用户的订单按时间倒序取前20条。如果只靠idx_user_id定位到该用户的大量订单还要额外排序。优化器估算全表扫描加上filesort的成本在某些数据分布下可能并不会比分两步走差太多。当然看到ALL你就不需要纠结为什么优化的核心思路很明确让索引同时满足过滤和排序从根本上消除filesort和全表扫描。4.3 优化方案和前后对比方案很简单给 (user_id, created_at) 建一个联合索引这个顺序必须固定等值的user_id在前范围/排序的created_at在后否则排序字段用不上。ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);建立后再次EXPLAIN同一句SQL*************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders type: ref possible_keys: idx_user_id,idx_user_created key: idx_user_created key_len: 4 ref: const rows: 136 filtered: 100.00 Extra: Using index condition这里type从ALL变成refrows从100万降到一百多key_len是4说明只用了联合索引的user_id前缀Extra里的filesort消失了说明排序走了索引的有序性。同样的SQL性能差距是数量级的。在建索引前我还要强调一个动作如果表数据量已经很大直接ALTER TABLE加索引会锁表线上往往要借助在线DDL工具或者选择业务低峰期操作。即使MySQL 8.0的ALGORITHMINPLACE让这个过程温和了很多也不太建议大表在业务高峰期直接加索引。4.4 深分页和延迟关联优化索引优化不是建完索引就结束了很多慢查询是翻页翻出来的。比如分页很深的场景SELECT order_no, amount, status, created_at FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 100000, 20;即使联合索引已经把定位和排序做好了深分页依然慢因为MySQL要从索引上找到前100020行再丢掉前100000行扫描量很大。最常见的优化手段是延迟关联先只查询主键ID然后通过主键回表取完整数据。SELECT t.order_no, t.amount, t.status, t.created_at FROM orders t INNER JOIN ( SELECT id FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;内层查询只用了索引和主键避免了大量无用回表外层再按主键取值。执行计划里能看到内层走索引非常快。这个写法应用面很广凡是深分页的查询都建议优先考虑。5. 配合EXPLAIN使用的其他诊断手段5.1 OPTIMIZER_TRACE看优化器决策过程EXPLAIN给的是结论但有时候你会困惑“为什么优化器选了这个索引而不是另一个”。MySQL有一个更底层的调试工具optimizer trace。开启方式是在会话里执行下面的命令再执行一次SQL最后查询 trace。SET optimizer_traceenabledon; SELECT order_no, amount, status, created_at FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 20; SELECT * FROM information_schema.OPTIMIZER_TRACE\Gtrace里会记录优化器评估各个可用索引的“成本估算”、选择索引的完整过程比如为什么忽略某个索引、认为某个方案成本更高等。MySQL优化器做的所有选择几乎都围绕成本模型看懂trace能解释很多EXPLAIN不好解释的“反直觉”现象。5.2 EXPLAIN ANALYZE看实际执行代价MySQL 8.0.18以后支持 EXPLAIN ANALYZE它不只是估算而是会真实执行SQL并输出每一步的实际时间、行数、循环次数。比如EXPLAIN ANALYZE SELECT order_no, amount, status, created_at FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 20;输出会包含类似actual time0.123..0.456 rows20 loops1的数据。它能直接反映实际执行时间和EXPLAIN估算之间的差距。虽然这个工具很好用但在生产库上执行真实SQL需要格外谨慎最好先看一眼预计影响行数或者放到从库上观察。5.3 ANALYZE TABLE与统计信息的维护索引选择和统计信息强相关。InnoDB默认会通过采样方式维护索引统计信息但当大量数据增删后统计信息可能严重滞后原本该走范围索引的SQL会被优化器判定成全表扫描更便宜。遇到EXPLAIN的行为异常时可以执行ANALYZE TABLE orders;这条命令会触发统计信息重新计算往往能解决“明明数据量不大却走了全表扫描”的怪问题。不过它也不是万能的统计信息准了但优化器依然选错时就要回头审视成本模型里索引尺寸、缓存命中率等因素了。这类问题属于比较深的优化范畴一般业务侧用不到太高级的手段。6. 常见问题排查与避坑实录6.1 生产环境常见问题速查我在日常运维和帮团队做review时总结出几个最容易反复踩的坑整理成一张速查表症状可能原因排查与修复EXPLAIN显示ALL且possible_keys有值key为空索引列被函数包裹、隐式类型转换、或统计信息错乱检查WHERE条件列是否“纯净”执行ANALYZE TABLErows估算与实际情况相差巨大统计信息过期ANALYZE TABLE 或重建索引统计有索引但ORDER BY仍filesort排序字段不在联合索引中或索引列顺序不符调整索引让排序字段成为索引一部分并按最左前缀匹配深分页响应极慢MySQL需要先扫描并跳过大量行用延迟关联或者在业务上限制翻页深度列类型不一致导致未走索引字符集或int/string混用统一字段类型注意字段字符集保持一致6.2 一次隐式类型转换的实战教训有次同事反馈用户手机号查询巨慢条件是WHERE phone 13800138000phone列明明是VARCHAR但代码里传入的参数被框架转换成了数值。EXPLAIN结果直接就是ALL。修复方式也很简单把SQL改成手机号字符串形式瞬间走了索引。这类问题非常隐蔽因为代码层面看起来就是“查一个值嘛”但数据库层面却因为类型对比规则导致索引失效。遇到VARCHAR字段配数字条件先怀疑类型转换。6.3 索引优化别贪多要和业务查询模式对齐最后想特别提醒一句索引不是越多越好也不是把所有查询字段都建一遍联合索引就完了。我见过一张表同时存在(a,b)、(a,c)、(a,b,c)三个冗余索引不仅写入性能受影响三个索引还占了额外磁盘空间而真正高频的查询反而没覆盖到。优化索引前先用慢查询日志统计一段时间内的高频SQL和对应WHERE/ORDER BY模式从真实查询里提炼共同点再设计尽量少的联合索引覆盖尽量多的查询。这样索引数量少、利用率高维护成本和写入成本都低。我在排查过程中也习惯在改索引前后各跑一次EXPLAIN把执行计划和实际响应时间一起记录下来。这个习惯帮我避掉过不少“看起来改了索引但没效果”的问题因为从EXPLAIN的type、rows、Extra三个维度就能看出新索引到底用没用上。如果加了一个索引后EXPLAIN毫无变化那说明要么索引设计不合理要么SQL写法有问题这时候停下来重新分析比盲目继续加索引更高效。 MySQL慢查询排查利器：EXPLAIN执行计划与索引优化实战
线上MySQL慢查询报警最常用的排查手段就是EXPLAIN。这个命令本身不复杂但用好了能直接定位一条SQL为什么慢慢在哪个环节是该建索引还是该改写法。索引优化说白了就是跟执行计划打交道而EXPLAIN恰好是MySQL优化器把执行计划摊开给你看的那扇窗。无论是刚写业务没多久的开发者还是专职负责数据库稳定性的同学把它吃透都特别值。先说一个我实际遇到过的场景。某天凌晨业务方反馈一个查询接口突然超时我上去一看监控有一条SELECT平时跑几十毫秒现在要两三秒。用EXPLAIN一看type列是ALLrows显示扫描了上千万行而possible_keys里面明明列着可用索引key却是NULL。看到这一眼基本就有数了索引失效。后来从SQL里删掉一个包裹在条件字段上的函数执行计划立刻从全表扫描变成了索引范围扫描接口直接恢复。整个过程不到十分钟靠的就是对EXPLAIN几个关键字段的快速解读。所以这篇我把EXPLAIN的完整用法、每个字段背后的含义、以及结合它做索引优化的思路逐步拆开讲最后附上几个真实优化案例。内容偏实战看的时候可以顺手把你自己库里的慢SQL拿出来对照。1. 为什么慢SQL排查要从EXPLAIN开始1.1 执行计划是你和MySQL优化器沟通的唯一窗口MySQL收到一条SQL后不会说“我打算这么干”但EXPLAIN会原原本本告诉你它想怎么干。优化器会根据表结构、索引分布、统计信息等因素选择它认为成本最低的执行路径而这个路径就体现在EXPLAIN输出的一行行数据里。你可以把优化器想成一个导航软件同样的目的地它能规划出多条路线EXPLAIN展示的就是它当前选择的路线以及为什么要走这条路的部分理由比如估计要经过多少个路口、预估耗时多少。这也是为什么我强调做查询优化时第一步永远是EXPLAIN而不是凭经验猜。很多新手碰到慢SQL第一反应是“加索引”但不加分析直接加很可能加错字段、建错顺序甚至因为新增了冗余索引拖慢写入。而EXPLAIN能先告诉你要不要加、加在哪个字段、加完效果如何。注意EXPLAIN默认只是执行计划的预估不会真正执行SQL。如果你需要看实际执行时间和真实行数可以用MySQL 8.0.18引入的EXPLAIN ANALYZE它会真的跑一遍SQL。但生产环境建议慎重使用尤其是写操作和大查询。1.2 EXPLAIN能帮你回答的三个核心问题拿到一条慢SQL使用EXPLAIN能看到三个维度的情况是否走了索引type字段直接体现访问类型从最好到最差大致是 system const eq_ref ref range index ALL。大致扫描多少行rows字段是优化器估算的扫描行数不是精确值但对成本判断很有参考意义。还有没有额外的排序/临时表开销Extra列会出现 Using filesort、Using temporary 这类字样它们往往是查询变慢的隐形杀手。把这三点看明白一条SQL为什么慢基本就有了答案。全表扫描、扫数百万行、还要文件排序这三点加起来不快才奇怪。反之如果能通过改变索引策略让type从ALL变成range/ref、rows大幅降低、Extra里的 filesort 和 temporary 消失性能往往立竿见影。EXPLAIN还有一个容易被忽略的用法它可以作用于多条SQL也可以用来对比优化前后的执行计划。我习惯把一条慢SQL优化前的执行计划和优化后的打包存在笔记里后续同类问题直接拿来做对照省很多重新分析的时间。2. EXPLAIN输出字段逐项拆解2.1 type字段一次扫到底还是走索引全看它type字段是最直观的访问类型标识也是大多数人判断SQL是否高效的第一参考。MySQL 8.0中常见类型按照性能从优到劣排列如下type含义典型场景system表只有一行系统表或派生表的特殊场景几乎见不到const通过主键或唯一索引查一行WHERE id 1且列是主键或唯一索引eq_ref多表连接时内表通过主键/唯一索引关联join查询中被驱动表走主键等值匹配ref通过普通二级索引等值匹配WHERE user_id 123user_id有普通索引range索引范围扫描WHERE id 100、BETWEEN、LIKE abc%index全索引扫描扫描整个索引树但不用回表的情况ALL全表扫描没有可用索引或优化器认为走索引更差看到ALL的时候要区分两种可能一种是真的没有可用索引属于索引缺失另一种是明明有索引但优化器算了一下觉得全表扫更快这常见于数据量不大的小表或者是统计信息偏差大导致误判。我见过不少人一看到ALL就疯狂建索引结果把小表建成了索引大全写入变慢不说查询也没提升多少。2.2 key、rows、filtered组合起来才是完整故事type看大方向key和key_len看具体用到了哪个索引以及用到多少前缀列rows和filtered用来估算查询代价。key字段表示优化器最终选择的索引名称possible_keys则列出所有可能被用到的索引。注意possible_keys有值不代表一定会用优化器是从候选索引里挑一个它认为成本最低的。所以当possible_keys列了索引、key却是NULL时就是在提醒你可能存在索引失效常见诱因包括对索引列做了函数运算、隐式类型转换、使用了前导模糊查询等。key_len也非常有信息量。它是MySQL根据索引列定义计算出的字节长度能侧面反映联合索引究竟用到了哪几列。举个例子一张表的索引是idx_user (user_id, created_at)user_id是INT长度为4字节created_at是DATETIME长度为8字节。如果EXPLAIN出来key_len只有4说明只用了user_id这一列作为匹配条件created_at还没被用上。提示key_len的计算可以自己推导一遍加深理解。比如一个非空的VARCHAR(64)字段如果表是utf8mb4字符集每个字符最多4字节VARCHAR还需要额外的1到2个字节记录长度64字符最坏情况是64*42258所以这条索引的key_len很可能就是258。看到这个数字你就能反推出联合索引用到了哪个位置比单看key名细致得多。rows字段是优化器估算的需要扫描的行数。它和filtered连起来看才有价值filtered表示经过条件过滤后预计剩余行数的百分比。比如rows10000、filtered10优化器预计过滤后还剩1000行。虽然这是基于统计信息的估算经常不准但如果估算值和实际执行结果相差极大通常意味着表的统计信息过期了可以试试执行ANALYZE TABLE重新收集统计信息。2.3 Extra字段里那些容易忽视的告警信号Extra这一列经常出现 Using filesort、Using temporary、Using index 等字样它们直接决定SQL在索引之外还要付出多少额外成本。Using filesort不是真的用磁盘文件排序而是说需要额外执行一次排序操作也就意味着没有直接利用索引的有序性。排序成本会随结果集快速增长是慢查询的常见来源。Using temporary排序或分组时使用了临时表数据量大的场景下会非常吃力。Using index说明是覆盖索引扫描查询需要的列都在索引里不需要回表这是比较理想的扫描方式。Using where表示存储引擎返回行后Server层再用WHERE条件过滤。注意它和索引扫描是两层事件。Using index condition出现了索引下推也就是部分WHERE条件在存储引擎层就被过滤掉了减少了回表次数这是MySQL 5.6以后比较好的优化。Extra里面出现 Using filesort 或者 Using temporary 时不要急着改SQL先想想能不能通过调整索引让排序字段和分组字段直接走索引。树形索引天然有序排序字段如果能作为联合索引的一部分并且符合最左前缀原则EXPLAIN里的 filesort 就会消失查询速度往往能提升一个量级。3. 索引设计与日常优化实操3.1 索引失效的几种高频场景把EXPLAIN看懂之后下一步就是用它的反馈来校验索引设计。索引失效的问题在真实环境里出现频率极高我整理几个典型的场景原因对策WHERE DATE(create_time) 2024-01-01对索引列套函数索引树无法快速定位改写为范围条件和WHERE phone 13800138000phone是VARCHAR隐式类型转换MySQL会把字符列统一转成数值比较写成字符串形式或确保类型一致WHERE name LIKE %张%前导模糊查询无法从索引树起点定位改成右模糊LIKE 张%或使用全文索引、倒排方案联合索引但未满足最左前缀比如索引是(a,b,c)条件只用了b调整查询条件顺序或按最左前缀重新设计索引很多人觉得“我建了索引查询就会走索引”其实这是最容易踩的坑。索引是否生效取决于条件表达式是否保持列本身的纯净性。只要在索引列上做了任何运算包括函数、隐式转换、表达式计算优化器几乎都会放弃索引。3.2 联合索引的设计原则顺序比数量更重要单列索引设计相对简单更考验功力的是联合索引。设计联合索引时我常用的思路有几点等值条件放前面查询里经常出现等值匹配的字段优先放在联合索引前面。比如订单查询通常带 user_id 等值条件那就让 user_id 做联合索引的第一列。把范围条件放在后面范围查询的字段如果放在前面后面列的索引利用率会受限。排序字段作为索引尾巴ORDER BY、GROUP BY 字段参与排序时利用索引有序性可以消除 filesort。区别度大的字段优先先放区分度高的列能更快定位到目标行。但具体情况还是结合查询模式来看不要机械套用。举个例子一张订单表常见的查询是WHERE user_id ? ORDER BY created_at DESC LIMIT 20。假设表上只有一个 user_id 单列索引那么EXPLAIN大概率会显示Using filesort因为索引只帮我们定位了user_id排序还得另做。这种情况下最适合的做法是把索引设计成(user_id, created_at)一次解决过滤和排序两个问题。3.3 覆盖索引与回表成本InnoDB的二级索引叶子节点保存的是索引列加主键值查询如果需要的列在索引中已经存在就不需要再根据主键去聚簇索引回表取其他列这就是覆盖索引。覆盖索引的效果在EXPLAIN里表现为Extra列出现Using index。比如业务上常见的SELECT user_id, order_no FROM orders WHERE user_id 123如果存在(user_id, order_no)联合索引这个查询就能完全走索引完成。反之如果你写成了SELECT *即使走索引定位到行也要把每一行的完整数据回表取出来回表次数多了性能自然下降。注意不要为了覆盖索引盲目把所有查询列都塞进索引。索引列越多写入和存储成本越高。正确做法是优先覆盖高频查询的列同时注意不要让索引过分臃肿。我在实际项目中见过一个表建了五六个联合索引写入慢得离谱就是因为过度优化读路径反而拖累了写路径。3.4 索引下推、MRR等优化机制怎么配合使用MySQL 5.6以后引入了索引条件下推Index Condition PushdownICP简单说就是部分WHERE条件从Server层下推到存储引擎层在读取索引记录时就对索引列做条件过滤减少回表次数。比如联合索引(age, city)查询WHERE age 20 AND city LIKE 杭%如果没有ICPInnoDB会先根据age定位数据再回表过滤city有了ICP后city的条件在引擎层就能过滤掉回表量大减。EXPLAIN里出现Using index condition时就是ICP生效的表现。MRRMulti-Range Read则是把回表操作从随机读优化成顺序读把主键值先排序再批量回表。它和ICP常常搭配使用。这些机制本身是透明的大部分情况下不需要手动干预只需要保证索引设计合理让优化器有足够的空间去使用它们。4. 实战案例一条慢查询从全表扫描到索引命中的完整过程4.1 建表、造数据和原始SQL为了更直观地展示EXPLAIN的用法我构造一个简化的订单表场景。下面的建表语句可以照抄到测试环境。CREATE TABLE orders ( id INT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(64) NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;造数据时如果数据量不够EXPLAIN很难看出变化。我会写一个简单的存储过程快速插入百万行顺便说一句存储过程在造数这个场景里确实比手动一条条INSERT高效得多。DELIMITER $$ CREATE PROCEDURE generate_orders() BEGIN DECLARE i INT DEFAULT 0; WHILE i 1000000 DO INSERT INTO orders (user_id, order_no, amount, status, created_at) VALUES (FLOOR(RAND() * 5000), CONCAT(NO, LPAD(i, 8, 0)), ROUND(RAND() * 1000, 2), FLOOR(RAND() * 5), DATE_ADD(2023-01-01, INTERVAL FLOOR(RAND() * 365) DAY)); SET i i 1; END WHILE; END$$ DELIMITER ; CALL generate_orders();业务SQL是查某用户最近的订单实际线上很常见SELECT order_no, amount, status, created_at FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 20;4.2 用EXPLAIN定位瓶颈执行EXPLAIN看原始状态EXPLAIN SELECT order_no, amount, status, created_at FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 20\G输出大概是*************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders type: ALL possible_keys: idx_user_id key: NULL key_len: NULL ref: NULL rows: 1000000 filtered: 0.02 Extra: Using where; Using filesort两个关键报警type是ALL说明直接全表扫描了一百万行虽然possible_keys里有 idx_user_id但优化器没用它。Extra里有Using filesort说明排序是在索引外额外做的扫完全表还要再排一次序。再想一下为什么不走idx_user_id这条SQL的WHERE条件只有user_id等值按理说应该能走上索引。但ORDER BY created_at DESC LIMIT 20需要把某个用户的订单按时间倒序取前20条。如果只靠idx_user_id定位到该用户的大量订单还要额外排序。优化器估算全表扫描加上filesort的成本在某些数据分布下可能并不会比分两步走差太多。当然看到ALL你就不需要纠结为什么优化的核心思路很明确让索引同时满足过滤和排序从根本上消除filesort和全表扫描。4.3 优化方案和前后对比方案很简单给 (user_id, created_at) 建一个联合索引这个顺序必须固定等值的user_id在前范围/排序的created_at在后否则排序字段用不上。ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);建立后再次EXPLAIN同一句SQL*************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders type: ref possible_keys: idx_user_id,idx_user_created key: idx_user_created key_len: 4 ref: const rows: 136 filtered: 100.00 Extra: Using index condition这里type从ALL变成refrows从100万降到一百多key_len是4说明只用了联合索引的user_id前缀Extra里的filesort消失了说明排序走了索引的有序性。同样的SQL性能差距是数量级的。在建索引前我还要强调一个动作如果表数据量已经很大直接ALTER TABLE加索引会锁表线上往往要借助在线DDL工具或者选择业务低峰期操作。即使MySQL 8.0的ALGORITHMINPLACE让这个过程温和了很多也不太建议大表在业务高峰期直接加索引。4.4 深分页和延迟关联优化索引优化不是建完索引就结束了很多慢查询是翻页翻出来的。比如分页很深的场景SELECT order_no, amount, status, created_at FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 100000, 20;即使联合索引已经把定位和排序做好了深分页依然慢因为MySQL要从索引上找到前100020行再丢掉前100000行扫描量很大。最常见的优化手段是延迟关联先只查询主键ID然后通过主键回表取完整数据。SELECT t.order_no, t.amount, t.status, t.created_at FROM orders t INNER JOIN ( SELECT id FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 100000, 20 ) tmp ON t.id tmp.id;内层查询只用了索引和主键避免了大量无用回表外层再按主键取值。执行计划里能看到内层走索引非常快。这个写法应用面很广凡是深分页的查询都建议优先考虑。5. 配合EXPLAIN使用的其他诊断手段5.1 OPTIMIZER_TRACE看优化器决策过程EXPLAIN给的是结论但有时候你会困惑“为什么优化器选了这个索引而不是另一个”。MySQL有一个更底层的调试工具optimizer trace。开启方式是在会话里执行下面的命令再执行一次SQL最后查询 trace。SET optimizer_traceenabledon; SELECT order_no, amount, status, created_at FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 20; SELECT * FROM information_schema.OPTIMIZER_TRACE\Gtrace里会记录优化器评估各个可用索引的“成本估算”、选择索引的完整过程比如为什么忽略某个索引、认为某个方案成本更高等。MySQL优化器做的所有选择几乎都围绕成本模型看懂trace能解释很多EXPLAIN不好解释的“反直觉”现象。5.2 EXPLAIN ANALYZE看实际执行代价MySQL 8.0.18以后支持 EXPLAIN ANALYZE它不只是估算而是会真实执行SQL并输出每一步的实际时间、行数、循环次数。比如EXPLAIN ANALYZE SELECT order_no, amount, status, created_at FROM orders WHERE user_id 1234 ORDER BY created_at DESC LIMIT 20;输出会包含类似actual time0.123..0.456 rows20 loops1的数据。它能直接反映实际执行时间和EXPLAIN估算之间的差距。虽然这个工具很好用但在生产库上执行真实SQL需要格外谨慎最好先看一眼预计影响行数或者放到从库上观察。5.3 ANALYZE TABLE与统计信息的维护索引选择和统计信息强相关。InnoDB默认会通过采样方式维护索引统计信息但当大量数据增删后统计信息可能严重滞后原本该走范围索引的SQL会被优化器判定成全表扫描更便宜。遇到EXPLAIN的行为异常时可以执行ANALYZE TABLE orders;这条命令会触发统计信息重新计算往往能解决“明明数据量不大却走了全表扫描”的怪问题。不过它也不是万能的统计信息准了但优化器依然选错时就要回头审视成本模型里索引尺寸、缓存命中率等因素了。这类问题属于比较深的优化范畴一般业务侧用不到太高级的手段。6. 常见问题排查与避坑实录6.1 生产环境常见问题速查我在日常运维和帮团队做review时总结出几个最容易反复踩的坑整理成一张速查表症状可能原因排查与修复EXPLAIN显示ALL且possible_keys有值key为空索引列被函数包裹、隐式类型转换、或统计信息错乱检查WHERE条件列是否“纯净”执行ANALYZE TABLErows估算与实际情况相差巨大统计信息过期ANALYZE TABLE 或重建索引统计有索引但ORDER BY仍filesort排序字段不在联合索引中或索引列顺序不符调整索引让排序字段成为索引一部分并按最左前缀匹配深分页响应极慢MySQL需要先扫描并跳过大量行用延迟关联或者在业务上限制翻页深度列类型不一致导致未走索引字符集或int/string混用统一字段类型注意字段字符集保持一致6.2 一次隐式类型转换的实战教训有次同事反馈用户手机号查询巨慢条件是WHERE phone 13800138000phone列明明是VARCHAR但代码里传入的参数被框架转换成了数值。EXPLAIN结果直接就是ALL。修复方式也很简单把SQL改成手机号字符串形式瞬间走了索引。这类问题非常隐蔽因为代码层面看起来就是“查一个值嘛”但数据库层面却因为类型对比规则导致索引失效。遇到VARCHAR字段配数字条件先怀疑类型转换。6.3 索引优化别贪多要和业务查询模式对齐最后想特别提醒一句索引不是越多越好也不是把所有查询字段都建一遍联合索引就完了。我见过一张表同时存在(a,b)、(a,c)、(a,b,c)三个冗余索引不仅写入性能受影响三个索引还占了额外磁盘空间而真正高频的查询反而没覆盖到。优化索引前先用慢查询日志统计一段时间内的高频SQL和对应WHERE/ORDER BY模式从真实查询里提炼共同点再设计尽量少的联合索引覆盖尽量多的查询。这样索引数量少、利用率高维护成本和写入成本都低。我在排查过程中也习惯在改索引前后各跑一次EXPLAIN把执行计划和实际响应时间一起记录下来。这个习惯帮我避掉过不少“看起来改了索引但没效果”的问题因为从EXPLAIN的type、rows、Extra三个维度就能看出新索引到底用没用上。如果加了一个索引后EXPLAIN毫无变化那说明要么索引设计不合理要么SQL写法有问题这时候停下来重新分析比盲目继续加索引更高效。