在InnoDB存储引擎中，每张表的数据都是按照主键构造的一棵B+树来组织的，这棵树的叶子节点存放完整行记录，非叶子节点仅保存用于路由的键值和指向下层页的指针。当我们为一个列或者多个列建立二级索引时，InnoDB会再单独生成一棵B+树，其叶子节点存储的是索引键加上对应的主键值。查询时如果可以使用索引，就会从树根出发，依靠页内二分查找逐层向下，直到叶子节点拿到主键，再回表取数据。一旦某些写法破坏了这种有序查找路径，优化器便只能放弃索引。
一、InnoDB的B+树搜索基本过程
B+树是一种多路平衡查找树，InnoDB将其逻辑结构映射到称为“页”的存储单元上，默认页大小为16KB。非叶子页中记录的是“子页最小键值和页号”的目录项，叶子页之间通过双向链表串联，页内部则是一个有序数组，方便二分。当我们执行WHERE id = 100这类等值查询时，从根页开始，每一层都通过比较键值决定进入哪个子页，最终在叶子页找到目标记录，时间复杂度仅为树高的对数级别。
二级索引的搜索与此类似，区别在于其叶子节点不存整行，而是存“索引列值+主键值”。如果查询需要索引列以外的字段，就得用主键再去聚簇索引走一次查找，这被称为回表。理解了这一点就能明白：索引失效并不是说树消失了，而是优化器认为沿着树找还不如直接扫描聚簇索引叶子链表来得划算，于是选择了全表扫描。
二、常见导致索引失效的场景
1. 对索引列使用函数或运算
如果在WHERE条件中对索引字段套了函数，例如WHERE YEAR(create_time) = 2023，那么B+树中按原始时间排序的键值无法直接参与比较，优化器无法利用树的有序性，只能把每行记录的create_time取出来算一遍函数，自然就退化为全表扫描。同理，WHERE amount + 1 > 100也会让amount上的索引失效。
正确的做法是把运算放到常量一侧，或者建立函数索引（MySQL 8.0支持），让树中存储的就是函数结果。下面这段SQL就避开了失效问题：
-- 错误写法，索引失效 SELECT * FROM orders WHERE YEAR(create_time) = 2023; -- 正确写法，可以利用create_time索引范围扫描 SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
2. 隐式类型转换
当字符串类型的索引列与数字比较时，MySQL会尝试把字符串转成数字，这相当于对列使用了函数，导致索引不可用。例如phone是varchar类型且建有索引，写WHERE phone = 13800000000就会发生转换；必须写成WHERE phone = '13800000000'才能命中。
我们可以通过EXPLAIN观察type列，如果出现ALL而非ref或range，通常就是发生了这类转换。保持比较两侧类型一致，是简单但极易被忽视的要点。
3. 联合索引最左匹配被中断
联合索引(a, b, c)在B+树中先按a排序，a相同再按b，以此类推。因此查询条件必须包含最左列a才能使用该索引。如果写WHERE b = 1 AND c = 2，则完全用不上索引。部分匹配如WHERE a = 1 AND c = 2只能用到的a部分，b缺失导致c在树中无序，c条件只能过滤而非查找。
范围查询也会阻断后续列，例如WHERE a = 1 AND b > 10 AND c = 2，c在b为范围时无法保证有序，所以c不能走索引查找。设计联合索引时应把等值条件列放前面，范围列放最后。
-- 联合索引 idx_name_age_city (name, age, city) -- 可以使用name和age EXPLAIN SELECT * FROM user WHERE name = 'tom' AND age = 20; -- 只用name，city因缺age无法利用索引有序性 EXPLAIN SELECT * FROM user WHERE name = 'tom' AND city = 'bj'; -- 缺失最左列，全表扫描 EXPLAIN SELECT * FROM user WHERE age = 20 AND city = 'bj';
4. 前模糊匹配与不等操作
像LIKE '%abc'这种前模糊，因为不知道开头字符，B+树的有序前缀路径无法使用，只能全扫。而LIKE 'abc%'则可以正常走范围。不等于（!=、<>）、NOT IN、IS NOT NULL通常也会让优化器倾向全表，因为符合条件的记录可能散布在树的各处，回表成本过高。
若业务必须做前后模糊，可考虑使用倒序存储配合LIKE 'cba%'，或引入全文索引、搜索引擎来承接。
三、如何从执行计划验证失效
使用EXPLAIN是确认索引是否生效最直接的方式。重点看type列：const、ref、range一般代表用了索引；ALL代表全表。key列显示实际选用的索引，如果为NULL且表有索引，就说明被放弃了。Extra中出现Using where表示在取数据后过滤，Using index表示覆盖索引无需回表。
此外，通过SHOW WARNINGS能看到优化器重写后的SQL，有时能发现隐式转换的痕迹。养成写完复杂查询就EXPLAIN的习惯，比死记规则更可靠。
四、小结与写SQL的建议
索引失效的根本原因，是查询条件无法借助B+树键值的有序性做快速定位，迫使InnoDB退化为顺序扫描叶子链表。写SQL时记住：不在索引列上做运算和函数、保持类型一致、遵守联合索引最左原则、把范围条件放索引末尾、避免前模糊与大量不等判断。当单个索引无法满足时，可设计覆盖索引减少回表，或调整索引顺序贴合查询模式。
只有把InnoDB的B+树组织方式和优化器的成本估算逻辑结合起来看，我们才能在遇到慢查询时迅速判断是不是索引失效，并给出针对性的表结构或SQL改造方案。