理解索引和聚簇——性能调整手册和参考

最新推荐文章于 2025-08-08 17:29:46 发布

转载最新推荐文章于 2025-08-08 17:29:46 发布 · 601 阅读

文章标签：

#存储 #oracle #table #数据仓库 #磁盘 #算法

database 专栏收录该内容

16 篇文章

订阅专栏

建立索引可以提高查询的性能，但是由于必须同时维护数据和索引，会增加DML操作的代价。因此，应该有选择性的建立索引。而对于被使用的索引应该及时清除。通过EXPLAIN PLAN可以查看一条SQL语句是否使用了索引，采用这种方法可以帮助选择并建立合适的索引。对于数据库中已经存在的索引，可以使用ALTER INDEX indexname MONITORING USAGE，经过一段时间的监视，如果一些索引一直没有使用，则可以从系统中清除掉。（注意控制好合适的监视周期）

在选择索引的列时应该考虑：

在WHERE语句中经常使用的列；

经常被用于连接到其他表的列；

B树索引的索引列应该具有较高的选择性；

不要在只包含少量不同值的列或表达式上建立B树索引；

不要索引经常修改的列；

如果一些列出现在WHERE语句中，但都是在函数或运算中使用，这些列不适合建立B树索引；

为了提高并发访问，外键列应该建立索引；

选择一个索引列时，应该从查询的性能收获和对于INSERT、UPDATE和DELETE操作的影响已经占用空间两个方面来进行权衡。

复合索引具有以下优点：

提高选择性：和单列索引相比，复合索引的选择性更高。

降低I/O：如果Oracle需要访问的所有列都存在于复合索引中，则可以根据索引直接返回结果，避免表扫描。

复合索引应该选择的列：

经常在WHERE语句中使用AND操作连接在一起使用，且组合起来比单个列具有更高的选择性的列。

如果多个查询包含相同列构成的结果集，可以考虑将这些列组合起来建立复合索引。

复合索引中确定列的顺序：

在WHERE语句中使用的列应该放到前面；

频繁出现在WHERE语句中的列应该放到复合索引的最前面，保证指定这些列的查询可以使用索引；

如果所有列的使用几率相差不多，把选择性高的列放到前面；

如果所有列的使用几率相差不多，而表的数据是按照某个列的键值顺序进行物理存储的，则将这一列放到最前面。

和一般索引相比，复合索引对DML的影响更大，而且占用磁盘空间也更多，因此应该慎重使用。

Oracle推荐按照如下方式建表、转载数据、建立索引并建立约束：

一：建立表和约束。NOT NULL约束可以不命名，并建立成ENABLE、VALIDATE状态，其他约束（PRIMARY KEY, FOREIGN KEY，UNIQUE和CHECK）都应该命名，并建立为DISABLE状态。

二：导入数据。

三：建立索引的索引。

后建索引比先建索引然后插入数据效率高。

四：将约束置为ENABLE、NOVALIDATE状态，先主键后外键。

这个过程会锁表，但是持续时间很短，操作完成后就可以保证后续插入数据的正确性了。

五：运行用户访问的修改表。

由于约束已经ENABLE，可以供用户使用了。

六：对每个约束进行VALIDATE操作，先主键后外键。

这一步花费时间较多，但是不会锁表，不影响用户访问，而且这一步可以并行执行。

下面介绍几种特殊类型的索引：

函数索引：将函数的表达式建立在索引中，使得对列的函数操作也可以使用索引。只有使用CBO才能利用函数索引。

索引组织表：其实是表的一种，表中的数据按照主键的顺序存储。适合对主键列的唯一扫描和范围扫描。如果包含较多DML，则不适合建立为索引组织表。

BITMAP索引：建立在选择性低的列上，对于多个列上的BITMAP索引的AND和OR操作具有很高的执行效率。BITMAP索引占用空间小，建立速度快，对批量操作只处理一次，因此特别合适在数据仓库中使用。但是BITMAP索引的锁粒度较大，大量的并发DML操作会极大的影响性能，因此不适合OLTP系统。BITMAP索引也只会被CBO使用。

BITMAP连接索引：将表连接操作通过BITMAP索引保存起来，当查询符合条件，则只需要访问BITMAP就可以得到正确的结构。特别适合于数据仓库的星形模式。

Domain索引：用户可以根据不同的使用情况自定制索引。

索引聚簇：将一张或多张表根据共同列（连接列）无论的存储在以前，这些存储在一起的表具有相同的物理属性。

索引聚簇把经常在一起访问的表存储在一起，这样查询的时候不必在执行连接操作，这些表在物理上已经连接在一起了。

索引聚簇适合对聚簇的多个表通过连接列进行查询，但是不适合对其中某个表进行全表扫描，因为这样将扫描更多的BLOCK。而且对索引聚簇进行DML的开销较大，因此包含较多DML操作的表，不适合进行索引聚簇。

