mysql索引失效的情况及原因

最新推荐文章于 2025-05-16 13:07:56 发布

张杨不张扬

最新推荐文章于 2025-05-16 13:07:56 发布

阅读量942

点赞数 23

CC 4.0 BY-SA版权

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/echo_zhangxu/article/details/147551663

以下是 MySQL 索引失效的 常见情况及原因，涵盖单列索引和联合索引的场景，并结合实际示例和优化建议：

一、索引失效的核心原因

索引失效的本质是 无法通过索引快速定位数据，导致需要全表扫描或部分扫描。以下是所有可能的失效场景：

二、索引失效的具体场景及原因

1. 不符合最左前缀原则（联合索引特有）

原因：联合索引 (a, b, c) 必须从最左字段 a 开始匹配，否则后续字段无法触发索引。

示例：

-- 有效：WHERE a=1 AND b=2 AND c=3
-- 失效：WHERE b=2 AND c=3（缺少最左字段 a）
-- 部分失效：WHERE a=1 AND c=3（跳过中间字段 b，c 无法走索引）

2. 范围查询（`>`、`<`、`BETWEEN`）中断后续字段

原因：范围查询后的字段无法继续使用索引（除非触发索引下推）。

示例：

-- 有效：WHERE a=1 AND b=2 AND c=3
-- 部分失效：WHERE a=1 AND b>2 AND c=3（c 无法走索引）

3. 对索引列进行运算或函数操作

原因：运算或函数会破坏索引值的原始存储结构。

示例：

-- 失效：WHERE YEAR(create_time) = 2023（对时间字段使用函数）
-- 失效：WHERE age + 10 > 30（对字段进行运算）

4. 隐式类型转换

原因：字段类型与查询值类型不匹配，导致隐式转换（等价于对字段使用函数）。

示例：

-- 失效：WHERE phone = 13800138000（phone 字段是字符串类型）
-- 有效：WHERE phone = '13800138000'

5. 使用 `OR` 连接非索引字段

原因：OR 连接的字段中有任意一个无索引，优化器会放弃索引。

示例：

-- 失效：WHERE a=1 OR unindexed_col=2（unindexed_col 无索引）
-- 有效：WHERE a=1 AND unindexed_col=2（AND 不影响索引）

6. `LIKE` 以通配符开头

原因：索引按前缀有序存储，无法反向匹配。

示例：

-- 失效：WHERE name LIKE '%John'
-- 有效：WHERE name LIKE 'John%'

7. 使用 `!=` 或 `<>` 操作符

原因：不等于操作符需要扫描大部分数据，优化器认为全表扫描更快。
示例：
```
-- 失效：WHERE age != 30
```

8. `IS NULL` 或 `IS NOT NULL` 查询

原因：数据分布影响优化器决策，大量 NULL 值时可能放弃索引。

示例：

-- 可能失效：WHERE name IS NULL（若 NULL 值占比高）

9. 数据分布不均匀（低选择性字段）

原因：索引字段重复值过多（如性别字段），优化器认为全表扫描更快。

示例：

-- 失效：WHERE gender = 'male'（gender 仅有 male/female）

10. 覆盖索引未命中

原因：查询字段包含非索引列，触发回表查询。

示例：

-- 索引 (a, b)，查询 SELECT * FROM table WHERE a=1 → 回表导致效率降低。
-- 优化：SELECT a, b FROM table WHERE a=1 → 覆盖索引高效。

11. 索引统计信息过期

原因：表数据频繁更新后，索引统计信息未及时刷新，优化器误判索引效率。
解决：执行 ANALYZE TABLE table_name 更新统计信息。

12. 强制类型转换导致索引失效

原因：显式强制转换可能破坏索引匹配。

示例：

-- 失效：WHERE CAST(id AS CHAR) = '100'（id 是整型）

13. 使用 `ORDER BY` 或 `GROUP BY` 中断索引

原因：排序或分组字段顺序与索引不一致。

示例：

-- 索引 (a, b)，失效：ORDER BY b, a

三、高级场景与优化技巧

1. 索引下推（Index Condition Pushdown, ICP）

作用：在存储引擎层直接过滤索引字段，减少回表次数。

示例：

-- 索引 (a, b)，查询 WHERE a>10 AND b=5 → 存储引擎直接过滤。

2. 松散索引扫描（Loose Index Scan）

适用场景：分组（GROUP BY）或去重（DISTINCT）时跳过部分索引键。

示例：

-- 索引 (a, b)，查询 SELECT a, MAX(b) FROM table GROUP BY a → 可能触发松散扫描。

3. 索引合并（Index Merge）

风险：多索引合并可能不如单个高效索引。

示例：

-- 同时使用索引 (a) 和 (b)，但不如直接创建联合索引 (a, b)。

四、验证索引是否生效的方法

使用 EXPLAIN 分析执行计划：

EXPLAIN SELECT * FROM table WHERE condition;

关键字段：
- type：ref、range 表示使用索引；ALL 表示全表扫描。
- key：显示实际使用的索引。
- key_len：索引使用的字节数（越长说明利用的字段越多）。
- Extra：Using index（覆盖索引）、Using where（回表过滤）。