Oracle中的索引

àì夳堔傛蜴生んèń 2024-03-16 16:19 8阅读 0赞

**目录**

一、索引概念

二、索引分类及应用实例

2.1B树索引

2.2位图索引

2.3函数索引

2.4普通索引

2.5唯一索引

2.6主键索引

2.7聚集索引

2.8全文索引

三、索引创建

四、索引修改

五、索引删除

六、索引作用

--------------------

## 一、索引概念 ##

索引是一种与表相关的可选数据对象。通过在表中的一个或多个列上创建索引，就能够为数据的检索提供快捷的存取路径，减少查询时所需的磁盘I/O操作，加快数据的检索速度。  
索引是将创建列的键值和对应记录的物理记录号(ROWID)排序后存储起来，需要占用额外的存储空间来存放。由于索引占用的空间远小于表所占用的实际空间，在系统通过索引进行数据检索时，可先将索引调入内存，通过索引对记录进行定位，大大减少了磁盘/O操作次数，提高检索效率。一般而言，表中的记录数越多，索引带来的效率提高就越明显，所以在数据库系统中，索引是必不可少的数据对象之一。

## 二、索引分类及应用实例 ##

在Oracle数据库中，*常用的索引有B树索引、位图索引和函数索引*。

### 2.1B树索引 ###

B树索引是一种最常用的索引类型，它可以用于单列或多列。它的优点是查询速度快、支持范围查询和排序，适用于高基数（不重复值多）的列，如主键、唯一约束、外键、日期等。B-Tree索引将数据按照一定的顺序存储在树形结构中，每个节点都包含多个数据项和指向下一级节点的指针。常见的语句如下：

CREATE INDEX index_name ON table_name(column_name);

例如在一个订单表中，经常需要查询某个用户的订单。因此，可以为该表的用户ID列创建一个B树索引，以加速查询。

CREATE INDEX order_user_id_idx ON orders(user_id);

### 2.2位图索引 ###

位图索引是一种特殊的索引类型，它适用于低基数（重复值多）的列，如性别、状态等。它的优点是查询速度快、存储空间小，但不支持范围查询。Bitmap索引将每个不同的值都映射到一个位图中，每个位代表一个行的存在或不存在。当查询条件中包含多个列时，可以使用位图索引合并技术，将多个位图合并为一个位图，从而提高查询效率。常见的语句如下：

CREATE BITMAP INDEX index_name ON table_name(column_name);

假设有一个名为"sales"的表格，其中包括唯一的"product\_id"列和"region"列。可以使用如下SQL语句创建一个基于"region"列的位图索引：

CREATE BITMAP INDEX sales_region_idx ON sales(region);

这将在"sales"表格上创建一个位图索引，它将包含所有"region"列值对应的行号列表。然后我可以使用位图索引来实现对"sales"表格的快速查询，例如，查找在某个特定地区内销售的产品数量：

SELECT COUNT(*) FROM sales WHERE region = 'North America';

查询优化器在执行此查询时会将位图索引作为候选方案之一，如果确定它是最优选择，则使用位图索引进行搜索。当使用位图索引进行搜索时，Oracle会将位表示成二进制格式，并进行逐位比较，以确定哪些行与条件匹配。

> **注意：**
> 
> 由于位图索引必须在许多行上进行比较，因此在高并发环境下，可能会对数据库的性能和响应时间产生影响。

### 2.3函数索引 ###

函数索引是一种特殊的索引类型，它适用于对列进行函数操作的查询，如TO\_CHAR、UPPER等。它的优点是可以加速带有函数的查询，但是在索引列上使用函数会降低索引的效率。常见的语句如下：

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

例如，创建一个基于 "UPPER" 函数的索引：

CREATE INDEX upper_last_name_idx ON employees(UPPER(last_name));

假如在一个客户表中，经常需要查询客户名和地址，而这些列都是VARCHAR2类型的。为了加速查询，就可以为这两个列创建函数索引，将其转换为大写字。

CREATE INDEX idx_upper_customer ON customer (UPPER(name), UPPER(address));

> **在实际应用中也会使用其他的索引，它们的含义和使用方法如下：**

### 2.4普通索引 ###

