oracle索引（包括复合索引）

在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。 

对于数据库来说，索引是一个必选项，但对于现在的各种大型数据库来说，索引可以大大提高数据库的性能，以至于它变成了数据库不可缺少的一部分。

 

索引特点： 

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。 

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。 

 

 

索引不足：

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 

 

 

应该建索引列的特点：

1）在经常需要搜索的列上，可以加快搜索的速度； 

2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构； 

3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度； 

4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 

5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间； 

6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。 

创建表：

S

SQL> create table dex (id int,sex char(1),name char(10));Table created.

向表中插入1000条数据

SQL> begin

   2   for i in 1..1000

   3   loop

   4   insert into dex values(i,'M','chongshi');

   5   end loop;

   6   commit;

   7   end;

   8   /

P

PL/SQL procedure successfully completed.

查看表记录

S

SQL> select * from dex;

 

            ID SE NAME

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

... . .....

991 M   chongshi

 

           992 M   chongshi

 

           993 M   chongshi

 

           994 M   chongshi

 

           995 M   chongshi

 

           996 M   chongshi

 

           997 M   chongshi

 

           998 M   chongshi

 

           999 M   chongshi

 

         1000 M   chongshi

1000 rows selected.

0.249    0.015

复合索引

概要

什么是单一索引,什么又是复合索引呢? 何时新建复合索引，复合索引又需要注意些什么呢？本篇文章主要是对网上一些讨论的总结。

一.概念

单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上。

用户可以在多个列上建立索引，这种索引叫做复合索引(组合索引)。复合索引的创建方法与创建单一索引的方法完全一样。但复合索引在数据库操作期间所需的开销更小，可以代替多个单一索引。当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度。

同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引,如果不特殊说明的话一般是指单一索引。宽索引也就是索引列超过2列的索引。

设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。拥有更多的窄索引，将给优化程序提供更多的选择余地，这通常有助于提高性能。

二.使用

创建索引 

create index idx1 on table1(col1,col2,col3)  

查询

select * from table1 where col1= A and col2= B and col3 = C

这时候查询优化器，不在扫描表了，而是直接的从索引中拿数据，因为索引中有这些数据，这叫覆盖式查询，这样的查询速度非常快。    

三.注意事项

1.何时是用复合索引

在where条件中字段用索引，如果用多字段就用复合索引。一般在select的字段不要建什么索引(如果是要查询select col1 ,col2, col3 from mytable,就不需要上面的索引了)。根据where条件建索引是极其重要的一个原则。注意不要过多用索引，否则对表更新的效率有很大的影响，因为在操作表的时候要化大量时间花在创建索引中.

2.对于复合索引，在查询使用时，最好将条件顺序按找索引的顺序，这样效率最高。如：  

   IDX1:create    index    idx1    on    table1(col2,col3,col5)  

   select    *    from    table1    where    col2=A    and    col3=B    and    col5=D  

如果是"select    *    from    table1    where    col3=B    and    col2=A    and    col5=D"

或者是"select    *    from    table1    where    col3=B"将不会使用索引，或者效果不明显

3.复合索引会替代单一索引么?

很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗？带着这个问题，我们来看一下以下的查询速度（结果集都是25万条数据）：（日期列fariqi首先排在复合聚集索引的起始列，用户名neibuyonghu排在后列）

IDX1:create    index    idx1    on    Tgongwen(fariqi,neibuyonghu)  

（1）select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi>'2004-5-5'

查询速度：2513毫秒

（2）select gid,fariqi,neibuyonghu,title from Tgongwen

where fariqi>'2004-5-5' and neibuyonghu='办公室'

查询速度：2516毫秒

（3）select gid,fariqi,neibuyonghu,title from Tgongwen

where neibuyonghu='办公室'

查询速度：60280毫秒

从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。

[参考: 查询优化及分页算法方案 http://blog.csdn.net/chiefsailor/archive/2007/05/28/1628339.aspx]

4.需要在同一列上同时建单一索引和复合索引么?

试验: sysbase    5.0    表table1    字段：col1,col2,col3  

试验步骤：  

（1）建立索引idx1    on    col1  

   执行select    *    from    table1    where    col1=A       使用idx1  

   执行select    *    from    table1    where    col1=A    and    col2=B    也使用idx1  

（2）删除索引idx1,然后建立idx2    on    (col1,col2)复合索引  

   执行以上两个查询，也都使用idx2  

（3）如果两个索引idx1,idx2都存在  

   并不是    where    col1='A'用idx1；where    col1=A    and    col2=B   用idx2。  

   其查询优化器使用其中一个以前常用索引。要么都用idx1,要么都用idx2.  

    

由此可见，

（1）对一张表来说，如果有一个复合索引 on    (col1,col2)，就没有必要同时建立一个单索引 on col1。

（2）如果查询条件需要，可以在已有单索引 on col1的情况下，添加复合索引on    (col1,col2)，对于效率有一定的提高。

（3）同时建立多字段(包含5、6个字段)的复合索引没有特别多的好处，相对而言，建立多个窄字段(仅包含一个，或顶多2个字段)的索引可以达到更好的效率和灵活性。

5. 一定需要覆盖性查询么？

通常最好不要采用一个强调完全覆盖查询的策略。如果Select子句中的所有列都被一个非群集索引覆盖，优化程序会识别出这一点，并提供很好的性能。不过，这通常会导致索引过宽，并会过度依赖于优化程序使用该策略的可能性。通常，是用数量更多的窄索引，这对于大量查询来说可以提供更好的性能。