补充-索引失效的几种情况

# 索引失效：明明建了索引，查询还是全表扫

有次线上一个列表接口很慢，同事说「这个字段有索引啊」。我让他 EXPLAIN 一下，结果 type 是 ALL，全表扫描。索引是建了，但查询写法让它用不上。这种情况太常见了，这篇把索引失效的几个典型场景列一列。

## 先确认是不是真失效

别猜，用 EXPLAIN：

```sql
EXPLAIN SELECT * FROM orders WHERE DATE(created_at) = '2026-11-07';
```

重点看 `key` 是不是 NULL。是 NULL 就说明没用索引，看 `type` 是 ALL 就更明确了。

## 场景一：对索引列做函数运算

```sql
-- 失效：对列做了函数
WHERE DATE(created_at) = '2026-11-07'

-- 有效：改成范围
WHERE created_at >= '2026-11-07' AND created_at < '2026-11-08'
```

索引存的是原始值，列一旦被函数包起来，数据库没法直接用索引里的顺序去比。同理 `substr`、`upper` 这些都会失效。

## 场景二：隐式类型转换

```sql
-- user_id 是 bigint，这里传了字符串
WHERE user_id = '12345'
```

字段是数字、条件传字符串，数据库会把列转成字符串再比，索引就废了。反过来，字符串字段传数字（比如手机号存 varchar 但传 int）也会。

这个坑最隐蔽，因为 SQL 能跑，结果也对，就是慢。排查时一定要看字段类型和参数类型是否一致。

## 场景三：前导模糊匹配

```sql
-- 失效
WHERE name LIKE '%张'

-- 有效（前缀确定）
WHERE name LIKE '张%'
```

索引是按前缀有序的，前面是通配符，没法定位起点，只能全扫。

## 场景四：最左前缀没满足

联合索引 `(a, b, c)`：

```sql
-- 有效：用到了 a
WHERE a = 1

-- 有效：用到了 a, b
WHERE a = 1 AND b = 2

-- 失效：没有 a，用不上这个联合索引
WHERE b = 2 AND c = 3
```

联合索引必须从最左列开始用。查询条件里没有最左列，整个索引就用不上。

## 场景五：范围条件之后的列失效

还是 `(a, b, c)`：

```sql
-- b 是范围条件，c 就用不上了
WHERE a = 1 AND b > 2 AND c = 3
```

范围条件后面的列，索引的有序性被打断。如果 c 是高频等值条件，考虑调整索引顺序。

## 场景六：OR 连接不同列

```sql
-- 两边的列都有索引才可能用上，否则容易全扫
WHERE a = 1 OR b = 2
```

OR 连接的列如果没有各自可用的索引，优化器干脆全表扫。可以改成 UNION，或者给两边都建索引。

## 场景七：不等于和 NOT IN

```sql
WHERE status != 'deleted'
WHERE id NOT IN (1,2,3)
```

不等于意味着要排除，范围太大，优化器往往选择全扫。如果这种查询很频繁，考虑换个建模方式，比如加一个「是否删除」的标记位只查有效数据。

## 场景八：统计信息过期

表数据变化很大，但统计信息没更新，优化器选错执行计划。

```sql
ANALYZE TABLE orders;
```

定期做，或者让数据库自动收集。

## 怎么避免

- 索引列上不做函数、不混类型
- 模糊查询尽量用前缀
- 联合索引按查询模式排最左列
- 范围条件放最后
- 用 EXPLAIN 验证，别凭感觉
- 上线前对高频 SQL 跑一遍执行计划

## 小结

索引失效不是因为没建索引，而是查询方式让优化器用不上。函数、类型转换、前导模糊、最左前缀、OR、不等于，这几个是高频原因。

记住一句话：索引是给「按原始值有序比较」用的。你对列做了加工、换了类型、模糊了前缀，它就帮不上忙了。养成 EXPLAIN 的习惯，比事后救火省心。