Oracle 索引怎么用？原理、分类及维护技巧

索引是 Oracle 数据库中提升查询性能的核心工具，通过 “空间换时间” 的策略，减少数据检索时的 I/O 操作。本文将系统讲解 Oracle 索引的原理、分类、使用限制及维护技巧，帮助数据库管理员和开发人员合理设计索引，平衡查询效率与维护成本。

一、索引概述：用空间换时间的性能利器

• ROWID 的作用：ROWID 是 Oracle 为每行数据分配的唯一标识符，包含数据文件号、块号、行号等信息，直接指向数据在磁盘上的物理位置。通过索引中的 ROWID，Oracle 能快速定位到目标行，甚至判断多行是否存储在同一个数据块中（减少块读取次数）。
• 空间换时间：索引会占用额外的存储空间（通常为表大小的 10%-30%），但能显著减少查询的 I/O 次数（全表扫描可能需要读取数千个块，而索引扫描可能只需几十个），尤其对大表查询效果明显。

二、索引的数据字典：查询索引元数据

1. 查看表的索引列表

-- 创建示例索引：在emp表的sal列上创建索引emp_id2 create index emp_id2 on emp (sal); -- 查询emp表的所有索引 select table_name, index_name, uniqueness -- uniqueness表示是否唯一索引 from user_indexes where table_name = 'EMP'; -- 表名需大写 

2. 查看索引包含的列

-- 格式化输出 column index_name format a15 column column_name format a10 column table_name format a10 -- 查询索引的列信息 select table_name, index_name, column_name, -- 索引列名 column_position -- 列在索引中的位置（复合索引关键） from user_ind_columns where table_name = 'EMP' order by index_name, column_position; 

三、限制索引使用的场景：并非所有查询都需要索引

1. 表数据量极小（如少于 100 行）：全表扫描的 I/O 成本可能低于索引扫描（索引本身也需 I/O）。
2. 查询返回表中大部分数据（如超过 30%）：此时全表扫描可能比索引扫描更高效（索引扫描需先读索引，再根据 ROWID 读数据，反而增加 I/O）。
3. 索引列频繁被修改（INSERT/UPDATE/DELETE）：索引会随数据修改同步更新，频繁修改会导致索引维护成本激增（如日志生成、碎片增加）。
4. 查询中索引列使用函数或表达式：如where upper(ename) = 'SCOTT'，若ename上有索引，该查询无法使用索引（需创建基于函数的索引解决）。
5. 使用特定运算符：
   • 不等于（!=、<>）、NOT IN：可能导致索引失效，改用NOT EXISTS更高效；
   • IS NULL/IS NOT NULL：若索引列允许为空，NULL 值不被索引存储，查询可能走全表扫描；
   • 模糊查询以%开头（如where ename like '%SMITH'）：无法使用索引（以%结尾可使用索引）。

四、Oracle 索引分类：按需选择合适类型

1. B 树索引：最常用的默认索引

• 核心优势：通过层级结构快速定位索引键，减少 I/O 操作（如百万级数据，通常只需 3-4 次 I/O 即可定位）。
• 适用场景：高基数列（列值重复率低，如身份证号、手机号）、频繁范围查询（如between、>、<）。
• 维护统计信息：索引的使用依赖于 Oracle 优化器的统计信息，需定期更新：
  sql

  -- 收集表及索引的统计信息（用于优化器生成执行计划） exec dbms_stats.gather_table_stats( ownname => 'SYS', -- 用户名 tabname => 'T1', -- 表名 method_opt => 'for all indexed columns size 2', -- 收集索引列统计 cascade => TRUE -- 同时收集索引统计 ); 

2. 索引组织表（IOT）：数据与索引合一

• 适用场景：
  • 频繁通过主键查询的表（如字典表、配置表）；
  • 范围查询频繁的表（如按时间排序的日志表）；
  • 需避免表与索引分离存储的场景（减少 I/O 次数）。
