在Oracle数据库的日常运维和开发工作中，索引是优化查询性能的核心手段之一，合理的索引设计能够在提升查询速度的同时，避免对数据写入、更新操作造成过大的性能负担。不同的业务场景需要匹配不同的索引类型和创建策略，盲目创建索引反而会引发更多性能问题。
Oracle索引的基本类型
Oracle数据库支持多种索引类型，开发者需要根据实际业务需求选择合适的类型，常见的索引类型如下：
- B树索引：最常用的索引类型，适合高基数列（列中不同值数量多）的等值查询和范围查询，默认创建的索引就是B树索引。
- 位图索引：适合低基数列（列中不同值数量少，比如性别、状态字段）的查询，多个位图索引组合查询时效率很高，但不适合频繁更新的列。
- 函数索引：基于列的函数或者表达式创建的索引，适合查询条件中对列使用了函数处理的场景，比如对日期字段做格式化后查询。
- 复合索引：基于多个列创建的索引，适合查询条件中经常同时出现多个列的场景，需要注意列的顺序符合最左前缀原则。
创建合理索引的核心原则
创建索引前需要先明确业务场景的查询特征，遵循以下原则才能设计出合理的索引：
1. 优先为高频查询列创建索引
首先分析业务中的高频查询语句，将查询条件中经常出现的列作为索引候选列，避免为很少用到的查询列创建索引，浪费存储和维护资源。
2. 控制索引数量
每张表的索引数量不宜过多，一般建议单表索引数量不超过5个。因为每次对表执行插入、更新、删除操作时，都需要同步维护所有索引，索引过多会大幅降低写入性能。
3. 复合索引遵循最左前缀原则
如果创建复合索引idx_emp_dept_job包含dept_id和job两个列，那么查询条件中包含dept_id时索引才会生效，只包含job的查询无法使用该复合索引。
4. 避免过度索引低基数列
对于性别、状态这类只有几个固定值的低基数列，普通B树索引的收益很低，这类列更适合创建位图索引，或者和其他高基数列组合成复合索引。
Oracle索引的创建语法与示例
Oracle中创建索引的基本语法如下：
-- 创建普通B树索引 CREATE INDEX 索引名 ON 表名(列名1, 列名2...); -- 创建唯一索引，确保索引列的值唯一 CREATE UNIQUE INDEX 索引名 ON 表名(列名); -- 创建位图索引 CREATE BITMAP INDEX 索引名 ON 表名(低基数列名); -- 创建函数索引 CREATE INDEX 索引名 ON 表名(函数(列名));
以下是几个实际的创建示例：
示例1：为员工表的部门编号列创建普通索引
员工表emp中经常需要根据dept_id查询员工信息，为该列创建B树索引：
-- 创建名为idx_emp_dept的索引，提升按部门查询员工的效率 CREATE INDEX idx_emp_dept ON emp(dept_id);
示例2：为订单表创建复合索引
订单表orders中经常需要查询某个用户某个时间段的订单，查询条件通常同时包含user_id和order_time，创建复合索引：
-- 复合索引列顺序按照查询频率从高到低排列，user_id查询频率更高放在前面 CREATE INDEX idx_orders_user_time ON orders(user_id, order_time);
示例3：创建函数索引
经常需要查询订单表中某个月份的订单，查询条件为TO_CHAR(order_time, 'YYYY-MM') = '2024-05'，直接对order_time创建索引无法生效，需要创建函数索引：
-- 函数索引匹配查询中的函数表达式，让查询能够走索引 CREATE INDEX idx_orders_time_func ON orders(TO_CHAR(order_time, 'YYYY-MM'));
索引的维护与优化
索引创建完成后不是一劳永逸的，需要定期维护保证其性能：
- 定期分析索引使用情况，对于长期没有被使用到的索引可以考虑删除，减少维护成本。
- 如果表的数据量发生较大变化，或者索引碎片率过高，可以重建索引：
ALTER INDEX 索引名 REBUILD; - 对于分区表，可以创建分区索引，进一步提升查询和维护的效率。
常见索引使用误区
很多开发者在创建索引时会犯以下错误，需要尽量避免：
- 认为索引越多越好，导致写入性能严重下降。
- 对查询中使用了函数的列直接创建普通索引，导致索引失效。
- 复合索引的列顺序不符合最左前缀原则，导致索引无法被查询使用。
- 为小表（数据量少于1000行）创建索引，小表全表扫描的性能已经足够好，创建索引反而增加额外开销。