聚簇索引相关知识

为什么通常不在自增的主键上建立聚簇索引呢？

 索引主要是为了提高查询速度,聚簇索引是把某类具有相同值的按照物理顺序存储;

为什么通常不在自增的主键上建立聚簇索引呢？
这是因为:
1 一个表只能建一个聚簇索引,因此不能随便浪费;
2 建立聚簇索引,能够大幅度提高查询速度;
比如当你查询 adrress 全国30多个省自治区时,你要具体查询某个省的数据,这是,对 adrress 建立聚簇索引是比较恰当的;
比如你要在网页的首页上显示最新的新闻,那么你应该按照新闻时间来建聚簇索引;
3 通常自增主键仅仅是标志一行数据,通常我们查询的时候不会把它作为条件,而聚簇索引是非常宝贵的,因此一般都不要在自增主键上建聚簇索引
总结 建聚簇索引主要就是提高查询速度,因此应该把它用于最能够提高整体性能的地方

1 是的,建立索引的本质是解决查询效率的,如果一列值大量重复,比如你上面举的例子(男/女),这时候建立聚簇索引,对大多数查询都不起到作用,这时就不应该使用;
还有,比如我上面说的,有XX个地区的销售记录,假如经常需要按地区汇总,这时候就可以对地区字段建立聚簇索引; 但要排除一种情况,假如在这 XX 地区中,其中大部分地区的记录很少,而少数地区的记录很多,这时采用聚簇索引也不能提高多少效率,不如把 聚簇索引 留给其他字段;
另外一种情况是:你在某个程序需要大量重复某些过程,就上面举的网站问题: 我们经常需要在首页放置最新新闻,因为别人一打开,就要做查询,而且时间也是新闻的一个重要特征,我认为这时也应该采用聚簇索引

2 假如一开始你对其他非自增字段建立聚簇索引,这时如果你开始插入数据,这时自增字段已经是排序的了,对有序字段查找,采用2分法速度也是非常快的;

////////////////

标题 索引在数据库中的应用分析 选择自 hellenlong 的 Blog
关键字 索引在数据库中的应用分析
出处

索引是提高数据查询最有效的方法，也是最难全面掌握的技术，因为正确的索引可能使效率提高10000倍，而无效的索

引可能是浪费了数据库空间，甚至大大降低查询性能。


索引的管理成本

1、 存储索引的磁盘空间

2、 执行数据修改操作（INSERT、UPDATE、DELETE）产生的索引维护

3、 在数据处理时回需额外的回退空间。

实际数据修改测试：

一个表有字段A、B、C，同时进行插入10000行记录测试

在没有建索引时平均完成时间是2.9秒

在对A字段建索引后平均完成时间是6.7秒

在对A字段和B字段建索引后平均完成时间是10.3秒

在对A字段、B字段和C字段都建索引后平均完成时间是11.7秒

从以上测试结果可以明显看出索引对数据修改产生的影响

索引按存储方法分类

B*树索引

B*树索引是最常用的索引，其存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目

录，叶块相当于索引到的具体的书页。一般索引及唯一约束索引都使用B*树索引。

位图索引

位图索引储存主要用来节省空间，减少ORACLE对数据块的访问，它采用位图偏移方式来与表的行ID号对应，采用位图索

引一般是重复值太多的表字段。位图索引在实际密集型OLTP（数据事务处理）中用得比较少，因为OLTP会对表进行大量

的删除、修改、新建操作，ORACLE每次进行操作都会对要操作的数据块加锁，所以多人操作很容易产生数据块锁等待甚

至死锁现象。在OLAP（数据分析处理）中应用位图有优势，因为OLAP中大部分是对数据库的查询操作，而且一般采用数

据仓库技术，所以大量数据采用位图索引节省空间比较明显。

索引按功能分类

唯一索引

唯一索引有两个作用，一个是数据约束，一个是数据索引，其中数据约束主要用来保证数据的完整性，唯一索引产生的

索引记录中每一条记录都对应一个唯一的ROWID。

主关键字索引

主关键字索引产生的索引同唯一索引，只不过它是在数据库建立主关键字时系统自动建立的。

一般索引

一般索引不产生数据约束作用，其功能主要是对字段建立索引表，以提高数据查询速度。


索引按索引对象分类

单列索引（表单个字段的索引）

多列索引（表多个字段的索引）

函数索引（对字段进行函数运算的索引）

建立函数索引的方法：

create index 收费日期索引 on GC_DFSS(trunc(sk_rq))

create index 完全客户编号索引 on yhzl(qc_bh||kh_bh)

在对函数进行了索引后，如果当前会话要引用应设置当前会话的query_rewrite_enabled为TRUE。

alter session set query_rewrite_enabled=true

注：如果对用户函数进行索引的话，那用户函数应加上 deterministic参数，意思是函数在输入值固定的情况下返回值

也固定。例：
create or replace function trunc_add（input_date date）return date deterministic

as
begin
return trunc(input_date+1);

end trunc_add;

应用索引的扫描分类

INDEX UNIQUE SCAN（按索引唯一值扫描）

select * from zl_yhjbqk where hbs_bh='5420016000'

INDEX RANGE SCAN（按索引值范围扫描）

