跳到主要内容

索引对象

索引是数据库中的一种可选数据结构，通常与表或表簇相关联。它的核心目的是加快数据检索速度。您可以将其想象成一本书的目录：没有目录，您需要逐页翻阅才能找到特定内容；有了目录，您可以直接定位到所需的页码。

在 Oracle 中，索引是通过减少磁盘 I/O 操作来提升查询性能的。

索引的核心工作原理​

  1. 创建：在表的一个或多个列上创建索引。
  2. 存储：Oracle 会自动创建并维护一个独立的数据结构（如 B-树），其中存储了索引列的值以及对应的行物理地址（ROWID）。
  3. 查询：当执行带有 WHERE 子句的查询时，Oracle 会首先在索引结构中查找符合条件的值，然后直接使用关联的 ROWID 去访问表中的特定行，从而避免全表扫描。

Oracle 主要索引类型​

1. B-树索引（Balanced Tree Index）​

这是 Oracle 默认 和最常用的索引类型。适用于高基数列（即列中唯一值多，数据重复度低）。

  • 结构：像一棵倒置的树，从根节点开始，到分支节点，最后到叶节点。叶节点包含索引键值和对应的 ROWID，并且彼此双向链接，有利于范围查询。
  • 适用场景：
    • 等值查询（=）
    • 范围查询（BETWEEN, >, <）
    • 前导列查询（用于复合索引）
  • 子类型：
    • 反向键索引：将索引列的字节顺序反转。适用于序列值（如主键）的插入，能有效减少索引右侧的“热点”争用。
    • 降序索引：允许索引按降序存储，优化 ORDER BY column_name DESC 查询。

2. 位图索引（Bitmap Index）​

使用位图（一串 0 和 1）来表示表中行的数据值。

  • 适用场景：
    • 低基数列（即列中唯一值少，数据重复度高），例如：性别、状态标志、地区等。
    • 主要用于数据仓库和大量读操作的 OLAP 系统，不适用于高并发的 OLTP 系统。
  • 优点：对于多条件的 AND、OR、NOT 查询，效率极高，因为可以通过位运算快速完成。
  • 缺点：对于频繁的 INSERT、UPDATE、DELETE 操作，锁定粒度大，性能很差。

3. 函数索引（Function-Based Index）​

基于列的表达式的索引，而不仅仅是列本身。

  • 适用场景：查询条件中经常对列使用函数或表达式。
  • 示例： 
    -- 创建索引
    CREATE INDEX idx_emp_upper_name ON employees(UPPER(last_name));
    -- 使用索引的查询
    SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

4. 唯一索引 vs 非唯一索引​

  • 唯一索引：保证索引列（或列组合）中的值是唯一的。主键约束和唯一约束会自动创建唯一索引。
  • 非唯一索引：不强制唯一性，是最常见的索引类型，仅用于提高查询速度。

5. 复合索引（Concatenated Index）​

在多个列上创建的索引。

  • 适用场景：查询条件经常同时使用多个列。
  • 重要概念 - 前导列：查询时必须使用索引的第一个列（前导列），索引才能最有效。例如，在 (last_name, department_id) 上的索引，查询 WHERE last_name = ... 会使用索引，但仅查询 WHERE department_id = ... 则可能不会使用。

6. 其他特殊索引​

  • 分区索引：与分区表配合使用，索引本身也可以被分区，提高可管理性和性能。
  • 虚拟列索引：在表的虚拟列上创建索引，是函数索引的一种替代实现。
  • 不可见索引：优化器在生成执行计划时会忽略该索引，但索引本身仍被维护。用于测试新索引而不影响现有应用。
  • 域索引：由用户自定义的、用于特定复杂数据类型（如文本、空间数据）的索引。

索引的优点与缺点​

优点缺点
大幅提升查询速度占用额外存储空间
保证数据的唯一性（唯一索引）降低 DML 操作速度（INSERT, UPDATE, DELETE），因为需要同时维护表和索引
加速表连接需要持续维护和优化，错误的索引设计可能适得其反

最佳实践与建议​

  1. 在 WHERE 子句和 JOIN 条件频繁使用的列上创建索引。
  2. 谨慎选择索引列：高基数、数据分布均匀的列是好的候选。
  3. 保持索引简洁：尽量使用选择性高的列，并避免过宽的索引（列数过多）。
  4. 利用复合索引，注意前导列顺序。
  5. 定期监控和重建索引：对于碎片化严重的索引，重建可以回收空间并提升性能。
  6. 不要滥用索引：索引不是越多越好。每个额外的索引都会增加写操作的开销。

总结​

Oracle 索引是数据库性能调优的强大工具。B-树索引是通用首选，位图索引适用于数据仓库的低基数列，函数索引解决了表达式查询的性能问题。成功的关键在于根据实际的数据特性和查询模式，明智地选择、设计和维护索引，在查询速度和写操作开销之间找到最佳平衡点。