17370845950

索引失效主要因函数操作、LIKE通配符前置、联合索引跳过字段、范围查询后字段失效及隐式类型转换；需用EXPLAIN验证key_len和converted_for_comparison。

WHERE 条件里对索引列用 UPPER()、DATE() 等函数会直接失效

MySQL 无法使用 B+ 树索引快速定位数据，因为索引存储的是原始列值，不是函数计算后的结果。优化器看到 WHERE UPPER(name) = 'ABC'，就只能全表扫描——哪怕 name 上建了索引。

• 常见触发函数： UPPER()、LOWER()、DATE()、YEAR()、SUBSTRING()、TRIM()、CONCAT()（左操作数为列时）
• 隐式转换也算“函数行为”：比如 WHERE create_time = '2025-01-01'，而 create_time 是 DATETIME 类型，MySQL 可能补上 CAST() 或内部转换，同样可能跳过索引
• 例外：部分 MySQL 8.0+ 支持函数索引（CREATE INDEX idx_name ON t ((UPPER(name)))），但需显式创建，且查询条件必须完全匹配该函数表达式

LIKE 以通配符开头导致索引无法做最左前缀匹配

当写成 WHERE name LIKE '%abc' 或 WHERE name LIKE '%abc%'，索引的有序性无法被利用，B+ 树没法从根节点往下高效过滤。

• 只有 LIKE 'abc%' 这种前缀匹配才走索引（前提是 name 是联合索引最左列或独立索引）
• LIKE 'ab_c'（下划线单字符）仍可走索引，因为它是确定长度的前缀
• 如果业务真要查中间匹配，考虑全文索引（FULLTEXT）或引入 Elasticsearch，别硬扛

联合索引中跳过非首字段，后续字段索引失效

假设建了联合索引 INDEX idx_user (status, city, age)，以下查询中 age 字段实际不参与索引查找：

SELECT * FROM user WHERE status = 1 AND age = 25;

因为没提供 city，索引树只能定位到 status = 1 的所有块，之后在这些块里线性扫描 age，age 部分不生效。

• 有效用法： status = 1 ✅，status = 1 AND city = 'BJ' ✅，status = 1 AND city = 'BJ' AND age > 20 ✅
• 范围查询（>、、BETWEEN）之后的字段也失效： WHERE status = 1 AND city > 'A' AND age = 25 → age 不走索引
• 等值 + 范围 + 等值，是高频误判点；执行前务必用 EXPLAIN 看 key_len 和 Extra 字段

隐式类型转换让索引“看不见”字段值

当比较的两边类型不一致，MySQL 会尝试转换——但往往把索引列转成常量类型，导致索引失效。典型例子：

SELECT * FROM order WHERE order_no = 12345;

如果 order_no 是 VARCHAR 类型，这个查询会让 MySQL 把所有 order_no 值转成数字再比，于是放弃索引。

• 字符串字段一定要用引号： WHERE order_no = '12345'
• 数字字段别拼字符串：避免 WHERE id = CONCAT('1', '23')
• 查看是否发生转换：用 EXPLAIN FORMAT=JSON 查 converted_for_comparison 字段

索引失效不是玄学，本质是优化器判断“无法用索引结构加速定位”。每次加 WHERE 条件，都值得用 EXPLAIN 看一眼——尤其是上线前和慢查优化时。最容易被忽略的是隐式转换和联合索引的字段顺序，这两处出问题，连 EXPLAIN 的 key 字段都可能显示用了索引，但 key_len 却远小于预期。