MySQL 8.0 索引失效通常是因为查询条件导致优化器无法利用 B+ 树有序性，常见于对索引列做函数运算或隐式类型转换。避免方法包括保持索引列“裸露”、确保类型一致及使用 EXPLAIN 验证执行计划。

命令速用版

使用 EXPLAIN 分析 SQL 执行计划，这是判断索引是否生效的最直接手段。

EXPLAIN SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

重点关注输出结果中的 key 字段（实际使用的索引）和 type 字段（访问类型）。

为什么会这样

索引失效的根本原因是查询条件破坏了 B+ 树索引的有序性或导致类型不匹配。

MySQL 的 B+ 树索引存储的是列的原始值。当对索引列进行函数计算（如 YEAR(create_time)）或隐式类型转换（如字符串字段比较数字）时，数据库无法直接利用索引树进行定位，必须逐行计算或转换后再比较，这等价于全表扫描。此外，优化器基于成本模型决策，当预计回表开销过大或数据区分度低时，也可能主动放弃索引。

分步处理

针对高频失效场景，按以下步骤调整 SQL 写法。

1. 移除索引列上的函数或表达式

避免在 WHERE 条件左侧对索引列使用函数。将计算移到条件右侧或使用范围查询。

错误写法：WHERE YEAR(create_time) = 2023

正确写法：WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'

MySQL 8.0 支持函数索引，若必须使用函数，可创建表达式索引：CREATE INDEX idx_year ON orders ((YEAR(create_time)));

2. 消除隐式类型转换

确保查询值的类型与字段定义完全一致。字符串字段查询值必须加引号，数字字段不加引号。

错误写法：WHERE phone = 13800138000（phone 为 VARCHAR）

正确写法：WHERE phone = '13800138000'

3. 遵循联合索引最左前缀原则

复合索引 (a, b, c) 必须从最左列开始匹配。跳过中间列或颠倒顺序会导致后续列索引失效。

有效：WHERE a = 1 AND b = 2

失效：WHERE b = 2 AND c = 3（跳过 a）

4. 优化 LIKE 查询

避免通配符 % 放在开头。前缀匹配可以利用索引，后缀匹配无法利用。

有效：WHERE name LIKE 'Tom%'

失效：WHERE name LIKE '%Tom'

5. 谨慎使用 OR 和不等值查询

OR 连接的条件中，若有一列无索引，可能导致全表扫描。建议拆分查询或使用 UNION。!= 或 <> 操作符通常会导致索引失效，除非数据区分度极高。

怎么验证是否生效

执行 EXPLAIN 后，检查以下字段确认优化结果。

key 字段：必须显示具体的索引名称，若为 NULL 表示未使用索引。

type 字段：最优值为 ref 或 range，若显示 ALL 表示进行了全表扫描。

Extra 字段：若出现 Using filesort 或 Using temporary，表示发生了额外的排序或临时表操作，需进一步优化。

常见坑

1. 数据量过小不走索引

当表数据量很少时，优化器可能认为全表扫描成本更低，此时索引失效是正常优化行为。

2. NULL 值处理

IS NULL 或 IS NOT NULL 在某些版本或数据分布下可能不使用索引，建议字段设计时添加 NOT NULL 约束。

3. 范围查询后的列失效

在联合索引中，一旦某列使用了范围查询（> 或 <），其右侧的列将无法使用索引进行查找。

常见问题

MySQL 8.0 的函数索引怎么用？

MySQL 8.0 支持创建表达式索引，语法为 CREATE INDEX idx_name ON table ((expression));，适用于必须对列进行函数处理的场景。

OR 条件一定会导致索引失效吗？

不一定，如果 OR 连接的所有列都有独立索引，优化器可能使用 index merge，但通常建议改写为 UNION 以保证稳定性。

为什么加了索引 EXPLAIN 还是显示 NULL？

可能是发生了隐式类型转换、违反了最左前缀原则，或者优化器评估后认为全表扫描效率更高。

参考来源

• 51CTO 博客 - 深入分析 MySQL 索引失效的十种常见原因与解决方案
• CSDN 博客 - MySQL 性能优化实战索引失效的八大场景与解决方案全解析
• 知乎 - MySQL 索引失效十大场景分析与优化实践
• 技术博客 - MySQL 索引真的“失效”了吗？8 大常见场景深度解析