文章详情页_第一章索引概述

索引是一种可以选择创建的数据库对象，它主要用于提高查询性能。

与书的索引类似。数据库索引把用户感兴趣的列值连同其行标识符（ROWID）存储在一起，ROWID 包含了存储列值的表行在磁盘上的物理位置。有了 ROWID，Oracle 就可以通过最少量的磁盘读取，有效地检索表中的数据。采用这种方式，索引的功能就像表中数据的快捷方式。如果没有可用的索引，那么 Oracle 就必须读取表中的每一行，才能确定该行是否包含所需的信息。

索引是有代价的，它们会消耗磁盘空间、CPU和内存资源，在列值被修改的同时也必须更新相应的索引。因此，创建十分糟糕的索引不仅会浪费磁盘空间，并且会过度消耗系统资源，也会导致数据库的性能下降。由于这些原因，在设计和构建基于 Oracle 数据库的应用程序时，必须从极专业的角度考虑索引策略。

用索引提高性能

要了解索引是如何提高性能的，首先要知道索引存储了两种类型的信息：表中的列值和相应的 ROWID 。在数据库中，ROWID 可唯一标识一行数据（对于堆组织表），并包含其物理位置（数据文件和数据块内行的位置）。创建了索引并执行后续查询后，查询优化器判断该索引是否能减少返回查询结果所需的资源量。

注： ROWID 唯一标识堆组织表中的一行。然而，对于聚簇表，有可能出现这种情况：不同表中的行位于同一数据块内，并具有相同的 ROWID。

确定使用哪些类型的索引

Oracle 提供了丰富的索引类型和功能。正确地使用索引可以产生良好的性能和可伸缩的数据库应用程序。

索引的类型和功能说明

序号	索引类型	用途
1	B树索引	默认的索引类型，适用于高基数（不同值的程度高）的列。除非有特殊原因需要使用不同的索引类型或功能，否则用正常的B树索引即可
2	索引组织表	当主键包含大多数的列值时很有效率，访问这种索引就像访问表一样。数据存储在一个类似B树的结构中
3	唯一索引	B树索引的一种形式，用于强制执行列值的唯一性。经常与主键和唯一键约束一起使用，但也可以独立于约束单独创建
4	反向键索引	B树索引的一种形式，在索引有许多顺序插入的情况下，用于平衡I/O
5	键压缩索引	适用于前导列经常重复的组合索引，压缩叶块条目。此功能适用于B树索引或IOT（索引组织表）索引
6	降序索引	B树索引的一种形式，在索引对应的列值按降序排序时使用。反向键索引不能指定降序，如果是位图索引，那么Oracle忽略降序
7	位图索引	对于包含低基数列以及在SQL语句的WHERE子句中使用许多AND和OR运算符的数据仓库环境，非常适合使用这种索引
8	位图连接索引	在数据仓库环境中，对于利用连接事实表和维度表的星型模式结构的查询非常有用
9	基于函数的索引	适用于应用了SQL函数的列，可与B树索引类型或位图索引类型结合使用
10	虚拟列索引	在表的虚拟列上定义的索引。适用于应用了SQL函数的列，可用来替代基于函数的索引
11	虚拟索引	允许通过CRETAE INDEX的NOSEGMENT子句创建没有物理段或区的索引，在调优SQL时有用，因无需建立物理索引从而避免消耗资源。任何类型的索引都可以创建为虚拟索引
12	不可见索引	该索引对查询优化器是不可见的。然而，在表中的数据被修改的同时也维护索引结构。用于在使索引对应用程序可见之前测试它。任何类型的索引都可以创建为不可见索引
13	全局分区索引	跨分区表的所有分区或常规表的全局索引。它的类型可以是B树索引，而不能是位图索引
14	本地分区索引	本地索引基于分区表的单个分区。它的类型可以是B树索引或位图索引
15	域索引	用于具体的应用程序或程序模块
16	B树聚簇索引	用于聚簇表
17	散列聚簇索引	用于散列聚簇

B树索引

Oracle 默认的索引类型就是B树索引。对于高基数的列值，该索引类型是非常有效的。

B树索引有多个子类型：

索引组织表
唯一索引
反向键索引
键压缩索引
降序索引

索引组织表

索引组织表（IOT）在一个B树索引结构中存储表行的全部内容。使用索引组织表，能缩短具有精确匹配和主键范围搜索的查询时间。

