索引在数据库中的应用分析
索引是提高数据查询最有效的方法,也是最难全面掌握的技术,因为正确的索引可能使效率提高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=’<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="7001" unitname="’">7001’</chmetcnv>
查询中与其它表关联的字段
字段常常建立了外键关系
如zl_ydcf(用电成份)中的jldb_bh(计量点表编号)
select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="540100214511" unitname="’">540100214511’</chmetcnv>
查询中排序的字段
排序的字段如果通过索引去访问那将大大提高排序速度
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=’<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="7001" unitname="’">7001’</chmetcnv>
执行路径:
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=’<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="7001" unitname="’">7001’</chmetcnv>
执行路径:
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=’<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="7001" unitname="’">7001’</chmetcnv>
执行路径:
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要将索引字段所有的内容取出并进行全面排序,数据量大的话可能导致服务器排序内存不足而引用磁盘交换空间进行,这将严重影响服务器数据库的工作。解决方法是增大数据库启动初始化中的排序内存参数,如果要进行大量的索引修改可以设置<chmetcnv w:st="on" tcsc="0" numbertype="1" negative="False" hasspace="False" sourcevalue="10" unitname="m"><span lang="EN-US">10M</span></chmetcnv>以上的排序内存(ORACLE缺省大小为64K),在索引建立完成后应将参数修改回来,因为在实际OLTP数据库应用中一般不会用到这么大的排序内存。
叶正盛
2003-08-15
分享到:
相关推荐
Oracle数据库中索引的维护,介绍关于索引的维护 oracle 版本 8.0
Oracle数据库索引的维护
oracle、sqlserver数据库批量删建索引,方便好用,提高数据库查询效率,提升系统运行效率,特别是数据量比较大的情况下
通过两个图形说明了在oracle数据库中b-tree索引和位图索引的工作原理
1.索引的创建与使用 2.创建索引的原则 3.索引的分类 4.创建索引的多种方法 5.管理索引 6.索引优化 7.查看、修改索引属性 8.修改索引名 9.删除索引
Oracle数据库中的索引管理技术.pdf
数据库索引
【完整课程列表】 oracle_系统表.pdf ...数据库开发 Oracle数据库 SQL开发教程 第16章 序列、索引、同义词(共34页).pdf 数据库开发 Oracle数据库 SQL开发教程 第17章 用户、权限和角色(共28页).pdf
oracle数据库中如何建立索引,建立索引有什么优势,索引该如何使用
自动生成表分析sql语句和索引分析语句: 表分析语句 analyTab.sql SELECT 'ANALYZE TABLE ZFMI.'||TABLE_NAME||' COMPUTE STATISTICS ;' FROM USER_TABLES; ----------------------------------------------...
Oracle中巧取指定记录 14 Oracle数据安全 15 Oracle字段上建立并使用索引 29 用Windows脚本宿主自动化Oracle工具 31 进程结构和内存结构 32 Oracle监控数据库性能的SQL汇总 36 Oracle如何精确计算row的大小 38 PL/...
导致索引失效: 1、表上频繁发生update,delete操作; 2、表上发生了alter table ..move操作(move操作导致了rowid变化)。
完整版Oracle11数据库教程课件 第8章_索引视图(共35页).ppt 完整版Oracle11数据库教程课件 第9章_PLSQL编程基础(共59页).ppt 完整版Oracle11数据库教程课件 第10章_存储过程_函数_触发器(共32页).ppt 完整版...
由浅入深地介绍了Oracle 10g系统的使用方法和基本管理。...本书全面介绍使用Oracle数据库管理应用与开发知识,适合作为普通高校计算机专业Oracle教材,也可以作为Oracle数据库开发和应用人员的参考资料。
详解Oracle数据库中文全文索引
【完整Oracle SQL开发教程课件如下】 ...数据库开发 Oracle数据库 SQL开发教程 第16章 序列、索引、同义词(共34页).pdf 数据库开发 Oracle数据库 SQL开发教程 第17章 用户、权限和角色(共28页).pdf
【完整Oracle SQL开发教程课件如下】 ...数据库开发 Oracle数据库 SQL开发教程 第16章 序列、索引、同义词(共34页).pdf 数据库开发 Oracle数据库 SQL开发教程 第17章 用户、权限和角色(共28页).pdf
Oracle 数据库的位图索引原理与应用.pdf
【完整Oracle SQL开发教程课件如下】 ...数据库开发 Oracle数据库 SQL开发教程 第16章 序列、索引、同义词(共34页).pdf 数据库开发 Oracle数据库 SQL开发教程 第17章 用户、权限和角色(共28页).pdf