Oracle中的索引

oracle中的索引

介绍：

1. 单列索引

是指在单个列上创建的索引，它是最常见和基本的索引类型之一。单列索引可以加快对特定列的查询速度，并提高查询的性能。

在 Oracle 数据库中，创建单列索引可以使用以下语法：

CREATE INDEX index_name ON table_name (column_name);

其中，index_name 是索引的名称，table_name 是要创建索引的表名，column_name 是要在其上创建索引的列名。

例如，假设有一个名为 employees 的表，其中包含一个名为 last_name 的列，我们可以创建一个名为 idx_last_name 的单列索引，以加快对 last_name 列的查询速度，可以使用以下语句：

CREATE INDEX idx_last_name ON employees (last_name);

这将在 employees 表的 last_name 列上创建名为 idx_last_name 的单列索引。

单列索引适用于在查询中使用单个列进行条件过滤、排序和连接操作的情况。它可以显著提高对特定列的查询性能，减少查询的扫描范围，从而提升整体的查询效率。但需要注意的是，在创建索引时需要权衡索引的数量和维护成本，以及对数据的更新操作可能带来的性能开销。

2. 多列索引（Composite Index）

是指在多个列上创建的索引，它可以包含两个或更多列。多列索引可以帮助优化涉及多个列的查询，提高查询的性能和效率。

在 Oracle 数据库中，创建多列索引可以使用以下语法：

CREATE INDEX index_name ON table_name (column1, column2, ...);

其中，index_name 是索引的名称，table_name 是要创建索引的表名，column1, column2, ... 是要在其上创建索引的列名列表。

例如，假设有一个名为 employees 的表，其中包含 last_name、first_name 和 department_id 三个列，我们可以创建一个名为 idx_employee_info 的多列索引，以加快涉及这三个列的查询速度，可以使用以下语句：

CREATE INDEX idx_employee_info ON employees (last_name, first_name, department_id);

这将在 employees 表的 last_name、first_name 和 department_id 列上创建名为 idx_employee_info 的多列索引。

多列索引适用于需要根据多个列进行条件过滤、排序和连接的查询操作。它可以更好地支持复合查询条件和多列的等值或范围查询，提高这些查询的性能。但需要注意的是，在创建多列索引时需要权衡索引的选择性和查询的覆盖度，确保索引能够真正提升查询性能，避免创建过多的冗余索引。此外，对于列顺序的选择也要考虑到查询中频繁使用的列放在前面，以提高索引的效率。

3. 唯一索引（Unique Index）

是指在列或列组合上具有唯一性约束的索引。它确保索引列中的值是唯一的，不允许重复值存在。

在 Oracle 数据库中，创建唯一索引可以使用以下语法：

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

其中，index_name 是索引的名称，table_name 是要创建索引的表名，column1, column2, ... 是要在其上创建索引的列名列表。

例如，假设有一个名为 employees 的表，其中包含一个名为 employee_id 的列，我们可以创建一个名为 idx_employee_id 的唯一索引，以确保 employee_id 列中的值是唯一的，可以使用以下语句：

CREATE UNIQUE INDEX idx_employee_id ON employees (employee_id);

这将在 employees 表的 employee_id 列上创建名为 idx_employee_id 的唯一索引。

唯一索引适用于需要保证列或列组合中的值的唯一性的情况。它可以用于避免数据重复和维护数据的完整性。当在唯一索引列上插入或更新数据时，数据库会检查索引的唯一性约束，并拒绝插入或更新重复值的操作。唯一索引还可以提高查询的性能，因为数据库可以使用索引进行快速唯一性检查。

需要注意的是，创建唯一索引会增加对索引的维护成本和存储空间的消耗。因此，需要谨慎考虑在哪些列上创建唯一索引，以及确保索引的选择性和适用性。

4. 非唯一索引（Non-Unique Index）

是指在列或列组合上创建的索引，允许出现重复的值。与唯一索引不同，非唯一索引允许多行具有相同的索引键值。

