MySQL索引失效与慢SQL优化实战：从EXPLAIN到联合索引设计

1. 索引失效的底层逻辑：优化器的选择困境

1.1 为什么明明建了索引，查询却还是慢

做SQL优化这几年，我见过太多开发者栽在同一道坎上：表里明明建了索引，EXPLAIN一看却是ALL全表扫描，慢查询日志里整天躺着那条“该死”的SQL。问题到底出在哪？

先说结论：索引不是建了就能用，而是优化器“愿意”用才会用。MySQL的优化器在收到一条SQL后，会基于统计信息估算各种执行计划的代价——走全表扫描要读多少页、走索引要回表多少次、条件过滤能筛掉多少行——然后选一个它认为成本最小的方案。所以索引失效，本质上不是数据库“坏了”，而是优化器通过成本计算后，认为你的索引根本不划算。

这里面最常见的两类情况：

**第一，函数操作导致索引列失去有序性。**比如WHERE DATE(create_time) = '2025-01-01'，你看着没问题，但优化器眼里DATE()已经改变了列值的原始排序，B+树里的有序结构完全用不上，只能全表扫。正确的写法应该是WHERE create_time >= '2025-01-01' AND create_time < '2025-01-02'，让索引列保持裸列状态。

**第二，隐式类型转换。**比如user_id是VARCHAR(32)，你写WHERE user_id = 10086，MySQL会把字符串列转成数字去比较，相当于对索引列做了一次隐形函数，索引失效。这种坑在联表查询里尤其隐蔽，两个表的关联字段类型不一致，两边都建了索引，结果一个能走一个不能走。

所以，当你发现索引“失效”时，第一反应不该是FORCE INDEX强行指定，而是去问：优化器为什么觉得走索引不划算？统计信息过期了？还是SQL写法本身破坏了索引的有序性？先把这个问题想清楚。

1.2 统计信息与行数估算：优化器也会“看走眼”

优化器的成本估算依赖统计信息，而统计信息不是实时更新的。InnoDB默认通过采样来估算索引的基数（Cardinality），当表数据频繁增删改时，统计信息可能严重滞后。

举个例子：某订单表有500万行数据，status字段只有3个值（0待支付、1已支付、2已取消），你在status上建了索引，然后查WHERE status = 1。优化器一看统计信息，估算出status=1占比40%，觉得回表成本太高，干脆全表扫。但实际上线上数据status=1只占5%，走索引完全更优。

这种场景下，你有两个选择：

• 执行ANALYZE TABLE 表名，强制更新统计信息，让优化器重新估算。
• 使用FORCE INDEX，人工干预执行计划。

我的建议是：优先更新统计信息。FORCE INDEX是最后的底牌，因为它会锁死执行计划，一旦数据分布再次变化，反而会造成更严重的性能问题。你需要的不是让优化器“听你的”，而是给它足够准确的信息，让它做出正确的判断。

2. 高性能索引设计：从单列到联合索引的演进

2.1 联合索引的最左前缀原则到底怎么理解

联合索引可能是面试里被问烂了，但实际开发中依然用不好的一个点。很多人背过“最左前缀原则”，但遇到具体查询就懵：(a, b, c)这个联合索引，到底哪些查询能用上？哪些不能？

我用一个最直观的方式来解释：联合索引就是按照字段顺序构建的一棵排序树。先按a排序，a相同再按b排序，b相同再按c排序。所以在等值匹配时，WHERE a = 1 AND b = 2能走索引，WHERE b = 2走不了——因为索引的全局有序性是建立在a的基础上的，跳过a直接查b，索引里根本没法二分定位。

但有几个容易被忽略的细节：

第一，最左前缀不一定从第一个字段开始才算“最左”。只要查询条件里包含联合索引的最左字段，后续字段的等值匹配都能用上。比如WHERE a = 1 AND c = 3，a能用索引定位，c用不上（因为中间隔了b），但回表次数已经被a大幅缩小了。这算是部分用到索引，而不是完全失效。

