一、最常见：对索引列做运算 / 函数

❌ 1. 对索引列使用函数

SELECT * FROM user WHERE YEAR(create_time) = 2024;

原因：

数据库需要对每一行先算 YEAR()，无法走 B+Tree 有序结构。

✅ 正确写法：

SELECT * FROM user 
WHERE create_time >= '2024-01-01' 
AND create_time < '2025-01-01';

❌ 2. 对索引列做计算

SELECT * FROM user WHERE age + 1 = 20;

等价于：

age = 19

但因为对列做了运算，索引失效。

二、隐式类型转换

❌ 字符串列用数字查询

-- phone 是 varchar
SELECT * FROM user WHERE phone = 13800000000;

数据库会对 phone 做隐式转换，索引失效。

✅ 应写成：

WHERE phone = '13800000000'

三、LIKE 使用不当

❌ 左模糊查询

WHERE name LIKE '%张'

原理：

B+Tree 是按前缀排序的，前面不确定，无法定位范围。

✅ 可以使用索引

WHERE name LIKE '张%'

因为是前缀匹配。

四、OR 使用不当

WHERE name = '张三' OR age = 18

如果：

• name 有索引
• age 没索引

👉 可能全表扫描

解决方法：

• 两边都加索引
• 或改成 UNION

五、违反最左前缀原则（联合索引）

假设有联合索引：

index(a, b, c)

❌ 跳过最左列

WHERE b = 10

索引失效。

❌ 中间断裂

WHERE a = 1 AND c = 3

只能用到 a，c 用不上。

⚠️ 遇到范围查询会停止匹配

WHERE a = 1 AND b > 2 AND c = 3

用到：

• a
• b

但 c 用不到。

因为范围查询会截断索引匹配。

六、!= 或 <> 不等于

WHERE age != 20

MySQL 可能放弃索引，因为选择性太差。

七、IS NULL / IS NOT NULL

• IS NULL 通常可以走索引
• IS NOT NULL 可能失效（取决于数据分布）

八、数据量太少 / 优化器选择放弃

即使有索引，也可能不用。

例如：

WHERE gender = '男'

如果 90% 都是男：

👉 选择性太差
👉 优化器直接全表扫描更快

九、索引列在表达式右边

WHERE 10 = age

通常还能用索引，但某些版本优化不好。

十、索引列参与字符串拼接

WHERE CONCAT(first_name, last_name) = '张三'

必定失效。

十一、使用 NOT IN

WHERE id NOT IN (1,2,3)

可能不走索引。

十二、覆盖索引 vs 回表

有时候你以为索引失效，其实：

• 索引用了
• 但需要回表
• 性能依然慢

十三、统计信息过期

MySQL 依赖统计信息决定是否使用索引。

统计失真：

ANALYZE TABLE user;

总结一张脑图逻辑

索引失效本质原因只有三类：

1️⃣ 破坏了“有序性”

• 函数
• 计算
• 左模糊

2️⃣ 优化器觉得不划算

• 选择性太低
• 数据量小

3️⃣ 违反 B+Tree 使用规则

• 最左前缀
• 范围截断

给你一个实战建议

判断是否失效，不要猜：

EXPLAIN SELECT ...

看：

• type 是否为 ALL（全表扫描）
• key 是否为 NULL
• rows 扫描行数