外观
索引失效的场景？
⭐ 题目日期：
京东 - 2024/12/26，小米 - 2024/12/26
📝 题解：
索引失效的场景通常涉及查询方式或数据特性导致数据库优化器无法有效利用索引，常见情况如下：
1. 对索引列使用函数或表达式
- 场景：在WHERE条件中对索引列应用函数或运算（如
YEAR(column) = 2023或column + 1 = 10）。 - 原因：索引存储的是原始值，运算或函数处理后无法直接匹配索引结构。
- 解决：将操作移至条件另一侧，例如
column = 2023 - 1。
2. 隐式类型转换
- 场景：查询条件与索引列类型不匹配（如字段为字符串类型，但使用数字值
WHERE column = 123）。 - 原因：数据库需隐式转换类型，导致索引无法直接匹配。
- 解决：确保类型一致，例如
WHERE column = '123'。
3. 模糊查询以通配符开头
- 场景：使用
LIKE '%abc'或LIKE '%abc%'。 - 原因：索引按前缀有序存储，无法定位中间或后缀的模糊匹配。
- 例外：
LIKE 'abc%'可利用索引。
4. OR 条件中的非索引列
- 场景：OR 连接的条件中有列未建立索引（如
WHERE a=1 OR b=2，仅a有索引）。 - 原因：优化器可能选择全表扫描而非索引合并。
- 例外：若所有OR条件列均有索引，可能触发索引合并（Index Merge）。
5. 联合索引未遵循最左前缀
- 场景：联合索引为
(col1, col2, col3)，但查询未包含最左列（如WHERE col2=2）。 - 原因：索引按最左列有序存储，跳过后无法有效检索。
- 例外：若查询包含最左列（如
WHERE col1=1 AND col3=3），可能部分使用索引。
6. 使用不等号或范围查询
- 场景：
!=、<>、NOT IN或范围查询（如WHERE age > 30）。 - 原因：不等操作需扫描大部分数据，可能全表扫描更快。
- 例外：覆盖索引或高选择性查询可能仍使用索引。
7. 数据量过小
- 场景：表数据量极小时（如几十行）。
- 原因：全表扫描成本低于索引查询的I/O开销。
8. 统计信息不准确
- 场景：索引统计信息未及时更新（如大表频繁增删后）。
- 原因：优化器误判索引效率，选择全表扫描。
- 解决：手动更新统计信息（如
ANALYZE TABLE）。
9. IS NULL / IS NOT NULL
- 场景：
WHERE column IS NULL（若索引未记录NULL值）。 - 原因：部分数据库索引不存储NULL值，需全表扫描。
10. JOIN条件类型不匹配
- 场景：多表关联字段类型不一致（如
INT与VARCHAR）。 - 原因：隐式类型转换导致索引失效，类似单表查询问题。
11. 查询返回大部分数据
- 场景：查询覆盖表中大部分记录（如超过30%）。
- 原因：优化器认为全表扫描比多次索引回表更高效。
总结
索引失效的核心原因是索引结构无法直接匹配查询条件或操作。优化时需注意：
- 避免对索引列进行运算或函数处理。
- 确保查询条件与索引列类型一致。
- 合理设计联合索引，遵循最左前缀原则。
- 定期更新统计信息，帮助优化器准确决策。
不同数据库（如MySQL、Oracle）的实现细节可能略有差异，建议结合执行计划（EXPLAIN）具体分析。