MySQL基础-索引失效

DataBase MySQL

发布日期: 2025-05-08

更新日期: 2025-07-01

文章字数: 806

阅读次数:

一、SQL 写法导致的失效

列上使用函数或表达式
```
WHERE UPPER(name) = 'JACK'
WHERE DATE(create_time) = '2025‑06‑18'
```
- 原因：函数或表达式会在每行数据上执行运算，无法利用原始索引结构。
前缀模糊匹配
```
WHERE username LIKE '%zhang'
```
- 原因：前置通配符让引擎无法定位索引树的起始位置，只能全表扫描。
隐式类型转换
```
WHERE user_id = '123'      -- user_id 为整型
```
- 原因：数据库会对列或常量做隐式转换，导致索引字段值“失配”，不能走索引。
OR 条件跨列或无关联
```
WHERE age = 30 OR gender = 'M'
```
- 原因：多个列上的 OR 可能导致无法复用单列索引，除非有专门的复合索引并且满足左前缀原则。
不等（<>、!=、NOT IN、NOT LIKE）条件
```
WHERE status <> 'ACTIVE'
WHERE id NOT IN (1,2,3)
```
- 原因：此类条件通常无法精准定位范围，只能全表或全分片扫描。

未遵循最左前缀原则
```
CREATE INDEX idx_ab ON t(a, b);
-- 下面的查询无法走 idx_ab
SELECT * FROM t WHERE b = 10;
```
- 只有在 SQL 中按照 (a,…) 或 (a, b) 的顺序使用条件，才能命中该索引。
范围查询后丢失后续列索引
```
WHERE a = 1 AND b > 10 AND c = 5
```
- 对 b 进行范围查询后，c 列即便有条件，也不会再走联合索引的 c 部分。
排序／分组字段与索引顺序不匹配
```
ORDER BY b, a   -- 索引是 (a, b)
GROUP BY c, a   -- 索引顺序需和 GROUP BY 一致
```
- 索引顺序要与 ORDER BY/GROUP BY 的列顺序完全一致，才能进行 “索引排序/分组” 优化。

低基数列建索引
```
CREATE INDEX idx_gender ON t(gender);
```
- 性别一般只有两三个取值（低选择性），优化效果有限，优化器可能直接选择全表扫描。
旧统计信息未更新
- 数据量变化后，如果没有及时 ANALYZE TABLE，优化器对索引选择不准确，可能误判走全表扫描。

UPDATE/DELETE 带子查询或 JOIN
```
UPDATE t1 
  SET … 
WHERE t1.id IN (SELECT id FROM t2 WHERE …);
```
- 子查询或多表关联，若没有优化得当，会失去单表索引优势。
OFFSET 大分页
```
SELECT * FROM t ORDER BY id LIMIT 1000000, 10;
```
- 虽然有索引排序，但大量偏移会累积扫描成本，表现类似全表扫描。
函数索引／虚拟列未使用
- 在某些场景下，可通过为常用表达式（如 DATE(create_time)）创建函数索引或虚拟列，再对该列建索引来规避函数失效问题。