哈希聚簇：通过哈希函数直接定位数据的物理地址。对于HASH列的定制访问，只需要一个I/O即可读出数据，效率很高。但是对HASH列不支持范围扫描，而且需要预先确定表的大小才能保证HASH聚簇表具有很高的效率，一旦表的大小超过了预期的估计，对HASH聚簇表的访问效率就会下降。

适合可以估计出大小的表，并且绝大多数查询都是通过HASH列进行等值查询。全表扫描效率较低，由于表在大小在创建时就确定了，因此当表中数据没有填满时，很多BLOCK没有填满，全表扫描要扫描很多空闲空间，效率比较低。HASH聚簇同样不适合大量DML操作，因为修改可能导致存储空间的位置发生变化。

1. 什么是聚簇

聚簇是根据码值找到数据的物理存储位置，从而达到快速检索数据的目的。聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。非聚簇索引的顺序与数据物理排列顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。一个表最多只能有一个聚簇索引。

2. 使用 Oracle 聚簇索引

聚簇是一种存储表的方法，这些表密切相关并经常一起连接进磁盘的同一区域。例如，表 BOOKSHELF 和BOOKSHELF_AUTHOR 数据行可以一起插入到称为簇（Cluster）的单个区域中，而不是将两个表放在磁盘上的不同扇区上。簇键（Cluster Key）可以是一列或多列，通过这些列可以将这些表在查询中连接起来（例如，BOOKSHELF表和BOOKSHELF_AUTHOR表中的 Title列）。为了将表聚集在一起，必须拥有这些将要聚集在一起的表。

下面是create cluster命令的基本格式：

create cluster (column datatype [, column datatype]...) [other options];

cluster的名字遵循表命名约定，column datatype是将作为簇键使用的名字和数据类型。column的名字可以与将要放进该簇中的表的一个列名相同，或者为其他有效名字。下面是一个例子：

create cluster BOOKandAUTHOR (Col1 VARCHAR2(100));

这样就建立了一个没有任何内容的簇（象给表分配了一块空间一样）。COL1的使用对于簇键是不相干的，不会再使用它。但是，它的定义应该与要增加的表的主键相符。接下来，建立包含在该簇中的表：

create table BOOKSHELF
            (Title VARCHAR2(100) primary key,
            Publisher VARCHAR2(20),
            CategoryName VARCHAR2(20),
            Rating VARCHAR2(2),
            constraint CATFK foreign key (CategoryName) references CATEGORY(CategoryName)
            )
            cluster BOOKandAUTHOR(Title);

在向BOOKSHELF表中插入数据行之前，必须建立一个聚簇索引：

create index BOOKandAUTHORndx on cluster BOOKandAUTHOR;

在上面的create table语句中，簇BOOKandAUTHOR(Title)子句放在表的列清单的闭括号的后面。BOOKandAUTHOR是前面建立的聚簇的名字。Title是将存储到聚簇Col1中的该表的列。create cluster语句中可能会有多个簇键，并且在created table语句中可能有多个列存储在这些键中。请注意，没有任何语句明确说明Title列进入到Col1中。这种匹配仅仅是通过位置做到的，即Col1和Title都是在它们各自的簇语句中提到的第一个对象。多个列和簇键是第一个与第一个匹配，第二个与第二个匹配，第三个与第三个匹配，等等。现在，添加第二个表到聚簇中：

create table BOOKSHELF_AUTHOR
            (Title VARCHAR2(100),
            AuthorName VARCHAR2(50),
            constraint TitleFK Foreign key (Title) references BOOKSHELF(Title),
            constraint AuthorNameFK Foreign key (AuthorName) references AUTHOR(AuthorName)
            )
            cluster BOOKandAUTHOR (Title);

当这两个表被聚在一起时，每个唯一的Title在簇中实际只存储一次。对于每个Title，都从这两个表中附加列。

来自这两个表的数据实际上存放在一个位置上，就好像簇是一个包含两个表中的所有数据的大表一样。

3. 散列聚簇

对于散列聚簇，它只有一个表。它通过散列算法求出存储行的物理存储位置，从而快速检索数据。创建散列聚簇时要指定码列的数据类型，数据行的大小及不同码值的个数。如果码值不是平均分布的，就可能有许多行存储到溢出块上，从而会降低查询该表的SQL语句的性能。

散列聚簇被用在总是通过主键查询数据的情况，例如要从表 T 查询数据并且查询语句总是是这样：

select * from T where id = :x;

这时散列聚簇是一个好的选择，因为不需要索引。Oracle 将通过散列算法得到值 :x 所对应的物理地址，从而直接取到数据。不用进行索引扫描，只通过散列值进行一次表访问。

散列聚簇语法示例：

CREATE CLUSTER personnel
            ( department_number NUMBER )
            SIZE 512 HASHKEYS 500
            STORAGE (INITIAL 100K NEXT 50K);
            CREATE CLUSTER personnel
            ( home_area_code NUMBER,
            home_prefix NUMBER )
            HASHKEYS 20
            HASH IS MOD(home_area_code + home_prefix, 101);
            CREATE CLUSTER personnel
            (deptno NUMBER)
            SIZE 512 SINGLE TABLE HASHKEYS 500;