3-002： MySQL 中使用索引一定有效吗？如何排查索引效果？-EW帮帮网

1. 索引失效的常见原因

虽然索引可以加速查询，但在某些情况下，MySQL 可能不会使用索引，甚至使用索引反而更慢。
以下是一些常见导致索引失效的原因：

① 查询条件使用了 != 或 <>

原因：索引通常用于范围或等值查询，而 != 无法高效利用 B+ 树索引。
示例
```
SELECT * FROM users WHERE age != 30;
```
- 失效原因：!= 使 MySQL 需要扫描大量行，索引无法高效过滤。

② OR 连接多个条件，但只有部分字段有索引

示例
```
SELECT * FROM users WHERE age = 30 OR name = 'Alice';
```
- 失效原因：如果 name 字段没有索引，MySQL 可能选择 全表扫描 而不是使用 age 的索引。

优化方法：为 name 字段也添加索引，或者拆分查询：

  SELECT * FROM users WHERE age = 30
UNION
SELECT * FROM users WHERE name = 'Alice';

③ LIKE 以 % 开头

示例

SELECT * FROM users WHERE name LIKE '%Alice%';

失效原因：B+ 树索引按照前缀匹配，以 % 开头无法使用索引。

优化方法：

如果 name需要前缀匹配，可以使用

前缀索引：

CREATE INDEX idx_name ON users(name(3)); -- 仅索引前3个字符

或者改用，全文索引：

ALTER TABLE users ADD FULLTEXT(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('Alice');

④ 隐式类型转换

示例
```
SELECT * FROM users WHERE phone = 13800001234;  -- phone 是 VARCHAR 类型
```
- 失效原因：phone 是 VARCHAR，但查询时写成 INT，MySQL 进行隐式转换，导致索引失效。
- 优化方法：使用正确的数据类型：
```
   
SELECT * FROM users WHERE phone = '13800001234';
```

⑤ IS NULL 或 IS NOT NULL

示例
```
SELECT * FROM users WHERE address IS NULL;
```
- 失效原因：B+ 树索引不存储 NULL 值，查询 NULL 可能导致索引失效。
优化方法：
- 避免 NULL 值，改用默认值：
```
ALTER TABLE users MODIFY address VARCHAR(255) NOT NULL DEFAULT '';
```
- 使用 col IS NOT NULL 可能仍然走索引（视索引情况而定）。

⑥ 低选择性字段

示例
- 失效原因：gender 只有 male 和 female，选择性低，索引加速效果不明显，MySQL 可能选择全表扫描。
优化方法：
- 索引一般适用于高选择性字段，如 id、email。
- 如果 gender 需要频繁查询，可以考虑
  
  联合索引，例如：
```
CREATE INDEX idx_gender_age ON users(gender, age);
```
  这样，查询 WHERE gender = 'male' AND age > 30时仍能利用索引。

2. 如何排查索引效果？

可以使用 EXPLAIN 命令分析 SQL 是否走索引，以及索引的效率。

① 使用 EXPLAIN 分析 SQL 执行计划

EXPLAIN SELECT * FROM users WHERE age = 30;

返回示例：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	ref	idx_age	idx_age	4	const	10	Using index

type = ref：表示使用了索引。
key = idx_age：表示使用了 age 索引。
rows = 10：表示扫描了 10 行数据，索引效果较好。

如果 type = ALL，表示全表扫描，说明索引可能失效！

② SHOW INDEX FROM table_name 查看索引

SHOW INDEX FROM users;

查看表 users 上的索引信息，确认索引是否创建正确。

③ ANALYZE TABLE & OPTIMIZE TABLE

如果表数据更新较多，索引可能变得不高效，可以手动优化：

ANALYZE TABLE users;  -- 更新索引统计信息
OPTIMIZE TABLE users; -- 重建索引

总结

影响索引使用的因素	是否会导致索引失效	解决方案
`!=` / `< >`	✅ 失效	改用 `BETWEEN` 或 `IN`
`OR` 但部分字段无索引	✅ 失效	拆分查询或为所有字段加索引
`LIKE '%xxx%'`	✅ 失效	改用前缀索引或全文索引
类型转换	✅ 失效	确保查询和字段类型一致
`IS NULL`	✅ 可能失效	使用默认值替代 `NULL`
低选择性索引	✅ 可能失效	使用联合索引提高选择性
`EXPLAIN` 显示 `ALL`	✅ 失效	重新设计索引或优化 SQL

使用 EXPLAIN + SHOW INDEX + ANALYZE TABLE 等工具，可以有效排查 MySQL 索引是否生效，并进行优化。

希望这份索引优化指南对你有帮助！如果有任何疑问，欢迎继续探讨 😊🚀

3-002： MySQL 中使用索引一定有效吗？如何排查索引效果？