做 MySQL 性能优化这几年我最怕听到的一句话就是“这个查询慢加个索引吧”。加索引确实是应对慢查询最常用的手段但方向错了索引反而会成为负担。今天这篇文章我们就围绕“MySQL EXPLAIN 怎么用”这个核心把索引的新增和删除讲透——不只是教你看懂 EXPLAIN 的输出更是告诉你怎样用 EXPLAIN 的结果来决定一个索引到底该建、该改、还是该删。EXPLAIN 是 MySQL 给出的查询执行计划说明书它能直接回答“这条 SQL 是怎么查数据的、用了哪些索引、扫描了多少行、有没有临时排序”。与其拍脑袋给每个需要查询的字段都建索引不如先学会用 EXPLAIN 去验证这个索引真的被用上了吗它是让查询变快了还是仅仅增加了写入成本和存储成本这篇文章会从 EXPLAIN 的基础用法、关键字段含义讲起再结合实际案例演示“根据查询增加和删除索引”的完整流程。如果你是开发人员或者负责线上数据库的维护这篇文章能帮你建立一套可复用的索引整理思路。1. 先聊清楚为什么不能一味创建索引1.1 索引的本质是“给数据建目录”理解索引之前先想一下我们查一本厚书时的状态。没有目录的时候你要找某个知识点只能一页一页翻这就是全表扫描有了目录你可以先定位到章节再翻到具体页码这就是索引查找。MySQL 的索引底层多用 B 树实现它把列值和对应的主键/行位置组织成树形结构让查询从“全表遍历”变成“树上的快速定位”。这个机制本身很好但如果目录建多了会怎样书确实变厚了每一页都可能因为目录过多而变得臃肿。数据库也一样每次插入、更新、删除时MySQL 不仅要修改数据行还要同步维护这张表上的每一个索引树。你多建一个索引就意味着写入路径上多一份维护成本当索引数量达到一定规模后写入性能会肉眼可见地下降。1.2 索引过量带来的三个真实成本第一个成本是写入变慢。这个很好理解数据每变更一次所有相关索引都要跟着调整。如果一张表有 6 个索引一次 INSERT 实际要写 1 份数据 6 份索引这个开销会被放大很多。第二个成本是存储膨胀。索引本身要占磁盘空间尤其对 InnoDB 来说二级索引每个都要额外存储主键值。一张几千万行的表多一个索引可能就是几个 GB 的空间。存储成本往往不会立刻暴露问题但备份、迁移、内存命中率都会受到影响。第三个成本是优化器“选择困难”。索引越多MySQL 优化器在选择执行计划时需要考虑的路径就越多。有些情况下优化器会选错索引宁可去用一个区分度不高的索引也不走我们直觉上更合理的那个。这类问题比“查得慢”更隐蔽往往表现为同样的 SQL 某天突然变慢就是因为统计信息或者数据分布变化导致执行计划拐到了错误的索引上。所以正确的心态是索引是服务查询的工具不是越多越安心。一个索引能否保留标准只有一个——它有没有在高频查询中真正发挥作用。而判断工具就是 EXPLAIN。2. EXPLAIN 怎么看先建立基本认知2.1 一条 SQL 就能查看执行计划EXPLAIN 的使用非常直接在普通 SELECT 语句前面加上 EXPLAIN 关键字即可。比如EXPLAIN SELECT user_id, order_no, total_amount FROM order_info WHERE merchant_id 138 AND status 2 ORDER BY pay_time DESC LIMIT 20;执行后MySQL 会返回一长串列每一行代表执行计划中的一个步骤有些复杂查询会有多行。需要注意EXPLAIN 只是输出“预计的执行计划”它不会真的执行这条 SQL。在 MySQL 8.0.18 及以上版本还有EXPLAIN ANALYZE会真实执行并返回实际耗时与行数这个我们后面再单独说。第一次用 EXPLAIN 的人容易犯两个错误。一是只看key列发现 “走索引了没问题”二是完全忽略rows和Extra导致虽然走了索引但扫描行数高得离谱性能依然很差。这两个误区会在后文结合案例展开。2.2 核心字段速查type、key、rows、Extra 要对着看不同 MySQL 版本输出的字段略有差异MySQL 5.7 常见的是 12 列MySQL 8.0 增加了partitions等列。日常优化重点关注这几个type访问类型代表 MySQL 是如何在表里找数据的。从好到差大致是systemconsteq_refrefrangeindexALL。其中ALL就是全表扫描是我们要重点消灭的目标index虽然也用了索引树但通常表示“扫描了整个索引”比全表扫描好一点但也不理想ref和range是常见的、能接受的访问类型const/eq_ref通常在主键或唯一索引等值匹配时出现效率最高。possible_keys与key前者是优化器“可能使用”的索引列表后者是“实际选用”的索引。如果possible_keys为空但查询很快说明表很小MySQL 认为直接扫全表更划算如果key为 NULL又查得慢这就是典型的缺少可用索引。rows优化器预估需要扫描的行数。它是一个估值不是精确值但用来对比不同执行计划的成本非常好用。rows越大潜在开销越高。filtered表示经过索引条件过滤后剩余行数占扫描行数的百分比。100% 表示过滤效果好如果 filtered 很低意味着索引把大量“不相关”的行捞了进来可能存在索引设计不合理。Extra这一列常常藏着最能说明问题的信息。出现Using filesort意味着 MySQL 需要额外的排序操作对性能影响很大出现Using temporary表示使用了临时表常见于 GROUP BY 或某些 DISTINCT 查询出现Using index说明这里用到了覆盖索引查询的字段都包含在索引里不需要回表这是很理想的状态。这些字段不能割裂来看。比如typeref且rows20000说明走了索引但选择性不强keyidx_a但ExtraUsing filesort说明排序字段没有和索引匹配好。把 type、key、rows、Extra 放在一起解读才能还原出查询的真实路径。3. 不要瞎建索引用 EXPLAIN 判断索引是否有效3.1 type 从 ALL 到 ref / range索引才真正派上用场我见过不少同事给表加了一堆索引但 EXPLAIN 里type依然显示ALL也就是索引根本没被用上。这类情况通常有三个原因索引列区分度太低、查询条件写法导致索引失效、优化器认为全表扫描成本更低。区分度低的典型例子是性别列。一张表里男女比例接近 1:1你在性别上建索引优化器一算走索引还要回表还不如直接扫全表。这种索引基本是摆设。真正能让type从ALL变成ref或range的条件是过滤性足够强的列比如订单号、用户 ID、商户 ID、时间范围等。还有一种常见情况是连接查询中驱动表和被驱动表的访问顺序没控制好。EXPLAIN 多行结果中靠前的一行往往是驱动表靠后的是被驱动表。如果两表关联字段上没有索引第二行很容易出现ALL。这时候不是盲目加索引而是要确保被驱动表的关联列有合适索引通常用eq_ref或ref来体现。3.2 rows 和 filtered判断“扫多了”和“筛掉了多少”只看 type 还不够。我曾经调过一条 SQLEXPLAIN 显示typeref、keyidx_merchant_status看起来很正常但rows高达 18000 多。为什么走了索引还扫这么多行因为索引(merchant_id, status)中merchant_id138的订单量本身就很大status2 的过滤条件虽然存在但索引把它放在第二列优化器仍然要先扫出该商户所有状态的 18000 行再逐一过滤。这种问题的典型特征就是rows偏高、filtered偏低比如 10% 以下。它说明索引结构没有完全匹配查询条件索引列的顺序可能需要调整。如果把 status 放在前面或者建立(status, merchant_id)的索引在特定查询模式下 rows 会明显下降。当然索引列顺序最终取决于你的实际业务查询分布不能一概而论。当我们对比两个候选索引时最实用的做法就是分别 EXPLAIN比较rows值。rows 越小说明需要读的数据越少查询成本越低。再结合filtered就能判断这个索引是否帮你充分过滤了无关数据。3.3 Extra 里那几个关键字每一个都是信号Using filesort是最值得关注的信号之一。MySQL 的排序并不是所有情况都能用索引完成当结果集需要按非索引顺序排序时就要额外排序。额外排序不一定慢但结果集超过内存排序缓冲区时会落盘到临时文件性能会断崖式下降。再看Using temporary它通常和 GROUP BY、DISTINCT、子查询关联MySQL 需要创建临时表来暂存中间结果。频繁出现这个标记的 SQL往往有比较明显的优化空间比如改写为合适的 JOIN或者利用覆盖索引减少临时表的使用。Using index则是比较理想的状态。它表示当前查询所需的数据列都能从索引树中直接取得不需要回表读取数据行。比如索引(merchant_id, status, pay_time)如果 SELECT 只需要这三列和主键EXPLAIN 的 Extra 里就会出现Using index。这种覆盖索引策略可以让查询速度再上一个台阶。所以一条 SQL 的 EXPLAIN 输出不是只看有没有索引而是要结合 type、rows、filtered、Extra 一起判断。索引有效是指它真的让查询路径变短了、扫描行数变少了、排序和临时表消失或减轻了。否则这个索引就该考虑调整或删除了。4. 索引的新增策略让 EXPLAIN 帮你设计索引4.1 基于 WHERE 和 ORDER BY 设计复合索引新增索引前先把高频查询的 WHERE 条件和 ORDER BY、GROUP BY 字段整理出来。在设计复合索引时有两个原则非常重要。第一是“等值条件优先放在索引前面”。WHERE merchant_id138 AND status2这两个都是等值条件而pay_time用于排序则可以放在后面的位置第二是“排序字段尽量融入索引”。如果查询中经常出现ORDER BY pay_time DESC把 pay_time 放进复合索引就不需要额外 filesort。比如前面的例子MySQL 原来用idx_merchant_status(merchant_id, status)导致ORDER BY pay_time DESC触发Using filesort。我把它改成idx_merchant_status_time(merchant_id, status, pay_time)再执行 EXPLAIN就可以看到 Extra 里不再出现Using filesortrows 也会因为更精确的索引范围而下降。有一点要注意复合索引的列顺序不能随便换。一般来说把区分度最高、最常用于等值匹配的列放前面范围查询的条件放后面。如果把范围条件的列放在前面后面的等值条件就没法有效用上索引了这就是大家常说的“范围之后列失效”问题。4.2 覆盖索引一次 SELECT 不回表覆盖索引是一个容易被忽视的优化手段。当查询的 SELECT 字段、WHERE 字段、ORDER BY 字段都落在同一个索引上时MySQL 可以直接使用索引数据返回结果不需要回表读取完整行记录。Extra 里的Using index就是它的标志。实际项目里覆盖索引也并非越宽越好。我见过有人为了让覆盖索引覆盖所有 SELECT 字段把七八个字段都塞进索引结果索引体积巨大写入成本直线上升最终得不偿失。正确用法是只针对线上最高频、最核心的查询把它们的回表成本压下去。普通查询反而没必要追求全覆盖。比如订单列表查询通常只需要order_no, user_id, total_amount, pay_time如果你把这三个字段加进现有复合索引变成(merchant_id, status, pay_time, order_no, user_id, total_amount)对这条路查询来说回表确实省了。但你要想清楚这会让索引占多少空间是否值得。4.3 索引加上去之后必须用 EXPLAIN 验证新增索引之后马上用 EXPLAIN 验证看possible_keys、key、rows、Extra是否符合预期。这一步很多人会省略结果建了索引发现查询计划压根没用等于白干。验证时一是看key列是否变成了新索引而不是 MySQL 选了另外一个二是看 rows 是否明显下降三是看 Extra 中之前出现的Using filesort、Using temporary是否消失。如果验证后发现优化器仍然不走新索引可以考虑使用FORCE INDEX临时测试确认效果但正式线上环境不建议长期使用FORCE INDEX因为统计数据变化后强制索引反而可能带来新问题。5. 索引的删除策略大胆清理冗余和失用索引5.1 冗余索引最隐蔽的性能损耗冗余索引是指一个索引能被另一个索引“覆盖”本身就是多余的存在。比如表里已经有idx_merchant_status(merchant_id, status)同时又建了idx_merchant(merchant_id)。因为idx_merchant_status的最左前缀已经包含了merchant_id所以idx_merchant在绝大多数查询中不会被单独需要它就是一个冗余索引。冗余索引的问题在于它平时“看起来有用”EXPLAIN 有时也会用上它但它带来的收益微乎其微写入维护成本和存储成本却是实打实的。之前我接手一个项目一张 2000 万行的表建了 9 个索引其中 3 个就是这种冗余关系。清理之后INSERT 和 UPDATE 的响应时间明显改善。比较稳妥的冗余检测方法是借助 MySQL 自带的 sys 库直接查询sys.schema_redundant_indexes它会列出冗余索引对。例如SELECT * FROM sys.schema_redundant_indexes;5.2 从未被使用的索引用 sys 库揪出来还有一种情况是索引既不冗余也没有被任何查询使用。这种索引纯属“废索引”。MySQL 的 performance_schema 和 sys 库提供了很好的工具SELECT * FROM sys.schema_unused_indexes;它会根据 performance_schema 的索引使用统计列出一段时间内完全没有被使用的索引。需要说明的是这个视图依赖performance_schema开启并且要在实例运行一段时间后才比较准确。新加的索引立刻出现在这个列表里是正常的等两三个业务周期后再查长期未被使用的索引才值得清理。5.3 删除索引前先用 INVISIBLE 索引观察清理索引最怕删错了导致线上慢查询。MySQL 8.0 提供了一个很贴心的功能invisible index不可见索引。你可以先把索引设置为不可见而不是直接删除让优化器暂时看不到它。观察一段时间如果业务无感再真正 DROP。ALTER TABLE order_info ALTER INDEX idx_status INVISIBLE;如果观察期间出现了慢查询说明这个索引还有价值立刻把它恢复可见即可ALTER TABLE order_info ALTER INDEX idx_status VISIBLE;这个方式特别适合在预发环境模拟、或在低峰窗口对线上索引做试探性清理比直接删索引安全得多。5.4 删除索引的时机与具体操作删除索引并不需要太复杂的命令ALTER TABLE order_info DROP INDEX idx_unnecessary;但有几个注意事项。第一删除索引会影响依赖于它的查询计划所以最好先保存当前慢查询日志和 EXPLAIN 基线方便对比。第二ALTER TABLE 在 InnoDB 中重建表可能会带来较长的锁时间建议在业务低峰期执行并优先使用在线 DDL。MySQL 5.6 及以上大部分 DROP INDEX 都是在线 DDL但仍然要留意大表带来的压力。第三一次性不要删太多索引最好是“删一个、观察一轮、再删下一个”防止出现问题时难以定位。6. 实操案例一个订单查询的完整优化过程6.1 初始状态慢查询日志里揪出了它假设我们有张订单表order_info核心字段如下字段类型说明idBIGINT UNSIGNED主键order_noVARCHAR(64)订单号唯一user_idBIGINT用户 IDmerchant_idBIGINT商户 IDstatusTINYINT订单状态1待支付2已支付3已取消total_amountDECIMAL(10,2)金额pay_timeDATETIME支付时间created_atDATETIME创建时间慢查询日志里出现了一条高频 SQLSELECT order_no, user_id, total_amount, pay_time FROM order_info WHERE merchant_id 138 AND status 2 ORDER BY pay_time DESC LIMIT 20;这条查询每分钟被调用上百次平均耗时约 1.8 秒已经严重拖慢了接口响应。6.2 EXPLAIN 定位问题索引不是没有而是不匹配先执行 EXPLAIN得到核心信息列值typerefkeyidx_merchant_statusrows15600filtered12.5%ExtraUsing filesort从结果看走了一个(merchant_id, status)复合索引但 rows 15600 说明这个商户的订单条数很多filtered 只有 12.5%意味着 15600 行里最终只匹配到不到 2000 行。更大的问题是 Extra 里有Using filesort说明pay_time DESC排序没有用到索引。问题定位清楚了现有复合索引只解决了两列等值过滤的问题但没法覆盖排序字段也没有让过滤更精准。需要根据查询重新设计索引结构。6.3 新增复合索引并验证效果将索引调整为(merchant_id, status, pay_time)这是典型的等值字段在前、排序字段在后的设计。执行ALTER TABLE order_info ADD INDEX idx_merchant_status_time (merchant_id, status, pay_time);再次 EXPLAIN结果变成了列值typerefkeyidx_merchant_status_timerows1980filtered100%Extra无 Using filesortrows 从 15600 降到 1980filtered 变成 100%Extra 里不再需要额外排序。一个索引调整把原来 1.8 秒的查询压到了 80 毫秒以内。这里补充一点ORDER BY pay_time DESC能复用索引是因为 MySQL 支持索引的逆向扫描。如果是 MySQL 5.7 及以下版本倒序扫描也能用上索引但 Mysql 8.0 还支持定义降序索引遇到复杂的多字段混合排序时可以更灵活地设计索引顺序。6.4 继续优化覆盖索引消除回表第一步解决后接口已经快了但EXPLAIN里 Extra 列依然是空说明每查到一行都要回表读一次完整行数据。对这条高频查询来说可以再进一步考虑覆盖索引。把 SELECT 需要的order_no, user_id, total_amount也放进索引形成(merchant_id, status, pay_time, order_no, user_id, total_amount)。新建后 EXPLAIN 的 Extra 变成Using index查询可以完全基于索引返回结果不再回表。不过这个案例里我并没有选择这么做因为total_amount和pay_time让索引膨胀明显而我判断当前 80 毫秒已经可以满足业务需求。覆盖索引适合在“查询命中率极高、回表成本占比大”的情况下使用属于锦上添花的步骤不是每次都值得做。6.5 顺藤摸瓜用 sys 库清理冗余和失用索引优化完这条查询后我顺手查了 sys 库SELECT * FROM sys.schema_redundant_indexes; SELECT * FROM sys.schema_unused_indexes;结果发现表里原有的idx_merchant(merchant_id)属于冗余索引因为(merchant_id, status, pay_time)的最左前缀已经覆盖它idx_status(status)则长期没有被任何查询使用因为业务查询都会带上 merchant_id 条件。清理掉这两个索引后表的写入性能有所回升。7. 常见索引失效与 EXPLAIN 排查技巧实录7.1 索引失效场景速查表日常开发里很多索引加得没问题但 SQL 写法间接让它失效了。我把常见场景整理成一个速查表场景示例后果隐式类型转换WHERE phone 13812345678phone 是 VARCHAR索引列隐式转换可能失效对索引列用函数WHERE DATE(created_at) 2024-01-01函数导致索引失效前导模糊匹配WHERE name LIKE %张无法使用该列索引OR 连接非索引列WHERE user_id 1 OR status 2可能退化为全表扫描复合索引未满足最左前缀WHERE status 2 AND pay_time ...索引是 merchant_id,status,pay_time首列未命中索引几乎失效范围条件后还有其他条件WHERE merchant_id 138 AND status 1 AND pay_time ...索引是 merchant_id,status,pay_time范围之后的条件列使用索引的能力变弱碰上这些情况先不要着急删除索引而是要确认 SQL 是否没法改写。比如隐式类型转换一段 Java 代码传了数字给字符串列调整代码参数的字符串类型索引马上就能用上。7.2 从慢查询日志到索引优化的标准路径线上调优不能靠瞎猜我的工作习惯是从慢查询日志进去找目标 SQL。开启慢查询日志的位置在 my.cnfslow_query_log ON slow_query_log_file /var/log/mysql/slow.log long_query_time 1设置完成后重启 MySQL 或在全局变量中动态开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;慢查询日志积累一段时间后用 mysqldumpslow 或 pt-query-digest 聚合出 TOP N SQL逐条 EXPLAIN 分析。把耗时高、频率高的 SQL 挑出来优先看 rows 和 Extra。这样的流程会让索引的新增和删除更有依据而不是凭感觉。7.3 几个容易踩的坑第一个坑是把rows当精确值。rows 是估算值对于 IN、OR 这类查询误差可能很大。它更适合用来对比不同执行计划的差距而不是和真实行数较真。第二个坑是忽略字符集不一致导致的关联索引失效。两张表关联字段如果一个是 utf8mb4一个是 latin1MySQL 需要对其中一个做转换索引可能依然能用但性能边界受影响。建议统一库、表、字段的字符集。第三个坑是“删索引之后查询变慢到底是不是删索引导致的”。线上环境变量很多数据量增长、统计信息更新、查询模式变化都会影响执行计划。建议在删除索引前后都做好 EXPLAIN 基线记录再配合监控而不是凭当时一次查询的感觉下结论。7.4 大型查询用 EXPLAIN ANALYZE 做最终验证MySQL 8.0.18 开始提供了EXPLAIN ANALYZE它会真实执行 SQL返回每一步的实际耗时、扫描行数和循环次数。它的输出是树形文本简单直接比如EXPLAIN ANALYZE SELECT order_no, user_id, total_amount, pay_time FROM order_info WHERE merchant_id 138 AND status 2 ORDER BY pay_time DESC LIMIT 20;执行后能看到类似这样的片段- Limit: 20 rows (actual time0.302..0.352 rows20 loops1) - Sort: pay_time DESC (actual time1.851..1.876 rows1980 loops1) - Index lookup on order_info using idx_merchant_status_time (merchant_id138, status2) (actual time0.081..0.294 rows1980 loops1)你可以清楚看到排序消耗多少时间、索引查找扫描了多少行。这比普通 EXPLAIN 的预估信息更有说服力。不过注意它会真实执行查询线上生产环境要谨慎使用遇到大查询一定要加 LIMIT避免把数据库压垮。我个人在实际操作中的体会是MySQL 的索引优化不是“加法题”而是“加减法结合”。看到一个慢查询先别急着建索引而是先用 EXPLAIN 搞清楚它慢在哪个环节。有可能是扫描行数太多有可能是排序落盘有可能是回表严重也有可能是索引根本没用上。不同病因对应不同药方一味加索引解决不了所有问题反而可能制造新问题。我习惯每季度从收获的慢查询日志里挑出 TOP SQL 做一轮 EXPLAIN 复盘同时检查 sys 库里的冗余索引和未用索引把长期不用的历史负担摘掉。这套方法不需要太复杂的工具坚持做线上查询基本能保持在一个比较稳定的水平。