• 创建示例：
  sql

  -- 创建索引组织表（主键即为索引键） create table emp_iot ( empno number(4) primary key, ename varchar2(10), sal number(7,2) ) organization index; -- 声明为索引组织表 

3. 唯一索引：保证列值唯一性

• 适用场景：需保证列值唯一的场景（如用户 ID、订单号）。
• 创建示例：
  sql

  -- 直接创建唯一索引 create unique index idx_emp_empno on emp(empno); -- 在建表时通过主键约束创建唯一索引（推荐） create table test_uid ( name varchar(10), constraint test_uid_pk primary key(name) -- 主键自动创建唯一索引 using index (create unique index uid_test_uid on test_uid(name) tablespace users) -- 指定索引表空间 ); 

4. 反向键索引：解决索引叶节点争用

• 适用场景：
  • 高并发插入场景（如订单表，主键为序列自增）；
  • 索引键值连续增长，导致叶节点集中在某一区域（热块争用）。
• 创建示例：
  sql

  -- 在序列生成的id列上创建反向键索引 create index idx_orders_id_rev on orders(id) reverse; 

5. 降序索引：优化排序查询

• 创建示例：
  sql

  -- 创建降序索引（sal列按降序存储） create index idx_emp_sal_desc on emp(sal desc); -- 该索引可优化如下查询（无需额外排序） select ename, sal from emp order by sal desc fetch first 10 rows only; 

6. 位图索引：适用于低基数列

• 优势：存储空间小（位图压缩存储），适合多列组合查询（如where gender='M' and deptno=10）。
• 限制：不适合高并发 DML 场景（更新时可能锁定多个位图，导致性能下降）。
• 创建示例：
  sql

  -- 在低基数列（如性别）上创建位图索引 create bitmap index idx_emp_gender on emp(gender); 

7. 基于函数的索引：优化含函数的查询

• 适用场景：查询中频繁对列使用固定函数（如大小写转换、日期截断）。
• 创建示例：
  sql

  -- 创建基于函数的索引（存储upper(ename)的结果） create index idx_emp_upper_ename on emp(upper(ename)); -- 该索引可优化如下查询 select ename from emp where upper(ename) = 'SCOTT'; 

五、索引维护：重建、合并与监控

1. 索引重建：彻底消除碎片

-- 重建索引（并行8个进程，指定表空间，在线执行不阻塞DML） alter index cust_idx1 
rebuild 
parallel 8 -- 并行处理，加速重建 tablespace cust_tblspc1 -- 指定新表空间 online; -- 在线重建，允许同时进行DML操作（推荐生产环境使用） 

2. 索引合并：轻度碎片整理

-- 合并索引碎片 alter index pk_emp coalesce; 

3. 索引监控：识别无用索引

-- 启动索引监控 alter index EMP_ENAME_SAL_IDX monitoring usage; -- （经过一段时间的业务运行后）查看索引使用情况 select index_name, table_name, monitoring, used from v$object_usage; -- used为YES表示被使用，NO表示未被使用 -- 查看监控时间范围 select index_name, start_monitoring, end_monitoring from v$object_usage where index_name = 'EMP_ENAME_SAL_IDX'; -- 终止监控 alter index EMP_ENAME_SAL_IDX nomonitoring usage; -- 删除无用索引 drop index dept_dname_idx; 

六、总结：索引使用的核心原则

1. 按需创建：根据查询场景选择合适的索引类型（如高基数列用 B 树索引，低基数列用位图索引），避免 “索引越多越好”。
2. 平衡读写：索引提升查询性能，但会降低 INSERT/UPDATE/DELETE 效率，写入频繁的表需控制索引数量。
3. 定期维护：通过重建或合并处理碎片，通过监控删除无用索引，保持索引高效。
4. 结合执行计划：创建索引后，需通过explain plan分析查询执行计划，确认索引被有效使用。