创建Oracle索引时那些容易忽略但又特别重要的细节和技巧分享

很多人知道索引能加快查询,但很容易忽略索引并不仅仅是给查询用的，它也会影响数据的插入、更新和删除操作，每次对表数据进行增删改，数据库都需要去维护相关的索引，相当于多做了好几份工作，所以第一个重要的细节就是：不要盲目创建索引，尤其是在频繁更新的列上，索引不是越多越好，而是要找到平衡点，比如一张每天有大量新数据插入的交易表，如果每个查询字段都建索引，那么插入新记录的速度就会明显变慢，这就像一本书的索引，如果每一页的每一个关键词都编入索引，那这本索引本身就厚得像字典一样，查找起来反而更费劲，这个观点在很多数据库优化实践中都被反复强调，比如在《Oracle Database Concepts》官方手册中就提到，索引在带来查询优势的同时，也伴随着维护开销。

第二个容易被忽略的点是索引列的顺序,这尤其针对组合索引（也叫复合索引），创建组合索引时，列的顺序至关重要，它决定了索引能否被查询有效利用，一个基本原则是：将查询中最常用作筛选条件的列放在最前面，选择性更高（即唯一值更多）的列也尽量靠前，有一张订单表，我们经常按“客户ID”和“订单状态”来查询，客户ID”的选择性远高于“订单状态”（即客户数量很多，但订单状态只有寥寥几种），那么创建一个（客户ID, 订单状态）的索引，会比（订单状态, 客户ID）的索引有效得多，因为索引的第一列就像电话簿的姓氏，必须先按姓氏查找，才能再按名字定位，如果查询条件只包含了组合索引的第二列而没有第一列，那么这个索引很可能就用不上了，这个技巧在Oracle官方优化文档和Thomas Kyte的《Expert Oracle Database Architecture》中都有深入阐述。

第三个细节是关于索引的“空值”问题，默认情况下，Oracle的B树索引是不存储完全为NULL的条目，这意味着，如果某条记录在索引列上的值全是NULL，那么这条记录就不会出现在这个索引中，这既是一个特点，也可能是一个陷阱，对于查询WHERE indexed_column IS NULL，如果只有这一列索引，优化器可能无法使用索引快速定位，而是会选择全表扫描，如果你需要频繁查询NULL值，可以考虑使用函数索引，例如CREATE INDEX idx_name ON table_name (NVL(column_name, 'SOME_VALUE'))，或者考虑在表设计时避免使用NULL而赋予默认值，这个特性在Oracle的SQL语言参考手册关于索引的部分有明确说明。

第四个技巧是关注索引的“聚簇因子”，这是一个比较底层的概念，但理解它非常重要，聚簇因子反映了索引中的数据顺序和表中数据实际物理存储顺序的匹配程度，如果聚簇因子接近表的数据块数量，说明数据存储非常无序，通过索引查询时，需要跳跃访问大量不同的数据块，I/O成本会很高，如果聚簇因子接近表的总行数，说明数据存储非常有序，一个糟糕的聚簇因子会让一个看似完美的索引性能大打折扣，虽然我们无法直接改变聚簇因子（除非对表按索引键重新组织数据，比如使用MOVE或在线重定义），但在评估索引效率时，一定要查看它的聚簇因子，如果值很高，就要意识到这可能成为性能瓶颈，DBA们在做性能调优时，经常会通过DBMS_STATS.GATHER_TABLE_STATS收集统计信息后，查询USER_INDEXES视图中的CLUSTERING_FACTOR列来分析这个问题。

第五个容易忽略的点是“函数索引”的巧妙应用，但也要注意其代价，当查询条件中对列使用了函数或表达式时，比如WHERE UPPER(name) = 'SMITH' 或 WHERE salary * 12 > 100000，普通的索引是无法被使用的，为了解决这个问题，可以创建函数索引，如CREATE INDEX idx_upper_name ON employees (UPPER(last_name))，这非常强大，但容易被忽略的是，函数索引的维护成本更高，因为每次DML操作时，数据库都需要计算函数值，函数索引要求函数是确定性的，即相同的输入总是得到相同的输出，像SYSDATE这样的非确定性函数就不能用于函数索引。

第六个细节是考虑“位图索引”的适用场景，但更要清楚其严重的局限性，位图索引非常适合在低基数列（即唯一值很少的列，比如性别、状态标志）上进行大量且复杂的AND/OR查询，数据仓库环境是它的主战场，它能通过位运算高效合并多个条件，但极其重要的是，位图索引不适合OLTP（联机事务处理）系统，因为它对并发DML操作的支持非常差，一个会话更新一个位图索引键，可能会锁住一系列相关的位图段，从而阻塞其他会话对相邻键值的更新，导致严重的并发性能问题，在传统的、高并发的业务系统中，应尽量避免使用位图索引。

一个实用的技巧是定期审视和清理“未被使用”或“效率低下”的索引，系统经过长时间演变，很多当初为特定查询创建的索引可能已经不再使用，或者因为业务逻辑变化而失效，这些“僵尸索引”不仅占用存储空间，更严重的是它们会拖慢所有的DML操作，可以通过查询Oracle的动态性能视图V$OBJECT_USAGE（对于单个索引的监控）或DBA_HIST_SQL_PLAN等历史视图，来分析哪些索引在长时间内没有被执行计划使用过，定期清理这些索引，是保持数据库高效运行的良好习惯，这个维护工作在Oracle的数据库管理指南中也被推荐为常规任务。

创建索引远不止是选择一个列那么简单,需要综合考虑查询需求、数据修改的频率、数据的分布特性、索引的类型以及后期的维护成本，每一次创建索引前，多问一句“这个索引真的必要吗？”、“它的顺序最优吗？”、“它会带来什么副作用？”，就能避免很多后续的性能问题。