在 Oracle 数据库中，创建非唯一索引的语法与创建唯一索引相同，只需省略 UNIQUE 关键字即可。下面是创建非唯一索引的示例：

CREATE INDEX index_name ON table_name (column1, column2, ...);

其中，index_name 是索引的名称，table_name 是要创建索引的表名，column1, column2, ... 是要在其上创建索引的列名列表。

例如，假设有一个名为 employees 的表，其中包含一个名为 department_id 的列，我们可以创建一个名为 idx_department_id 的非唯一索引，以加快对 department_id 列的查询速度，可以使用以下语句：

CREATE INDEX idx_department_id ON employees (department_id);

这将在 employees 表的 department_id 列上创建名为 idx_department_id 的非唯一索引。

非唯一索引适用于在查询中使用非唯一索引列进行条件过滤、排序和连接的情况。它可以提高查询的性能，减少查询的扫描范围，从而提升整体的查询效率。相对于唯一索引，非唯一索引具有更低的维护成本和存储空间消耗。

需要注意的是，非唯一索引允许重复值的存在，因此在查询时可能会返回多行结果。在选择索引列时，需要根据具体的查询需求和数据特征来确定是否创建非唯一索引。

7. 基于函数的索引（Function-based Index）

是一种在列上使用函数表达式创建的索引。它允许在索引中使用函数对列值进行转换、计算或提取，以支持更灵活的查询和优化性能。

在 Oracle 数据库中，创建基于函数的索引可以使用以下语法：

CREATE INDEX index_name ON table_name (function(column));

其中，index_name 是索引的名称，table_name 是要创建索引的表名，function(column) 是应用于列的函数表达式。

例如，假设有一个名为 employees 的表，其中包含一个名为 full_name 的列，我们希望创建一个索引，以在查询中按照姓氏进行排序。可以使用 SUBSTR 函数来提取姓氏，并创建基于函数的索引，如下所示：

CREATE INDEX idx_last_name ON employees (SUBSTR(full_name, 1, INSTR(full_name, ' ') - 1));

这将在 employees 表的 full_name 列上创建名为 idx_last_name 的基于函数的索引，使用 SUBSTR 函数提取姓氏部分。

基于函数的索引可以帮助优化查询，特别是在对列进行函数操作或复杂计算时。它可以提前计算并存储函数的结果，加快查询的执行速度。同时，它还可以支持更灵活的查询，允许在查询中使用函数表达式。

需要注意的是，创建基于函数的索引需要谨慎选择适当的函数和表达式，以确保索引的有效性和适用性。在使用基于函数的索引时，查询中的函数表达式应该与索引的函数表达式相匹配，以确保索引的使用。此外，基于函数的索引也会增加索引的维护成本和存储空间消耗。

8. 分区索引（Partitioned Index）和非分区索引（Non-Partitioned Index）

是根据索引所依赖的表是否进行分区而区分的。

分区索引是指与分区表对应的索引，其中索引的分区结构与表的分区结构一致。每个分区都有一个对应的索引分区，该索引分区仅包含与相应表分区中的数据相关的索引项。分区索引与分区表一起使用，可以提高查询性能和管理分区数据的效率。

非分区索引是指不与分区表对应的索引，它与普通的表一起使用，不考虑表的分区结构。非分区索引将索引项存储在一个结构中，无论表的分区如何划分，所有数据都存储在同一个索引结构中。

在 Oracle 数据库中，可以根据具体的表分区需求选择使用分区索引或非分区索引。分区索引适用于对分区表进行查询的情况，可以提高查询性能和分区数据管理的效率。非分区索引适用于普通的表查询，没有考虑表的分区结构。

需要注意的是，分区索引的创建和维护可能比非分区索引更复杂和耗费资源。在选择分区索引或非分区索引时，需要根据具体的业务需求、查询模式和数据规模进行评估和权衡。

9. 位图索引（Bitmap Index）

是一种特殊类型的索引，它使用位图数据结构来加速查询操作。位图索引适用于列具有较低基数（唯一值较少）且具有高度重复模式的情况。

