在 MySQL 中，索引的使用可以大大提升查询性能，但在某些情况下，索引可能会失效，导致 MySQL 无法利用索引，转而进行全表扫描或其他低效操作。以下是索引失效的常见情况：
1. 查询条件导致索引失效
-
条件中使用了函数：
- 如果查询条件中的列被函数包裹，MySQL 无法利用索引。
- 示例：
SELECT * FROM users WHERE YEAR(created_at) = 2024; -- 索引失效 - 优化： 改为不使用函数，直接查询：
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-
条件中使用隐式类型转换：
- 如果列的类型和查询条件的类型不一致，MySQL 会进行隐式类型转换，导致索引失效。
- 示例：
SELECT * FROM users WHERE phone_number = 1234567890; -- phone_number 为字符串类型 - 优化： 确保查询条件与列的类型一致：
SELECT * FROM users WHERE phone_number = '1234567890';
-
使用不等于操作符：
- 查询中使用
!=或<>，MySQL 通常无法利用索引。 - 示例：
SELECT * FROM users WHERE age != 30; -- 索引失效
- 查询中使用
-
使用
OR条件且未对所有字段加索引：-
如果查询条件中包含
OR，并且OR两侧的列未同时加索引，MySQL 无法使用索引。 -
示例：
SELECT * FROM users WHERE name = 'Alice' OR age = 30; -- name 有索引，但 age 没有索引 -
优化： 确保
OR的所有列都加索引，或改写为UNION：SELECT * FROM users WHERE name = 'Alice' UNION SELECT * FROM users WHERE age =30;
-
2. 索引设计或使用不当导致失效
-
查询未使用索引的最左前缀原则：
-
MySQL 索引遵循 最左前缀匹配 原则。如果查询未使用索引的前缀部分，索引会失效。
-
示例：
CREATE INDEX idx_name_age ON users (name, age); SELECT * FROM users WHERE age =30; -- 索引失效 -
优化： 查询必须包含索引的最左前缀部分：
SELECT * FROM users WHERE name = 'Alice' AND age = 30;
-
-
使用通配符
%开头的 LIKE 查询：-
如果
LIKE查询的模式以%开头，MySQL 无法使用索引。 -
示例：
SELECT * FROM users WHERE name LIKE '%Alice'; -- 索引失效 -
优化： 改为非
%开头的查询：SELECT * FROM users WHERE name LIKE 'Alice%';
-
-
索引列参与计算或表达式：
-
如果查询条件中的索引列被用作计算或表达式的一部分，索引失效。
-
示例：
SELECT * FROM users WHERE age + 1 = 30; -- 索引失效 -
优化： 将计算挪到条件外：
SELECT * FROM users WHERE age = 29;
-
3. 数据特性导致索引失效
-
查询结果的记录过多（选择性低）：
- 当查询的条件匹配大部分数据时，MySQL 可能会放弃使用索引，转而选择全表扫描。
- 示例：
SELECT * FROM users WHERE gender = 'male'; -- 假设性别列的值分布不均
-
统计信息不准确：
- 如果表的数据变化频繁，索引的统计信息可能不准确，MySQL 会错误选择不使用索引。
- 解决： 手动更新索引统计信息：
ANALYZE TABLE users;
4. 查询优化器的行为
-
小表扫描优先：
- 如果表数据量较小，MySQL 优化器可能会选择全表扫描，而不是使用索引。
-
组合索引中范围条件的限制：
-
当组合索引中包含范围条件（
>、<、BETWEEN），后续的列索引无法被使用。 -
示例：
CREATE INDEX idx_name_age_salary ON users (name, age, salary); SELECT * FROMusers WHERE name = 'Alice' AND age > 30 AND salary = 5000; -- salary 索引失效 -
优化： 根据查询需求调整索引顺序。
-
5. 特殊情况
-
使用
IS NULL或IS NOT NULL：- 某些版本的 MySQL 中，
IS NULL或IS NOT NULL查询可能无法使用索引。
- 某些版本的 MySQL 中，
-
未使用支持索引的存储引擎：
- 例如 MySQL 的
MEMORY引擎不支持TEXT或BLOB字段上的索引。
- 例如 MySQL 的
总结
索引失效的原因总结：
- 查询中使用了函数、表达式或隐式类型转换。
- 未遵循最左前缀原则，或组合索引的范围查询限制了后续字段的索引使用。
- 使用
%开头的LIKE查询或OR查询未优化。 - 数据分布特性或查询优化器的选择导致索引被放弃。
解决方案：
- 了解查询条件的特性，设计合适的索引。
- 避免索引失效的常见操作，优化 SQL 查询。
- 定期更新表统计信息，确保查询优化器正确选择索引。