SQL> create table student (
  s_id    number,
  s_name  varchar2(20),
  constraint pk_student primary key (s_id, s_name)
) organization index;

唯一索引

当创建B树索引时，可以定义它是唯一索引。在这方面，它就像唯一约束。当插入数据到相应的表时，唯一索引将保证插入到表中的非空值都是不同的。出于这个原因，唯一索引通常与主键和唯一约束联合使用。

SQL> create table symbol (
  s_code    varchar2(20),
  s_name    varchar2(20),
  s_market  varchar2(20)
);

SQL> create unique index uidx_symbol on symbol(s_code, s_market);

反向键索引

反向键索引对于平衡有大量顺序插入的索引的 I/O 是非常有用的。在需要一种方式均匀地分布索引数据，以避免将相似的值聚集在一起时，这种索引表现更好。因此，在大量插入顺序值时，如果使用反向键索引，就可以避免 I/O 集中在索引内的某个物理磁盘位置。

反向键索引使用 REVERSE 子句指定，如下所示：

SQL> create index ridx_symbol on symbol(s_code) reverse;

注：不能对位图索引或索引组织表指定 REVERSE 子句。

键压缩索引

键压缩索引有助于减少前导列经常重复的组合索引的存储和 I/O 要求。使用 compress N 子句创建压缩的索引。

SQL> create index idx_symbol_1 on symbol(s_code, s_market) compress 2;

注：不能在位图索引上创建键压缩索引。

降序索引

默认情况下，Oracle 用升序方式存储B树索引。例如，如果在一个列值为数值型数据的列上创建索引，最小的数值将首先出现在索引（最左边的叶节点）中，而最大的数值将被存储在最右边的叶节点上。

通过对一列指定 DESC 关键字可以指示 Oracle 反转这种顺序为降序，这将创建降序索引。

SQL> create index idx_student_1 on student(s_id desc);

降序索引对于某些列以升序排序而另一些列以降序排序的查询是有用的。

特定的索引类型

有时B树索引对于提供所需的性能改善是不够的的。以下是在特定的情况下应使用的索引类型：

位图索引
位图连接索引
基于函数的索引
虚拟列索引
虚拟索引
不可见索引
全局分区索引
本地分区索引
域索引
B树聚簇索引
散列聚簇索引

位图索引

位图索引通常用在数据仓库环境中，这些索引适用于具有相对较低数量不同值（低基数）的列。

对于 WHERE 子句中使用多个 AND 或 OR 连接操作的 SQL 语句，位图索引也是高效的。

在经常执行 INSERT、UPDATE、DELETE 等操作的 OLTP 数据库中不应该使用位图索引。这是因为位图索引的结构导致在许多单独的 DML 操作期间锁定多个行（从而导致高事务的 OLTP 系统中的锁定问题）。

使用关键字 BITMAP 创建位图索引。

SQL> create table sales (
  f_sales_amt    number,
  f_date_id      number,
  f_product_id   number,
  f_customer_id  number
);

SQL> create bitmap index bidx_sales on sales(f_date_id);

位图连接索引

位图连接索引在索引中存储两个表之间的连接结果。它们避免了检索结果时对表进行连接。在使用一个表的外键列和另一个表的主键列连接两个表时，适合用位图连接索引。

SQL> create bitmap index bidx_sales on sales (customers.c_cust_name)
from sales, customers 
where sales.f_customer_id = customers.f_customer_id;

基于函数的索引

基于函数的索引引用其定义中的 SQL 函数或表达式创建。基于函数的索引允许在被查询的 WHERE 子句中的 SQL 函数引用的列使用索引查找。

SQL> create index idx_customers_1 on customers(upper(c_cust_name));

虚拟列索引

一种代替基于函数的索引的方法是在表中添加一个虚拟列，然后为该虚拟列创建索引。

创建一个 INV 表，并在它上面创建一个虚拟列。

SQL> create table inv (
  f_inv_id   number,
  f_inv_count number,
  c_inv_status generated always as (
    case
      when f_inv_count <= 100 then 'GETTING_LOW'
      when f_inv_count > 100  then 'OKAY'
    end
  )
);

然后在虚拟列上创建常规索引

SQL> create index idx_inv on inv(c_inv_status);

虚拟索引

通过 NOSEGMENT 子句可以指示 Oracle 创建永远不会被使用的索引，并且不会将任何区分配给它的索引。

