在 MySQL 中，如果查询优化器 (Optimizer) 判断使用索引的成本高于全表扫描，或者 WHERE 子句的用法不满足特定规则，就会导致索引失效，转而执行全表扫描。

索引列进行运算、函数调用或隐式类型转换

失效原理与示例

当对索引列进行任何形式的计算、函数操作或类型转换时，会导致索引失效。索引存储的是列的原始值，对列值进行修改后，数据库无法直接匹配索引树中的键值。

• 表达式计算导致失效

  SELECT * FROM user WHERE age + 10 = 30;  -- 索引失效

• 函数调用导致失效

  SELECT * FROM t_user WHERE LENGTH(name) = 6;  -- 索引失效

• 隐式类型转换导致失效

  /* 假设 phone 为 VARCHAR 类型 */
  SELECT * FROM user WHERE phone = 18812345678;
  -- 等价于 CAST(phone AS SIGNED) = 18812345678，索引失效

违反联合索引的最左前缀法则

实现原理与查询规则

联合索引 INDEX(a, b, c) 的 B+ 树结构按列顺序构建：先按 a 排序，a 相同再按 b 排序，最后按 c 排序。查询必须从最左列开始且不能跳过中间列。

有效查询示例

-- 使用最左列 a
WHERE a = 1; 

-- 使用连续列 a, b
WHERE a = 1 AND b = 2; 

-- 使用全列 a, b, c
WHERE a = 1 AND b = 2 AND c = 3;

-- 使用 a 和 c（部分有效，仅 a 走索引）
WHERE a = 1 AND c = 3;

失效查询示例

-- 缺少最左列 a
WHERE b = 2; 

-- 缺少最左列 a
WHERE b = 2 AND c = 3; 

-- 缺少最左列 a 和 b
WHERE c = 3;

使用 LIKE 查询以通配符开头

失效原理与示例

B+ 树索引依赖值的前缀进行定位。以通配符（% 或 _）开头的模糊查询无法确定索引查找起点。

• 索引失效（通配符开头）

  SELECT * FROM user WHERE name LIKE '%name';

• 索引有效（通配符结尾）

  SELECT * FROM user WHERE name LIKE 'name%';  -- 使用索引范围扫描

在 WHERE 子句中使用 OR 连接非索引列

失效原理与示例

当 OR 条件的一侧涉及非索引列时，优化器会选择全表扫描而非索引查找，因为需要同时检查索引列和非索引列。

/* 假设 id 是主键索引，age 无索引 */
SELECT * FROM user WHERE id = 1 OR age = 18;  -- 全表扫描

优化方案

-- 改写为 UNION 查询
SELECT * FROM user WHERE id = 1
UNION ALL
SELECT * FROM user WHERE age = 18;

总结

常见的索引失效场景有哪些？

常见索引失效场景主要有以下几类：

• 条件写法问题：比如在索引列上使用函数、表达式、隐式类型转换，或者在 WHERE 子句中用 OR、NOT IN、!= 等条件连接非索引列。
• LIKE 模糊匹配问题：前缀 % 会导致索引失效，但后缀 % 仍然可以利用索引。
• 联合索引规则问题：必须满足最左前缀原则，查询必须从最左列开始且不能跳过中间列
• 数据分布问题：低选择性字段，比如 sex 字段只有 男/女，即使有索引，优化器也可能走全表扫描。