select * from zl_yhjbqk where hbs_bh>'5420016000'

select * from zl_yhjbqk where qc_bh>'7001'

INDEX FAST FULL SCAN（按索引值快速全部扫描）

select hbs_bh from zl_yhjbqk order by hbs_bh

select count(*) from zl_yhjbqk

select qc_bh from zl_yhjbqk group by qc_bh

什么情况下应该建立索引

表的主关键字

自动建立唯一索引

如zl_yhjbqk（用户基本情况）中的hbs_bh（户标识编号）

表的字段唯一约束

ORACLE利用索引来保证数据的完整性

如lc_hj（流程环节）中的lc_bh+hj_sx（流程编号+环节顺序）

直接条件查询的字段

在SQL中用于条件约束的字段

如zl_yhjbqk（用户基本情况）中的qc_bh（区册编号）

select * from zl_yhjbqk where qc_bh=’7001’

查询中与其它表关联的字段

字段常常建立了外键关系

如zl_ydcf（用电成份）中的jldb_bh（计量点表编号）

select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’
序的字段

排序的字段如果通过索引去访问那将大大提高排序速度

select * from zl_yhjbqk order by qc_bh（建立qc_bh索引）

select * from zl_yhjbqk where qc_bh='7001' order by cb_sx（建立qc_bh+cb_sx索引，注：只是一个索引，其中包

括qc_bh和cb_sx字段）

查询中统计或分组统计的字段

select max(hbs_bh) from zl_yhjbqk

select qc_bh,count(*) from zl_yhjbqk group by qc_bh

什么情况下应不建或少建索引

表记录太少

如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数

据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一

次读出，处理速度显然会比用索引快。

如表zl_sybm（使用部门）一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果

对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如：

select * from zl_sybm where sydw_bh='5401'（对sydw_bh建立索引不会产生性能优化）




经常插入、删除、修改的表

对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。

数据重复且分布平均的表字段

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一

般不会提高数据库的查询速度。

经常和主字段一块查询但主字段索引值比较多的表字段

如gc_dfss（电费实收）表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的

情况，如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收

费序号索引就已经将记录减少到只有几条，如果再按后面的几个字段索引查询将对性能不产生太大的影响。

如何只通过索引返回结果

一个索引一般包括单个或多个字段，如果能不访问表直接应用索引就返回结果那将大大提高数据库查询的性能。对比以

下三个SQL，其中对表zl_yhjbqk的hbs_bh和qc_bh字段建立了索引：

1 select hbs_bh,qc_bh,xh_bz from zl_yhjbqk where qc_bh=’7001’

执行路径:

SELECT STATEMENT, GOAL = CHOOSE 11 265 5565

TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 5565

INDEX RANGE SCAN DLYX 区册索引 1 265


平均执行时间（0.078秒）

2 select hbs_bh,qc_bh from zl_yhjbqk where qc_bh=’7001’

执行路径:

SELECT STATEMENT, GOAL = CHOOSE 11 265 3710

TABLE ACCESS BY INDEX ROWID DLYX ZL_YHJBQK 11 265 3710

INDEX RANGE SCAN DLYX 区册索引 1 265

平均执行时间（0.078秒）

3 select qc_bh from zl_yhjbqk where qc_bh=’7001’

执行路径:

SELECT STATEMENT, GOAL = CHOOSE 1 265 1060

INDEX RANGE SCAN DLYX 区册索引 1 265 1060


平均执行时间（0.062秒）


从执行结果可以看出第三条SQL的效率最高。执行路径可以看出第1、2条SQL都多执行了TABLE ACCESS BY INDEX ROWID（

通过ROWID访问表） 这个步骤，因为返回的结果列中包括当前使用索引(qc_bh)中未索引的列（hbs_bh,xh_bz），而第3

条SQL直接通过QC_BH返回了结果，这就是通过索引直接返回结果的方法。


如何重建索引

alter index 表电量结果表主键 rebuild


如何快速新建大数据量表的索引

如果一个表的记录达到100万以上的话，要对其中一个字段建索引可能要花很长的时间，甚至导致服务器数据库死机，因

为在建索引的时候ORACLE要将索引字段所有的内容取出并进行全面排序，数据量大的话可能导致服务器排序内存不足而

引用磁盘交换空间进行，这将严重影响服务器数据库的工作。解决方法是增大数据库启动初始化中的排序内存参数，如

果要进行大量的索引修改可以设置10M以上的排序内存（ORACLE缺省大小为64K），在索引建立完成后应将参数修改回来

，因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。

 

若要除去为实现   PRIMARY   KEY   或   UNIQUE   约束而创建的索引，必须除去约束  
   
  --创建测试环境  
  create   table   A  
  (  
      ID   int   unique   clustered,  
      name   varchar(20)  
  )  
   
   
  --删除索引  
  declare   @cname   varchar(20)  
  select   @cname=object_name(constid)   from   sysconstraints   where   id=object_id('A')  
  exec('alter   table   A   drop   constraint   '+@cname)  
   
  --创建索引  
  create   clustered   index   name_index   on   A(name)  
   
  --查看索引  
  select   *   from   sysindexes   where   id=object_id('A')  
   
  --删除测试环境  
  drop   table   A