有索引却用不上的场景有哪些

在 Oracle 中，即使存在索引，优化器也可能因各种原因选择不使用索引。以下是常见的索引未被使用的场景及原因分析：

一、数据分布导致索引失效

  1. 索引列数据高度重复（低选择性）
    • 原因：若索引列的唯一值很少（如性别、状态等枚举列），索引扫描的成本可能高于全表扫描（Full Table Scan, FTS）。
    • 示例：表中有 100 万行数据，某列 status 仅有 'VALID' 和 'INVALID' 两个值，此时索引扫描需要频繁回表，效率可能低于全表扫描。
    • 优化器行为：优化器通过统计信息判断，当索引扫描的成本（Cost）高于全表扫描时，会放弃索引。
  2. 表数据量极小
    • 原因：对于小表（如几千行以内），全表扫描的 I/O 次数可能比索引扫描更少，优化器倾向于直接全表扫描。
    • 示例：仅有 100 行的表，全表扫描只需读取几个数据块，比通过索引定位数据更高效。

二、查询条件导致索引无法使用

  1. 索引列上使用函数或表达式
    • 原因：若在索引列上使用函数（如 UPPER(col)、TO_CHAR(col)）或表达式（如 col + 1），索引键与查询条件不匹配，无法直接使用索引。
    • 错误示例： 
      -- 索引在 col 列，但查询中使用了函数，索引失效
SELECT * FROM table WHERE UPPER(col) = 'ABC';
    • 正确做法：创建基于函数的索引（如 CREATE INDEX idx_func ON table(UPPER(col))）。
  2. 隐式数据类型转换
    • 原因：当查询条件的类型与索引列类型不匹配时，Oracle 会隐式转换数据类型，导致索引失效。
    • 示例： 
      -- col 为 NUMBER 类型，但查询中传入字符串，触发隐式转换
SELECT * FROM table WHERE col = '123'; 
-- 等价于 SELECT * FROM table WHERE TO_NUMBER(col) = 123（索引失效）
    • 优化：确保查询条件的数据类型与索引列一致（如使用 123 而非 '123'）。
  3. 查询条件包含 OR 且部分列无索引
    • 原因：若 OR 连接的列中，部分列没有索引或索引效率低，优化器可能放弃索引，选择全表扫描。
    • 示例： 
      -- col1 有索引，col2 无索引，OR 导致索引失效
SELECT * FROM table WHERE col1 = 1 OR col2 = 'A';
    • 优化：为 col2 添加索引，或使用 UNION ALL 替代 OR（需注意重复数据）。
  4. 复合索引未满足 “最左匹配” 原则
    • 原因：复合索引（如 (col1, col2, col3)）需按索引列顺序使用前导列，若查询条件未包含前导列，索引可能失效。
    • 示例： 
      -- 索引为 (col1, col2)，但查询条件仅有 col2，索引失效
SELECT * FROM table WHERE col2 = 10;
    • 例外：若复合索引的后续列使用了等值查询，且前导列使用 = 或 IN，可能部分使用索引（需具体分析执行计划）。
  5. IS NULL 或 IS NOT NULL 与索引设计冲突
    • 原因：普通 B 树索引不存储 NULL 值，若查询条件为 col IS NULL，索引无法直接定位（但可通过全索引扫描查找）。若表中 NULL 值过多，优化器可能选择全表扫描。
    • 示例： 
      -- 若 col 允许 NULL 且大量数据为 NULL，索引扫描效率可能低于全表扫描
SELECT * FROM table WHERE col IS NULL;

三、索引自身状态或结构问题

  1. 索引不可用或失效
    • 原因：索引因重建中断、表结构变更未关联索引等原因变为 UNUSABLE 状态，无法被使用。
    • 检查方法： 
      SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = 'YOUR_TABLE';
    • 修复：重建索引（ALTER INDEX idx REBUILD;）。
  2. 位图索引用于高并发 DML 表
    • 原因：位图索引适用于低并发、高选择性的查询场景。若表频繁执行 UPDATE/DELETE/INSERT，位图索引的锁竞争会导致性能问题，优化器可能主动规避。
  3. 索引统计信息陈旧
    • 原因：统计信息（如 ANALYZE 或 DBMS_STATS 生成的数据）未及时更新，优化器无法正确评估索引效率，导致执行计划错误。
    • 解决：定期更新统计信息： 
      EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'USER', TABNAME => 'TABLE');

四、优化器策略或提示强制不使用索引

  1. 优化器模式影响
    • 场景：当优化器模式设置为 FIRST_ROWS（优先获取前几条数据）或 ALL_ROWS（优化整体吞吐量）时，可能因成本计算选择不同策略。
    • 示例：对于分页查询（如 WHERE rownum < 10），优化器可能认为全表扫描获取前几条数据更快，而非通过索引有序扫描。
  2. 手动提示禁用索引
    • 原因：若 SQL 中使用 /*+ FULL(table) */ 或 /*+ NO_INDEX(table idx) */ 等提示，会强制优化器不使用索引。
    • 示例： 
      -- 强制全表扫描，忽略所有索引
SELECT /*+ FULL(t) */ * FROM table t;

五、其他特殊场景

  1. 索引列参与计算或复杂表达式
    • 原因：若查询条件为 col > SYSDATE - 7 等时间范围，且索引为普通 B 树索引，可能正常使用索引；但若条件为 col + 1 = 10，需创建表达式索引。
  2. 分区表索引与分区裁剪不匹配
    • 原因：若分区表的查询条件未包含分区键，且索引为本地分区索引，优化器可能无法裁剪分区，导致索引扫描效率低下，转而使用全表扫描。

如何排查索引未被使用的问题？

  1. 查看执行计划：使用 EXPLAIN PLAN 或 SQL Developer 的图形化执行计划，确认是否出现 TABLE ACCESS FULL（全表扫描）而非索引相关操作（如 INDEX RANGE SCAN）。
  2. 检查统计信息：确保表和索引的统计信息最新（USER_TABLES.STATS_DATE、USER_INDEXES.STATUS）。
  3. 分析索引选择性：通过 SELECT COUNT(DISTINCT col)/COUNT(*) FROM table; 评估索引列的选择性，选择性低于 10% 时可能被优化器忽略。
  4. 检查索引状态：确认索引是否为 VALID 状态，无 UNUSABLE 或 DISABLED 情况。

总结

索引未被使用的核心原因是优化器认为索引扫描的成本高于全表扫描。解决方法需结合具体场景：
  • 避免在索引列上使用函数或隐式转换；
  • 确保复合索引的前导列被正确使用；
  • 定期更新统计信息；
  • 针对低选择性列使用位图索引或删除不必要的索引；
  • 通过执行计划和 DBMS_XPLAN 分析具体原因。

posted on 2025-04-09 10:04  数据派  阅读(78)  评论(0)    收藏  举报