第二，ORDER BY也能利用联合索引的有序性。WHERE a = 1 ORDER BY b，这里b不需要排序，直接按索引顺序读取就行。这也解释了为什么联合索引的字段顺序设计要同时考虑查询条件和排序需求。如果你想查WHERE a = 1 ORDER BY c，那c就免不了filesort，因为b跳过了，c在索引里的顺序是“在相同b值下才有序”，跨b值的时候是乱序的。

第三，范围查询右边的字段会失效。WHERE a >= 1 AND b = 2，a的范围条件已经确定了索引的扫描区间，b的有序性在这个区间内无法保持，所以b用不上索引。这也是为什么我反复强调：联合索引设计时，等值条件放前面，范围条件放后面。

2.2 覆盖索引：让查询连回表都省了

回表是InnoDB二级索引查询不可避免的代价——先在二级索引B+树上找到主键，再拿着主键去聚簇索引里取整行数据。如果查询的列恰好都包含在索引里，优化器就能直接在二级索引上拿到所有需要的数据，这一步全省了。

这就是覆盖索引，性能提升非常可观。比如经常要查SELECT user_id, name FROM users WHERE status = 1，你建一个(status, user_id, name)的联合索引，这个查询的所有列都在索引里，Extra列会显示Using index，回表次数为零。

实践里我经常靠覆盖索引来优化高频查询，思路是：先圈定高频查询的WHERE和SELECT列，然后把这些列揉进同一个索引。数据量越大，覆盖索引带来的收益越明显。举个例子，一个千万级的订单表，统计每天订单数用的是SELECT COUNT(*) FROM orders WHERE create_time BETWEEN ...，如果create_time上有索引，COUNT(*)可以直接走索引统计行数，不需要回表取每行数据。

覆盖索引还有一个隐藏收益：二级索引通常比聚簇索引小得多，同样的数据量，扫描索引页的数量可能只有聚簇索引的几分之一，IO成本大幅降低。

2.3 索引字段的顺序如何取舍：区分度优先还是查询频率优先

设计联合索引字段顺序时，最常见的争论是：区分度高的字段放前面，还是查询频率高的字段放前面？

先说结论：区分度优先，但前提是等值匹配。在WHERE条件都是等值匹配的情况下，区分度高的字段放前面能更快缩小扫描范围。比如(gender, user_id)和(user_id, gender)，前者gender只有两个值，扫描范围缩小到一半；后者user_id直接定位到一行。

但如果是范围查询，情况就变了。前面说过，范围查询右边的字段索引会失效，所以应该把范围查询的字段往后放，前面的字段尽量用等值匹配来缩窄扫描区间。

还有一个容易忽略的点是查询频率。如果两个字段区分度接近，优先把查询频率更高的字段放前面。因为联合索引本身也能覆盖到“只查最左字段”的场景，高频字段放前面，可以让更多查询直接复用这个索引，避免额外建索引的成本。

举个综合例子。假设有一个user_orders表，高频查询是“查某个用户在某个时间段内的订单”，SQL长这样：

SELECT order_id, amount FROM user_orders WHERE user_id = 10086 AND create_time >= '2025-01-01' AND create_time < '2025-02-01';

这时候联合索引(user_id, create_time)是最优解：user_id等值命中，create_time范围命中（但注意create_time右边不能再有字段了）。如果要查的列order_id和amount也加进来，变成(user_id, create_time, order_id, amount)，还能顺便凑成覆盖索引，连回表都省了。

3. EXPLAIN实战：看懂执行计划里的潜台词

3.1 type列：从ALL到const的优化路径

EXPLAIN是SQL优化最重要的工具，没有之一。很多人会跑EXPLAIN，但只会看key列有没有值，这是远远不够的。type列才是执行效率最直观的体现，它描述了访问类型，从好到差大概是：

system > const > eq_ref > ref > range > index > ALL

• const：通过主键或唯一索引等值查询，最多返回一行，这是最优状态。
• eq_ref：联表查询时，被驱动表通过主键或唯一索引等值匹配，也是很好。
• ref：通过普通索引等值匹配，可能返回多行，多数情况下可以接受。
• range：索引范围扫描，比如BETWEEN、>、<，有索引的辅助下还算高效。
• index：全索引扫描，遍历整个索引树。比全表扫描好一点，但本质还是不理想。
• ALL：全表扫描，实力劝退。