SQL> create index idx_customers_2 on customers(c_cust_name) nosegment;

尽管该索引没有实例化，但还是可以通过 _USE_NOSEGMENT_INDEXES 初始化参数指示 Oracle 来确定该索引是否可能被优化器使用。

SQL> alter session set "_use_nosegment_indexes" = true;

在什么情况下这个功能有用？如果你想创建一个非常大的索引，并且不分配空间，以便确定优化器是否会用到它，那就可以使用 NOSEGMENT 创建索引并进行测试。如果确定该索引有用，再删除该索引并使用不带 NOSEGMENT 子句的命令重新创建它。

不可见索引

不可见索引意味着优化器为查询语句检索数据时不使用该索引。然而，当对基础表插入、更新或删除记录时，数据库仍然在维护该索引结构。如果想在不影响现有应用程序代码的前提下，测试索引的可行性，就可以使用此功能。

使用 INVISIBLE 关键字即可创建不可见索引

SQL> create index idx_customers_3 on customers(c_cust_name) invisible;

全局分区索引

分区索引在逻辑上是一个索引，但实际上它是在几个不同的段中实现的。这能保证即使是非常大的数据库也能具有良好的性能。分区索引可以是全局的也可以是本地的。

全局分区索引是使用分区策略的索引，但这种分区并不映射到基础表的段。可以为常规表或分区表创建全局分区索引。全局分区索引实现为B树类型，并且可以定义为唯一索引。使用 GLOBAL PARITION 子句创建全局分区索引。

SQL> create index gidx_sales on sales(f_sales_amt)
global partition by range(f_sales_amt) 
(
  partition pg1 values less than (25),
  partition pg2 values less than (50),
  partition pg3 values less than (maxvalue) 
);

本地分区索引

本地分区索引必须建立在分区表上。这种索引与其基础表遵循相同的分区策略。本地分区索引只包含其相应的表分区中的值。

使用 LOCAL 关键字创建次类型的索引

SQL> create table sales (
  f_sales_amt    number,
  f_date_id      number,
  f_product_id   number,
  f_customer_id  number
)
partition by range(f_sales_amt)
(
  partition p1 values less than (25),
  partition p2 values less than (50),
  partition p3 values less than (maxvalue) 
);

SQL> create index idx_sales on sales(f_sales_amt) local;

域索引

应用程序域索引是针对一个特定的应用程序自定义的。这适用于自定义数据类型、文档、图像、视频和空间数据。

B树聚簇索引

B树聚簇索引是聚簇表键上定义的列，B树聚簇索引与一个数据库块地址想关联。该类型索引与聚簇表一同使用。

散列聚簇索引

散列聚簇索引也用于聚簇表，散列聚簇索引与B树聚簇索引的差异是，前者使用散列函数替代了索引键。

确定需要建立索引的列

对于大多数应用程序，建议在下列情况下创建索引：

为每个表定义主键约束：这导致在主键指定的列上自动创建索引
在要求唯一且不同与主键列上创建唯一键约束：每个唯一约束导致在约束中指定的列上自动创建一个索引
手动创建外键列上的索引：这是为了得到更好的性能，以避免某些特定的锁问题

主键列和唯一键列的索引

在大多数情况下，应该为每个表创建主键约束。如果没有为主键列定义索引，那么 Oracle 会自动为你创建一个索引。

同样，对于在表上定义的任何唯一约束，如果唯一键列上没有已定义的索引，Oracle 也将自动创建一个合适的B树索引。

SQL> create table employee(
  c_employee_no  number       primary key,
  c_first_name   varchar2(20),
  c_last_name    varchar2(20),
  c_id_card      varchar2(20) unique
);


SQL> select
  2    t.table_name,
  3    t.table_type,
  4    t.index_name,
  5    t.index_type,
  6    t.status
  7  from user_indexes t where t.table_name = 'EMPLOYEE';

TABLE_NAME      TABLE_TYPE  INDEX_NAME      INDEX_TYPE STATUS
--------------- ----------- --------------- ---------- --------
EMPLOYEE        TABLE       SYS_C0012990    NORMAL     VALID
EMPLOYEE        TABLE       SYS_C0012991    NORMAL     VALID

外键列的索引

Oracle 不会自动创建外键列上的索引。建议在外键列上创建B树索引的原因之一就是：外键列经常在 WHERE 子句中被引用，并因此可以改善这些查询的性能。