普通索引是最基本和最常见的索引类型，以某个或多个列作为键建立，用于加速查询。它可以用于单列或多列，可以包含重复值。因为普通索引没有任何附加约束条件，所以可以在数据库中创建更多这样的索引。使用CREATE INDEX语句来创建普通索引，例如：

CREATE INDEX idx_last_name ON employees(last_name);

普通索引的使用方法：

SELECT *
    FROM employees
    WHERE last_name = 'Smith';

我在employees表中的last\_name列创建了普通索引。当查询特定姓氏的员工时，可以使用该索引来加速查找。

例如，创建一个基于 "last\_name" 列的普通索引：

CREATE INDEX last_name_idx ON employees(last_name);

### 2.5唯一索引 ###

唯一索引是一种限制索引类型，用于保证列的唯一性。它可以用于单列或多列，不允许包含重复值。由于包含多列的值，因此整个组合可以重复。唯一组合索引通常用于识别数据库表（或视图）中的特定行。使用CREATE UNIQUE INDEX语句来创建唯一索引，例如：

CREATE UNIQUE INDEX idx_employee_id ON employees(employee_id);

唯一索引的使用方法：

SELECT *
    FROM employees
    WHERE employee_id = 1001;

我在employees表中的employee\_id列创建了唯一索引。当查询特定员工时，可以使用该索引来加速查找，并且保证结果唯一。

> 备注：
> 
> 还有一种唯一组合索引（Unique Composite Index）：
> 
> 这种索引上的值必须唯一，但是由于包含多列的值，因此整个组合可以重复。唯一组合索引通常用于识别数据库表（或视图）中的特定行。

例如，创建一个基于"employee\_id"和 "department\_id"列的唯一组合索引：

CREATE UNIQUE INDEX emp_dept_idx on employees(employee_id, department_id);

### 2.6主键索引 ###

主键索引是一种特殊的唯一索引类型，用于标识表中的主键列。主键索引可以用于单列或多列（可以包含单个列或多个列作为其组合键），不允许包含重复值。因为主键也限制为非空且不能更改，所以对于具有活动事务的大型表，使用主键索引比使用唯一索引进行经常性的检查操作要有效得多。使用ALTER TABLE语句来添加主键索引，例如：

ALTER TABLE employees ADD CONSTRAINT pk_employee_id PRIMARY KEY (employee_id);

例如，创建一个基于 "employee\_id" 列的主键索引：

ALTER TABLE employees ADD CONSTRAINT emp_pk PRIMARY KEY (employee_id);

### 2.7聚集索引 ###

聚集索引是一种特殊的索引类型，用于按照表的物理顺序存储行数据。每个表只能有一个聚集索引，通常与主键索引相同。它适用于经常需要按照某个列进行查询的表。

使用CREATE CLUSTERED INDEX语句来创建聚集索引，例如：

CREATE CLUSTERED INDEX idx_employee_id ON employees(employee_id);

举个例子，假设有一个包含大量租车记录的表格。每条租车记录包含了租车者的ID、起始时间和结束时间，以及租赁汽车的ID。可以构建一个名为 "rentals" 的表格来表示这些租车记录：

CREATE TABLE rentals (
      id NUMBER PRIMARY KEY,
      start_date DATE NOT NULL,
      end_date DATE NOT NULL,
      car_id NUMBER REFERENCES cars(id),
      renter_id NUMBER REFERENCES renters(id)
    );

现在可以使用一个聚集索引来优化该表格的查询效率。例如，可以按照租赁开始时间来排序租车记录，从而加速按照时间段进行查询。因此，我可以创建以下聚集索引：

CREATE CLUSTERED INDEX rentals_by_start_date ON rentals (start_date);

当按照 `start_date` 进行查询时，数据库管理系统可以更快地扫描数据页，从而提高查询效率。

