MySQL性能调优中，索引失效是最容易被误判的问题之一。查询条件明明写在索引列上，执行计划却可能显示type=ALL，导致全表扫描。这并非优化器的随机选择，而是SQL写法触碰了B+树索引的使用边界。理解这些边界，比单纯堆叠索引更能解决慢查询。
一、索引失效的常见原因与执行计划表现
B+树索引依赖列值的原始有序性进行快速定位。一旦查询条件改变了索引列的值，或者没有按照索引的最左顺序缩小范围，优化器就可能放弃索引。下面先看一个常见的订单表结构，后续示例都基于这张表展开。
CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, order_no VARCHAR(64) NOT NULL, status TINYINT NOT NULL, amount DECIMAL(10,2) NOT NULL, create_time DATETIME NOT NULL, KEY idx_user_status (user_id, status), KEY idx_create_time (create_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
第一个典型原因是对索引列使用函数。比如查询某一年创建的订单时，很多SQL会写成 YEAR(create_time) = 2024。虽然create_time上有索引，但函数作用后的结果并不是索引树中存储的原始值，MySQL无法根据原始列值的有序性定位区间，只能逐行计算函数结果，因此退化为全表扫描。正确的写法是使用范围条件，让create_time保持原始形态。
第二个常见原因是隐式类型转换。当字符串类型的order_no与数字进行比较时，如果字段本身没有使用引号，MySQL会把字符串列转换为数字，这同样破坏了索引的有序性。比如 WHERE order_no = 2024001，优化器会把order_no列的值逐个转成数字后再比较，而 WHERE order_no = '2024001' 则能正常使用索引。开发中由于参数类型不匹配，这类问题非常隐蔽。
第三个原因是LIKE前导通配。B+树索引只能根据左侧连续字符定位，如果LIKE模式以百分号开头，例如 '%2024%'，数据库不知道从哪个位置开始扫描，只能全表检查。但如果是后导通配，例如 '2024%'，索引可以定位到以2024开头的起始位置，仍然可以走范围扫描。
第四个原因是联合索引没有满足最左前缀。对于 idx_user_status(user_id, status)，单独使用status作为查询条件时，索引树的第一层排序依据是user_id，无法跳过它直接定位status，因此索引不会被使用。如果查询同时包含user_id和status，或者只包含user_id，索引可以正常工作。
其他原因还包括OR条件中部分列没有索引、使用NOT、<>、!=等否定条件、数据量过小导致优化器认为全表扫描成本更低，以及回表比例过高时优化器可能放弃二级索引。下面用一个代码块集中展示这些场景的执行计划分析思路。
-- 索引失效示例：对索引列使用函数 EXPLAIN SELECT * FROM orders WHERE YEAR(create_time) = 2024; -- 隐式类型转换：order_no为varchar，传入数字会触发列类型转换 EXPLAIN SELECT * FROM orders WHERE order_no = 2024001; -- 前导通配：无法利用B+树有序性 EXPLAIN SELECT * FROM orders WHERE order_no LIKE '%2024%'; -- 后导通配：通常可以使用索引 EXPLAIN SELECT * FROM orders WHERE order_no LIKE '2024%'; -- 违反最左前缀：联合索引idx_user_status(user_id, status)，仅用status无法定位 EXPLAIN SELECT * FROM orders WHERE status = 1;
通过这些EXPLAIN结果，可以观察type列从ref或range退化为ALL，key列变为NULL，rows列急剧增大。理解这些表现，是快速判断索引是否失效的基础。需要说明的是，并非所有type=ALL都代表存在性能问题，如果表本身就很小，或者回表代价过高，全表扫描也可能是更优选择。
二、索引设计与使用中的典型误区
很多情况下索引失效并不是SQL写错，而是索引设计从一开始就不匹配查询模式。第一个误区是盲目为每一列建立单列索引。开发初期容易把WHERE条件中出现过的字段都加上索引，却忽略了联合查询的实际情况。MySQL通常一次查询只能高效使用一个索引，如果查询涉及多个条件，单列索引只能选择其中成本最低的一个，其余条件仍然需要回表过滤，性能提升有限。
第二个误区是联合索引字段顺序随意。联合索引的字段顺序决定了索引树的分组和排序能力。应该把等值查询字段放在前面，范围查询字段放在后面，同时考虑字段区分度。例如业务中经常使用user_id和status查询订单，并且可能再加上create_time做排序，那么建立 idx_user_status_time(user_id, status, create_time) 通常比三个单列索引更有效。如果反过来把create_time放在最前面，等值条件user_id和status无法直接缩小范围，索引效果会大打折扣。
第三个误区是忽略覆盖索引的回表成本。二级索引中只保存索引列和主键值，如果查询还需要其他字段，就必须根据主键回表查询聚簇索引。当结果集很大时，回表操作带来的随机I/O可能比全表扫描更慢。覆盖索引的思路是让查询所需字段全部包含在索引中，避免回表。例如查询只需要user_id和status，那么联合索引本身就覆盖了这两个字段，执行计划Extra列会显示Using index，这是最理想的索引使用方式。
-- 不推荐：单列索引堆砌，无法满足多条件组合查询 KEY idx_user_id (user_id), KEY idx_status (status), KEY idx_create_time (create_time) -- 推荐：根据真实查询条件设计联合索引 KEY idx_user_status_time (user_id, status, create_time) -- 覆盖索引示例：查询字段都在联合索引中，避免回表 EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 1001 AND status = 1; -- 选择性评估 SELECT COUNT(DISTINCT status) / COUNT(*) AS status_selectivity FROM orders;
第四个误区是在低区分度字段上建立索引。如果某个字段的值只有两三种，例如状态字段，索引树中每个值对应的行数非常多，即使使用索引也需要扫描大量数据，优化器可能直接选择全表扫描。此时应该将该字段放到联合索引的非首位，或者与其他高区分度字段组合使用，而不是单独建索引。
第五个误区是忽视写入成本。索引并非越多越好，每次INSERT、UPDATE、DELETE操作都需要同步维护所有索引。如果更新频繁的字段上建有索引，写入性能会明显下降，而且这些索引还可能因为数据变化导致页分裂，进一步放大开销。设计索引时必须权衡查询收益与写入代价，避免为了偶尔执行的查询建立过多索引。
三、如何排查索引失效并优化查询
排查索引失效最直接的工具是EXPLAIN。执行计划中的type、key、rows和Extra字段能够揭示优化器选择的访问路径。type从优到劣依次为：system、const、eq_ref、ref、range、index、ALL。通常出现ALL或index时就要检查是否索引失效。key列为NULL表示没有可用的索引，rows表示预估扫描行数，Extra中出现Using filesort或Using temporary则需要关注排序和临时表开销。
慢查询日志是另一个重要入口。开启 slow_query_log 并设置合适的 long_query_time，可以捕获执行时间超过阈值的SQL。配合 mysqldumpslow 或 pt-query-digest 汇总慢SQL，再逐条执行EXPLAIN分析，能够高效定位问题。同时，也可以通过 SHOW INDEX FROM orders 查看表上的索引基数，判断索引选择性是否合理。
优化时首先应改写SQL，避免对索引列使用函数、运算和隐式类型转换。对于日期范围查询，使用 BETWEEN 或直接使用 >= 与 < 的形式。对于模糊查询，如果业务允许，尽量使用后导通配。对于多条件查询，优先设计符合最左前缀的联合索引，并让查询条件按联合索引字段顺序书写，尽管优化器会自动调整条件顺序，但保持一致性有助于阅读和维护。
-- 范围条件改写：避免YEAR函数 EXPLAIN SELECT * FROM orders WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2025-01-01 00:00:00'; -- 使用覆盖索引减少回表 EXPLAIN SELECT user_id, status FROM orders WHERE user_id = 1001 AND status = 1; -- 分析表统计信息，帮助优化器选择更准的执行计划 ANALYZE TABLE orders;
如果执行计划仍然不理想，可以借助 FORCE INDEX 临时验证某个索引的收益，但不要在生产环境长期依赖它，因为优化器的成本模型通常会随着数据分布变化而调整，强制指定索引可能在数据量增长后导致更差的执行计划。更稳妥的做法是定期执行 ANALYZE TABLE 更新统计信息，让优化器基于真实数据分布做出选择。
最后，索引优化不是一次性工作。随着业务增长和查询模式变化，原先合理的索引可能逐渐失效，新的查询又需要新的索引支持。建议建立慢查询监控与索引审计机制，定期复盘执行计划，删除长期未使用的索引，合并冗余索引，保持索引集合精简且与查询模式高度匹配。这样才能从根本上减少索引失效带来的性能问题。