当外键列上存在索引时，可以避免或减少锁定问题。也就是说，当插入或删除子表中的记录时，将在父表上放置一个表级别的锁，该锁将阻止其它进程在父表中插入或删除记录。

SQL> create table customers (
  f_customer_id   number primary key,
  c_cust_name     varchar2(20)
);

SQL> create table address (
  f_address_id   number,
  f_customer_id  number  references customers(f_customer_id),
  c_address      varchar2(256)
);

SQL> create index idx_address on address(f_address_id);

其他适合创建索引的列

在选择创建什么样的索引时，需牢记这一基本原则：根据查询表时使用的列制定索引策略。既可以在一个表上创建多个索引，也可以创建一个包含多个列的索引。

符合以下条件的列，通常可以考虑为其创建索引：

为经常用作 WHERE 子句中谓词的列创建索引，如果 WHERE 子句中使用表的多个列，可以考虑使用组合索引
为在 SELECT 子句中使用的列创建符合索引
考虑为在 ORDER BY、GROUP BY、UNION 或者 DISTINCT 子句中使用的列创建索引

每个表上有多个索引没有关系。但是，在一个表上建立的索引越多，DML 语句就会运行得越慢（因为表列值发生变化时，Oracle 有越来越多的索引需要维护）。

不要在表中随机添加索引，直到偶然发现索引列的正确组合，这是一个陷阱。相反，需要在生产环境创建索引前，验证它的性能。

索引指南

利用 Oracle 索引有助于高效访问大型数据集。只有确定了 SELECT 语句在性能上的改进与索引所消耗的空间成本和更新表时的开销相比是否值得，才能确定是否使用该索引。

序号	指南	论证
1	创建所需数量的索引，但尽量减少创建索引，明智的添加索引。首先要测试以确定可量化的性能收益	索引提高性能，但也消耗磁盘空间和处理资源。不要添加不必要的索引
2	对表执行的查询所需的性能，应该成为制定索引策略的基础	为在 SQL 查询中使用的列创建索引将最大限度的提升性能
3	考虑使用 SQL 调优顾问或 SQL 访问顾问获得索引的建议	这些工具提供了建议和第二双索引决策的眼睛
4	为所有表创建主键约束	这将自动创建一个B树索引（如果在主键列上还没有建立索引）
5	在合适的地方创建唯一键约束	这将自动创建一个B树索引（如果在唯一键列上还没有建立索引）
6	为包含外键的列创建索引	连接表时，外键列通常包含在 WHERE 子句中，从而能提高 SELECT 语句的性能。同时还可以减少更新和插入子表时的锁定问题
7	小心的选择小表的索引	即使对于小表，有时候访问索引也可能比全表扫描性能更好
8	使用正确的索引类型	正确使用索引能最大限度地提高性能
9	如果不能证明使用不同类型索引可获得性能增益，那就使用基本的B树索引类型	对于大多数具有高基数列值的应用程序，都适合使用B树索引
10	数据仓库环境中，考虑使用位图索引	对不经常更新的低基数列值来说，使用这些索引是理想选择
11	考虑为索引使用单独的表空间	表和索引数据可能有不同的存储、备份和恢复要求，单独的表空间，可以把索引和表分开管理
12	让索引从表空间继承它的存储属性	这使得它更易于管理和维护索引的存储
13	使用一致的命名标准	这使得维护和故障排除更容易
14	不要重建索引，除非有充分的理由这样做	重建索引通常是不必要的，除非索引损坏或需要把索引移动到不同的表空间
15	监测索引，并删除不被使用的索引	这样做能释放物理空间，并提高 DML 语句的性能
16	删除索引之前，考虑把它标记为不可用或不可见	这使得你在删除索引之前，可以更好的确定是否有任何性能问题

小结

索引的存在主要是为了提供查询性能，因此仔细考虑如何实现索引是至关重要的。精心设计的索引策略将会使数据库应用程序的性能优异，相反，欠考虑的索引设计将导致性能不佳。

索引占用磁盘空间，并与表分开存放。

建议在主键列、唯一键列和外键列上创建索引。然后，对执行缓慢的 SQL 语句进行分析，观察会用到哪些列，并制定相应的索引策略。

索引决策流程图

参考资料

《Oracle 索引技术》

原创文章，转载请注明出处：http://www.opcoder.cn/article/34/

第一章 索引概述