mysql索引失效的常见原因与解决方法

mysql索引失效的五大场景：①WHERE中对索引列使用函数/表达式；②LIKE以通配符开头；③隐式类型转换；④联合索引未遵循最左前缀原则；⑤索引过多或结构不匹配查询模式，需结合EXPLaiN验证。

WHERE 条件中对索引列使用函数或表达式

MySQL 无法使用索引进行快速查找，只要在 WHERE 子句里对索引列做了计算、函数调用或类型转换，优化器就会放弃走索引。比如 WHERE YEAR(create_time) = 2023 或 WHERE price + 10 > 100，哪怕 create_time 和 price 都建了索引，也会全表扫描。

• 改写为范围查询：WHERE create_time >= '2023-01-01' AND create_time
• 避免在索引列上做运算，把计算移到等号右边（如 WHERE price > 90 而非 WHERE price + 10 > 100）
• 如果必须用函数，可考虑生成列（generated column）+ 索引：

  ALTER TABLE orders ADD COLUMN year_create INT AS (YEAR(create_time)) STOred;
  CREATE INDEX idx_year_create ON orders(year_create);

LIKE 查询以通配符开头

LIKE '%abc' 或 LIKE '%abc%' 会导致索引失效，因为 B+ 树索引是按字典序排序的，前缀不固定就无法定位起始位置。

• 能用 LIKE 'abc%' 就绝不用 LIKE '%abc'
• 全文检索场景改用 FULLTEXT 索引 + MATCH ... AGAINST
• 模糊匹配需求强且数据量大时，考虑外部方案（elasticsearch、redisearch）

隐式类型转换导致索引失效

当索引列是字符串类型（如 VARCHAR），而 WHERE 中传入的是数字（WHERE mobile = 13812345678），MySQL 会自动把字段转成数字比较，触发全表扫描。同理，字符集不一致（如 utf8mb4 vs utf8）也可能触发隐式转换。

• 始终保证查询值与字段类型一致：WHERE mobile = '13812345678'
• 用 EXPLAIN 检查 type 是否为 ALL，并观察 Extra 列是否出现 using where; Using index 或 Using filesort
• 查看实际执行时的字符集和校对规则：

  SHOW CREATE TABLE users;

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

联合索引 (a, b, c) 只对 a、a,b、a,b,c 有效；单独查 b 或 b,c 不走索引；a,c 虽然含最左列，但跳过了 b，c 也无法命中索引（除非 index condition pushdown 生效，但仅用于过滤，不改变扫描范围）。

• WHERE 条件尽量从左到右连续使用索引列
• 高频独立查询字段，不要盲目塞进联合索引，宁可单列索引
• 必要时拆分或调整顺序：比如 WHERE a = ? AND c = ? 频繁出现，可考虑索引改为 (a, c, b) 或新增 (a, c)

索引不是越多越好，也不是建了就一定生效。真正决定是否走索引的，是查询条件如何书写、数据类型是否严格匹配、以及联合索引的结构是否贴合访问模式。每次加索引前，先用 EXPLAIN 看一眼，比凭经验猜要可靠得多。