> **注意：**
> 
> 由于聚集索引直接操作存储在磁盘上的数据，因此在新增、更新或删除数据时可能会影响到磁盘上的物理排列顺序，因此应该仔细考虑何时创建聚集索引。
> 
> **那么何时可以创建聚集索引呢？**
> 
> 聚集索引（Clustered Index）只适用于堆表与索引组织表。
> 
> 对于堆表（Heap-organized Table），它是一个没有聚集索引的表格类型。创建聚集索引的唯一方法是使用 `CREATE CLUSTER` 语句创建簇（Cluster），并将堆表之间相关联的行分配到同一个簇中。聚集索引因此自然而然地形成，以聚集的方式存储行数据，这些聚集的行数据按照特定顺序排列。
> 
> 对于索引组织表（Index-organized Table），每个表行都唯一对应于一个主键值，并且采用 B-tree 或类似结构来管理它们。在这种情况下，Oracle会自动为每个索引组织表建立一个基于主键的聚集索引，因为索引组织表本身就是通过主键进行排序的。
> 
> *在许多情况下，并不需要手动创建聚集索引，因为 Oracle 数据库系统会自动为许多索引和约束默认创建适当的聚集索引。但是，在某些情况下可能需要手动创建额外的聚集索引（例如对于大型表或复杂查询的性能优化）。*

### 2.8全文索引 ###

全文索引是一种用于文本数据的索引类型，用于加速全文搜索。它可以包含多个单词，并支持模糊搜索和语言特定的搜索。使用CREATE INDEX语句来创建全文索引，例如：

CREATE INDEX idx_job_history ON employees(job_history) INDEXTYPE IS CTXSYS.CONTEXT;

例如，可以使用全文索引搜索包含某个关键字的博客文章或新闻文章：

首先创建一个包含博客文章或新闻文章内容的表格，例如：

CREATE TABLE articles (
      id NUMBER PRIMARY KEY,
      title VARCHAR2(100),
      content CLOB
    );

然后，使用 `CTXSYS.CONTEXT` 包来创建全文索引。例如，可以创建一个称为 "idx\_articles" 的全文索引，用于 "articles" 表格的 "content" 列：

CREATE INDEX idx_articles ON articles(content) INDEXTYPE IS CTXSYS.CONTEXT;

现在就可以根据关键字执行全文搜索了。例如，要查找包含“Oracle”关键字的文章，可以执行类似于以下示例的SQL查询：

SELECT id, title 
    FROM articles 
    WHERE CONTAINS(content, 'Oracle', 1) > 0;

`CONTAINS` 函数接受三个参数：要进行搜索的列、搜索字符串以及开始位置（这里指从哪一行开始搜索）。搜索结果是包含了关键字的文章的 `id` 和 `title`。

> **注意：**
> 
> 在执行全文搜索之前，需要确保已经安装了 Oracle Text 组件，并启用了适当的语言和过滤器设置。此外，对于较大的数据集，全文索引可能需要相当长的时间来构建，因此建议使用增量索引构建策略。

## 三、索引创建 ##

在索引分类章节的实例中其实就已经用到了索引创建，那么创建索引可以使用CREATE INDEX语句。以下是创建索引的语法格式：

CREATE [UNIQUE] [BITMAP | FUNCTION-BASED | DOMAIN] 
    INDEX index_name 
    ON table_name (column_name[ASC | DESC] [, column_name[ASC | DESC]]...)
                   [TABLESPACE tablespace_name];

*  UNIQUE：可选关键字，指定该索是否为唯一索引。
 *  BITMAP：可选关键字，指定该索引是否为位图索引。
 *  FUNCTION-BASED：可选关键字，指定该索引是否为函数-Based索引。
 *  DOMAIN：可选关键字，指定该索引是否为域索引。
 *  index\_name：索引的名称。
 *  table\_name：被索引的表的名称。
 *  column\_name：要作为索引键的列名。
 *  ASC：表示升序。
 *  DESC：表示降序。
 *  **`TABLESPACE tablespace_name`**可选项：指定索引所存储数据的表空间名。如果未指定，则使用默认表空间。当数据库中存在多个表空间时，该选项特别有用。

例如，创建一个名为emp\_salary\_idx的索引，基于employees表的salary和hire\_date两个列：

CREATE INDEX emp_salary_idx ON employees (salary, hire_date);

我创建一个非唯一索引，使用了employees表的salary和hire\_date两个列作为索引键，并将其命名为emp\_salary\_idx。

例如，在 **`employees`**表中创建员工姓名 (last\_name) 的索引：

CREATE INDEX employees_last_name_idx ON employees (last_name);

## 四、索引修改 ##

修改索引通常意味着增加或删除索引键列、更改索引存储选项或重命名索引。要修改索引，可以使用ALTER INDEX语句。以下是修改索引的语法格式：

