索引失效的场景

索引失效场景

数据库索引（如 MySQL 的 B+ 树索引）旨在加速查询，但在以下情况下会失效，导致全表扫描： 1. 条件中使用函数或运算：索引列被加工，无法直接匹配。 2. 隐式类型转换：查询条件与索引列类型不一致。 3. LIKE 模糊查询前缀通配：如 %abc，无法利用索引。 4. OR 条件跨非索引列：部分条件无索引。 5. 不是最左前缀匹配：复合索引未按顺序使用。 6. 查询范围过大：优化器认为全表扫描更快。 7. NULL 或 IS NULL 判断：某些情况下索引失效。

核心点

索引失效本质是优化器无法利用索引树。

1. 失效场景详解

(1) 条件中使用函数或运算

原因：
对索引列应用函数或运算（如 func(col)、col + 1），改变了原始值，索引无法直接查找。
示例：

CREATE INDEX idx_name ON user(name);
SELECT * FROM user WHERE UPPER(name) = 'JOHN'; -- 索引失效
SELECT * FROM user WHERE name + 1 = 2; -- 索引失效

解决：
避免加工索引列：

SELECT * FROM user WHERE name = 'John';

(2) 隐式类型转换

原因：
查询条件与索引列类型不匹配，触发转换，索引失效。
示例：

CREATE INDEX idx_phone ON user(phone); -- phone 是 varchar
SELECT * FROM user WHERE phone = 12345; -- 数字转字符串，失效

解决：
保持类型一致：

SELECT * FROM user WHERE phone = '12345';

(3) LIKE 模糊查询前缀通配

原因：
% 在开头（如 %abc），无法确定范围，索引失效。
示例：

CREATE INDEX idx_name ON user(name);
SELECT * FROM user WHERE name LIKE '%son'; -- 失效

解决：
后缀通配有效：

SELECT * FROM user WHERE name LIKE 'John%'; -- 有效

或用全文索引。

(4) OR 条件跨非索引列

原因：
OR 连接的条件中，若部分列无索引，优化器可能放弃索引。
示例：

CREATE INDEX idx_age ON user(age);
SELECT * FROM user WHERE age = 25 OR name = 'John'; -- name 无索引，可能失效

解决：
确保所有条件列有索引，或拆分查询。

(5) 不是最左前缀匹配

原因：
复合索引要求查询条件遵循最左原则，未满足则失效。
示例：

CREATE INDEX idx_composite ON user(age, name);
SELECT * FROM user WHERE age = 25; -- 有效
SELECT * FROM user WHERE name = 'John'; -- 失效

解决：
按索引顺序查询：

SELECT * FROM user WHERE age = 25 AND name = 'John';

(6) 查询范围过大

原因：
数据选择性低（如返回 80% 数据），优化器认为全表扫描更快。
示例：

CREATE INDEX idx_age ON user(age);
SELECT * FROM user WHERE age > 0; -- 数据量大，失效

解决：
优化条件提高选择性，或强制索引（FORCE INDEX）。

(7) NULL 或 IS NULL 判断

原因：
B+ 树索引对 NULL 处理有限，IS NULL 可能失效。
示例：

CREATE INDEX idx_name ON user(name);
SELECT * FROM user WHERE name IS NULL; -- 可能失效

解决：
避免 NULL，或用默认值替代。

2. 原理分析

B+ 树索引：
索引是按列值排序的树结构，查询需快速定位。
加工列值、类型转换等破坏了直接匹配能力。
优化器决策：
MySQL 优化器基于成本（Cost-Based Optimizer），若索引开销高于全表扫描，则放弃索引。
执行计划：
用 EXPLAIN 查看：

EXPLAIN SELECT * FROM user WHERE UPPER(name) = 'JOHN';
-- type: ALL (全表扫描)

3. 延伸与面试角度

与数据库版本：
MySQL 8.0+ 对函数索引支持改善。
实际应用：
订单查询：避免 WHERE YEAR(order_date) = 2023。
优化建议：
创建覆盖索引。
重写 SQL 避免函数。
面试点：
问“场景”时，提函数和最左原则。
问“排查”时，提 EXPLAIN。

总结

索引失效多因查询条件破坏索引树的匹配性（如函数、类型转换、最左前缀）或优化器选择全表扫描。理解 B+ 树和优化器原理是关键，解决靠调整 SQL 或索引设计。面试时，可提示例或 EXPLAIN，展示理解深度。