索引对象
索引是数据库中的一种可选数据结构,通常与表或表簇相关联。它的核心目的是加快数据检索速度。您可以将其想象成一本书的目录:没有目录,您需要逐页翻阅才能找到特定内容;有了目录,您可以直接定位到所需的页码。
在 Oracle 中,索引是通过减少磁盘 I/O 操作来提升查询性能的。
索引的核心工作原理
- 创建:在表的一个或多个列上创建索引。
- 存储:Oracle 会自动创建并维护一个独立的数据结构(如 B-树),其中存储了索引列的值以及对应的行物理地址(ROWID)。
- 查询:当执行带有
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),因为需要同时维护表和索引 |
| 加速表连接 | 需要持续维护和优化,错误的索引设计可能适得其反 |
最佳实践与建议
- 在 WHERE 子句和 JOIN 条件频繁使用的列上创建索引。
- 谨慎选择索引列:高基数、数据分布均匀的列是好的候选。
- 保持索引简洁:尽量使用选择性高的列,并避免过宽的索引(列数过多)。
- 利用复合索引,注意前导列顺序。
- 定期监控和重建索引:对于碎片化严重的索引,重建可以回收空间并提升性能。
- 不要滥用索引:索引不是越多越好。每个额外的索引都会增加写操作的开销。
总结
Oracle 索引是数据库性能调优的强大工具。B-树索引是通用首选,位图索引适用于数据仓库的低基数列,函数索引解决了表达式查询的性能问题。成功的关键在于根据实际的数据特性和查询模式,明智地选择、设计和维护索引,在查询速度和写操作开销之间找到最佳平衡点。