索引在数据库中的应用分析

来源：作者：出处：巧巧读书 2005-11-29 进入讨论组

索引是提高数据查询最有效的方法，也是最难全面掌握的技术，因为正确的索引可能使效率提高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*树索引。

位图索引

位图索引储存主要用来节省空间，减少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

(完)

打开： http://www.qqread.com/database/v400108052.html