优化的核心目标就是把ALL提升到至少range，能到ref更好，const可遇不可求（只有主键和唯一索引等值查询才能到）。

我看执行计划时的习惯是：先看type，如果出现ALL，立刻标记为优化对象；然后看key与rows，确认是否真的走了索引以及估算扫描行数；最后看Extra，判断有没有Using filesort、Using temporary这种隐藏的坑。

3.2 Extra列里最坑的两种提示

Extra列藏着很多优化器的小动作，其中有两种几乎总是性能杀手：

一是Using filesort。这不是说在磁盘上排序，而是表示MySQL需要额外执行一次排序操作，而不是直接利用索引的有序性。比如WHERE status = 1 ORDER BY create_time DESC，如果联合索引是(status, create_time)，排序用不上，因为索引里create_time是按升序排列的，但你要降序——这里又涉及一个MySQL 8.0的改进，8.0之后支持降序索引，可以真正物理存储降序排列，解决这类问题。

二是Using temporary。表示查询使用了临时表，常见于GROUP BY、DISTINCT、子查询等场景。临时表的内存版本叫MEMORY，数据量一大就会落到磁盘临时表，性能断崖式下跌。优化方向通常是改写SQL或调整索引，让分组和去重操作能直接利用索引的有序性。

顺便说一句：判断Using filesort要不要优化，得看数据量。几千行的小表排个序也就几个毫秒，没必要为了消除filesort大动干戈加索引。但百万行以上的表，filesort就非常致命了，必须想办法用索引顺序替代。

3.3 如何用EXPLAIN对比索引方案

实际工作中，我经常需要对比不同索引方案的效果。做法很简单：同一个SQL，分别建不同索引，跑EXPLAIN对比rows估算值。虽然rows是估算的，但用来横向对比索引方案，参考价值很高。

比如上面那个订单统计的例子：

EXPLAIN SELECT COUNT(*) FROM orders WHERE create_time BETWEEN '2025-01-01' AND '2025-01-31';

方案A是仅create_time单列索引，方案B是(create_time, status)联合索引。多数情况下方案B的rows会比方案A少，因为联合索引覆盖了更多可能用到的条件。如果还能改成覆盖索引，Extra列出现Using index，那基本就是接近最优了。

这里要注意一点：对比rows时也要看filtered列。filtered表示满足条件的行数占比估算，如果rows = 10000但filtered = 1，说明实际命中的只有100行，优化器可能低估了索引的过滤能力。

4. 典型慢SQL优化实战：从定位到上线

4.1 定位慢SQL：慢查询日志和性能分析工具怎么配合

优化慢SQL的第一步是找到它们。MySQL开启慢查询日志是基础操作：

SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; -- 超过1秒的SQL都记下来 SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

线上环境建议把long_query_time设成1秒甚至0.5秒，太大会漏掉很多“潜在的慢查询”——有些SQL平均执行200毫秒，但调用频率极高，累积的资源消耗比偶发的2秒查询更可怕。

拿到慢查询日志后，我先用mysqldumpslow工具做聚合统计，看看哪些SQL是“又慢又频繁”的：

mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

-s at按平均执行时间排序，-t 10取前10条。聚合出来的SQL往往是优化的高优先级对象。有了具体SQL之后，再针对单条做EXPLAIN和EXPLAIN ANALYZE（MySQL 8.0+，能给出实际执行时间）。

4.2 一个OR条件引发的血案：从全表扫描到索引命中

分享一个我实战中遇到的案例。有个商品表products，规模约300万行，线上有一条查询经常超过3秒：

SELECT id, name, price, stock FROM products WHERE brand_id = 101 OR category_id = 88 ORDER BY sales_volume DESC LIMIT 20;

brand_id和category_id分别建了索引，但type显示ALL。原因很经典：OR条件使优化器难以同时利用两个独立索引，除非用UNION拆开或者INDEX MERGE优化生效，否则它倾向于全表扫描。

我的优化方案是把SQL拆成两个查询后合并：

SELECT id, name, price, stock FROM products WHERE brand_id = 101 UNION SELECT id, name, price, stock FROM products WHERE category_id = 88 ORDER BY sales_volume DESC LIMIT 20;