ALTER INDEX index_name [RENAME TO new_index_name] 
                        [TABLESPACE tablespace_name];

以下是常用的一些修改索引的语法：

-- 重命名索引
    ALTER INDEX index_name RENAME TO new_index_name;
    
    -- 重建索引
    ALTER INDEX index_name REBUILD; 
    
    -- 修改索引列的数据类型
    ALTER INDEX index_name MODIFY (column_name new_data_type);
    
    -- 修改索引表空间
    ALTER INDEX index_name REBUILD TABLESPACE new_tablespace_name;
    
    -- 增加索引键列
    ALTER INDEX index_name ADD (column_name);
    
    -- 删除索引键列
    ALTER INDEX index_name DROP (column_name);

其中：

*  index\_name：要修改的索引的名称。
 *  new\_index\_name：新的索引名称（可选）。
 *  new\_tablespace\_name：索引的新表空间（仅当需要移动索引的存储位置时）。
 *  column\_name：要添加或删除的新列名。

例如，将名为emp\_salary\_idx的索引添加了一个新的列job\_title:

ALTER INDEX emp_salary_idx ADD (job_title);

这将向现有的索引emp\_salary\_idx中添加一个新的键列job\_title。

例如，将名为 **`idx_emp_last_name`** 的索引重命名为 **`employees_last_name_idx`，**并将其存储到 **`new_tablespace`** 表空间中，语句如下：

ALTER INDEX idx_emp_last_name 
    RENAME TO employees_last_name_idx TABLESPACE new_tablespace;

> **注意：**
> 
> 修改索引可能会影响数据库的性能和可用性，在进行此类操作之前，请务必进行全面的测试和评估。

## 五、索引删除 ##

删除索引可以使用DROP INDEX语句。以下是删除索引的语法格式：

DROP INDEX index_name [FORCE];

其中：

*  **`index_name`** ：是要删除的索引的名称。如果存在具有相同名称的其他对象（如程序包、表格等），则必须在索引名称前加上所有者前缀。
 *  **`FORCE` ：**为可选项，它告诉Oracle忽略与该索引相关联的任何约束和依赖关系，并立即删除索引。如果省略了此选项，则Oracle将检查并拒绝删除任何具有依赖关系的索引。

例如，删除名为emp\_salary\_idx的索引：

DROP INDEX emp_salary_idx;

例如，删除名为 **`employees_last_name_idx`**的索引：

DROP INDEX employees_last_name_idx;

> **注意：**
> 
> 删除索引可能会影响数据库性能和可用性。在进行此操作之前，请确定该索引是否不再需要。除非允许，否则用户需要拥有适当的权限才能删除索引。删除索引将导致相关表的查询速度变慢，因此请谨慎操作。

## 六、索引作用 ##

在Oracle数据库中，索引是一种数据结构，用于加快查询操作的速度。索引是建立在表或视图上的对象，可以对一个或多个表列进行排序，并提供快速访问该表的能力。

因此索引主要用于优化查询操作性能。当一个表包含大量的数据时，查询整个表可能会非常缓慢，因此可以创建一个或多个索引来提高查询性能。

通过使用索引，可以让查询得到快速执行，因为它们允许系统跳过扫描整个表而只搜索分析的那部分数据。如果查询可以利用索引，则通常会比不使用索引时更快地响应查询。

具体来说，索引能够做到以下几点：

*  1. 提高检索效率：索引可以减少需要扫描的数据量，缩小了扫描范围，加快了查询速度。
 *  2. 加快数据排序：索引将关键字存储在文件系统中，可以对其进行排序，并且在 SQL 执行时，这些排序好的值可被迅速输出。
 *  3. 提高数据完整性：由于索引都是基于表约束创建的，因此它们可以帮助保证数据的一致性和完整性。
 *  4. 优化查询计划：通过正确的索引设计，可以使查询优化器更容易选择最佳的执行计划，从而提高执行效率。

> **注意：**
> 
> 虽然索引对数据库的性能有很大的帮助，但过多、不恰当的索引也可能会产生反作用，导致性能下降。因此，在建立索引时需要根据具体情况仔细权衡，比如需要权衡查询效率和存储成本，避免过度索引导致性能下降，确保其适当。此外，索引也会影响数据的插入、更新和删除操作的性能，因此需要根据具体情况进行优化。