在位图索引中，对于每个不同的索引键值，都会创建一个位图，其中的位表示数据行是否包含该索引键值。每个位图使用一个位来表示一个行的存在或不存在，从而可以快速定位满足特定索引条件的数据行。

位图索引在数据压缩和查询效率方面具有优势。它可以大大减少索引的存储空间需求，因为位图只需要一个位来表示一个行的存在或不存在。此外，位图索引在涉及多个列的联合查询和位运算时效果显著，可以快速筛选满足多个条件的数据行。

在 Oracle 数据库中，可以使用以下语法创建位图索引：

CREATE BITMAP INDEX index_name ON table_name (column_name);

其中，index_name 是索引的名称，table_name 是要创建索引的表名，column_name 是要在其上创建位图索引的列名。

例如，假设有一个名为 employees 的表，其中包含一个名为 gender 的列，该列只包含两个可能的值：‘M’ 和 ‘F’。我们可以创建一个名为 idx_gender 的位图索引，以加快对 gender 列进行性别过滤的查询，可以使用以下语句：

CREATE BITMAP INDEX idx_gender ON employees (gender);

需要注意的是，位图索引适用于具有低基数且重复性高的列。如果列具有高基数（唯一值较多）或变化频繁，位图索引的效果可能不理想。此外，位图索引对于数据更新和维护的开销较大，因为每次数据更新都需要更新相关的位图。

因此，在选择使用位图索引时，需要根据具体的数据特征、查询模式和性能需求进行评估和权衡。

10. B树索引（B-tree Index）

是一种常用的索引结构，用于在数据库中加快数据的检索速度。它适用于高基数（唯一值较多）的列，可以快速定位满足特定条件的数据。

B树索引是一种平衡树结构，它将索引键和对应的数据地址存储在树节点中。每个节点包含多个索引键，并按照特定的排序顺序组织。根节点位于树的顶部，包含最小和最大索引键的值，并根据索引键的值将树分割成多个子树。

B树索引的特点包括：

1. 平衡性：B树索引保持平衡，即每个节点的左右子树的高度差不超过一个预设的界限。这样可以保证在最坏情况下，树的高度仍然较小，查询的效率较高。

2. 多路搜索：每个节点包含多个索引键，可以在一个节点中进行多路搜索，减少磁盘访问次数，提高查询性能。

3. 范围查询支持：B树索引支持范围查询，可以快速定位满足特定范围条件的数据。

4. 自平衡：在插入或删除索引键时，B树索引会自动进行平衡操作，保持树的平衡性。

B树索引广泛应用于数据库系统中，包括关系型数据库和许多其他类型的数据库。它是一种高效的索引结构，能够有效地支持数据的快速检索和排序。

sql举例:

在 Oracle 数据库中创建索引时，默认情况下会使用 B 树索引结构。

假设我们有一个名为 employees 的表，其中包含列 employee_id 和 last_name。我们想在 last_name 列上创建一个 B 树索引。

1. 首先，使用以下语句创建表 employees：

CREATE TABLE employees (
  employee_id   NUMBER,
  last_name     VARCHAR2(50),
  -- other columns
);

2. 接下来，使用以下语句创建 B 树索引：

CREATE INDEX idx_last_name ON employees (last_name);

这将在 last_name 列上创建名为 idx_last_name 的 B 树索引。

3. 现在，可以使用该索引来加速查询。例如，执行以下查询：

SELECT * FROM employees WHERE last_name = 'Smith';

由于我们在 last_name 列上创建了 B 树索引，Oracle 将使用该索引快速定位满足条件的行，而无需扫描整个表。这样可以提高查询的性能。

需要注意的是，当插入、更新或删除表中的数据时，Oracle 会自动维护 B 树索引，以保持索引的平衡性和有效性。

B 树索引在 Oracle 数据库中被广泛使用，它提供O快速的数据检索和排序能力，能够提高查询性能和数据访问效率。