改写后两个子查询分别命中brand_id和category_id索引，type从ALL变成了ref，单条查询耗时从3.2秒降到0.4秒。这个改动上线后，接口P95延迟直接降了一个数量级。

不过这里有个小细节：改写成UNION后，ORDER BY和LIMIT要放在最后一个查询才生效，如果放在第一个子查询里，排序只会作用于第一个分支，结果就错了。

4.3 分页深翻页的优化：延迟关联到底强在哪

分页查询是另一个高频翻车现场。LIMIT 200000, 20这种深分页，即使走了索引，前200000条数据都得扫描后丢弃，效率极其低下。我在运营后台的系统里经常处理这种需求，标准的优化手段是延迟关联：

-- 原始写法（慢） SELECT id, name, price FROM products ORDER BY id LIMIT 200000, 20; -- 延迟关联写法（快） SELECT p.id, p.name, p.price FROM products p INNER JOIN ( SELECT id FROM products ORDER BY id LIMIT 200000, 20 ) t ON p.id = t.id;

思路很简单：先用覆盖索引在主键上定位到第200001到200020条的id，这一步扫描的是紧凑的索引页，不回表；然后再用这些id回到聚簇索引取完整行数据。相比原始写法每扫一条都要回表一次，性能提升是数量级的。

类似的场景还有“基于游标的分页”：WHERE id > 上一页最大id ORDER BY id LIMIT 20。如果业务允许，这种方案比LIMIT深翻页更优雅，但需要前端配合改造。

4.4 优化上线前，一定要做的三件事

改完SQL和索引，不要急着上线。我踩过的坑告诉我，至少要过三关：

第一关：EXPLAIN验证执行计划。确认type明显改善，key列是预期索引，rows估算合理，Extra没有filesort和temporary。

第二关：压测环境测试。拿生产数据脱敏后恢复到预发环境，模拟线上真实流量跑一遍。注意观察锁等待和IO情况——有些优化虽然单个查询快了，但可能引入更频繁的锁冲突，整体吞吐未必提升。

第三关：灰度上线。先放5%~10%的流量观察，对比优化前后的慢查询数量和接口延迟。如果出现性能回退，立即回滚索引或SQL版本。

5. 索引维护与常见失效场景排查

5.1 索引下推：被低估的优化利器

MySQL 5.6引入的索引下推（Index Condition Pushdown, ICP），很多人不知道，但它对联合索引的查询效率提升非常明显。

原理一句话：在存储引擎层遍历索引时，直接把WHERE条件中能被索引列覆盖的部分下推到存储引擎进行过滤，减少回表次数。举个例子，联合索引(age, city)，查询WHERE age > 20 AND city = '上海'——age是范围查询，city本来到不了索引层面过滤，但有了ICP，存储引擎在遍历索引时就会用city = '上海'过滤掉不符合条件的记录，只有真正满足条件的才回表。

判断ICP是否生效，看EXPLAIN的Extra列有没有Using index condition。触发ICP有几个条件：索引包含相关列、存储引擎支持该特性（InnoDB默认支持）、查询不是覆盖索引（如果是覆盖索引，本来就无需回表，ICP意义不大）。

5.2 一张表建立多少个索引合适

索引不是越多越好。每多一个索引，意味着插入、更新、删除时都要多维护一棵B+树，写入性能直接受损。磁盘空间也从“几乎不用考虑”变成了“真金白银的成本”。

我个人的经验规则：

• 单表索引数量：5个以内是比较健康的，超过8个就要反思是否过度索引。
• 单索引字段数：3~4个以内，再多意义就不大了，而且会显著增加索引体积。
• 高频写表：索引数量要更克制，优先保证写入吞吐。

有个很典型的反面案例：运营后台为了“响应各种筛选条件”，在十几列上各建了一个单列索引，结果写接口从50ms涨到800ms。最后我把所有单列索引删除，设计了两个联合索引覆盖主要查询场景，写入恢复，查询也没降速——因为大部分查询本来就是组合条件，单列索引本来就不该被用上。

5.3 MySQL 8.0新增的索引能力，值得升级吗

如果你还在用MySQL 5.7，考虑升级到8.0时，索引相关的改进有几个值得关注的亮点：

第一个是降序索引。5.7里ORDER BY a DESC, b ASC这种混合排序经常导致filesort，8.0可以在索引定义时指定每个字段的排序方向，让索引顺序和查询排序完全匹配。

第二个是隐藏索引（Invisible Index）。可以把索引设为INVISIBLE，优化器会忽略它，但索引依然在维护。这给索引下线提供了很好的过渡手段——先在测试环境把目标索引隐藏，观察慢查询是否有变化，确认不依赖后再真正删除，避免误删索引导致线上事故。

第三个是索引跳过扫描（Skip Scan）。当联合索引的最左列是低区分度字段、且查询条件跳过它时，优化器可以自动扫描所有不同的最左值来“模拟”索引查找，部分缓解了最左前缀的局限性。不过这个特性有前提条件，效果因数据分布而异，不要期望太高。

5.4 索引失效场景速查表

结合我日常排查的经验，整理一份高频失效场景清单，遇到问题可以直接对照：

表格里说的“通常失效”不一定绝对，有些场景取决于优化器的成本判断和数据分布。比如OR如果两个条件都覆盖同一个索引，也可能走INDEX MERGE。遇到具体问题，还是以EXPLAIN的结果为准。

5.5 在线DDL与索引维护的注意事项

生产环境加索引，最怕的是锁表导致业务中断。MySQL 8.0的INPLACE算法支持在大部分场景下在线加索引，但有几个细节要留意：

• 在大表上加索引，不管是不是INPLACE，都会产生额外的磁盘IO和主从复制延迟。凌晨低峰期操作是标配。
• 如果表有外键约束，部分DDL还是会退化为COPY，锁表时间不可控。
• ALGORITHM=INPLACE不是万能药，它只会减少锁的粒度，不会消除对写入的短暂阻塞。

我曾经在一张5亿行的流水表上加过索引，当时预估耗时2小时，我以为可以摸鱼了，结果半小时后从库延迟飙到10分钟，业务报警。后来我学乖了：先用pt-online-schema-change这类工具在从库演练，再在低峰期分批操作，同时监控主从延迟。大表DDL，永远要当成一次小型的运维变更来做，而不是“执行一条SQL就完事”。

另外，索引维护不只是加索引和删索引，还包括定期更新统计信息。如果表的增删改很频繁，可以设置innodb_stats_auto_recalc自动重算，或者周期性执行ANALYZE TABLE。否则就会回到第一章说的：统计信息失真，优化器做出不明智的选择。

6. 独家经验：我在SQL优化中用过的三板斧

优化SQL这件事，说难也难，说简单也简单。这些年我沉淀了一套自己的排查套路，每次遇到性能问题，基本按这个顺序走：

第一板斧：先看执行计划，不看代码。不管业务逻辑写得再复杂，最终落到数据库上就是一条SQL的执行计划。先EXPLAIN，把type、key、rows、Extra看明白，问题基本就定位了一半。

第二板斧：揪出回表和排序。90%的慢查询都能归因到两个操作：回表太多、排序太慢。回表多就考虑覆盖索引；排序慢就考虑联合索引的有序性。这两个点优化到位，大部分SQL都能跑回百毫秒以内。

第三板斧：验证，验证，再验证。用生产数据量级做压测，用EXPLAIN ANALYZE看实际执行代价，灰度上线后对比监控指标。没有验证过的索引设计，都是纸面优化。

最后再分享一个小技巧：优化完别忘了清理冗余索引。有时候为了覆盖某个新场景，你加了个索引，旧的索引可能就不再被任何查询利用了。用sys.schema_unused_indexes视图可以查出来哪些索引从未被使用过，定期清理这些“僵尸索引”，既能省空间，还能提升写入性能。我每次上线索引调整后，都会顺手查一下这个视图，清理效果非常显著。

索引优化说到底就是一场“用空间换时间”的平衡艺术。它不是堆索引的数量，而是精准匹配查询模式，让每一棵B+树都被用在刀刃上。希望这篇实战经验能帮你少踩几个坑。